Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
PivotTable Issues
As you can see from the attached I have a PivotTable which currently has 4 month columns and I have had to add these individually.
What I want to know is, is there a way to add all months at once, set their formatting once, and have grand totals for each row at the right end of that pivottable because right now that is proving impossible.
I would like to be able to sort and filter by dates (being the months) also and then the table update automatically.
Basically I just dont understand whats going on as I have used dates with PivotTables a million times and never have I had to add the months individually like this and then have no ability to sort, group and filter those dates (ie. sometimes I group into quarters) as well as have no ability to total the values within the date periods.
Please help!
Pivotable.xlsx
What I want to know is, is there a way to add all months at once, set their formatting once, and have grand totals for each row at the right end of that pivottable because right now that is proving impossible.
I would like to be able to sort and filter by dates (being the months) also and then the table update automatically.
Basically I just dont understand whats going on as I have used dates with PivotTables a million times and never have I had to add the months individually like this and then have no ability to sort, group and filter those dates (ie. sometimes I group into quarters) as well as have no ability to total the values within the date periods.
Please help!
Pivotable.xlsx
Who is Participating?
Couple of issues that I have spotted:
Dates - they aren't true dates, they are text.
Layout of data - because the data is in separate columns for each month, the pivot recognises this as another field so if you want another month added you have to add another field.
Possible Resolution
Change dates to proper dates and change layout of the data to have only 5 columns:
Site
Product #
Product
Date
Value
You could then have an additional column that determines whether the row is to be included in the pivot, presumably its the last four months that you display; simple IF statement to determine whether date is less than 4 months ago.
Dates - they aren't true dates, they are text.
Layout of data - because the data is in separate columns for each month, the pivot recognises this as another field so if you want another month added you have to add another field.
Possible Resolution
Change dates to proper dates and change layout of the data to have only 5 columns:
Site
Product #
Product
Date
Value
You could then have an additional column that determines whether the row is to be included in the pivot, presumably its the last four months that you display; simple IF statement to determine whether date is less than 4 months ago.
Rob I am sorry but I am not following your solution... could you show me what you mean and I can try to implement your recommendation into my full spreadsheet
See attached.
Pivotable.xlsx
Pivotable.xlsx
Rob there is no date limitation, on the full spreadsheet (what I've included in the question in just a subset of the data) it actually goes out to 24 months and my intention is to group it by quarters.
My question is how did you automatically change the source data from the layout in 'Sheet 1' to the 'newdata' sheet. I assume you did not manually copy and paste?
My question is how did you automatically change the source data from the layout in 'Sheet 1' to the 'newdata' sheet. I assume you did not manually copy and paste?
Look at this version attached instead. I overwrote the conversion calculations and then realised that you might ask that question so have reinstated them.
Additional column at left hand end of original data concatenating Site and Product number.
I did have to Copy and paste the Site and Product details the required number of times though.
If your source data comes from another system in the format you have, the update each month would be copying and pasting another block at the bottom of the new data table. The formulas to pull the values would need adjusting to allow for extra columns.
I have also now grouped the data in the pivot by Year and Quarters.
Thanks
Rob H
Pivotable.xlsx
Additional column at left hand end of original data concatenating Site and Product number.
I did have to Copy and paste the Site and Product details the required number of times though.
If your source data comes from another system in the format you have, the update each month would be copying and pasting another block at the bottom of the new data table. The formulas to pull the values would need adjusting to allow for extra columns.
I have also now grouped the data in the pivot by Year and Quarters.
Thanks
Rob H
Pivotable.xlsx
You can use the Power Query add on (Excel 2010 or 2013) to change the source data from how it is received to how Rob transposed it. It works very well, and has the ability to save the steps so they are repeatable the next time the source data is updated.
Let me know if you think this is of interest to you. - Tom
Also you can group by quarter within the pivot table functionality...
Let me know if you think this is of interest to you. - Tom
Also you can group by quarter within the pivot table functionality...
OK, I'm way out of my league here and so I apologise for the lack of understanding on my part.
I think its easier if I just attach the 'real' workbook. I have hidden the other pages but basically you can see the table that exists there.
Hopefully Rob or Tom you can quickly set things up correctly for me. What I am trying to achieve is to use that data and create exactly the pivot table that Rob has put together and then each month I simply add data to the background table and the pivottable updates accordingly.
1606---EOM---EcoCycle.xlsx
I think its easier if I just attach the 'real' workbook. I have hidden the other pages but basically you can see the table that exists there.
Hopefully Rob or Tom you can quickly set things up correctly for me. What I am trying to achieve is to use that data and create exactly the pivot table that Rob has put together and then each month I simply add data to the background table and the pivottable updates accordingly.
1606---EOM---EcoCycle.xlsx
The first question is do you have Excel 2010 or 2013? If so do you know how to activate the Power Query add-in? If not, you can use a macro (which was given to me years ago by one of the Experts here) to change your data to well-formed data for a pivot table. I will find that macro if you need it.
In short, the dates you show in multiple columns should be shown in one column and not many. This is a step in normalizing data that is not well-formed. Once the data is in one column, then the pivot table gives you the reporting you want.
Another suggestion would be to ask for the data in a better format if that is possible. It may be the provider thinks you prefer the data in a two-dimensional view. This would be a great solution if you are having to transform the data every time you get it. Just a thought....
In short, the dates you show in multiple columns should be shown in one column and not many. This is a step in normalizing data that is not well-formed. Once the data is in one column, then the pivot table gives you the reporting you want.
Another suggestion would be to ask for the data in a better format if that is possible. It may be the provider thinks you prefer the data in a two-dimensional view. This would be a great solution if you are having to transform the data every time you get it. Just a thought....
Attached is what you data looks like (Unpivoted Data Tab) using the Power Query Add-In. I was able to create it with basically four mouse clicks. From this data, the pivot table is pretty easy.
1606---EOM---EcoCycle-Unpivoted.xlsx
1606---EOM---EcoCycle-Unpivoted.xlsx
Tom, I have excel 2016 and google tells me Power Query is built in as a result? And agreed, once the data is in that form its childs play.
Unfortunately that is how the data is available. There is essentially no other option without paying for an expensive custom report to be generated from our accounting system.
Are you able to to outline the 4 steps involved that you mention above. Usually I am quite a competent Excel user (albeit this issue has put that to question! haha) so I should be able to follow straight forward steps and do this monthly.
Thanks
Unfortunately that is how the data is available. There is essentially no other option without paying for an expensive custom report to be generated from our accounting system.
Are you able to to outline the 4 steps involved that you mention above. Usually I am quite a competent Excel user (albeit this issue has put that to question! haha) so I should be able to follow straight forward steps and do this monthly.
Thanks
Do you see the Power Query Tab at the top of your Excel along with the other Tabs? If not, go to File>Options>AddIns. At the bottom change Manage to COM Add Ins. Then check
Power Query
Power Pivot
Power Map
Don't need all now, but you will eventually want to use.
Restart Excel and you should see the Power Query tab at the top of the page along with the other tabs. When you have this let me know and I will give you the remaining steps.
Power Query
Power Pivot
Power Map
Don't need all now, but you will eventually want to use.
Restart Excel and you should see the Power Query tab at the top of the page along with the other tabs. When you have this let me know and I will give you the remaining steps.
Tom... all 3 are in per the attached
Screenshot-2016-07-25-06.40.12.png
Screenshot-2016-07-25-06.40.12.png
If you go out to YouTube and watch some videos about Power Query and/or Power Pivot you can begin to get really familiar with the tools. They really have taken Excel to a different level. I have not yet found the time to further develop my skills here either.
Tom, thanks for finishing off and with a better solution as well. I have yet to investigate Power Pivot; I still use 2010 at work but do have 2016 at home but don't use it for much.
Regards
Rob
Regards
Rob
Thanks, Rob, Power Pivot is worth investigating as is Power Query and Power Map. Though I have not had the opportunity to use them extensively, I have used enough to know they are very powerful.
In short from what I know:
Power Pivot enables you to work with tables that are linked together, and can handle many millions of rows of data. Not limited to Excel's 1,000,000 rows.
Power Query lets you cleanse data, and I think you can save the query that does it for doing it over and over.
Power Map enables you to visually plot data on maps.
In short from what I know:
Power Pivot enables you to work with tables that are linked together, and can handle many millions of rows of data. Not limited to Excel's 1,000,000 rows.
Power Query lets you cleanse data, and I think you can save the query that does it for doing it over and over.
Power Map enables you to visually plot data on maps.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month5 days, 2 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
Pivot-Issues.docx