I have seen several posts where people want to make their area chart stop and show a cliff when there is a blank category value (notice that there is a blank in cell B7 that is part of the chart range). Much like this:
NOT what they wanted . It appears that Excel treats any Blank data point in an Area Chart as though it were a zero.
For Excel 2007 there is also a very fun Excel feature (maybe bug is the better term) in that when you create the area chart it shows a cliff, but when you save and open the file your cliff has fallen and it can’t get up.
Well here is how to create your Area Cliff Chart in a step-by-step fashion and there is also a Video Tutorial link at the bottom of the page.
2) Create Area Chart
As you can see the Excel default is to treat Blanks in an Area Chart as a zero and draws the area from the previous category to the horizontal axis.
However, in Excel 2007, your graph will look like this, but don’t be fooled, if you save the file, close the file and reopen, your chart will look like the Excel 2010 version. You still need to follow the next steps.
3) Change Select Data Option
Excel 2010: You will want to select GAPS for the “Show empty cells as” radio button.
Excel 2007: You will want to select ZERO and then choose the GAPS for the “Show empty cells as” radio button again. I think that the Gaps selection you see is a bug and it is really has Zero chosen for you. If you do not do this, then your chart will default back to Zero when you reopen the file. IMPORTANT: So to reiterate this: When you create an Area chart in Excel 2007, even though the newly created chart shows a cliff and when you open the Hidden and Empty Cell Settings dialog box you see that Gaps are chosen. This will change when you save the file, close and reopen the file. To prevent this, open up the Hidden and Empty Cell Settings dialog box and then choose Zero and then Gaps for the “Show empty cells as”:” radio button. This will fix the issue.
Look at that, we saved the U.S. from the Fiscal Cliff using Excel Charts and Graphs Make sure you sign up for the blog so that you are sure to get the latest posting.
Here is the video tutorial demonstrating exactly how to create an Area Chart Cliff in Excel 2007 and Excel 2010:
Please make sure and sign up for my blog so you get the latest Excel Tip and Trick.