Conditional formatting bar charts in Excel 2010 make it very easy to visualize the values in a range of cells. The longer the histogram, the larger the value.
To add a histogram, do the following:
- Select a range.
- On the Advanced tab Home (Home) press Conditional Formatting > Histograms (Conditional Formatting > Data Bars) and select a subtype. Result:
Explanation:
- The cell that contains the minimum value (0 if there are no negatives) is not filled.
- The cell containing the maximum value (95) is filled completely.
- All other cells are filled proportionally.
- Change the values. Result: Excel has automatically updated the histograms.
Read on to learn how you can customize histograms.
- Highlight a range A1: A10.
- On the Advanced tab Home (Home) select a team Conditional Formatting > Rule management (Conditional Formatting > Manage Rules).
- Нажмите кнопку Change the rule (Edit rule).Excel will open a dialog box Change a formatting rule (Edit Formatting Rule). Here you can further configure the following histogram options: Show column only (Show Bar Only), Minimum and maximum values (Minimum and Maximum), Column Appearance (Bar Appearance), Negative Values and the Axis (Negative Value and Axis), Column Direction (Bar Direction), etc.
Note: To open this dialog box for new rules, in the 2nd step of this instruction, click Other rules (More Rules).
- From drop down list Minimum (Minimum) select Number (Number) and enter the value 100. In the drop-down list Maximum (Maximum) select also Number (Number) and enter the value 150.
- Press ОК twice.Result:
Explanation:
- The cell that contains the value 100 (if any) is left blank because it is now the minimum value.
- The cell containing the value 150 (if any), the histogram fills completely.
- All other cells are filled proportionally.
Result: