Data Bars Formatting Question

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.

I am essentially asking if you can have the length of the bars to be driven by the percentage, but the label on the bars to be driven by the absolute value?

Here is an example of my data set.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Category[/TD]
[TD]Budget[/TD]
[TD]Actual Spent[/TD]
[TD]Remaining[/TD]
[TD]notes[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Car Payment[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]0[/TD]
[TD](This data bar shows nothing[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Food[/TD]
[TD]400[/TD]
[TD]350[/TD]
[TD]50[/TD]
[TD]This data bar shows a little left[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Brewing[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]This shows a longer bar but isnt full (I'd like it to be full based on percentage but still show the $100)[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Rent[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]This is the longest and fullest bar because it is the most value but I want it to be the same as all other 100 percents but show $400[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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