Musiha Mahfuza Mukta
Sometimes for data visualization, you may need to use an Excel chart. But when you hide your data, your chart may disappear. So, if you find the solutions of why an Excel chart disappears when data is hidden. Then you have come to the right place.
In this article, I will explain the solutions of why an Excel chart disappears when data is hidden. Additionally, for conducting the session, I’m going to use Microsoft 365 version.
Download Practice Workbook
You can download the practice workbook from here:
3 Issues with Solutions When Excel Chart Disappears after Chart Data Is Hidden
Here, I will describe 3 suitable solutions to why an Excel chart disappears when data is hidden. In addition, for your better understanding, I’m going to use a sample dataset. Which contains 3 columns. They are Product, Sales, and Profit. The dataset along with the chart is given below.
Here, you can see that when I have hidden rows 5 to 9 then the Excel chart disappears. Now, I will show the solutions to not disappear from your chart even if you hide the data.
1. Using Chart Tools to Show Chart When Data Is Hidden
Here, I will use the Chart tools as a solution to not disappear the chart in Excel even if you hide the related data.
Solutions:
- Firstly, select the chart.
As a result, you will see two new contextual tabs in the custom ribbon.
- Secondly, from the Chart Design tab >> go to the Select Data option.
After that, you will see the following dialog box named Select Data Source.
- Now, from the dialog box of Select Data Source, you have to choose the Hidden and Empty Cells option.
Subsequently, you will see another dialog box named Hidden and Empty Cell Settings.
- Then, tick mark on Show data in hidden rows and columns.
- After that, press OK.
Again, the previous dialog box named Select Data Source will appear.
- After this, press OK on the Select Data Source box.
Finally, you will see that I have hidden rows 5 to 9 but still, the Excel chart is visible.
Read More: How to Create Excel Chart Using Data Range Based on Cell Value
2. Use of Context Menu Bar to Keep Chart Visible
Now, I will use the Context Menu Bar for opening the Select Data feature as a solution to not disappear the chart in Excel even if you hide the related data.
Solutions:
- Firstly, right-click on the chart.
- Secondly, from the Context Menu Bar >> choose Select Data option.
After that, you will see the following dialog box named Select Data Source.
- Now, from the dialog box of Select Data Source, you have to choose the Hidden and Empty Cells option.
- Then, follow the steps of method-1.
Lastly, you will see that if I hide rows 5 to 9 but still the Excel chart will be visible.
Read More: How to Add Data to an Existing Chart in Excel (5 Easy Ways)
Similar Readings
- How to Group Data in Excel Chart (2 Suitable Methods)
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
- How to Create a Scatter Chart in Excel (with Easy Steps)
- Create Excel VBA UserForm (with Detailed Steps)
- How to Show Only Dates with Data in Excel Chart
3. Applying VBA to Show Chart When Data Is Hidden
Here, you can employ the VBA code as a solution to not disappear the chart in Excel even if you hide the related data. Furthermore, I will explain two different VBA codes to keep the Excel chart visible.
3.1. Always Appearing Excel Chart
The first code is for always appearing in the Excel chart even when you hide the data. Now, follow the steps given below.
Steps:
- Firstly, you need to open your worksheet. Here, you must save the Excel file as Excel Macro-Enabled Workbook (*xlsm).
- Secondly, you have to choose the Developer tab >> then select Visual Basic.
- At this time, from the Insert tab >> you have to select Module.
- After that, write down the Code given below in the Module1.
Sub chart_appearing_when_data_hidden()
Dim my_chart As Chart
Set my_chart = ActiveChart
On Error Resume Next
my_chart.PlotVisibleOnly = Not my_chart.PlotVisibleOnly
On Error GoTo 0
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named chart_appearing_when_data_hidden.
- Next, I declare a variable my_chart as Chart.
- After that, I used the property PlotVisibleOnly to keep the chart always visible.
- Then, Save the code then go back to Excel File.
- Then, from the Developer tab >> select Macros.
- At this time, select Macro (chart_appearing_when_data_hidden) and click on Run.
Lastly, you will see that if you hide rows 5 to 9 then still the Excel chart will be visible.
Read More: How to Hide Unused Columns in Excel (5 Quick Tricks)
3.2. Keep Visible Excel Chart with Mouse Click
The second code is for keeping the Excel chart visible even when you scroll the data. Now, follow the steps given below.
Furthermore, for this method, I’m going to use the following dataset which contains sales recordings for 30 days of a company.
- Now, right-click on the worksheet where the data is. Here, I have right-clicked on the worksheet named VBA.
- Then, choose View Code.
- Then, write the following code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim my_variable As Double
    Application.ScreenUpdating = False
    my_variable = ActiveWindow.ScrollRow * ActiveCell.RowHeight
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.Shapes("Chart 1").Top = my_variable
    ActiveWindow.Visible = False
    Application.ScreenUpdating = True
End Sub
Code Breakdown
- Here, I have created a Private Sub Procedure where I’ve selected the event SelectionChange. So that whenever I select any cell the Chart will move along with the selection.
- Next, I declare a variable my_variable as Double.
- After that, I used some properties to keep the chart always visible.
- Furthermore, in the code, Chart 1 is the name of the chart. So, you must write the chart view name of your chart inside the code.
- Then, Save the code then go back to Excel File.
- Now, if you click any cell you will see that the chart will be visible there.
Read More: How to Add Data Table in an Excel Chart (4 Quick Methods)
How to Resolve the Issue When Charts Are Not Displaying in Excel
Sometimes, you may face another problem like inserting a chart but that chart is not displaying in Excel. In this section, I will demonstrate why an Excel chart is not working.
Basically, if Excel stores the cell’s value as Text format rather than Number format then you may face this problem.
Moreover, I have attached an example of it. Here, if you notice then you will see that the values of the Sales column are stored in Text format.
Now, let’s talk about the solution.
Solution:
- Firstly, select the cells C5:C9.
- Secondly, from the drop-down arrow under the Number format >> choose Number.
- Then, remove the Apostrophe symbol.
Finally, you have converted the C5 cell value a Number.
- Similarly, convert all the cell values into Numbers.
Lastly, you will see the chart. Here, I have used the Currency sign. This is not mandatory.
Read More: Excel Chart Not Updating with New Data (2 Suitable Solutions)
💬 Things to Remember
- Actually, the easiest and most useful way is to keep on the Show data in hidden rows and columns option.
- Furthermore, for hidden cells use 1st code.
- Moreover, to keep the chart visible on the worksheet while scrolling data use 2nd code.
Practice Section
Now, you can practice the explained method by yourself.
Conclusion
I hope you found this article helpful. Here, I have explained 3 solutions as an Excel chart disappears when data is hidden. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Change Data Source in Excel Chart (3 Useful Examples)
- Excel VBA: Get Source Data Range from a Chart (with Quick Steps)
- How to Plot Time over Multiple Days in Excel (With Easy Steps)
- Excel Chart by Month and Year (2 Suitable Examples)
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- Excel VBA Worksheet Events and Their Uses
- List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)
