Fix Excel Histogram Chart Cannot Display Hidden Data
Question details
The user is unable to display data from hidden rows or columns in an Excel histogram chart because the option to show hidden data is completely disabled or grayed out.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a histogram chart using a dataset that contains hidden cells, rows, or UNIQUE formulas.
- Observed behavior
- The 'Show data in hidden rows and columns' option is disabled specifically for histogram charts, whereas it functions normally for other types like line and column charts.
Verify whether you are hiding entire worksheets or just specific rows and columns, as this can affect chart behavior differently. Ensure your Excel application is updated to the latest version before running tests.
Test Chart Data and Submit Microsoft Feedback
Since this is a known limitation specific to Excel's native statistical histogram charts, verifying the issue with simple data and reporting it is the official recommendation.
Excel's built-in Histogram and Pareto charts have specific calculation engines that currently do not support pulling data from hidden cells. It is recommended to verify this limitation in a clean workbook to ensure no other file corruption is causing the issue.
Open a new Excel worksheet and enter a simple, static vertical range of numbers without utilizing any dynamic array formulas like UNIQUE.
Highlight your new data range, navigate to the 'Insert' tab on the ribbon, and choose 'Histogram' from the Statistic Chart dropdown menu.
Right-click one of the row numbers containing your test data and select 'Hide'. Right-click your new chart, choose 'Select Data', and click 'Hidden and Empty Cells' to confirm the option is still disabled.
If the option remains grayed out, navigate to 'File' > 'Feedback' > 'Send a Frown' to report this histogram-specific limitation directly to the Excel development team.
Use a Column Chart as a Workaround
Because standard column charts do not restrict displaying hidden data, you can manually calculate bins and format a column chart to mimic a histogram.
Try WPS Office for Seamless Charting and Data Analysis
If you are frustrated by specific chart limitations and statistical bugs in Microsoft Excel, WPS Office offers a free, lightweight, and highly compatible alternative. Enjoy flexible charting tools, a familiar user interface, and robust spreadsheet capabilities without the constraints.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your Windows, Mac, or Linux operating system.
- 2. Install the software: Run the downloaded installer file and follow the quick on-screen prompts to complete the setup.
- 3. Open your Excel files: Launch WPS Spreadsheet and open your existing .xlsx workbooks directly. All your original data, formulas, and charts will be preserved.
Frequently Asked Questions
Why is the 'Show data in hidden rows and columns' option grayed out in Excel?
In Excel, certain newer statistical chart types—specifically Histogram, Pareto, and Box & Whisker charts—utilize a different background calculation engine that does not support pulling data from hidden cells. This option is intentionally disabled for these charts, whereas it remains fully available for standard line and column charts.
Can I make a histogram out of a regular column chart?
Yes. By manually calculating your data frequencies into bins using the FREQUENCY function and creating a Clustered Column chart, you can change the series Gap Width to 0%. This visually replicates a histogram while retaining the ability to show hidden data.
Does using UNIQUE formulas affect hidden data in charts?
While dynamic array formulas like UNIQUE dynamically resize based on the source array, the core issue with the histogram chart disabling the hidden data feature relates to the specific chart type's native limitations rather than the formula being used to generate the data.
How do I report Excel chart bugs to Microsoft?
You can report unexpected behavior or request feature improvements directly within the Excel application by navigating to File > Feedback > Send a Frown. Providing a clear description of the grayed-out option helps their development team investigate the issue.
