Excel customize: Customizing the data bar format

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:

Click the  command to open the Conditional Formatting Rules Manager dialog box:

 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.


.
You will now see all rules of the current spreadsheet, regardless of whether the cursor is inside the corresponding data range or not.

Excel customize:

To customize a rule in the Conditional Formatting Rules Manager dialog box, click the corresponding rule first and then click the    button:

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:

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen