Three Excel tricks today: Fill in the empty cells when you have multiple row fields in a pivot table. Fill in the empty cells because there were not any sales. And Change the Pivot Table Defaults for all future pivot tables.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2259. Fill in the blanks in the annoying outline view of a pivot table.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen.
This is one of those things that has driven me crazy for years. If I create a new pivot table with two fields in the row field. For example, maybe sector and customer and then show revenue. These blanks over here, I hate those blanks. And let’s add the product.
And these blanks, I hate both of those blanks.
Easy, easy way to solve this. Alright now, the design tab. Come here to report layout and choose, repeat all item labels which will get those to fill in but not these to fill in. Right click the pivot table and go to pivot table options and for empty cells, show zero. I’ll tell you what, if I had a dollar for every time I’ve done that, I would have thousands of dollars. But the great news.
If you have Office 365, just come here to file, options, data, make changes to layout to the default pivot table, edit default layout, repeat all item label, just turn that on for every pivot table and then pivot table options for empty cells show zero.
From now on, when I create the pivot table, by the way, I already have show in tabular form but that compact layout is horrible.
So, that was already said before we started.
Now, when you create your pivot tables, everything will just be filled in which is the way we want it because we might be copying this data later to reuse and you don’t want blanks there.
Hey I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen.
This is one of those things that has driven me crazy for years. If I create a new pivot table with two fields in the row field. For example, maybe sector and customer and then show revenue. These blanks over here, I hate those blanks. And let’s add the product.
And these blanks, I hate both of those blanks.
Easy, easy way to solve this. Alright now, the design tab. Come here to report layout and choose, repeat all item labels which will get those to fill in but not these to fill in. Right click the pivot table and go to pivot table options and for empty cells, show zero. I’ll tell you what, if I had a dollar for every time I’ve done that, I would have thousands of dollars. But the great news.
If you have Office 365, just come here to file, options, data, make changes to layout to the default pivot table, edit default layout, repeat all item label, just turn that on for every pivot table and then pivot table options for empty cells show zero.
From now on, when I create the pivot table, by the way, I already have show in tabular form but that compact layout is horrible.
So, that was already said before we started.
Now, when you create your pivot tables, everything will just be filled in which is the way we want it because we might be copying this data later to reuse and you don’t want blanks there.
Hey I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
