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.
How to Hide Chart Series with No Data in Excel: 4 Easy Methods
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.
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.
- 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.
- 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.
- As a result, we will get our desired result. See the screenshot.
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.
- Then, go to the dataset and click on the drop-down arrow of the Sales column.
- 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.
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.
- 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.
- 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.
- Finally, it will give us the desired result. See the screenshot.
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.
- 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.
- 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.
Read More: How to Format Data Series in Excel
Things to Remember
While using the connecting data points with the line method, you have to use a line chart. Otherwise, you won’t get the perfect result that you want.
Download Practice Workbook
Download the practice workbook below.
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.
Related Articles
<< Go Back To Data Series in Excel |Â Excel Charts |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
