Today, Mike and Bill address the "Drop to Zero" problem in a Chart. This time, because it is a Stacked Line Chart and the =NA() trick isn't working here. Follow along with Episode #1712 as Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen work out solutions to this 'Drop to Zero' issue. Also, be sure to visit Episode #1704 for different approaches for single Line charting).
Dueling Excel Podcast #123...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
...for Chart and Graph information? Use Excel 2013's radically revamped charting and graphing tools to communicate more clearly, powerfully, and quickly... so you drive your message home, and get the decisions and actions you're looking for! Charts and Graphs Excel 2013 by Bill Jelen Charts and Graphs Excel 2013
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #123...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
...for Chart and Graph information? Use Excel 2013's radically revamped charting and graphing tools to communicate more clearly, powerfully, and quickly... so you drive your message home, and get the decisions and actions you're looking for! Charts and Graphs Excel 2013 by Bill Jelen Charts and Graphs Excel 2013
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun, this is our episode 124 - Stacked Line Chart Drop to Zero!
Hey Mike, today's question sent in, Patricia from CFO magazines says "I understand how to fill the empty cells with #N/A if a formula is present.
But in my chart, there's no formula or data in the future cells - they're just blank." Patricia goes on "The 'hidden or empty cells' choices in the Select Data area default to treat a zero, and everything else is just greyed out." Well that tells me something about Patricia's chart, because normally in a regular chart just, a regular Line chart here.
If we go to Design, Select, Data, Hidden and Empty Cells, we could change those two gaps, and the future cells would go away, I'm going to undo that, Ctrl+Z.
But in this chart, this chart is not a regular Line chart, it is a stacked Line chart, it's the stacked item there.
And when we go to Select, Data, Hidden and Empty Cells, she's right, everything else is greyed out.
Alright, so you know, my normal trick is, well hey, let's just fill with =NA() , Ctrl+Enter, and it fixes the chart as I would expect, but here it does not fix the chart.
Oh boy, so in a stacked Line chart, it's not following the rules.
The common trick that we all use.
Now, Mike before I see if you have a solution for this, let's just talk about these #N/A's.
You know, people say "Well, I don't like the #N/A's because they print poorly." And you know, if I do Ctrl+1 and use the normal trick of ;;; to hide the data, that would hide numbers, but it doesn't hide the #N/A's.
I still have a trick though to prevent them from printing, we come here to Page Layout, click on Print Titles, which is just a great shortcut way to get to the Page Setup.
Sheet tab, Cell errors as: Displayed, can change that to Blank, or even --, let's just go with Blank, click OK.
Now Print Preview, and then when we print this, you see that those future months will not print as #N/A's, they print as Blank.
But OK, so this whole issue is moot if you have a stacked Line chart, because there doesn't appear to be a good way to make those future months disappear in a stacked Line chart.
Mike, you have a solution for this?
Mike: Thanks MrExcel!
Hey, this is a great question here, we want to be able to enter numbers here, and have labels and numbers appear on the chart.
Now I'm sure there's a much easier way to do it than the way I'm going to do it.
I'm going to do it the old stand-by way, create a formula that creates a dynamic range, and then enter it into a defined name, and then put the defined names into the chart.
Now I'm going to go ahead and use the INDEX function to look up the last cell reference in a range, and then I'm going to build a dynamic range formula.
So I'm going to start with INDEX, now INDEX usually looks up a thing, like a number, or word, or something like that.
So I'm going to highlight the entire potential range, hit the F4, comma, I want to look up the last number, so in the row_num argument, I'm going to say "Hey MATCH, find the relative position of the last number." Well, I'm going to use approximate match with a really big number, so 9.99E+307, that's the approximation of the biggest number Excel knows.
And then I'm going to go ahead and use this range right here, so what it will do is, it'll just keep looking, and when it can't find anything bigger, it'll get the last one, and that's the number it will return using approximate match.
Now, if I enter this right here, of course, INDEX looks up the last number, right?
But check this out, I'm going to put the INDEX function into the context of a cell reference, and it will look up the cell reference instead of the item.
Now I'm going to click on that B3, and put a : here.
You know, someone showed me how to put a : in a formula like this, and not get that extra B3, but I totally forgot what that trick is, maybe someone will post a comment there.
But I'm going to lock that now, check this out, that : says to the INDEX "Hey, I don't want you to look up the thing, I want you to look up the cell reference." In essence, INDEX is in the context of a cell reference.
If I highlight this and hit the F9, it won't show me the cell references, but sure enough, it'll show me the values, if I start adding values out here, this thing will expand, Ctrl+Z.
I'm going to copy and then Ctrl+Shift+Enter.
Now I need to amend this for the East, I actually want the lookup range, not that, I want the cell reference to be in 2, so I'll put a 2 here, and 2, 2.
So I'm still going to base the MATCH for all three ranges on the last row, so it's not until we put in the last number that the chart will adjust.
Alright, Ctrl+Shift+Enter, and then I'm going to paste this here, and change this to 1, so the lookup range is changing, even though it's going to determine the position down here, it'll get the value from up there.
Now I'm going to have to do this one by one, into the Define Name box.
So I'll start with the months, Ctrl+C, Ctrl+F3 opens up the Name Manager.
I'm going to say New, and I'm going to call it DMonth, and down here I'm going to paste, Ctrl+V.
Now I'm going to click OK, and immediately click this Collapse Down button to check and see if the formulas work.
And I'm going to close, Ctrl+C, and Edit mode, Ctrl+F3, New, and I'm going to call this DEast.
Oh look, it's got the DEast there, so I'm going to come down here, Ctrl+V, click OK, check, Esc, close.
And then I'm going to come here, Ctrl+C, Esc, Ctrl+F3, New, and look at that, I didn't know that, it's somehow picking that up, that is so cool, click OK, check, alright, so we have three names.
Now I'm going to come over here, and we have to go up to Design, Select, Data, and very carefully I'm going to start with the edit.
I'm going to do each one of these, and the trick is, is that you have to highlight just the cell reference in leave the sheet reference.
I'm going to hit the F3 key to paste my name, and this is DMonth.
Now notice sheet reference and Define Name, I'm going to click OK, but I'm going to immediately click it to show what happens.
It actually puts a workbook reference in, so that's supposed to happen.
Alright, so now I'm going to do this to West, highlight very carefully just the cell reference, F3 to paste a name, DWest, click OK.
So now I have East, edit very carefully, highlight those cell references, F3, DEast, click OK, click OK.
So now, as I add down, if I add the first bit of data here, it doesn't do anything, but then I add this one down here, and BOOM!
It is working, and it picks up Jun.
Alright, throwback to MrExcel!
Bill: Wow Mike, that was brilliant, =B3:INDEX, and the INDEX that returns to cell reference.
I've got nothing, I mean, well OK, this is just so cheap.
I want to take this data here, now in this case, both are stacked Line charts, both pointing at that data.
I want to take this data, I want to right click, I want to right-drag to a new location.
In the new location I'm going to say Link Here, that's a great way to create a whole bunch of formulas that point up above.
I'm going to fake the West series, and it being West+East, and we'll copy that across.
Then I'll take this chart, and make this chart use the new fake data, which initially is incorrect.
But then we'll go to the Chart Tools, Design, Change Chart Type, and change to not a stacked Line, but just a railroad Line with markers, and we get the same scale and same data that we have in the original chart, but without the drop to zero.
The drop to zero is just part of the stacked Line chart, so by changing the underlying data to do what a stacked Line chart is doing, we get the answer.
I don't know, that's really, really cheap.
Alright well hey, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and Excel Is Fun!
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun, this is our episode 124 - Stacked Line Chart Drop to Zero!
Hey Mike, today's question sent in, Patricia from CFO magazines says "I understand how to fill the empty cells with #N/A if a formula is present.
But in my chart, there's no formula or data in the future cells - they're just blank." Patricia goes on "The 'hidden or empty cells' choices in the Select Data area default to treat a zero, and everything else is just greyed out." Well that tells me something about Patricia's chart, because normally in a regular chart just, a regular Line chart here.
If we go to Design, Select, Data, Hidden and Empty Cells, we could change those two gaps, and the future cells would go away, I'm going to undo that, Ctrl+Z.
But in this chart, this chart is not a regular Line chart, it is a stacked Line chart, it's the stacked item there.
And when we go to Select, Data, Hidden and Empty Cells, she's right, everything else is greyed out.
Alright, so you know, my normal trick is, well hey, let's just fill with =NA() , Ctrl+Enter, and it fixes the chart as I would expect, but here it does not fix the chart.
Oh boy, so in a stacked Line chart, it's not following the rules.
The common trick that we all use.
Now, Mike before I see if you have a solution for this, let's just talk about these #N/A's.
You know, people say "Well, I don't like the #N/A's because they print poorly." And you know, if I do Ctrl+1 and use the normal trick of ;;; to hide the data, that would hide numbers, but it doesn't hide the #N/A's.
I still have a trick though to prevent them from printing, we come here to Page Layout, click on Print Titles, which is just a great shortcut way to get to the Page Setup.
Sheet tab, Cell errors as: Displayed, can change that to Blank, or even --, let's just go with Blank, click OK.
Now Print Preview, and then when we print this, you see that those future months will not print as #N/A's, they print as Blank.
But OK, so this whole issue is moot if you have a stacked Line chart, because there doesn't appear to be a good way to make those future months disappear in a stacked Line chart.
Mike, you have a solution for this?
Mike: Thanks MrExcel!
Hey, this is a great question here, we want to be able to enter numbers here, and have labels and numbers appear on the chart.
Now I'm sure there's a much easier way to do it than the way I'm going to do it.
I'm going to do it the old stand-by way, create a formula that creates a dynamic range, and then enter it into a defined name, and then put the defined names into the chart.
Now I'm going to go ahead and use the INDEX function to look up the last cell reference in a range, and then I'm going to build a dynamic range formula.
So I'm going to start with INDEX, now INDEX usually looks up a thing, like a number, or word, or something like that.
So I'm going to highlight the entire potential range, hit the F4, comma, I want to look up the last number, so in the row_num argument, I'm going to say "Hey MATCH, find the relative position of the last number." Well, I'm going to use approximate match with a really big number, so 9.99E+307, that's the approximation of the biggest number Excel knows.
And then I'm going to go ahead and use this range right here, so what it will do is, it'll just keep looking, and when it can't find anything bigger, it'll get the last one, and that's the number it will return using approximate match.
Now, if I enter this right here, of course, INDEX looks up the last number, right?
But check this out, I'm going to put the INDEX function into the context of a cell reference, and it will look up the cell reference instead of the item.
Now I'm going to click on that B3, and put a : here.
You know, someone showed me how to put a : in a formula like this, and not get that extra B3, but I totally forgot what that trick is, maybe someone will post a comment there.
But I'm going to lock that now, check this out, that : says to the INDEX "Hey, I don't want you to look up the thing, I want you to look up the cell reference." In essence, INDEX is in the context of a cell reference.
If I highlight this and hit the F9, it won't show me the cell references, but sure enough, it'll show me the values, if I start adding values out here, this thing will expand, Ctrl+Z.
I'm going to copy and then Ctrl+Shift+Enter.
Now I need to amend this for the East, I actually want the lookup range, not that, I want the cell reference to be in 2, so I'll put a 2 here, and 2, 2.
So I'm still going to base the MATCH for all three ranges on the last row, so it's not until we put in the last number that the chart will adjust.
Alright, Ctrl+Shift+Enter, and then I'm going to paste this here, and change this to 1, so the lookup range is changing, even though it's going to determine the position down here, it'll get the value from up there.
Now I'm going to have to do this one by one, into the Define Name box.
So I'll start with the months, Ctrl+C, Ctrl+F3 opens up the Name Manager.
I'm going to say New, and I'm going to call it DMonth, and down here I'm going to paste, Ctrl+V.
Now I'm going to click OK, and immediately click this Collapse Down button to check and see if the formulas work.
And I'm going to close, Ctrl+C, and Edit mode, Ctrl+F3, New, and I'm going to call this DEast.
Oh look, it's got the DEast there, so I'm going to come down here, Ctrl+V, click OK, check, Esc, close.
And then I'm going to come here, Ctrl+C, Esc, Ctrl+F3, New, and look at that, I didn't know that, it's somehow picking that up, that is so cool, click OK, check, alright, so we have three names.
Now I'm going to come over here, and we have to go up to Design, Select, Data, and very carefully I'm going to start with the edit.
I'm going to do each one of these, and the trick is, is that you have to highlight just the cell reference in leave the sheet reference.
I'm going to hit the F3 key to paste my name, and this is DMonth.
Now notice sheet reference and Define Name, I'm going to click OK, but I'm going to immediately click it to show what happens.
It actually puts a workbook reference in, so that's supposed to happen.
Alright, so now I'm going to do this to West, highlight very carefully just the cell reference, F3 to paste a name, DWest, click OK.
So now I have East, edit very carefully, highlight those cell references, F3, DEast, click OK, click OK.
So now, as I add down, if I add the first bit of data here, it doesn't do anything, but then I add this one down here, and BOOM!
It is working, and it picks up Jun.
Alright, throwback to MrExcel!
Bill: Wow Mike, that was brilliant, =B3:INDEX, and the INDEX that returns to cell reference.
I've got nothing, I mean, well OK, this is just so cheap.
I want to take this data here, now in this case, both are stacked Line charts, both pointing at that data.
I want to take this data, I want to right click, I want to right-drag to a new location.
In the new location I'm going to say Link Here, that's a great way to create a whole bunch of formulas that point up above.
I'm going to fake the West series, and it being West+East, and we'll copy that across.
Then I'll take this chart, and make this chart use the new fake data, which initially is incorrect.
But then we'll go to the Chart Tools, Design, Change Chart Type, and change to not a stacked Line, but just a railroad Line with markers, and we get the same scale and same data that we have in the original chart, but without the drop to zero.
The drop to zero is just part of the stacked Line chart, so by changing the underlying data to do what a stacked Line chart is doing, we get the answer.
I don't know, that's really, really cheap.
Alright well hey, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and Excel Is Fun!
