Data Bars to show Percentage

[WizardBrews]

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.

 

[Gerald Higgins]

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

 

[WizardBrews]

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)

 

[WizardBrews]

Sorry that did not post how I planned it to post

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Category[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Remaining[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Car payment[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Food[/TD]
[TD]400[/TD]
[TD]350[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Brewing[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Rent[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]