logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Showing Data Bars as a ‘Proportion to Total’ in Excel

March 17, 2016 1 comment Article Easy, 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

[Excel 2010,2013,2016]

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

[ENTER]

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!

Related

Tags: 2013, 2016, Charts, Conditional, Excel, Formatting

1 comment

  • fuck you October 23, 2020 at 5:35 pm - Reply

    fuck this dumbass webstie does not help at all fuck you

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Showing a Picture Based on the Value of another Cell in Excel
Showing Data Bars as a 'Proportion to Total' in Excel
Populate a Dropdown Based on the Value of Another Dropdown in PowerApps
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Using the same Named Range in multiple worksheets, but with different values, in Excel
Creating Virtual Tables in Power BI Using DAX

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2021 - Theme by ThemeinProgress