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!

1 comment