Excel - When an Excel Pivot Table is Missing Some Months for One Customer - Episode 970
Microsoft Excel Tutorial: Showing Missing Months in Excel Pivot Table.
Welcome back to the MrExcel netcast! In today's video, we're going to tackle a common issue that many pivot table users face - missing months. This problem occurs when we have a pivot table with months on the left-hand side and customers on the top, and we select a customer who only has data for certain months. This can be frustrating, but fear not, as I have a solution for you.
This question was sent in by Michael from Shanghai, China, who is a big fan of pivot tables. He has tried adding dummy data to ensure that every customer has data for all 12 months, but this can be time-consuming and not always practical. So, what's the solution? Well, it's actually quite simple. We just need to adjust the settings for the month field in our pivot table.
First, select one of the months in the pivot table and look for the Field Settings icon. In Excel 2007, it's on the Options tab, and in Excel 2003, it's on the pivot table toolbar. It looks like a spreadsheet with a blue "i" on it. Click on this icon and look for the option called "Show Items with no data". In Excel 2007, it's on the second tab, and in Excel 2003, it's on the first tab. Make sure this option is selected and click OK. Now, all the months will be visible in the pivot table, even if there is no data for that month.
But wait, there's one more step. We need to make sure that the empty cells show a value of 0. To do this, right-click on the pivot table, go to Table Options, and select "For empty cells show: 0". This will ensure that all the empty cells in the pivot table show a value of 0. However, you may notice that there are some extra months showing up before Jan 1st, 2008. This is because there may be some data in your data set that goes back that far. You can simply uncheck those months if you don't want them to show up in your pivot table.
And there you have it! Thanks to Michael for sending in this question and for being a loyal fan of pivot tables. I hope this solution helps you and others who may be facing the same issue. Don't forget to subscribe to our channel for more helpful tips and tricks for using Excel. Thanks for watching and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
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!
Table of Contents:
(00:00) Introduction
(00:14) Question from Michael in Shanghai
(00:24) Issue with occasional customers in pivot table
(00:39) Solution using dummy data
(01:00) Finding "Show Items with no data" option
(01:22) Adjusting table options for empty cells
(01:45) Removing unnecessary data
(01:57) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Analyzing data
Customer data
Empty cells show: 0
Excel 2003
Field settings
Michael Shanghai
Pivot table
Report
Show items with no data
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Смотрите видео Excel - When an Excel Pivot Table is Missing Some Months for One Customer - Episode 970 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь MrExcel.com 14 Март 2009, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 3,201 раз и оно понравилось 18 людям.