Data Bars to show Percentage

WizardBrews

New Member
Joined
Jan 29, 2018
Messages
5
I have made a budget sheet on excel and I have a few columns per month. Category, Budget, Actual, Remaining. My question pertains to the Budget, Actual, And Remaining sections. Currently I have an Amount X in the budget column that remains constant. In the Actual column I have a formula that grabs amounts from underneath the budget section and puts it into the correct category and adds up all the amounts in that category. The Remaining section, I simply subtract the two. However I use data bars to show amount remaining as well to add some flair. But these data bars are kind of aesthetically useless because they are based on relative amounts currently. So my food budget, which is higher than my Misc budget is massive while the Misc is very small. I was wondering if there was a way that I could get the Bars to actually show the percentage Left of the budget in that category, while also still showing the dollar amount I have left in text. This would make all the bars even because there is 100% left but the text on the bar would say the dollar amount left. I hope this makes sense. Would appreciate advice on this.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi, welcome to the board.

It sounds like you want the length of the bars to be driven by the percentage, but the label on the bars to be driven by the absolute value, is that correct ?

If yes, this is do-able.

I just tried to work it through, and giving a full and precise answer is probably going to be difficult without understanding more about how your data is laid out, and what exactly you want to do.
Try this link - has a lot of good stuff on Excel charting.
https://peltiertech.com/Excel/Charts/ChartIndex.html
 
Upvote 0
Hi, welcome to the board.

It sounds like you want the length of the bars to be driven by the percentage, but the label on the bars to be driven by the absolute value, is that correct ?

If yes, this is do-able.

I just tried to work it through, and giving a full and precise answer is probably going to be difficult without understanding more about how your data is laid out, and what exactly you want to do.
Try this link - has a lot of good stuff on Excel charting.
https://peltiertech.com/Excel/Charts/ChartIndex.html

That is exactly what I am asking. I can give you an example of how its laid out.

A B C D
7 Category Budget Actual Spent Remaining
8 Car Payment $150 $150 -(Blank Data Bar)
9 Food $400 $350 $50 (Tiny Data Bar)
10 Brewing $100 $0 $100 (bigger bar but not full) I want this to be full though
11 Rent $400 $0 $400(Biggest so it is full)
 
Upvote 0
Sorry that did not post how I planned it to post

abcd
8CategoryBudgetActualRemaining
9Car payment1501500
10Food40035050
11Brewing1000100
12Rent4000400

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top