How to Handle Blank Cells in Microsoft Excel 2007

This content requires Flash

To view this content, JavaScript must be enabled, and you need the latest version of the Adobe Flash Player.

Video Transcript

I think that it is kind of funny that Excel looks through your original data set and examines those values to figure out whether the column is a date column, a value column or a text column and it is kind of funny to me that if I have 10,000 rows with values in one row that contains a blank, that completely trips Excel up and think that I have a text column instead of a value column. The reason that this is frustrating is if you look at our current pivot table, the output for Microsoft is leaving us many, many blanks. For example, cell B8 right now contains a blank and that is Excel's way of saying that we did not fill any of A354 on January 2. Well, that is fine but if we did not fill any, I would really rather see a zero there.

This was a problem back in the original pivot table over 15 years ago and luckily, they have given us a way to correct the problem. We need to go to the pivot table Tools options tab. In the pivot table group on the left hand side, there is an Options Drop Down. From that drop down, choose Options. The pivot table Options dialog box is now four different tabs but the tab that we need is here on the layout and format tab. It is the setting for empty cells show. Now, this is already checked in the default to show a blank. If you like to show this a zero, click inside that textbox and type a zero. Click okay and now, Excel fills all those blank with zero. This is a great improvement.

I get a question sometimes in my power seminars where people say, "Hey is there a way that we could fill that with something else?" A couple of dash or something like that, yes, you could pull whatever you like in that field and Excel will use that anytime that there is no value for that period. Personally though, I like the zeros. Another minor frustration is that we are obviously working with a pivot table. And on sheet one, the only thing that is here is a pivot table. One of Microsoft's rules for the ribbon is that if you are not working with the pivot table, you will put away the pivot table tabs on the ribbon. Watch what happens when I click outside of the table. Those two ribbon tabs have completely disappeared.

Now there is really strange logic that if I accidentally click out and then immediately click back in, Excel will redisplay the tabs and take me back to the options tab where I was. However, if you would happen to accidentally click out and click anther cell and then go click back in, Excel redisplays the tab. But, you are left on the Home Ribbon instead of the Option Ribbon where you last left off. I understand Microsoft's logic in this. They are trying to make sure that no one has a picture tool bar left up on the screen for six months after they are no longer using pictures. However, in this particular case since there is nothing else on the sheet other than the pivot table, I really think that their desire to hide those options when I accidentally click out is really annoying. You just have to remember that if the pivot table field list and the Ribbon tabs disappeared, you need to click back into your pivot table.

Now let us rearrange our pivot table some more, let us take the Product field off and take the Date field off. Put the Customer field along the left hand side. We just have to click the Customer field and drag the Region field to go across the column labels. You will notice that Excel still remembers that we still have our top ten auto filters turned on the Customer field. You can see the little funnel next to customer in the top of the pivot table field list. Let us turn that filter off so that we could see all of our customers. I will open the drop down next to the customer field and choose clear filter from customer. I now see all of the customers in our data set.