Hey, welcome back to the Mr. Excel net cast. I’m Bill Jelen. Basic, we start out with massive amounts of data, but how we we’re going to analyze is well let’s—table, see if we can solve this problem.

Hey, welcome back to Mr. Excel net cast, I'm Bill Jelen. What a great question they are sent by Glenn. Glenn has three components A, B and C and then a total over here and he wants to create charts from this and the chart is working great on the component items, A, B or C so you’re going to have a nice little chart here, it shows your day revenue, life is good right, but then when he tries to the same chart from the total, it has this—they drop to zero so it create the chart all of a sudden towards the future months, where the zero it’s like the company went on a business. Well, it’s not that the company went on a business, it’s just that we don’t have any featured data but you’re going to take a look at how good it looks here and how bad it looks here. Glenn says,”Oh, I have a lot of charts. I don’t want to update this every month. I don’t want to go and edit the series formula” or you’re doing anything like that. And he admitted, “Friends well, look I’m using this formula over here in the total to make sure that we don’t see anything that is not filled in so if the sum of these three cells is zero then put call quote in other words nothing and then otherwise put the sum. Well, that was really close. All you have to do is instead of call quote is to put the NA function. NA will force the NA or the show up.

The great thing, usually we hate NAs but the great thing about NAs is that they cause our chart to not plot any data. The chart won’t show any data for NA so it makes the chart look good. Now unfortunately, it makes the spreadsheet look really bad. We have introduced a new problem. If you’re going to print this, you go to page layout, click the dialog launcher and back on the sheet tab say cell errors as use this as displayed but I changed it to blank. To make sure those cell errors don’t print this, they’ll show up in the spreadsheet though, that’s driving you crazy. After this, let’s choose these cells and we’re going to go into conditional format, so home conditional formatting new rule and we’re going to use a formula to determine which cells to format. The formula has to refer to the active cell, the active cell in this case being “K2” so equal is NA-K2. If that’s true and it will be replicated throughout then I want to use a white font. So, let’s do that. Click okay, click okay and see that now, the future numbers are not appearing as NA. They won’t print as NA because we’ve changed the page setup and also the total is working. Now, let’s just test to make sure this is working 1800, see if the chart updates there for the new value. So, it seems like it shouldn’t be that hard but there you have it. Once you get it setup with those NAs and get those NAs hidden and then Glenn you will be able to go through, update this every month just by plugging it in your number and your charts will update. There you have it. Want to thank you for stopping by. We’ll see you next time for another net cast from Mr. Excel.

Well, thank you for stopping by. We’ll see you next time for another next net cast from Mr. Excel.