'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Show Empty Cells as Gaps in Excel for the Web Pivot Charts
Question details
Users need to display blank or #N/A values as gaps instead of zeros in Pivot Charts when working in a web browser.
- Product
- Excel for the Web
- Device & OS
- not provided
- Scenario
- Formatting and presenting missing data points accurately within Pivot Charts online.
- Observed behavior
- Blank or #N/A values in pivot charts are plotted as zero because Excel for the web lacks the desktop option for configuring hidden and empty cells.
Because Excel for the Web has restricted formatting capabilities, ensure you have access to a desktop spreadsheet application (like Microsoft Excel or WPS Office) to access the advanced chart configuration menus.
Use the Desktop Application to Configure Empty Cells
Since Excel for the Web lacks the 'Hidden and Empty Cells' setting, you must open the workbook in the desktop version to force the chart to display gaps instead of zeros.
Excel for the Web is a streamlined version of the software and does not support all the advanced charting options found in the desktop version. To change how missing data is plotted, the file must be edited locally.
While viewing your workbook in Excel for the Web, click the 'Viewing' or 'Editing' button on the top ribbon, and select 'Open in Desktop App' to launch the file locally.
In the desktop application, right-click on the Pivot Chart where the zero values are appearing and select 'Select Data' from the context menu.
In the Select Data Source dialog box, click the 'Hidden and Empty Cells' button located in the bottom-left corner.
Select the radio button for 'Show empty cells as: Gaps', click 'OK' to close the prompt, and click 'OK' again to apply the changes to your chart.
Use WPS Spreadsheet to Easily Configure Pivot Charts
Don't let web-based limitations restrict your data presentation. WPS Spreadsheet offers a completely free, desktop-grade environment where you have full control over advanced features, including how empty cells and #N/A errors are plotted in your charts.
- 1. Open the File in WPS Spreadsheet: Download your workbook from the web and open the .xlsx file using WPS Spreadsheet.
- 2. Open Data Selection: Right-click your Pivot Chart and choose 'Select Data' to access the advanced source settings.
- 3. Configure Empty Cells as Gaps: Click on 'Hidden and Empty Cells' and select 'Gaps' so your missing data is no longer plotted as a zero.
Frequently Asked Questions
Why are my blank cells showing as zero in Excel for the Web?
Excel for the Web defaults to plotting blank or #N/A values as zero because it does not currently support the 'Hidden and Empty Cells' configuration options available in the desktop version.
Can I fix this empty cell issue directly in my web browser?
No, Excel for the Web does not have the interface to change this specific setting. You must open the file in a desktop application to configure how empty cells are displayed.
Will the gap settings remain if I upload the file back to OneDrive?
Yes, the metadata for 'Hidden and Empty Cells' is saved within the workbook. However, Excel for the Web might still fail to render the gaps accurately in some chart types due to its online feature limitations.
