Laura has attended:
Excel Advanced course
Excel Advanced course
Grouping dates on pivot tables
Hi there, I am trying to group dates on a piviot table, which pulls data from another source (Access database). However, it says that it cannot group that data. I have tried it on other simple pivot tables, and it works, so is it because I am pulling the data from else where?
I'm really disapointed because this was one of the key things that I wanted to get out of excel!
I did see this post here, but it does not really answer my question.
thanks!
Laura
https://www.stl-training.co.uk/post-6406-pivot-tables.html
RE: Grouping dates on pivot tables
Hi Laura
Thanks for your question
The most likely reason for this is that the date column contains some data that Excel doesn't recognise as a date.
The first thing to check is whether there is additional data at the end of the table. The date column should start with a label and then rows of dates. Crucially, after the last row of data there must be a blank row. Having additional stuff in this row often causes the error you describe.
If this is not the case it would be helpful for me to see the spreadsheet. Could you email it to me at sw@stl-training.co.uk and I will see if I can find a solution
Regards
Stephen
RE: Grouping dates on pivot tables
Hello Laura,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding grouping dates in a PivotTable using imported data.
I have created a PivotTable using data from an Access database with a date field included. I placed the date field into the Rows section. I then selected all dates for a particular month... e.g. January 2010, then I right-clicked on the selection, navigated to Group and Show Detail, then I clicked on Group. This gave me a generic name which I changed to Jan 2010. I then double-clicked the new group name which hides the detail.
It works for me and the data I am using comes from an Access database so I'm not sure why you are not able perform the same action.
It may be easier if you go to the Data menu, Import External Data and select New Database Query. This will open the Create New Data Source dialog box. In the first box type the Name for your database. In the second box, click the drop-down arrow and select a driver for Microsoft Access then click Connect.
Click the Select button and then go to the folder where your Access database is located and click the required database file name, click OK. This takes you back to the Create New Data Source dialog box where you can select the default table in field number 4. It doesn't matter which one you choose.Click OK and this will now have added your Access Database to the list of data sources available.
Then choose the Data menu, Import External Data and select New Database Query and select your database from the list. From there select the fields you require and Excel will position these into a spreadsheet. From there you can create your PivotTable and hopefully won't have any more problems with grouping your date fields.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: Grouping dates on pivot tables
Hello Laura,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding grouping dates in a PivotTable using imported data.
I have created a PivotTable using data from an Access database with a date field included. I placed the date field into the Rows section. I then selected all dates for a particular month... e.g. January 2010, then I right-clicked on the selection, navigated to Group and Show Detail, then I clicked on Group. This gave me a generic name which I changed to Jan 2010. I then double-clicked the new group name which hides the detail.
It works for me and the data I am using comes from an Access database so I'm not sure why you are not able perform the same action.
It may be easier if you go to the Data menu, Import External Data and select New Database Query. This will open the Create New Data Source dialog box. In the first box type the Name for your database. In the second box, click the drop-down arrow and select a driver for Microsoft Access then click Connect.
Click the Select button and then go to the folder where your Access database is located and click the required database file name, click OK. This takes you back to the Create New Data Source dialog box where you can select the default table in field number 4. It doesn't matter which one you choose.Click OK and this will now have added your Access Database to the list of data sources available.
Then choose the Data menu, Import External Data and select New Database Query and select your database from the list. From there select the fields you require and Excel will position these into a spreadsheet. From there you can create your PivotTable and hopefully won't have any more problems with grouping your date fields.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
