If you create a chart from a worksheet with data that is hidden in rows and columns or that has empty cells, by default the hidden data will not be displayed in the chart and the empty cells will be displayed as gaps in the chart. To override this you can use the Hidden and Empty Cells function which allows you to determine how the chart should deal with hidden data and empty cells, giving you the option to either show them as gaps, or as zeros or to smooth the chart over the gaps.
In this tip we show you how to replace empty cells with zero values in your chart. For example, you could be a Sales Manager and you want all your product sales to be plotted as values on your chart. Follow the steps below to do this. You can download the workbook to practice here.
Applies To: MS Excel 2007, 2010 and 2013
The screen shots below will be used for this example.
1. To show blank values as zeros in your chart:
- Right click the chart.
- Click on Select Data.
- Click on the Hidden and Empty Cells button as per screen shot below.
2. Select Zero as per screen shot below.
3. Select OK.
4. Select OK again.
5. The chart will now display the empty cells as zero values as per the screenshot below.
