Chart & Visualization Issues

How to Apply a Sequential Color Scale to an Excel Bar Chart

Amos Gikunda Oct 19, 2026 868 views

Question details

The user wants to apply a sequential or divergent color scale to bars within a standard single-series Excel chart.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing varying data values in a bar chart where colors dynamically reflect the numbers based on a sequential or divergent gradient.
Observed behavior: Excel does not provide an automatic option to apply sequential color scales to a standard single-series bar chart, requiring a manual multi-series workaround.

Before you start

Ensure your dataset is finalized and decide on the numerical thresholds you want to use for your different sequential color tiers.

Solution 1Recommended

Use Multiple Data Series with 100% Overlap

This workaround manually separates your values into different columns to represent color tiers, which are then merged into a single-looking bar chart.

Since standard Excel bar charts do not support conditional color scales for a single data series, you can separate your data into multiple columns.

By adding these newly separated columns as individual series and setting their overlap to 100%, the bars align seamlessly to appear as one continuous chart with customized, sequential colors.

Separate your data into columns

Create multiple new columns next to your original data, assigning one column per color shade. Manually distribute your data values into these columns based on their numerical range.

Insert a clustered bar chart

Highlight the newly created columns (including category labels) and navigate to the 'Insert' tab. Click the 'Insert Column or Bar Chart' icon and choose a standard 2D Clustered Bar.

Adjust series overlap to 100%

Right-click on any of the bars in the newly created chart and select 'Format Data Series' from the context menu. In the format pane that opens on the right, drag the 'Series Overlap' slider to exactly 100%.

Apply sequential colors

Click on each data series individually within the chart. Go to the 'Fill & Line' tab in the format pane, select 'Solid fill', and choose a distinct shade to create your sequential color scale.

Use Multiple Data Series with 100% Overlap

Handling Empty Data: Leave the cells blank where a value doesn't fit the color tier. Excel will naturally skip these blanks when plotting the 100% overlapped bars.

Solution 2

Automate Color Tiers Using IF Formulas

For larger datasets, use IF formulas to automatically sort your data into separate color-coded columns rather than doing it manually.

Data Visualization with WPS

Create Beautiful Custom Charts Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive data visualization tools. You can effortlessly manage complex datasets, separate them into series, and adjust overlap settings to create stunning sequential color bar charts.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the chart data.
2. Organize data into series: Split your values into multiple columns based on your desired sequential color tiers.
3. Insert the chart: Navigate to the 'Insert' tab on the ribbon, click 'Chart', and select a clustered bar or column chart.
4. Format series overlap: Double-click the bars in the chart to open the properties pane on the right, and set 'Series Overlap' to 100%.

100% compatibility with Microsoft Excel chart formats and formulasIntuitive formatting pane for quick series overlap adjustmentsRich library of customizable chart templates and built-in color palettesLightweight software that runs smoothly on low-spec devices

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Conditional Formatting directly on an Excel bar chart?

No, Excel's built-in Conditional Formatting feature applies only to worksheet cells, not directly to chart elements like bars or columns. You must use the multiple-series overlap workaround to achieve a sequential color effect.

Why do my overlapping bars hide smaller values?

When setting series overlap to 100%, larger values might cover smaller ones if multiple series contain data for the same category. Ensure that each row of your dataset only has a numerical value in one specific color-tier column, leaving the others blank or marked as #N/A.

Does this workaround apply to Excel map charts?

No, map charts in Excel handle data differently and often include their own built-in options for sequential or divergent color scales. The multi-series 100% overlap workaround is exclusively required for standard 2D bar and column charts.

What is the best way to handle empty cells when automating this chart?

When using formulas to separate data into color tiers, it is highly recommended to have the formula return the #N/A error (using the NA() function) instead of a zero or an empty string (""). Excel charts ignore #N/A values, ensuring no empty gaps or zero-value bars disrupt your chart.

Excel & Data Problems