grouping dates pivot tables

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Grouping dates on pivot tables

Grouping dates on pivot tables

resolvedResolved · Low Priority · Version 2003

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

Mon 27 Jun 2011: Automatically marked as resolved.


 

Excel tip:

Shortcut for deleting all comments in a spreadsheet

If you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by:

1. Holding down Ctrl, then Shift, then O - this will select all cells containing comments in the worksheet you are looking at.

2. Right-clicking on one of the selected cells, and selecting Delete Comment from the menu that appears.

3. Clicking anywhere else in the spreadsheet to deselect comments - all comments should have disappeared from the spreadsheet.

View all Excel hints and tips


Server loaded in 0.06 secs.