Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Many users fear #N/A values appearing in their spreadsheets. This module explores how to create this result with the NA() function, and why it's fantastic for charts
Exercise files
Download this lesson’s related exercise files.
Benefits of NA Values45.8 KB Benefits of NA Values - Completed
45.8 KB
Quick reference
Benefits of NA Values
Why #N/A values can actually be a good thing in Excel.
When to use
Use #N/A values to suppress data points from being plotted on your charts.
Instructions
The benefits of #N/A values
- Data points with a value of #N/A are not plotted on the chart
- When an axis label is also set to an #N/A value, it will not show on the axis either
#N/A vs 0
- A 0 will plot as a valid data point on the 0 axis
- #N/A will not plot on the chart at all
Triggering #N/A values
- An #N/A value can be triggered by using the =NA() function
Hints & tips
- Remember that the NA() function can also be used as a valid True or False result in an IF() function
- In order to completely suppress a value from both the plot area and axis, the field that drives the axis must also show as #N/A
- 00:05 What I want to focus on now is building for the future, and
- 00:08 what happens in a specific scenario.
- 00:11 Let's go and delete this data for just a second here.
- 00:15 And I want you to think about how this works when you're working from January all
- 00:19 the way through June and you keep adding data to your chart it builds out, and
- 00:22 it starts to look like this.
- 00:24 And this is what we'd like to see, because let's face it, it's focusing on January to
- 00:27 June, these are the only pieces of the year that have happened yet so
- 00:30 everything looks pretty good.
- 00:31 And I'm gonna bring back the data.
- 00:33 I'm going to say all right, but I need you to build your chart so
- 00:37 that it will actually take data all the way to December.
- 00:40 So what you'd probably do is build out your data, you'd use some if statements in
- 00:44 here to get the data, or even, let's pretend it just doesn't exist yet.
- 00:48 But you've built it out with a framework.
- 00:51 And this chart doesn't look so good.
- 00:53 Because now, even though we don't have data to fill in these gaps,
- 00:57 we still see July through December plotted on the axis and this is not ideal.
- 01:01 But how do you go back and say, well yeah, I need to get rid of this stuff until it
- 01:06 actually happens if you're building for future.
- 01:08 And this is the challenge that I wanna deal with now.
- 01:09 It's how do we build this chart so that it shows just the current range and
- 01:13 extends automatically as we add new data to it?
- 01:17 So, the first thing that I wanna call out before we do this,
- 01:19 I wanna drive this based on the results of this cell.
- 01:22 So I wanna be able to say, hey we're gonna do our forecast up to this point.
- 01:26 Just for May, going forward, is gonna be blank and
- 01:29 I wanna suppress the entire thing.
- 01:31 Now I made a small change to the index function that you saw in the last video.
- 01:36 Instead of picking up my index of the range of dates from down below here,
- 01:42 these guys I wanna change up dynamically,
- 01:44 which means I can't index them if they don't exist.
- 01:47 So, what I've done is I've pointed this to a different area.
- 01:50 We have another area at the top here that actually has my original dates.
- 01:53 These are not going to be supressed.
- 01:55 They're always going to exist in my data.
- 01:57 People aren't necessarily going to look at them, but
- 01:59 they're always going to be there.
- 02:00 So that's where I pointed my index across and said give me the first row, and
- 02:04 tell me what's the matching data point for H35, which is 5,
- 02:07 that's why it's gonna bring back May.
- 02:11 With that in place, I can now start toggling and
- 02:14 playing around with the data that we have down here.
- 02:16 Now, I want to show you something kind of interesting.
- 02:20 If I go and delete the two data points in December,
- 02:24 you'll notice that the December data points don't plot anymore.
- 02:27 Of course if I were to put in zeros, they do, but they plot as zeroes.
- 02:31 So I definitely don't want to see that happening because that's inferring that
- 02:35 there's no sales where, in fact, it just means they haven't happened yet, so
- 02:39 we'll just undo that for a second.
- 02:41 But how do I make December go away?
- 02:44 And the secret to doing this is to trigger one of everyone's favorite errors.
- 02:48 And basically we do it like this.
- 02:51 =NA().
- 02:52 And when you hit enter, you'll notice that this triggers in the cell a #NA error.
- 02:59 Nobody likes these.
- 03:01 Except your chart.
- 03:03 Notice that December just disappeared.
- 03:06 So that's pretty cool because it doesn't have any data, so we can actually use
- 03:10 an if function to return a #NA error which will prevent the data from being plotted.
- 03:16 When it hasn't happened yet.
- 03:18 So let me just roll that back and here's what we're gonna do.
- 03:21 We're gonna select January through December.
- 03:25 And instead of just saying =B4 in January here, what we're going to do is we're
- 03:30 gonna say, equals if B4 is greater than,
- 03:37 the value that we actually have sitting over here for our Forecast To.
- 03:42 Then, what we're gonna do is, we're gonna say, NA().
- 03:48 If it's not greater than, then we're gonna go with B4.
- 03:53 Now, important though, of course, that we make all of this stuff absolute and
- 03:57 relative the way that we need.
- 03:59 B4 is sitting up at the top up here, so
- 04:01 we're checking, is January greater than G35?
- 04:06 This guy, we're going to lock in and
- 04:09 make him absolute, because I want to apply the same formula all the way across.
- 04:13 I'm perfectly happy with B4 becoming C4, D4, E4.
- 04:16 But I definitely don't want the G35 moving around.
- 04:20 Now that I've got the set, of course I selected all of my values first.
- 04:23 I'm writing my formula from the active cell.
- 04:25 I'll hold down Control, press the Enter key, and
- 04:28 it will commit the formula all the way along.
- 04:31 And isn't that a thing of beauty?
- 04:32 Look at this, my chart now shows only data to May.
- 04:36 If I go and say, change this to June, it now gives me June.
- 04:40 If I go and say, show me data all the way to October, boom, look at that.
- 04:44 It's working beautifully.
- 04:46 So, this is an awesome way that you can suppress the headers.
- 04:50 So if there's no data in there, you can return an NA, using an if function.
- 04:54 And the beautiful thing about it too,
- 04:55 and this is something that I don't think is worth glossing over at all.
- 04:59 I really want to see this.
- 05:01 I did not modify my index match functions at all.
- 05:05 All I've modified is this piece.
- 05:07 Why does this work?
- 05:09 Well, because when you look at the formula for my index match, I'm looking for
- 05:14 a match based on a value that's returning NA.
- 05:17 Whenever there's an NA value error, that cascades through the rest of the formula,
- 05:21 setting the rest of the data points to NAs.
- 05:23 Now these look Ugly, they look really ugly.
- 05:27 But at the end of the day the cool thing here, when I go and now click on this guy
- 05:31 here and say ten, at this point any one of those #NAs doesn't chart.
- 05:36 And am I gonna leave a user staring at this?
- 05:38 No way, I'm gonna cut my chart, I'm gonna take it over to my dashboard page,
- 05:43 paste it, and do my little resizing to get this to look exactly right.
- 05:48 And guess what?
- 05:49 My user isn't even gonna be seeing what's going on on background at all,
- 05:53 because instead they get a chart that's looking very nice and
- 05:57 doesn't have those #NA things staring them in the face.
Lesson notes are only available for subscribers.