Chart & Visualization Issues

How to Create an Excel Chart with Color-Coded Bars by Score

Tauseeq Magsi Oct 20, 2026 868 views

Question details

The user wants to display student scores in an Excel bar chart where the bars are dynamically color-coded (e.g., red, yellow, and green) based on specific numerical score ranges.

How to Create an Excel Chart with Color-Coded Bars by Score

Product: Microsoft Excel for Microsoft 365
Device & OS: not provided
Scenario: Visualizing performance metrics or student scores where different data ranges require distinct colors for easier reading and analysis.
Observed behavior: To achieve conditional coloring in charts, the user needs to divide the data series into separate columns based on score thresholds before creating and formatting the chart.

Before you start

Ensure your dataset is organized in a single column without empty rows, and determine the exact numerical thresholds for your color categories (for example, 1-5 for Red, 6-10 for Yellow, and 11-15 for Green).

Solution 1Recommended

Use Helper Columns to Separate Data Series

The most reliable and easiest way to apply conditional coloring to a chart is by using formulas to split your data into helper columns, and then assigning a specific color to each resulting series.

By dividing the main data column into multiple helper columns based on your target ranges, Excel will treat each range as a separate data series. This allows you to format each series with a unique color while keeping the chart dynamic.

Set up helper columns

Create new adjacent columns for each of your ranges. For example, label them '1-5', '6-10', and '11-15'.

Apply IF formulas with NA()

In the first helper cell, use a formula like =IF(AND(B2>=1, B2<=5), B2, NA()) to extract values in that range. The NA() ensures Excel doesn't plot zero values. Drag the formula down, and repeat for the other columns adjusting the ranges.

Insert the chart

Select your label column (e.g., Student Names) and the newly populated helper columns. Go to the Insert tab and choose a Clustered Column or Clustered Bar chart.

Format series colors

Right-click on a bar belonging to the first series (the 1-5 range) and select Format Data Series. Change the Fill Color to red. Repeat this process, choosing yellow for the 6-10 series and green for the 11-15 series.

Overlap the bars

To make the bars align perfectly in a single row without gaps, stay in the Format Data Series pane and set the Series Overlap slider to 100%.

Use Helper Columns to Separate Data Series

Dynamic Updates: Because the helper columns use formulas, any changes to the original scores will automatically update the chart and apply the correct color category.

Solution 2

Automate Color-Coding Using VBA Macros

If you frequently need to color-code charts based on complex or changing criteria, a VBA macro can dynamically format the bar colors without the need for helper columns.

Easily Visualize Data

Create Stunning Color-Coded Charts in WPS Office

WPS Spreadsheet offers a seamless, highly compatible alternative to Microsoft Excel for analyzing data and creating dynamic charts. You can effortlessly use IF formulas, format series overlaps, and build conditional charts for free.

1. Organize data in WPS Spreadsheet: Open your data file and use the =IF() function to categorize your scores into separate helper columns.
2. Insert the chart: Highlight the categorized data along with the labels, navigate to the Insert tab, and select a Column or Bar Chart.
3. Customize and overlap: Double-click the data series to open the formatting pane, set 'Series Overlap' to 100%, and select your desired red, yellow, and green colors.

Fully compatible with Microsoft Excel (.xlsx) formulas, charts, and data formatting.Intuitive and clean interface for managing data series and chart customization.Free, lightweight suite that runs smoothly on Windows, Mac, and Linux environments.Extensive built-in chart templates and advanced data visualization options.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard conditional formatting directly on an Excel chart?

No, Excel does not have a native 'Conditional Formatting' button for charts like it does for cells. You must use workarounds such as creating helper columns to split the data series or using VBA macros to achieve dynamically colored bars.

Why use NA() instead of empty quotes ("") or zero in the IF formulas?

Using NA() generates a #N/A error when the condition isn't met. Excel charts naturally ignore #N/A errors, preventing the chart from plotting a zero value or an empty gap on the axis. If you use zero, Excel may draw a flat bar at the bottom of your chart.

How do I remove the gaps between the different colored bars?

When you use helper columns, Excel initially plots them side-by-side. To align them into a single line, right-click any bar, select 'Format Data Series', and change the 'Series Overlap' setting to 100%.

Excel & Data Problems