Excel Chart Legend Showing Blank Entries: How to Remove Empty Items

Your Excel chart legend may show empty boxes or blank labels, cluttering the visual. This happens when the chart’s data source includes hidden rows, empty cells, or formulas that return blanks. This article explains why these phantom entries appear and provides clear steps to clean up your legend.

Why Blank Cells Create Phantom Legend Entries

Excel charts are linked directly to worksheet cells. The legend is generated from the series names defined in your data. If your selected data range includes cells that appear empty, Excel may still try to plot them, resulting in a legend entry with no label. There are three common causes for this.

Hidden Rows and Filtered Data

When you hide rows or apply a filter, the data in those rows is not deleted. By default, Excel’s charting engine still includes this hidden data in the chart series. This can create legend entries for data points that are not visible on the chart itself, showing up as gaps or blank labels.

Formulas Returning Empty Strings

A cell containing a formula like =IF(A1>10, A1, “”) is not truly empty. It contains a formula that outputs a zero-length text string. Excel interprets this text string as a valid data point label, often plotting it as a zero value and adding a blank-seeming entry to the legend.

Gaps in the Data Range Selection

If you manually select a data range that includes entire blank rows or columns between data blocks, those blanks become part of the chart’s data source. Excel will allocate a legend entry for each segment of the series, including those with no actual values.

Steps to Clean Up Your Chart Legend

Follow these methods to remove unwanted blank items from your chart legend. Start with the first method, as it addresses the most common setting.

Method 1: Use the Hidden and Empty Cells Settings

1. Select your chart
   Click anywhere on the chart you want to fix. This activates the Chart Design and Format tabs on the ribbon.
2. Open the Select Data Source dialog
   Go to the Chart Design tab. In the Data group, click the Select Data button.
3. Access the hidden cell settings
   In the lower-left corner of the Select Data Source dialog, click the Hidden and Empty Cells button.
4. Change the display for empty cells
   A small dialog box appears. Under Show empty cells as, select the Gaps option. Ensure the Show data in hidden rows and columns checkbox is NOT checked. Click OK, then click OK again to close the main dialog.

Method 2: Fix the Underlying Data Source

1. Inspect cells for formulas
   Click on the cells in your data range that correspond to the blank legend entries. Look in the formula bar. If you see a formula like =””, that cell contains a text string, not a true blank.
2. Convert formulas to values
   Select the problematic cells. Press Ctrl+C to copy. Then, go to the Home tab, click the Paste dropdown arrow, and select Paste Values (the clipboard icon with 123). This replaces the formula with its result.
3. Delete truly empty rows
   If your data range includes entire blank rows, it is better to remove them. Select the row number, right-click, and choose Delete. This shrinks the chart’s data range.

Method 3: Use a Dynamic Named Range

1. Open the Name Manager
   Go to the Formulas tab and click Name Manager. In the dialog, click New.
2. Create a name for your data
   In the Name field, type something like ChartData. In the Refers to box, enter a formula like =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). Adjust Sheet1!$A$1 to your top-left data cell.
3. Apply the name to your chart
   Select your chart, open the Select Data Source dialog. Edit the series values and category labels to reference the defined name, like =Sheet1!ChartData.

If Blank Legend Entries Persist

Sometimes blank entries remain after trying the standard fixes. These specific situations require targeted solutions.

Chart Still Shows Blanks After Changing Settings

If you changed the Hidden and Empty Cells setting but blanks remain, the chart may be using an outdated data range. Select the chart, go to Chart Design > Select Data. Carefully check the Chart data range field at the top. Manually adjust the range reference to exclude blank rows and columns at the edges of your data block.

Legacy Chart Type From Older Excel File

Charts created in much older versions of Excel sometimes behave differently. Try converting the chart to a modern type. Right-click the chart, select Change Chart Type. Switch from an older type like “Line with Markers” to a standard “Line” chart, then click OK. This can reset the chart’s internal data handling.

Manual Legend Entry Was Added

A blank entry might have been added manually. Right-click the chart legend and select Format Legend. In the pane that opens, click Legend Options. Click on the specific blank entry in the legend to select it, then press the Delete key on your keyboard to remove it individually.

Data Source Methods Compared

Item	Hidden and Empty Cells Setting	Dynamic Named Range
Primary use	Fix existing charts with hidden rows or empty cells	Prevent blanks in charts that update with new data
Best for	One-time cleanup of a static chart	Dashboards and reports with frequently changing data
Technical skill required	Low	Medium
Impact on data	Changes chart display only	Requires defining a formula-based range name
Maintenance	Manual update if data range expands	Automatic, adjusts to data length

You can now eliminate distracting blank boxes from your chart legends. Use the Hidden and Empty Cells dialog for a quick fix on most charts. For recurring reports, set up a dynamic named range with the OFFSET function to automate the process. Remember that pressing F9 to recalculate the workbook can sometimes refresh a chart’s link to its corrected data source.