Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-01
3
Medium Priority
?
8,792 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

598 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