In this article, we will learn to hide chart data in Excel. Sometimes, we need to protect data or show less information on the chart. In that case, we can hide the chart data. If we hide chart data, then we will see only the graph on the chart. Today, we will demonstrate step-by-step procedures to hide chart data in Excel. We will also discuss the way to hide empty cells in an Excel chart. So, without any delay, let’s start the discussion.
To explain the steps, we will use a dataset that contains information about the sales amount of some sellers for the first three months. We will hide the sales amount for February in the chart data and still show the graph on the chart.
Let’s pay attention to the steps below to see how we can hide chart data in Excel.
STEP 1: Plotting Chart in Excel
- First of all, we need to plot the chart.
- To do so, select any cell of the dataset and press Ctrl + A to select all used cells.
- Secondly, go to the Insert tab and select the Insert Line icon. A drop-down menu will appear.
- Select the Line icon from there. You can choose other types of charts according to your needs.
- As a result, you will see a line chart on the excel sheet.
STEP 2: Inserting Chart Data Table
- In step 2, we will learn to insert the data table inside the chart.
- For that purpose, select the chart. A plus (+) icon will appear on the top right side of the chart.
- Now, click on the plus (+) icon to open the menu.
- Check the Data Table from there.
- After checking the Data Table, the chart will look like the picture below. You can see the dataset inside the chart.
STEP 3: Hiding Chart Data in Excel
- In the third step, we will hide the data for February from the data table.
- To hide chart data for February, you need to select the range D4:D9.
- After that, press Ctrl + 1 to open the Format Cells dialog box.
- In the Format Cells dialog box, click on the Number tab and select Custom from the category section.
- Then, insert three semi–colons (;;;) in the Type field.
- Click OK to proceed.
- As a result, you will be able to hide the data for February inside the dataset.
- Also, the data inside the Data Table will be hidden.
STEP 4: Removing Legend Keys to Hide Chart Data
- In the previous step, we hid chart data for February.
- But we can still see the Legend Keys inside the Data Table.
- To remove Legend Keys, select the chart and then, click on the plus (+) icon to open the menu.
- After that, select the arrow of the Data Table element to see options.
- Select No Legend Keys from there.
- As a result, you will have no Legend Keys for February.
STEP 5: Erasing Chart Data Table Borders
- We removed the Legend keys successfully in the previous step, but the Data Table Borders still exist.
- In this step, we will erase the Table Borders.
- For that purpose, select the chart and click on the plus (+) icon.
- In the following step, select the arrow of the Data Table element to see options.
- Select More Options from there.
- This will open the Format Data Table on the right side of the sheet.
- Now, deselect the Table Borders from the Data Table Options.
- As a result, you can erase the borders from the Data Table.
STEP 6: Deleting Chart Legends
- In the last step, we will delete the Legends from the chart.
- To do so, click on the Legends and press the Delete key on the keyboard.
- After hiding chart data, the chart will look like the picture below.
How to Hide Empty Data in Excel Chart
We use Excel Chart to plot data on the sheet. Sometimes, we may have empty data inside the dataset. In that case, we can hide the empty data and still show a meaningful chart. To explain this topic, we will use the previous dataset. But this time, we will have empty data in Cell C7.
Let’s follow the steps below to see how we can hide empty data in an Excel chart.
STEPS:
- Firstly, select the column that stores the empty cell.
- Then, right–click on the column index to open the Context Menu.
- Select Hide from there.
- Secondly, select all cells of the dataset.
- Thirdly, go to the Insert tab and select the Insert Column icon. A drop-down menu will appear.
- Select the Clustered Column icon from there.
- As a result, you will see the chart on the sheet. You can see the data for January are missing because we hid them.
- After that, select the chart and navigate to the Chart Design tab in the ribbon.
- Then, click on the Select Data It will open the Select Data Source dialog box.
- In the Select Data Source dialog box, click on the Hidden and Empty Cells option.
- Check Show data in hidden rows and columns in the Hidden and Empty Cell Settings box.
- Click OK to proceed.
- Finally, you will see the data for January on the chart.
- In case you have a line chart, then the chart will look like the picture below for our dataset.
- You can see a disconnected line for January because of the empty cell.
- To solve this, you need to select ‘Connect data points with line’ instead of Gaps in the Hidden and Empty Cell Settings box.
- Instantly, you will have the connected line for January.
Download Practice Workbook
You can download the practice book from here.
Conclusion
In this article, we have discussed step-by-step procedures to Hide Chart Data in Excel. I hope this article will help you to perform your tasks easily. Also, we have demonstrated the method to hide empty cells in an Excel chart. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
