My data that I'm creating a pivot table from does not have data for selected months. When I create the pivot table naturally shows months missing.
I need to show in the pivot table if the month does not have a value show the month with a value of 0
I have tried adding a row in my source data indicating month but then it shows up in pivot table with a data value of 1.
Please advise
Pivot1.JPG
Source Data
PivotSourceData.JPG
Sight of the workbook would be useful. It's always easier if we can see the context.
Are you show yuo don't have a space character in the months?
In the PT Options have you ticked the 'for empty cells show' choice and set it to show zero?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks for reply:
What does this mean: Are you show yuo don't have a space character in the months?
In the PT Options have you ticked the 'for empty cells show' choice and set it to show zero? No as looks god awful
PivotSourceData.JPG
Pivot1.JPG
Data crossed-out or deleted as sensitive public info.
Sorry that was a typo
I should have said are you sure you don't have a spce character in the months. And by the months I meant the blank months you've added to the database.
Sorry I don't know what you mean by your response to my question about ticking the 'for empty cells..' option.
Plese upload the workbook. Pictures are rarely much use. Anonymise the data if necessary.
See attachedCoffee Pivot Table.xlsx
Perhaps this will be of help:
1. Produce a Months column (F) of dates using: =DATE(RIGHT(A2,2)+2000,MONTH(DATEVALUE(A2)),1)
2. In the pivot table Columns field replace Month with month and year from the new column (Months)
3. In the field settings for Months select Layout & Print > check Show items with no data
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
That did it - Thank you very much
I spoke to quick. Mgt. only wants to see 12 months worth of data not 2 years. When I select the pull down I see 2019 and 2020 and no way to say show from Feb 2019 to Jan 2020. Ideas?
Do you mean any 12 months?
e.g. May 2019 to April 2020
Yes they want proceeding 12 month from current month. So if I had to report today 1/31 it would be February 2019 to January 2020. In Feb, I will report March 2019 to Feb 2020. Thanks
Here is a low tech proposal which will require manual monthly update to group the columns containing months that you do not want to see.
I'll look into a more automatic solution later, unless someone else comes up with a solution beforehand.
Let us know if you have any question.
THANKS! What was the specific changes you made?
The columns have been grouped. Select the 3 at the top left of the spreadsheet, below the Name Box and above the Row Numbers, and the grouped columns will appear. Selecting the 1 will again group the columns so that they are not visible.
Let us know if you have any questions.
I cleaned the pivot table up a bit so that it has only two levels. The range on sheet 1 has been converted to a table so that when rows are added the source data of the pivot table will change automatically.
Sorry not to be able to get the display of months to update along with the current month, I'll ask some of the other contributors to take a look.
Really appreciate the work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks