Excel offers a few ways to deal with empty cells in a chart’s source data range. This is the cause of much confusion, especially over the definition of “empty cells”. Let’s take a look at this problem.
Note
A new feature in Office 365 (and Excel 2019), Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.
Read about it in Plot Blank Cells and #N/A in Excel Charts.
This new behavior in Excel makes this article obsolete.
Plotting Without Empty Cells
Here are some typical Excel charts, to remind us what they look like with fully populated data ranges. I’ve placed data labels on the charts to help show their behavior when empty cells (and other non-numeric fillers) are included in the source data.
Plotting With Empty Cells
The default behavior for most charts is to treat an empty cell by leaving a gap on the chart. Here are XY and Line charts that show how this looks. Note the lack of data labels where the gaps occur.
You can instead have Excel treat blanks as zeros (which in general is deceiving, since empty cells mean the absence of a value, not a value of zero). Note the data labels indicating a value of zero.
You can also tell Excel to interpolate over the missing data point. No data point means no data label.
Purists may say that the interpolation option may cause readers to think there is data along the line that spans the gap. Maybe so, but if the line or XY chart plots actual data points with markers, this is less of a risk.
Column charts offer only the gap and zero options for empty cells. Interpolating makes no sense in a column chart, which has discrete bars for each actual data value. The chart with a gap has no data label at the gap, the chart with zeros has a zero data label.
If we change the vertical axis so that the horizontal axis crosses below zero, we see that the first chart has no data point (bar) corresponding to the gap, but the second has a point (bar) with a value of zero.
Area charts seem to offer both the zero and interpolation options, but in both cases, the chart plunges to zero without a corresponding data label. If we mess with the vertical axis, the chart still plunges exactly to zero in both cases.
A more suitable appearance for an area chart would be one that leaves a real gap, with vertical edges, as below. To get this I had to make a two-axis chart, with a hidden series on the primary axis to provide the A-B-C category axis labels, and an area chart on the secondary axis, with two points at the second category (Y=4 and zero) and two at the fourth category (Y=zero and 8). A date scale on the secondary category axis aligns these points above each other to produce the vertical-sided gap. This only took me three minutes to construct, but an average user might never quite get it.
The protocol to produce an area chart with a gap that has vertical sides is given in Area Chart With Gap » Peltier Tech Blog
Pie charts don’t seem to offer any options for dealing with blanks, but the grayed-out default is the gap option. In the chart at the right, there is no point (wedge) and no corresponding data label where the empty cell would be plotted.
Apparently all charts except the Area chart provide a non-plotted point for an empty cell in the data range, though we can spoil that by selecting the option to treat a gap as a zero value.
Simulating Empty Cells
The main confusion comes along when someone uses formulas to fill the source data range.
The problem arises because a formula that links to an empty cell
=A1
doesn’t display a blank, it displays the value zero.
Unfortunately Excel has no formulaic way to simulate a blank in a cell. There is no formulaic way, then, to get an actual gap in a chart. A function like BLANK() or NULL() would be nice, and we’ll keep asking. But we won’t hold our breath. And if we really need it, we can hack something in VBA.
Andy Pope shares a workaround in Broken Lines for formula linked data, but it requires one or more dummy series with a line color matching the chart background, which obscure the existing line where the gaps should go.
What people try to do to fake a blank is return “” in the formula.
=IF(A1=””,””,A1)
The result looks to you and me like a blank, but to Excel, “” is a text string, albeit a short one, and it is therefore assigned a value of zero. These charts use “” in a logical yet misguided attempt to simulate an empty cell. All charts plunge to zero, and all display a data label showing the value zero.
People have learned that the #N/A error in a worksheet cell is often not plotted in a chart. In a formula, the function NA() returns this error.
=IF(A1=””,NA(),A1)
What #N/A does is prevent the rendering of a marker in XY and line charts: notice the lack of markers and data labels. It’s not a gap, but it’s pretty much the next best thing.
This doesn’t work as well in other chart types. The column and area charts place a data label showing #N/A at zero. For these chart types it would be better to use “” or even zero, and apply a custom number format that suppresses the display of zeros (a format like “0;-0;;”).
A pie chart will behave like a column or area chart: there is no visible point (wedge) but there is a data label showing zero or #N/A where the wedge would be located.
Summary
The least deceptive way to display an empty cell in a chart, is to treat it as a blank. In line or XY charts, it is generally acceptable to interpolate across the blank with a line, if there are markers plotted for every actual data value. In the vast majority of cases, it is not appropriate to treat empty cells as zeros.
To simulate an empty cell in a line or XY chart, use NA() in your formula to produce the #N/A error in the cell. This produces an ugly #N/A error in the cell, but this error suppresses plotting of a point, and the line spans the position corresponding to the error’s slot in the data range. It’s not as good as a gap, but it’s perhaps the next best thing.
In an area or pie chart, the best approach is to use the null string “”, in conjunction with a number format that suppresses the display of zero values.
For an area chart you have to stand on your head to create a complicated chart that simulates a vertical edged gap and provides appropriate category axis labels.
Alternatively you could rely on VBA to clear cells which need to be cleared, but this routine would have to run every time the data range is changed, and it would have to reintroduce formulas into previously cleared cells as well as clear cells where blanks are needed. Or it could do all of the calculations in VBA, eliminating the worksheet formulas.
Update
Microsoft recently launched the Excel UserVoice channel, and I proposed Give us a proper NULL() worksheet function. The suggestion has received 164 votes (as of 9 January 2016), I’ve had discussions with Microsoft Excel Product Group people so they understand how I would use this function, ind it is now officially listed as “Planned”. So Microsoft is listening, and hopefully soon we’ll get a function that will help with this in an upcoming update of Excel 2016.
Update 2
A new feature in Office 365 (and Excel 2019), Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.
Read about it in Plot Blank Cells and #N/A in Excel Charts.
Gerad Suyderhoud says
Nice summary. I’m glad to see this covered. Missing data often confuses people, and dealing with it is pretty important. The Excel workarounds aren’t exactly straight-forward either.
In the next version of Swivel, if there’s missing data between points, we connect them with a dotted line (in the case of a line or area chart).
Colin Bowern says
New to advanced excel charting – looking to create the gapped area chart shown above. Can you provide a little more detail in the steps? I’m in the Select Data Window and unsure how to create the secondary category axis.
Jon Peltier says
Colin –
How mean to hint at an approach and not to share it.
You can find an explanation at Area Chart with Gap.
Colin Bowern says
@Jon – Thanks so much! I’ll give it a shot this morning.
Debbi Barnes-Josiah says
Thank you, you dealt with exactly the problem I was looking to solve – Excel reading not-quite-blank cells as 0s. The NA() approach worked just fine!
Daryl says
I’ve been banging my head against the problem for upwards of 40 hours….that problem begin, needing a chart that grows as data appears based on formulas in those cells, without the chart treating the future values as zero and providing false data in the chart.
THANK YOU!
Jason says
A work-around I’ve just discovered to create the illustion of a line with a gap in an X-Y plot….
Use the method described above with the NA() formula to produce NA() for cells where you want a gap in your plot.
Then pad the data set so it is very dense at least in the non-gap areas.
Then create the plot with the style as “points” (without the connecting line).
If the points are all close enough together and the markers appropriately sized, they will produce the illusion of a continuous line in the non-gap area.
Jon Peltier says
Jason –
It’s probably easier, if you have to add a lot of extra points, to simply select the line segment in question and format it to use no line.
Jason says
If you have one or two gaps in one or two lines, I would agree with you.
If you have many gaps in many series, you would want something that worked more autonomously.
Ryan says
Thank you very much for this post, it helped me with the jam I was in.
Here is something I found:
If you don’t want #N/A to show up in your table (for presentation reasons) I recommend using conditional formatting.
– Click conditional formatting
– Then choose new rule
– Click “format only cells that contain”
– Click dropdown that has “cell value” and change to errors.
-Then click the format button and change the font color to match the background.
This gives the appearance that there is no value in the cell and your graph won’t pick up a zero.
Thanks again, and I hope that this work around might help you too!
Kim says
I found a work-around to the line-chart-gap-problem that got the job done for me. As John explained so well in this post, the #N/A works to not have excel plot the data points for missing data, but i didn’t want to have it interpolate a line between the points on either side of the missing data. Ill try to see if i can explain what i did (I don’t know how i can upload a workbook where i have set up a dummy chart, so if someone can help with that i can upload it). Here is what i did:
Lets say we want to build a line chart of a time series of 5 years. For the Main data series i bring in my data for each year with any kind of formula (index/match;getpivotdata etc.) Make sure to have an IFERROR in there to make sure that missing data is not plottet!. This line I plot WITHOUT a line color, so it only functions a a shadow series.
Then i set up 4 interval series consisting of only two data points per series (Interval1= year1 and year2; Interval 2 = year2 and year3; Interval 3 = year3 and year4; and Interval 4 = year4 and year5). The input to these series i simply bring in from my Main series, linking year1 of Inverval 1 to year1 or the main data series and the same with year2. But do not link more than just two points per interval series.
I then plot all the interval series with the same line color and type. If there is no missing data, it will look like a normal continuous line, even though it’s actually 4 separate line series. If there are missing data somewhere, in year 4 for example, the interval series affected (here interval 3) will not plot a line because the last point of its series is marked by an Error.
Finally i set up a series called Markers. This is only because i didn’t want to have data markers on the chart because i think it looks bettwer with only a line. But if there is missing data in year 2 or year 4, years 1 and 5 respectively will not plot unless we have a series to plot data markers on these years ONLY if the error on year 2 and 5.
I know this probably doesn’t make sense how i wrote it up but it’s acutally pretty simple to show in a workbook.
Jon Peltier says
Hi Kim –
Thanks for sharing your workaround. Of course, your method is essentially manual, and must be reapplied if the data changes.
If you have to fix the data manually, it might be easier to use just the main series, and manually format some line segments so they use no line at all. Like your method, this must be reapplied if the data changes.
Kim says
Hi John,
no it actually doesnt need manual updating as long as the time series is fixed (in my case 5 years). I have linked the data to a pivot table so it is dynamic, with the lines and gaps varying according to the data i select.
Kim says
Just to follow up on my last post, I have uploaded a dummy chart example to box.com. this may help illustrate what i did.
https://www.box.com/s/mk2hdfn8l9x0udn4ynxb
KDM says
Hello Jon,
I am using your advice of =IF(A1=””,NA(),A1) to avoid the zero result in a line graph. My graphed data series is based on a calculation, whereas the empty cell occurs in raw data. I had hoped I could use =if(A1=””, NA(), A1/30). Excel balks in trying to use a value [A1] as both a text string in one instance and numeric in the next. Is there a simple solution? Although I have chosen a fixed data set at the moment, ultimately my x-axis date range could go on indefinitely.
Jon Peltier says
Kelly –
Try again. =IF(A1=””,NA(),A1/30) works if A! is blank or contains a number. If A1 also may contain text, you need something like =IF(A1=””,NA(),IF(ISNUMBER(A1),A1/30,na()) to avoid a #VALUE error.
KDM says
That did it (with one more closing parenthesis, in case someone else needs it). Thanks so much for your help! I always come to your site first when I’m looking for answers.
Natasha says
I’d really like to know if there’s an option of making the interpolated data appear as a dotted line while all the true data appears solid… I’d really really appreciate help with this… :( I do not want to give false readings on my line graphs, but I also need a continuous line…
Jon Peltier says
There’s no such animal. If there were an option to show a different line style for certain data, then you could use the “no line” style.
Debbi Barnes-Josiah says
Jon – For Natasha’s interpolated data, it seems to me she can still highlihgt the point at the end of the interpolated part, and change the line style for that segment? It worked when I tried it.
Jon Peltier says
Debbi –
Sure, you can always manually reformat line segments, but then if the data changes, you’ll have to go back in and reformat again. There is no built-in way to handle this automatically.
Debbi Barnes-Josiah says
Yes, good point.
Natasha says
Thank you Jon, Debbi, Josiah;
The comments are much appreciated. I’m just being lazy and wishing it’d fill automatically. :(
-Natasha
Lane says
Great!
I replaced all blank cells with #N/A and got the lines connecting the points.
thanks
Felix says
Hi everyone,
Thank you Jon for this post. It shows that I am “up to date” with Excel, apparently.
Excel is very very deeply DISASTROUS at dealing with this, and it has always been so, I think. There is a total confusion between zeros, empty cells and errors. It seems Microsoft engineers do not understand the differences between the 3 !
Instead of creating a simple BLANK() or NULL() or EMPTY() function, or simply dealing with =”” the same way as an empty cell, they have spent years making new colours, shadows, WordArt and plenty of unuseful stuff… This is simply pathetic ! hooo !
But here is my problem:
I have formulas that do not always give a result. When there is no result, as discussed above, you have the choice between =”” or =NA().
On a (line-XY) Chart, the first is taken as a zero value, the second will be interpolated. In my case, I am forced to use =NA() to avoid false and confusing zeros appearing on the chart.
But when I try and calculate the average value of the range of cells that are plotted on the chart, =”” is considered as an empty cell and is not taken into account in the average value ! But the presence of NA() in the range prevents the calculation of the average value, which is also #N/A!
Bad !
Therefore, you can’t both plot correctly AND calculate the average value of a range of cells, except by duplicating the whole table: one with =”” when empty (for the average), the other with =NA() when empty (for the chart).
I have been fighting against this for years now.
Any idea anyone ?
Felix
Jon Peltier says
Felix –
I’ve been preaching for years that you need to maintain separate ranges for different purposes. There is the raw data or inputs. There is a range containing calculations, which links to the raw data. There is a range intended to be formatted for display (and you may need two display regions, one optimized for on-screen, the other for print). There is also a range for use as input to any charts (and again, certain charts may have different data requirements, in which case you’ll have multiple chart source data ranges). Depending on the capabilities of my users, I might show only a table for their own inputs, a table for on-screen display, and any charts. The raw data, calculations, and chart source data go onto hidden worksheets.
This is more work to set up, but if you do it well, then it is hardly any additional work to maintain.
Felix says
Dear Jon,
Thank you for your reaction, although a strongly disagree with you ! You can’t justify Excel’s weaknesses and bugs (inthis case the inexistance of an “=EMPTY()” function) by the false and non-existing need or benefit for the customers to “separate ranges for different purposes”. Have you you been sponsored by Microsoft to give this reply ?
Do it if you like, but Excel should not force customers to work in a certain way. For instance, I have been working on “huge” data sets where duplicating data is simply not an option. Also, writing formulas like “=IF(X2″”;X2;NA())”, and this is the simplest case, is both time consuming and difficult to read, rather than “=X2”.
By the way, I have found a solution for my problem: AVERAGEIF(range,”<999999999") will ignore #N/A values, so you can calculate the average in a range that is suitable for a graph plot, but again it is not at all elegant and nearly impossible to read !
Best regards
Felix
Best regards and please transmit my opinion to your boss at Microsoft.
Felix
Jon Peltier says
Oh please. I have been advocating for a BLANK() or NULL() function from Microsoft for longer than you’ve probably been using Excel. I don’t have a boss at Microsoft, but I do know program managers on the Excel product team. Unfortunately products like MS Office sell more licenses by introducing fancy new features than fixing bugs and shortcomings.
The use of separate ranges for different purposes would be a best practice (as long as the data is all linked to an original source) even if we didn’t have the issue of non-blank blanks. The raw data is a poor format to try to read; a pivot table is great for the analyst, but is often not quite right for chart data and definitely isn’t good for your manager to read. You should even use differently formatted ranges for tables intended for printed reports than for web pages.
I’m glad you’ve discovered a solution to your problem that you like. I prefer the solution I suggested, but to each his own, eh?
Felix says
Dear Jon,
Ok, thanks. I like to share opinions like this.
Congratulations if you’ve been advocating =BLANK() for 20 years !!! Can’t remember exactly when I started using Excel (Excel seems to be about 20 years old according to Wikipedia), but I was doing some complex stuff 15 yeas ago. I also remember working with Lotes 1-2-3 probably before Excel existed or became widely distributed.
You’d hope that Microsoft, selling 10^7 to 10^8 copies of Excel worldwide, would be able to do both: developing “new” features (+test them) AND fix some “old” bugs, instead of introducing new ones. But that’s market reality…
It is a shame that there is no serious alternative to Excel, as it would induce some healthy competition for quality.
You’re right, everyone can and should use Excel the way he finds most appropriate for his own tasks.
I often end up “bypassing” bugs, incoherent behaviour, shortcomings and missing features, as Excel has a lot of flexibility. But in most cases, the solution is poorly readable, often time consuming, not very robust and inelegant.
Best wishes
Felix
Jon Peltier says
Well, no, I haven’t been advocating blank for 20 years, but I have for more than ten (this is my 12th year as MVP), and I first used Excel in version 0.99 on my old Mac Plus. That must have been before the end of 1992, because in early 1993 my company standardized on Windows. When we switched to Windows, my colleagues were using 1-2-3 in a DOS window in Windows 3.1. I hated either 1-2-3 or the DOS environment, or both, so I got myself the first Windows version of Excel, and it’s been downhill since.
Seth says
Well done! Perfect work-around and the message was clearly communicated in moments; thank you.
George says
John (and Felix):
I, too have been fighting the way “Function()”s and charts handle =””, =0, =NA() real blanks and the like. I also like to do as Natasha suggests and SIMULATE a line with closely-spaced markers.
I prefer to do everything on the sheet itself (though I’ve finally broken-out VBA to fix some things), and it is essential that my spreadsheets be portable and automatic (so other ppl can paste new data). That pretty-much rules-out add-ins.
One suggestion I have that I think might be helpful, and applies to the idea of “separate ranges for separate purposes” is to define a little array of “Flag Values” (similar to “futzfactors”) that the formulae can refer to (rather than encoding the formulae with “=NA()” or whatever directly). Each formula (or family of formulae) refers to a different flag, so that you can try the various alternatives to see the effects.
Also, I may win the prize for the most inappropriate use of Excel: I wrote a decoder/parser for a proprietary I2C-like serial bus! (linking to oscilloscope internal data files). It’s still in use, years later, because it actually works, and is eminently portable, unlike the hardware decoder box that was supposed to replace it. I just worry that, ten years hence, a big-hat will see it and “You’re using a SPREADSHEET for that? Find-out who initiated that, and BRING ME HIS HEAD!”
GeorgeIsLegend says
@ Jon Peltier, here is a way to use pretty much any function and ignore the #N/A or any other errors (switch ISNA to another is function like ISERROR). It utilizes an array formula so you have to press control+shift+enter after you finished typing the formula bar( cursor must be in the formula bar).
Lets assume you want to create a graph that plots a line from a data series, which contains zeros or blanks. These blanks or zeros are as a result of some calculation that has been performed to obtain the data in the first instance.
The charting tool takes into account anything that is either a number (zero) or a nonnumber (i.e. a blank). However it does ignore Excel generated error messages, such as #N/A.
So, lets assume, you are getting your data from cells A1 through A5 in worksheet Sheet2. Your chart data resides in cells A1 through A5 in worksheet Sheet1.
The formula you use in cell A1 in Sheet1, to ensure you get the #N/A, so that the charting tool ignores it as a value (zero or blank) is:
=IF(Sheet2!A1=0,NA(),Sheet2!A1)
The NA() function places a #N/A into the cell. This method of clearing a non value or a zero in the cell so that the charting tool ignores it is useful especially if you are trying to superimpose a trendline.
One small caviat though. Any column that has an error value such as #N/A will not be processed if you try and apply a numberic function to it, such as SUM, or AVERAGE. The result of this will be #N/A.
However, one of the options you have to get around this is to check for every instance of #N/A, disregard it and SUM (or AVERAGE or whatever) the rest of the entries.
This can only be achieved if the formula is constructed as an array type. For example, if in the above example, you have numbers and #N/A’s in cells A1 through A5. The formula would be:
{=SUM(IF(ISNA($A$1:$A$5),0,$A$1:$A$5))}
Here, the ISNA is used to test cells to see if the entry equals #N/A and if it does, to ignore it in the SUM function.
Note the curly {} brackets which formats the formula as an array. You create an array formula by creating the formula and then hitting CTRL-SHIFT-ENTER.
You could of course use the ISNUMBER option too, where the formula construct would look like:
{=SUM(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))}
or if you wanted and AVERAGE:
{=AVERAGE(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))}
Jon Peltier says
George –
This approach is great for cases when a range containing blanks/errors is used to calculate subsequent values. However, it in no way helps provide a gap in a chart the way a blank cell can do.
Felix says
@GeorgeIsLegend & @Jon Peltier,
Hello and thanks to George for this workaround (actually nicer that mine !).
But please keep in mind that if there were a few more (I almolst wrote “any” :-) ) clever people at Microsoft, we wouldn’t be talking of that matter at the first place. Would we ?
And when reading your, or even SOMEONE ELSE’s formulas, {=AVERAGE(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))} is far less readeable than =AVERAGE($A$1:$A$5), which looks pretty clear to any user (not only recognized experts like Jon) and would simply work if Excel were a good piece of software.
Jon, you once said that you “…know program managers on the Excel product team”. What are they actually doing ? Creating new bugs ? Have they lost control of their code ? Could you pass this message to them ?
Thanks again and best wishes.
Felix
Jon Peltier says
Felix –
1. Yes, =AVERAGE($A$1:$A$5) is easy to understand. But there is a convention that an operation carries an error in a predecessor cell into the result. If this didn’t happen, then a user may not realize that a cell off-screen contained an error. So =AVERAGE($A$1:$A$5) would show an error if one occurred in $A$1:$A$5. Hence the need to write formulas to ignore errors. Anyone with more than a passing knowledge of Excel should understand or be able to figure out something like {=AVERAGE(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))}. Complaining about this won’t produce much sympathy.
2. I’ve heard it said that new features sell software, bug fixes do not. Now that I have my own line of software, I understand this. Microsoft does try to eliminate bugs, but their resources (even for Microsoft, resources are limited) are focused on the new things, not the old. Things like tables and pivot tables, which were new once and which continue to have major enhancements. PowerPivot. The enhanced charting interface introduced in Excel 2013. And many supplementary functions for statistics and other analysis that address shortcomings in older functions (which must be retained for compatibility).
3. When modifying code, it is important that no changes in one place have unintended consequences elsewhere. In small software packages like mine, it’s not too hard to control this, but the complexity of code is proportional to the number of lines of code squared (or raised to an even higher power). Office 2007 had massive changes, and much of the work in Office 2010 and 2013 was dealing with the unintended behaviors introduced by these changes, in addition to the new things listed above.
Martin Klos says
I have also struggled with this issue of charting with gaps for blanks. Today it dawned on me another way: always chart from a ‘shadow’ range. To illustrate the idea, here is some crude VBA to copy a range which has formulas to a shadow range with values only.
Cells with a ‘blank’ value become empty (true blanks) in the shadow range.
The trick then is to generalize and automate this. One way automate it would involve hooking the events Worksheet_Calculate() and Worksheet_Change() to automatically refresh the shadow range when the range with the formula are modified.
Hope this helps. Good luck.
Marty
Jon Peltier says
Marty –
There are two useful techniques in your comment:
1. I often use some VBA to clean up data in this way.
2. I often use shadow ranges (or “staging areas”) to feed my charts
Stuart Harder says
This entire discussion has been quite helpful. I work with school-aged students and write programs to help improve behavior outcomes for anger and cooperation issues. In my line of work, the chart must be properly structured to ensure that connecting lines do not cross over gaps in the data. For example, I would not want connecting lines crossing weekends or absent days. Interpolation implied by crossing the gap is quite meaningless as there would be no student present to generate countable behavior.
I use an XY scattergram and the NA() function solves a number of problems but not the crossing the gap issue. However, for those of you who use semilog plots on an XY scattergram, the NA() function inhibits crossing the gap. A minor irritant presents itself every time you enter the chart sheet with warning that you cannot display ‘0’ values on a log scale. Not so hard to hit OK and dismiss the dialog box.
In the long run, having a NULL or BLANK function would be ideal.
Fern says
I’m working with a simple line chart where the y-axis shows dollar amounts and the x-axis shows dates. My input table has an empty row which I would like to show up as a gap on my chart. I’m using Excel as part of Microsoft Office and Business 2010. In earlier versions of Excel I could achieve this with no problem. For some reason, though, I can’t seem to get Excel 2010 to do this. When I do “Select Data”, press on the “Hidden or Empty Cells” button and select “Show Empty Cells as Gaps” there is no change in my chart. It’s as though the date axis format overrides everything, making it impossible for a gap between, say, September and October. Is there any way around this?
Jon Peltier says
Hi Fern –
I just did a little investigation. If you insert a blank row between September 30 and October 1, the line chart will not show a gap. If you insert a row, keep the value column blank, but put September 30 into the blank cell in the date column, you will get your gap.
Fern says
Since I’m only interested in displaying mmm-yy on the x-axis, I’ve listed my dates in the data table as the first of the month, so 9/1 displays as Sep 13, 10/1 displays as Oct 13, etc. Before contacting you, I had tried listing 9/1 in the row that included values, then 9/1 again in the date column of the blank row, then 10/1 in the next row containing values. This produced a gap in the chart, but between Aug 13 and Sep 13 rather than between Sep13 and Oct 13. Based on what you said, I’ve just tried putting a date between 9/1 and 10/1 in the date column of the blank row and I get my gap between Sep and Oct. It works if I use anything from 9/2 to 9/30 in that cell. Thanks for nudging me a little further down the right path!
mihhon says
great on NA() , thanks
matt says
Thanks a million, you have saved my data and all my hardwork! Now I can continue thanks to your work around. Thanks!
Anonymous says
I have a table for July 2013 to June 2014 since December to June does not have any value as yet, the graph is plotting at ). How can I tell excel to stop plotting when there is not value?
cynthia says
I need to know how to tell excel to stop plotting when there is no value for the formula.
Jon Peltier says
Reread the section entitled “Simulating Empty Cells”.
Do your formulas look something like this:
=IF(something,Value,””)
The “” looks like a blank, but it’s text, to which Excel assigns a value of zero.
To prevent plotting this text as zeros, change “” to NA().
cynthia says
I am doing a cumulative table. When I copy the formula the figure for november is carried through june 2014. How do I tell excel not to put the figure in the december through june cells unless dataa in in the december to june cells. The december through June data is in another worksheet.
Also, I would like to put in a YTD cell the last cumulative data. for example in Novemeber, the YTD cell would have the november cumulative, in december the YTD cell would have the december cumulative, etc.
cynthia says
can you please fix this formula for me. (IF(‘Mass Market’!H15=0,””,’Mass Market’!H15)+G13)
Jon Peltier says
You mean this?
=IF(‘Mass Market’!H15=0,NA(),’Mass Market’!H15+’Mass Market’!G13)
cynthia says
=IF(‘Mass Market’!H15=0,NA(),’Mass Market’!H15+’Mass Market’!G13)
Cell G13 is the cell in the cumulatie spreadsheet sheet and not in Mass market..
G13 is for October and theh formula is in G14. The data is entered into the Mass Maket worksheet.
I am tyring to put the formula into cell G14 without the data from cell G13 copied unless there is data in the Mass Market H15 cell.
Jon Peltier says
So remove the sheet name in front of G13 in the formula:
=IF(‘Mass Market’!H15=0,NA(),’Mass Market’!H15+G13)
Anonymous says
=IF(‘Mass Market’!H15=0,NA(),’Mass Market’!H15+’Cumulative target’!G13)
Even with the above formula i still get ‘#value!’ in the cell……..
Jon Peltier says
If you copied and pasted the formula from the blog comment, change the curly single quotes around the sheet names to regular old straight single quotes.
If that doesn’t fix it, what does this formula return:
=’Mass Market’!H15
Or this one:
=’Cumulative target’!G13
cynthia says
Both of thm returned ‘#NAME?’
They both have the straight single quotes.
I am hoping that your understanding is that I am working with 2 worksheets. ‘Mass Market’ is the data input sheet. I am having a cumulative total of the data from ‘Mass Market’ in hte cumulative worksheet.
So, when I put the data for Nov in the ‘Mass Market’ worksheet, i would like a formula in the ‘cumulative target’ worksheet to get the data for Nov from the ‘Mass Market’ worksheet and add the data from Oct in the ‘cumulative target’ worksheet to make a cumulative total for Nov in the ‘cumulative target’ worksheet. The months are running horizontally in a row.
Jon Peltier says
I understand what you’re trying to do, a conditional cumulative sum covering two worksheets. I don’t know why you’re getting #value and #name errors. I especially don’t know why you got a #value error in the formula I gave you, and #name errors in the more recent formulas.
cynthia says
Thanks so much.
I re-keyed the formula and it works perfectly.
Also, I would like to put in a YTD colum that would pick up the current’s month YTD. for example in Novemeber, the YTD cell would have the november cumulative, in december the YTD cell would have the december cumulative, etc.
I do not know how to start a formula for that as i can generate a formula that will get the data from a particular cell, for example, for the month of Nov; but how do I tell excel that if november is not blank to go to the next cell, Dec????? . Can you please help me.
Jon Peltier says
Cynthia –
Surprise, surprise. I suspected as much, which I hinted at with the curly quote comment.
YTD is just the sum of the per-month column, right? Any blank months are not included. So if the monthly values are in B1:B12, YTD would be =SUM(B1:B12).
Alteernatively, YTD is the maximum value f all the monthly YTDs. So if your running monthly totals are in C1:C12, YTD would be =MAX(C1:C12).
Cynthia says
Thanks so much Jon. You are a God send.
The formua with “max’ worked perfectly.
I am not familiar with that formula; so thanks again.
Have a happy holiday……
I might need to contact you again. Many thanks.
Rich says
Hi Jon – have read this article with great interest, but I don’t see the specific issue I’m having addressed. I have a bar chart that is pretty simple, but I have some cells that are blank and some that contain a value of zero. I want to show data labels if the value is zero, but suppress the label if the cell is blank. Clearly I could force this to happen via VBA, but it would not be as elegant a solution as I would like. I’ve tried various custom formats for the data labels, but nothing seems to work as I would like. Any recommendations would be greatly appreciated!
Cynthia says
Hi Jon,
how do i add these 3 cells that contain these formulas:=IF(‘Crosscutting Programs’!K47=””,””,’Crosscutting Programs’!K47+’Cumulative Target’!J114), =IF(‘Crosscutting Programs’!K47=””,””,’Crosscutting Programs’!K47+’Cumulative Target’!J114)?
Jon Peltier says
I don’t understand…
Cynthia says
The formula are in 3 different cells.
I need to sum the cells.
When I do, I get a value error.
eg. sum (a1+b1+c1) Cells a1,b1, and c1 contain the ‘if’ functions that I sent previously.
Jon Peltier says
Cynthia –
You can’t add text (the “” is a short piece of text) and numbers using mathematical operators.
You can use SUM() and include both text and numbers in the parentheses, and the text is treated as zeros.
You could also put 0 in the cell instead of “” if the test is true.
Jon Peltier says
Rich –
I just tried this with a column chart. If the Y value cell contains a zero, the data label says “0”. If the Y value contains any text, including “”, the data label says “0”. If the Y value cell contains the #N/A error, the data label says “#N/A”, because the trick about #N/A suppressing the point doesn’t work for column charts. If the Y value cell is totally blank, the data label is also totally blank.
Cynthia says
Jon,
Sum() is not hleping me; I am still getting #value! in my cells.
Are there any other recommmnedations?
Jon Peltier says
Cynthia –
You must have something more going on than I know about.
Change the “” in your if statements to zeros, and either SUM or simple addition should work.
Cynthia says
Jon,
The formula in J117 is =SUM(J114+J82+J50).
This give me this is a response of ‘ #value!”
J 114 has =IF(‘Crosscutting Programs’!K47=””,””,’Crosscutting Programs’!K47+’Cumulative Target’!J114)
J82 has =IF(ISBLANK(‘Crosscutting Programs’!J10),””,IF(‘Crosscutting Programs’!J10>=0,’Crosscutting Programs’!J10+’Cumulative Target’!I86))
J50 has =IF(‘Mass Market’!J60=0,””,’Mass Market’!J60+I50)
Can you please help with the formula in J117.
Thanks
Jon Peltier says
I get it. You’re getting an error from the + operations inside the SUM(). Try this:
=SUM(J114,J82,J50)
cynthia says
Jon,
Thanks for the last help. I need your help again.
I have a row, lets say D7 to My.
I want to tell excel that whenever a number is entered into the cell to multiply the number by 611.
what formula is used for that.
Help!!!!!!! thanks
Jon Peltier says
There must be something more to it than just
=A1*611
or you wouldn’t have asked.
cynthia says
Jon,
I am converting acreage-foot (AF) to kilwatt hours.
YOu know what, you are correct. I looked straight over this one.
The number to be multiplied by the 611 is in the AF cell. Sorry to bother you. I am doing too much.
Cynthia says
Jon,
I got the formula below from one of your responses.
1. Try again. =IF(A1=””,NA(),A1/30) works if A! is blank or contains a number. If A1 also may contain text, you need something like =IF(A1=””,NA(),IF(ISNUMBER(A1),A1/30,na()) to avoid a #VALUE error.
I am getting the #value error with my formula so I tried to use your response with my formula but i get that I have entered too many arguments for the function.
Here is my formula: =IF(‘Cumulative Target’!K140=””,NA(),’Cumulative Target’!K140)/1000000
I modified it to this: =IF(‘Cumulative Target’!K140=””,NA(),IF(ISNUMBER(‘Cumulative Target’!K140),’Cumulative Target’!K140)/1000000,na()))
Can you please help.
Thanks
Cynthia
Keith says
I am using your ‘Stacked charts with vertical separation’ method and cells in my data with #n/a error value are being plotted as zeros, where as I dont want them plotted. The #N/a seems to work with other charts but not on my stacked chart.
Any help would be greatly appreciated
Jon Peltier says
Keith –
#N/A suppresses plotting of a data point, but the chart still knows it’s there. The category axis reserves a slot for the unplotted point. In a column or bar chart with data labels, a label is positioned as if a zero value were plotted instead of #N/A. In a line or XY chart with points connected by markers, a line is drawn connecting the point before the #N/A with the point after the #N/A.
Keith says
Thank you for the response. The data I am plotting on a stacked line graph with vertical separation is as follows
ESE Blank 1 Company Blank 2
Jan-14 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Feb-14 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Mar-14 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Apr-14 49 51 47 53
May-14 #N/A #N/A #N/A #N/A
Jun-14 #N/A #N/A #N/A #N/A
Jul-14 #N/A #N/A #N/A #N/A
Aug-14 #N/A #N/A #N/A #N/A
Sep-14 #N/A #N/A #N/A #N/A
Oct-14 #N/A #N/A #N/A #N/A
Nov-14 #N/A #N/A #N/A #N/A
Dec-14 #N/A #N/A #N/A #N/A
If I plot ESE and Company data as 2 series on a standard line graph it works fine and just plots up to April and then stops as I would expect.
If I plot all 4 series on a stacked chart with vertical separation using your instructions it plots upto Dec-14 with the #n/a’s as 0s.
I cant work out why?
Jon Peltier says
Keith –
Yeah, about that. Stacked line charts are a bit different. Since each value of a series depends on there being a corresponding value in the series below, the behavior that omits points at these values is omitted. Same change occurs with blanks instead of blanks.
It doesn’t matter, by the way, whether you use only two or all four series, if they’re stacked
What happens is that behind the scenes, Excel changes the “Plot blanks as” from Gap to Zero in stacked charts. If you don’t change it back when converting the chart to an unstacked line chart, you will still have zeros instead of gaps.
Lionel Gunn says
Dear Jon
I came across a webpage showing how to deal simply with this problem, but in Excel 2007. It seems to have disappeared in Excel 2010. Do you know anything about this?. Regards, Lionel Gunn
http://www.excelforum.com/excel-charting-and-pivots/526803-discontinuous-lines-in-series-due-to-gaps-in-data-points.html
Jon Peltier says
The behavior is the same in Excel 2007, 2010, and 2013. It deals only with blank cells, that is, completely empty cells. Cells that have a formula that returns “” are not empty, they contain a formula, even though the cell looks blank.
Ron Roth says
Thank you for the NA() function suggestion. I was working on a work spreadsheet and needed to not plot blank cells. I looked all around the Internet for a solution and found your blog. Thanks for saving me a bunch of time and a nice solution that I did not know about.
For those who like nice formatting — i always set up my spreadsheets with a defined print area. Anything I don’t want printed (like my rows of data that I wanted plotted with the NA() function) — I place outside of my print area so the user of the spreadsheet cannot see it. That way I get plotted the stuff I want and the user is not confused. Win – win.
Cori says
You just saved me a ton of frustration. Thank you!
Kevin says
Brilliant article , the NA() workaround solved a problem with a Trendline. Until recently I only used Linux and “” meant nothing there and everything worked, you could also assign a cell a value from another cell which I could not use as a workaround here.
Charles Ehrlich says
This is such an old thread and MS still hasn’t bothered to fix this (yet). Hard to believe. My first use of Excel was on the Mac in 1998. Before that I used an Excel lookalike / precursor on the Apple IIc, called AppleWorks in 1984.
One workaround that I don’t see mentioned here, albeit very similar to NA() suggestions, is to use the special value #N/A (no quotes). Functions like ISNA(A1) return TRUE when A1 contains the #N/A “value”.
And, one can use #N/A in an inequality statement to help with summing ranges with =sumif(A1:A10,”<>#N/A”)
If any of the cells in the A1:A10 contain #N/A they are not =count()ed or =sum()ed.
Whereas, using =NA() does not work unless one uses the more cryptic =sumif(A1:A10,<999999) as suggested by someone else, but that WAY IS much less readable. #N/A also works with plotting in the same way that =NA() does. =if(A1=TRUE,99,#N/A) also works as expected and =sumif(A1:A10,"<>"&#N/A) will ignore those values from the if functions in the sum range, as expected.
Thanks for this thread!
-Chas
Jon Peltier says
We’ve been asking Microsoft for a long time, but now they’re listening to us users. I posted this suggestion, Give us a proper NULL() worksheet function on the Excel UserVoice site, and Microsoft has contacted me to get more information about how this NULL() function would work. Last I heard it’s in the planning stages.
Wendy says
Hi I’ve added an empty cell in order to put a gap between my column bars. (Overlapping thing doesn’t work with my data because it is only 1 series but with different categories). Unfortunately now my axis labels go haywire. I could not insert a photo here else I would do it.
Jon Peltier says
Wendy –
Could you post the picture on imgur.com or one of the other upload sites?
Argo says
I agree. I need a formula to return a gap in a line chart. Not a zero, and not an interpolated line. Sure, I can go in and delete the formula to get a gap. Or some other manipulation that might be inaccurate when the data change, or for when I want to copy the range to another table. I could also just get out some graph paper and a ruler….
bs0d says
One issue I’ve had that is similar to this topic is the interpolated plotting of values with date gaps. That is, assuming a typical line chart — Excel will plot a continuous line for a chart series where the source data skips dates and has a non-zero value. Regardless of the “Hidden and Empty Cells” chart setting, you see a line connecting across dates.
To fix, it seems you have to fill in the dates. Then the features within hidden and empty cells work as expected. Assuming I’m not missing anything here with the functionality of the charts- I’m thinking about what’s the best approach to fill the dates. You could use VBA to loop, but it seem inefficient. If your data comes from a database, I think there maybe a query workaround. I don’t have it figured out yet- but I think you can create a dates table, and do a left join on the data to include all dates from the dates table, and those that match the data table. Then you’d need to limit to the min and max of whatever you’re querying so you don’t get the full time series of the dates table. Anyone do anything like this? Or have a similar workaround?
Jon Peltier says
There are no gaps in the data, that is, no empty cells. Each X and Y cell has a value, even if they aren’t populated as continuously as you’d like. So the chart is working the way it should with the data you started with. And as you say, you need to fill in dates with blank value cells if you want the lines not to connect across these dates.
If I needed to fill in dates like this, I might put all my dates into the first column of a new table, and in the next column put a LOOKUP formula using an exact match so it returned #N/A for those missing dates. Then I’d use Goto (Ctrl+G) > Special > Formujlas > Errors to select and delete these #N/A values.
TRobinson says
I have created a spreadsheet with dynamic charts utilizing named/defined data sets to automatically update charts as inputing data. Everything works perfectly as long as there is data for every included cell. As soon as any data point is unavailable on a given date, the charts no longer graph the correct range. I have tried IF formulas including “”, #N/A and … to replace empty cells. I have tried simply deleting the formula in cells without available data. The outcome is the same: however many blank cells are in the data set, the chart decreases the number of data points included on the chart by an equal number, still including the blank cells in the charted data and eliminating data points at the end of the desired range.
I am in over my head and the answer to my question may already appear in previous conversation, but I didn’t recognize it as such. For that, or any strange use of excel-related terminology, I apologize.
Jon Peltier says
If you’re using some kind of OFFSET and COUNT formula to provide a dynamic range for charting, the COUNT will count the numeric values in the whole range, and give you that many cells in the dynamic range, but it starts at the top and counts out that many, without skipping the uncounted cells. You need to get NA() into the values and use the whole range; in a scatter chart, the #N/A will not be plotted.
Lori says
There is now an option to display #N/A as empty in a recent Office 365 update:
https://support.office.com/en-us/article/display-empty-cells-null-n-a-values-and-hidden-worksheet-data-in-a-chart-a1ee6f0c-192f-4248-abeb-9ca49cb92274
A trick to plot gaps is to convert #N/A to a NaN value via a formula like:
=IF(ISNA([range]),NAN(),[range])
Where (using Excel-DNA or similar):
NAN() = double.NaN
Or else from VBA:
NAN() = GetIEEE754SpecialValue(2)
(https://stackoverflow.com/questions/885994/how-do-you-get-vb6-to-initialize-doubles-with-infinity-infinity-and-nan)
Jon Peltier says
Lori –
That’s interesting. I was going to say, in light of the recent ability to plot #N/A as a gap (empty cell), we don’t need anything fancy for NAN. But I discovered a shortcoming with the new “Treat NA as Empty Cell” feature in Excel, and I’ll have to see whether it will help with that situation.
Jon Peltier says
Lori –
I experimented with the routines in the Stack Overflow post. I was able to get various special values in VBA, but there is no way to use them in the worksheet or as chart data. They break the chart or even crash Excel.
lori says
Jon – does this link work for you?
https://github.com/lhem/excel/blob/master/SpecialNum.xlsm
NaNs lie outside the standard Dbl range (+/-):
2^-1022 < Dbl < 2^1024
2^1024 < NaN < 2^1025
The largest negative NaN is treated as empty in charts
Jon Peltier says
Lori –
That’s great, yes it’s working as you described. Many thanks. Now I can try to make sense of it.