Excel customize: YOUR OBJECTIVE
You have applied the Data Bar format to a column of your Excel sheet. Now you want to set a different color and hide the corresponding number values.
In this learning module “ Excel customize” you will learn how to customize the Data Bar format and what you have to pay attention to.
Excel customize: HOW DOES IT WORK?
You can customize the data bars anytime using the Conditional Formatting Rules Manager.
To customize the relevant rule, move your cursor in the data range for which this rule has been defined. Then click the button in the Styles group on the Home tab to open the following drop-down menu:
Excel customize: CAUTION
If you do not see any formatting rules, even though you have already created one, then your cursor has probably not been positioned within a data range for which a formatting rule has been defined. In this case, click the arrow button of the Show formatting rules for: drop-down menu and select the This Worksheet option.
Excel customize: HINT
You can also open a rule by double-clicking it to modify it.
This opens the Edit Formatting Rule dialog box:
The bottom half of the dialog box now displays some options to customize the selected rule:
The Data Bar formatting style has been defined for the current rule. Alternatively, you can also select the 2-color scale, 3-color scale or icon sets as formatting style.
Select this option to hide the number values and show only the data bars.
The shortest bar represents the lowest value by default. You can set Number, Percent, Formula or Quantile as lowest value with this option.
The longest bar represents the highest value by default. You can set Number, Percent, Formula or Quantile as highest value with this option.
The currently selected bar color is shown here. You can change it with the corresponding arrow button.
For instance, select the Show Bar Only option:
Additionally, you want to set a different bar color. Click the arrow button to do so:
A color palette opens then:
Select the color you want by clicking it with your mouse:
In the live preview you can immediately view how the change in color will affect the bars:
Close the Edit Formatting Rule dialog box by clicking the OK button.
Excel customize: This will take you back to the Conditional Formatting Rules Manager dialog box:
Close the dialog box by clicking the OK button.
This is what the conditional formatting will look like with the selected Show Bar Only option and the data bar color purple: