Excel Data Bars Conditional Formatting with examples

This tutorial shows you how to quickly add and customize colored data bars in Excel to visually compare data. Instead of charts, data bars directly within cells offer a clearer comparison of numerical values. You can choose to display bars alongside the numbers or hide the numbers and show only the bars.

What are Excel Data Bars?

Excel data bars are a built-in conditional formatting feature. They insert colored bars into cells, visually representing the relative size of each cell's value compared to others in the selected range. Longer bars indicate higher values, and shorter bars indicate lower values. This allows for quick identification of highest and lowest values, such as top and bottom performers in sales data. Unlike bar charts, which are separate chart objects, data bars are integrated directly into the cells themselves.

Adding Data Bars in Excel

Follow these steps to add data bars:

1. Select the cells you want to format.
2. Go to the Home tab, click Conditional Formatting, then select Data Bars.
3. Choose either Gradient Fill (for a gradual color change) or Solid Fill (for a single color).

Gradient fill blue data bars will appear immediately:

Solid fill bars allow you to select your preferred color:

To adjust settings, select a formatted cell, go to Conditional Formatting > Manage Rules > Edit Rule, and customize colors and other options. Wider columns improve readability, especially with gradient fills, by separating values from the darker bar sections.

Gradient vs. Solid Fill

• Gradient Fill: Ideal when both bars and values are visible; the lighter bar ends enhance number readability.
• Solid Fill: Best when only bars are displayed, with values hidden.

Creating Custom Data Bars

For personalized data bars, create a custom rule:

1. Select the cells.
2. Click Conditional Formatting > Data Bars > More Rules.
3. In the New Formatting Rule dialog, customize:
   • Minimum/Maximum: Choose Automatic (default) or specify values using percentages, numbers, or formulas.
   • Fill/Border: Select your preferred colors.
   • Bar Direction: Choose from context (default), left-to-right, or right-to-left.
   • Show Bar Only: Hide cell values, showing only bars.

Example of custom gradient data bars:

Defining Minimum and Maximum Values

By default, Excel automatically sets minimum and maximum values. For more control:

1. Create a new rule (Conditional Formatting > Data Bars > More Rules) or edit an existing one (Conditional Formatting > Manage Rules > Edit).
2. In the rule dialog, under Edit the Rule Description, choose your desired Minimum and Maximum options (e.g., percentages).

Example of setting data bar percentages:

Data Bars Based on Formulas

Use formulas to dynamically calculate minimum and maximum values:

• Minimum: =MIN($D$3:$D$12)*0.95 (sets minimum 5% below the lowest value)
• Maximum: =MAX($D$3:$D$12)*1.05 (sets maximum 5% above the highest value)

Data Bars Based on Another Cell

To apply data bars based on values in different cells, copy the original values to a new column using a formula (e.g., =A1), then apply data bars to the new column and check "Show Bar Only" to hide the numbers.

Data Bars for Negative Values

Data bars work with negative numbers. To use different colors for positive and negative values:

1. Select cells, go to Conditional Formatting > Data Bars > More Rules.
2. Set positive bar color, then click Negative Value and Axis.
3. Customize negative bar colors and axis settings (or choose white for no axis).

Result:

Showing Only Bars (Hiding Values)

To show only bars, check the Show Bar Only box in the Formatting Rule dialog.

This completes the tutorial on adding and customizing data bars in Excel. A practice workbook is available for download.

The above is the detailed content of Excel Data Bars Conditional Formatting with examples. For more information, please follow other related articles on the PHP Chinese website!