Conditional Formatting for Charts in Excel

Download your free chart template 👉 HERE and follow along.

Want your Excel charts to stand out? You can add color to your charts to make them easier to read and more engaging. For example:

• Use different colors for positive and negative values in bar charts.
• Highlight the bar with the highest value in a unique color.

Here’s how to do it in a few simple steps.

How to Make a Bar Chart in Excel

Want to create a bar chart in Excel that looks clean and professional?

Let’s use an example with Actual vs. Budget data for five products (A through E).

Follow these steps to make a clear and visually appealing chart.

1. Create a Basic Clustered Bar Chart

1. Select your data: Highlight A2:B7 (Product names and Actual values).
2. Go to Insert → Charts → 2-D Bar → Select Clustered Bar Chart.
3. You’ll see a basic chart appear in your worksheet.

2. Clean Up the Chart for Better Readability

To make the chart easier to read, we’ll remove unnecessary elements:

1. Remove Gridlines

• Click on the horizontal gridlines.
• Press Delete or go to Chart Design → Add Chart Element → Gridlines → None.

2. Remove Horizontal Axis:

• Right-click on the horizontal axis (at the bottom of the chart).
• Select Delete.

3. Add Data Labels

1. Right-click on any bar.
2. Choose Add Data Labels from the menu.
3. Now, each bar will display its value.

4. Format the Chart for a Professional Look

1. Adjust Bar Spacing:
   • Click any bar, then press Ctrl+1 to open the Format Data Series panel.
   • Under Series Options, set the Gap Width to 80%.
2. Change Bar Color:
   • Select the bars, go to Format → Shape Fill, and choose a medium gray.
3. Remove the Chart Border:
   • Click the chart edge, then go to Format Chart Area→ Border → No Line.
4. Resize the Chart:
   • Drag the chart edges to make it narrower—reduce the width by about 50%.

5. Customize Product Names and Gridlines

1. Reorder Product Names:
   • Click on the product names (vertical axis).
   • Go to Format Axis → Categories in Reverse Order to match your data order.
2. Add Light Gridlines:
   • Click on the chart → Go to Chart Design → Add Chart Element → Gridlines → Primary Major Horizontal.
   • Select the gridlines and change their color to a light gray.

6. Adjust the Plot Area for Better Visibility

1. Click the plot area (where the bars are).
2. Drag its edges to ensure the largest value fits comfortably.
3. Adjust until all data is clear.

How to Create a Budget vs. Actual Difference Chart in Excel

Now we’ll visualize how your Actuals compare to your Budget. Here’s how to set it up, step by step.

1. Prepare the Data

To show the differences between Actual and Budget, we’ll create a new column for the difference values.

Steps:

• In cell E1, enter the title: Data Prep.
• In cell E2, enter the subtitle: Difference.
• Enter this formula in cell E3

=B3 - C3

• Drag the formula down to cell E7 to calculate differences for all products.

Now you have a new column showing how much each Actual value deviates from the Budget.

2. Create the Difference Chart

Next, we’ll make a bar chart using the new difference data.

Steps:

1. Highlight the Product and Difference data:
   • Select cells A2:A7 (Products).
   • Hold Ctrl and also select cells E2:E7 (Difference values).
2. Go to Insert → Charts → 2-D Bar → Select Clustered Bar Chart.

You’ll see a new chart with your difference values!

3. Copy Formatting from the First Chart

To save time, we’ll copy the formatting from your first chart.

Steps:

1. Click the edge of your first chart and press Ctrl+C (or use the Copy button).
2. Click the edge of your second chart (the difference chart).
3. Go to Home → Clipboard → Click the lower part of the Paste button → Select Paste Special → Choose Formats.

This applies the same styles from the first chart to the second chart!

4. Adjust the Second Chart

Some settings won’t carry over with the formatting shortcut. Let’s fix those manually:

1. Remove Product Names from the Vertical Axis:

• Select a product name on the vertical axis.
• Go to Format Axis → Axis Options → Labels → Label Position → Select None.

💡 Important: Don’t press Delete; it will remove the entire axis!

2. Adjust Chart Width:

• Drag the edges of the chart to match the width of your first chart.

Featured Course

Professional Excel Charts for Business Reports

Create professional charts that tell a story AND update automatically. Learn formulas and design techniques that get your reports noticed.

Learn More

How to Use Conditional Formatting for Positive and Negative Values in Excel Charts

Now, we’ll use different colors for positive and negative values to highlight trends. Follow these simple steps to set it up.

1. Align Two Charts for a Double Chart Effect

To create a “double chart” (placing two charts side-by-side), follow these steps:

1. Select your second chart (the one with difference values).
2. Remove the chart’s background fill:
   • Go to Format → Shape Fill → Select No Fill.
3. Drag the second chart and position it next to the first chart. Use Excel’s alignment guides to ensure they are evenly spaced.

2. Add Conditional Formatting to Highlight Positive and Negative Values

Now, let’s use different colors for positive and negative bars in the second chart.

Steps:

1. Click on any bar in the second chart to select the series.
2. Press Ctrl+1 to open the Format Data Series pane.
3. Go to Fill & Line → Fill.
4. Check the box for Invert if Negative.
   • You’ll see two color options: one for positive values and another for negative values.
5. Choose colors:
   • For positive values, select a color like blue or green.
   • For negative values, select a contrasting color like red.

Now, you have two charts side-by-side, with conditional formatting applied to highlight positive and negative values.

This makes it easier to spot trends and deviations at a glance.

How to Highlight the Highest Value in an Excel Chart

Now we’ll highlight the highest value with a different color using Excel’s conditional formatting tools. Here’s how to do it step by step.

1. Create a Formula to Identify the Highest Value

First, we need a formula to find the highest value in your data.

Steps:

• In cell F2, enter the title: Max Condition.
• In cell F3, enter this formula:

=IF(B3=MAX($B$3:$B$7), B3, “”)

• Drag the formula down to cell F7.
  • This will display the value only if it is the maximum in the range F3:F7.
  • If multiple values are tied for the maximum, all will be included.

2. Add the Highlighted Data to Your Chart

Now, let’s add this new column to your existing chart.

Steps:

1. Click on your first chart to select it.
2. Go to Chart Design → Select Data.
3. In the Select Data Source dialog box, click Add under Legend Entries.
4. In the Edit Series dialog box:
   • Series Name: Enter Max Condition.
   • Series Values: Highlight the range F3.
5. Click OK to close the dialog box.

The newly added data series appears as follows:

3. Format the Highlighted Bars

Let’s make the highlighted bars stand out.

Steps:

• Click on the newly added bars to select them.
• Open the Format Data Series panel.
• Adjust the Gap Width under Series Options to 100%. This places the highlighted bars directly over the original bars.

• Change the color of the highlighted bars:
  • Go to Format → Shape Fill → Choose a color like blue or green.

💡 Pro Tip: Use contrasting colors to ensure the highlighted bars are easy to spot.

4. Final Adjustments to the Charts

To complete your charts, we’ll make a couple of final tweaks:

1. Center Title: Move the title to be centered above both charts and link the title element to cell B1.
2. Group Charts: Select both charts Shape Format → Arrange → Group → Select Group..

Save Time with Our Free Excel Chart Template!

Download our ready-to-use Excel chart template and:

• Instantly visualize your data with professional designs.
• Highlight key insights, like Budget vs Actual differences or conditional formatting for trends.
• Customize easily to fit your specific needs—no advanced Excel skills required!

👉 Download the Free Excel Chart Template Here.

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.

As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.

I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.

More About Leila Join 400,000+ professionals in our courses