Solved

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

Posted on 2014-01-01
3
8,193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

689 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