Showing Data Bars as a ‘Proportion to Total’ in Excel
Use this trick to show Excel Data Bars (the in-cell micro-chart) as a ‘percentage to total’ rather than relative to the max value
In Excel , Microsoft introduced some additional Conditional Formatting rules.
One of these, Data Bars, will fill/shade a cell based on its value, in relation to other cells in the group.
They are a useful way of visualising multiple records of data simply, without creating full charts.
By default, the range for your bars will be equal to the largest value.
That is to say, you will always have 1 bar where the cell is fully shaded.
In some cases, this may be confusing, and suggest a ‘100%’ value.
What if you want to represent the values where the size of the shading represents that value’s proportion to the total?
Thankfully, whilst not obvious, this is fairly straightforward to achieve.
Once you have setup your data bar formatting, select the entire data bar range again
On the HOME tab, Conditional Formatting à Manage Rules
Ensuring your rule is selected, EDIT RULE
Under the ‘Maximum’ value, change the Type to Formula
Still under ‘Maximum’, select the Range Picker icon attached to ‘Value’
Enter the formula ‘=SUM(‘ (No closing Bracket)
Select your value range, and add the closing bracket
You should look something like this…
Press ‘OK’, ‘OK’ to save and come out
You’re Data Bars should now be ‘proportion to total’ in nature. I’ve added background shading for illustrative purposes
And that’s it!