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

Sometimes there is a situation when you plot a chart in Excel but some of your data is missing from the dataset. In that case, we will see that portion as blank in the chart. Personally think it is really annoying to see in the chart. That’s why we need to hide them and form a perfect chart. In Microsoft Excel, you’ll have a good platform to do this. This article will show how to hide Excel chart series with no data effectively. I hope you find this article interesting and gain some valuable knowledge.


Download Practice Workbook

Download the practice workbook below.


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

To hide Excel chart series with no data, we have found four different approaches through which we can discuss the topic in detail. In the Excel chart, there are some possibilities when you have some blank rows or columns in your dataset. In that case, the chart will keep that portion blank. To have better visibility and better understanding, we need to hide those blank cells and make a better chart all around.  We use some Excel commands to solve this problem effectively.


1. Use of Chart Filter

Our first method is to use of chart filter command. By using the chart filter option, we can easily hide Excel chart series with no data. To do this, we take a dataset that includes some name, income, expenses, and profit. IF you look closely into the dataset, you’ll get some of the data are missing.

How to Hide Chart Series with No Data in Excel

By using this dataset, we create a chart and then hide those blank series from the chart. Follow the steps carefully.

Steps

  • First, select the range of cells B4 to E12.

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

  • Then, the Insert Chart option will appear.
  • From there, select the Clustered Bar chart.
  • Then, click on OK.

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

How to Hide Chart Series with No Data in Excel

  • It looks bad while presenting something like that.
  • That’s why we need to use a chart filter and remove these portion and make the chart looks better overall.
  • First, click on the Chart Filter icon on the right side of the chart.

How to Hide Chart Series with No Data in Excel

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

How to Hide Chart Series with No Data in Excel

  • As a result, we will get our desired result. See the screenshot.

How to Hide Chart Series with No Data in Excel

Read More: How to Ignore Blank Cells with Formulas in Excel Chart


2. Utilizing Data Filter

Our next method is to utilize the data filter option. Here, we basically filter the dataset and then create a chart. This method is also helpful to hide Excel chart series with no data. To show this step, we take a dataset that includes some months and the corresponding sales amount.

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

Steps

  • First, select the range of cells B4 to C16.

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

  • As a result, it will filter our dataset. See the screenshot.

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

  • Then, the Insert Chart option will appear.
  • From there, select the Clustered Column chart.
  • Then, click on OK.

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

How to Hide Chart Series with No Data in Excel

  • Then, go to the dataset and click on the drop-down arrow of the Sales column.

How to Hide Chart Series with No Data in Excel

  • A new menu will appear.
  • From there, uncheck the Blanks.
  • Then, click on OK.

  • It will give us the desired result where the blank portions are neglected. See the screenshot.

How to Hide Chart Series with No Data in Excel

Read More: How to Leave Cell Blank If There Is No Data in Excel (5 Ways)


Similar Readings


3. Modifying Axis Options

We can hide Excel chart series with no data by modifying axis options. For this method, we need to create months and the corresponding sales as a dataset. In our dataset, some of the months are not there. But if we create a chart, it will take all the 12 months because those months are in the date format.

Steps

  • First, select the range of cells B4 to C13.

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

  • Then, the Insert Chart option will appear.
  • From there, select the Clustered Column chart.
  • Then, click on OK.

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

How to Hide Chart Series with No Data in Excel

  • 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.
  • Then, select Format Axis from the Context Menu.

How to Hide Chart Series with No Data in Excel

  • After that, the Format Axis dialog box will appear.
  • From the Axis Options section, select the Text axis.
  • It basically gives the command to take values from the dataset.

How to Hide Chart Series with No Data in Excel

  • Finally, it will give us the desired result. See the screenshot.

Read More: Excel Chart: Ignore Blank Axis Labels (with Easy Steps)


4. Connecting Data Points with Line

Our fourth method is to connect data points with lines. While using the line chart, if some values of our dataset are blank, it won’t create a complete line chart. We need to hide those chart series with no data and form a complete line. We take a dataset where some of the values are blank.

Steps

  • First, select the range of cells B4 to C16.

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

  • Then, the Insert Chart option will appear.
  • From there, select the Line chart.
  • Then, click on OK.

  • It will give us the following chart where some of the data are missing. That’s why it doesn’t create a complete chart. See the screenshot.

How to Hide Chart Series with No Data in Excel

  • Then, right-click on the chart.
  • It will open up the Context Menu.
  • Then, from there, click on Select Data.

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

  • Then, the Hidden and Empty Cells Settings dialog box will appear.
  • After that, click on the Connect data points with line.
  • Finally, click on OK.

How to Hide Chart Series with No Data in Excel

  • After that, in the Select Data Source dialog box, click on OK to apply the change.

  • Finally, we will get the desired chart that hides the series with no data.

How to Hide Chart Series with No Data in Excel

Read More: How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)


Things to Remember

  • While using the connecting data points with the line method, you have to use a line chart. Otherwise, you don’t get the perfect result that you want.

Conclusion

We have shown four different methods to hide Excel chart series with no data. All of these methods are really easy to use. By using these methods, you can easily solve numerous chart-related problems. We basically use some Excel commands to solve the issue. I hope we cover all possible questions to hide the Excel chart series. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our ExcelDemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo