How to Hide Chart Series with No Data in Excel (4 Easy Methods)

Method 1 – Use of Chart Filter

Steps:

• Select the range of cells B4 to E12.

• Go to the Insert tab in the ribbon.
• From the Charts group, select Recommended Charts.

• The Insert Chart option will appear.
• Select the Clustered Bar chart.
• Click on OK.

• We have a bar chart. But some of the portions are blank as there are no values in them.

• We need to use a chart filter and remove the blank portions.
• Click on the Chart Filter icon on the right side of the chart.

• It will open up several options.
• As we don’t have values for Oliver and Hopper, we need to uncheck them from the Categories section.
• Click on Apply.

• We will get the result as shown in the image below.

Method 2 – Utilizing Data Filter

In the dataset, we don’t any sales amounts for the months of June and October.

Steps:

• Select the range of cells B4 to C16.

• Go to the Data tab in the ribbon.
• From the Sort & Filter group, select the Filter option.

• It will add filter arrows to the dataset.

• Go to the Insert tab in the ribbon.
• From the Charts group, select Recommended Charts.

• The Insert Chart option will appear.
• Select the Clustered Column chart.
• Click on OK.

• We have a bar chart. But some of the portions are blank as there are no values in them.

• Go to the dataset and click on the drop-down arrow of the Sales column.

• A new menu will appear.
• Uncheck the Blanks.
• Click on OK.

• The result will be as shown in the image below.

Method 3 – Modifying Axis Options

Steps

• Select the range of cells B4 to C13.

• Go to the Insert tab in the ribbon.
• From the Charts group, select Recommended Charts option.

• The Insert Chart option will appear.
• Select the Clustered Column chart.
• Click on OK.

• It will give us the following results where we will see some blank portions.
• This is because we do not have any data in those months.

• To hide those blank portions, we need to double-click on the horizontal axis or right-click on the horizontal axis to open the Context Menu.
• Select Format Axis.

• The Format Axis dialog box will open.
• From the Axis Options section, select the Text axis.

• The result will be as shown in the image below.

Method 4 – Connecting Data Points with Line

Steps:

• Select the range of cells B4 to C16.

• Go to the Insert tab in the ribbon.
• From the Charts group, select Recommended Charts.

• The Insert Chart option will open.
• Select the Line chart.
• Click on OK.

• It will give us the following chart where some of the data are missing.

• Right-click on the chart.
• It will open up the Context Menu.
• Click on Select Data.

• It will open up the Select Data Source dialog box.
• Select Hidden and Empty Cells.

• The Hidden and Empty Cells Settings dialog box will open.
• Click on the Connect data points with line.
• Click on OK.

• In the Select Data Source dialog box, click on OK.

• We will get the desired chart that hides the series with no data.

Download Practice Workbook

Related Articles

• How to Rename Series in Excel
• How to Change Series Color in Excel Chart

<< Go Back To Data Series in Excel | Excel Charts | Learn Excel