Solved

Why won't my pivot table slicer group the dates into months, with the new data?

Posted on 2014-01-01
3
7,702 Views
Last Modified: 2014-01-12
I am currently working with a pivot table someone else created. The data set has dates in one of the columns in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.

Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these new months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.

How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
0
Comment
Question by:lora2014
  • 2
3 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39750692
I can't be sure (since you didn't post a workbook that demonstrates the problem), but the symptoms sound like the new dates are text instead of date/time serial numbers. To check, try changing the format of the new dates. If you can't change it, the date data are text that looks like dates.

To convert text into real date/time serial data, here are two good methods:
1.  Select a blank cell, then copy it
2.  Select the data to be converted
3.  Use the Paste Special...Add item
4.  Format the data using one of the date formats

1.  Select the data to be converted
2.  Open the Text to Columns menu item
3.  Choose Delimited in the first step of the wizard
4.  In the second step of the wizard, uncheck all the delimiter options
5.  In the third step of the wizard, select the column with your dates, and make sure you choose MDY or other date specification for your raw data
6.  Format the data using one of the date formats
0
 

Accepted Solution

by:
lora2014 earned 0 total points
ID: 39763991
Thank you for your response. I however found a solution from my colleague as follows:

1. Remove all items in the Pivot table from the Rows Tables section
2. Add the dates to the rows (turn off any filters)
3. Select the dates - ungroup
4. Re-add the dates to the Rows tables section
5. Select the dates - group
6. Choose to group by month and year
7. Insert a slicer for the date and year
8. Remove the dates from the Rows table
9. Re-add any items removed in item 1

The issue was that with there being so many filters in the Rows Table, the dates could not be manipulated at all.
0
 

Author Closing Comment

by:lora2014
ID: 39774515
Because...it worked. There was no issue with the dates data, just the Pivot Table. It had too many filters going in the rows.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question