Michael from Shanghai asks about missing data in a pivot table. If some customers have data from every month and other customers have data from only a few months, the pivot table will change shape as you select new customers from the filter. Episode 970 will show you the hidden setting to prevent this problem.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze as well plus file up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question is sent in by Michael Shanghai, China.
Michael is a big fan of pivot tables, and he has a pivot a table, where he has months down the left-hand side and customer up in the the page Filter.
And so, when he chooses a customer that has bought something every month, of course he gets all 12 months.
but then if he chooses a customer whose only occasional customer, they show up only for the months where they have data.
He says this is really frustrating.
He's even gone through and added dummy data.
That's that way every customer would at least have a zero.
And every month and there's actually a very bizarre way to solve this.
You need to go to that month field.
Select one of the months and then look for the Field settings icon.
Now in Excel 2007, it's on the Options tab.
In Excel 2003, it's on the pivot table toolbar.
It's just this little icon here, the spreadsheet with a blue i.
Click that field settings, and we're looking for something called Show Items with no data.
In Excel 2007, It's on the second tab I think in Excel 2003, of us back on the first half.
but it still said show items with no data Click OK and then what we're going to get is we're going to see every month.
Every time now you see that we get the blank cells of course , so right-click Table Options and for empty cells show: 0.
Make sure that you get zeros there.
A little annoying that it's adding this data before Jan 1st, 2008.
Not sure what's up with that.
I must have one record in the data set that goes back that early.
You can always go in and uncheck those, If they are showing up in your data set.
Make it back to a nice Report that we can ease to another customer and see all of the months all the time.
So, there you go.
Thanks to Michael for sending that question in.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
How we're going to analyze as well plus file up a pivot table.
Let’s see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Today's question is sent in by Michael Shanghai, China.
Michael is a big fan of pivot tables, and he has a pivot a table, where he has months down the left-hand side and customer up in the the page Filter.
And so, when he chooses a customer that has bought something every month, of course he gets all 12 months.
but then if he chooses a customer whose only occasional customer, they show up only for the months where they have data.
He says this is really frustrating.
He's even gone through and added dummy data.
That's that way every customer would at least have a zero.
And every month and there's actually a very bizarre way to solve this.
You need to go to that month field.
Select one of the months and then look for the Field settings icon.
Now in Excel 2007, it's on the Options tab.
In Excel 2003, it's on the pivot table toolbar.
It's just this little icon here, the spreadsheet with a blue i.
Click that field settings, and we're looking for something called Show Items with no data.
In Excel 2007, It's on the second tab I think in Excel 2003, of us back on the first half.
but it still said show items with no data Click OK and then what we're going to get is we're going to see every month.
Every time now you see that we get the blank cells of course , so right-click Table Options and for empty cells show: 0.
Make sure that you get zeros there.
A little annoying that it's adding this data before Jan 1st, 2008.
Not sure what's up with that.
I must have one record in the data set that goes back that early.
You can always go in and uncheck those, If they are showing up in your data set.
Make it back to a nice Report that we can ease to another customer and see all of the months all the time.
So, there you go.
Thanks to Michael for sending that question in.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
