Chart & Visualization Issues

How to Show a Custom Excel Chart Legend Based on Percentage Values

Chanuka Geekiyanage Oct 21, 2026 869 views

Question details

The user wants to display a custom legend entry (e.g., 'Achieved') in an Excel chart when a data point reaches a 50% threshold or more, automatically updating without manually modifying individual bar colors.

How to Show a Custom Excel Chart Legend Based on Percentage Values

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic chart where the legend and data series automatically categorize and colorize based on conditional percentage thresholds.
Observed behavior: Excel does not natively generate custom dynamic legends from conditional values without restructuring the chart data into separate series or utilizing a VBA macro.

Before you start

Before proceeding, determine whether you want a safe, formula-based solution (which involves adding new columns to your data) or a VBA-based solution (which requires saving your file as a Macro-Enabled Workbook).

Solution 1Recommended

Use Helper Columns to Create Separate Data Series

This is the most reliable, macro-free method. By splitting your data into conditional columns, Excel can plot them as distinct series with their own automated legend entries and colors.

Excel charts base their legends on the data series plotted. To create dynamic categories like 'Achieved' and 'Not Achieved', you must split the original single column of data into two separate columns using an IF formula.

Set up helper columns

Next to your original data, create two new columns named 'Achieved' and 'Not Achieved'. These names will automatically become your new chart legend entries.

Apply conditional formulas

In the first cell of the 'Achieved' column, enter the formula =IF(B2>=0.5, B2, NA()). In the 'Not Achieved' column, enter =IF(B2<0.5, B2, NA()). Drag these formulas down to fill the rows. The NA() function ensures Excel ignores unmet conditions in the chart.

Insert the chart

Select your category labels (e.g., Names/Months) along with the two new helper columns. Go to the Insert tab and select a Clustered Column Chart.

Overlap the series

Right-click any bar in the newly created chart and select Format Data Series. In the Format pane, set the Series Overlap to 100%. This aligns both series on the same axis, making it look like a single dynamic column.

Use Helper Columns to Create Separate Data Series

Why use NA()?: Using NA() instead of a blank string ("") or zero prevents Excel from plotting an empty space or a zero-value bar on your chart, keeping your visualization clean.

Solution 2

Use a VBA Macro to Dynamically Format Chart Elements

A macro can scan your existing chart data and automatically apply colors to individual bars without altering your spreadsheet structure.

Data Visualization in WPS

Create Conditional Charts Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible environment for utilizing helper columns and advanced charting functions. You can easily create dynamic charts with conditional formatting formulas.

1. Prepare your conditional data: Open your dataset in WPS Spreadsheet. Create 'Achieved' and 'Not Achieved' columns using the =IF(B2>=50%, B2, NA()) formula structure.
2. Insert a column chart: Highlight your axis labels and the two helper columns. Go to the Insert tab on the top ribbon and choose the Column Chart icon.
3. Format series overlap: Double-click one of the chart bars to open the Object Formatting pane on the right side of the screen. Under Series Options, slide 'Series Overlap' to 100%.
4. Customize legend colors: Click the specific series color box in the formatting pane to assign distinct colors (e.g., Green for Achieved, Red for Not Achieved).

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Lightweight interface that smoothly handles large datasets and complex charts.Intuitive chart formatting pane to easily adjust series overlap and gap widths.Completely free to download and use as a Microsoft Office alternative.

microsoft office alternative - wps office

Frequently Asked Questions

Why are the empty values still showing as gaps or zero bars in my chart?

This happens if your IF formula returns an empty string ("") or 0 for unmet conditions. Always use the NA() function (e.g., =IF(A1>=0.5, A1, NA())). Spreadsheet applications ignore #N/A errors when plotting line or bar charts.

Can I apply conditional formatting directly to an Excel chart like I do with cells?

Native conditional formatting rules (like Highlight Cell Rules) cannot be applied directly to chart visual elements in standard Excel. The most robust workaround is using helper columns to split the data into different series.

Why doesn't my VBA macro update the chart automatically when data changes?

Standard macros only run when triggered manually via a button or Alt+F8. To make the chart update automatically, the VBA code must be placed inside a Worksheet_Change event module so it executes whenever the specific data range is modified.

Excel & Data Problems