Grouping in a Pivot Table

[Emma]

I am trying to group dates into months and quarters in a pivot table and I am not getting the dialog box to select the options. I have a row label called price dates, with dates like 1/1/2014, I right click on a date and select group but all it does is give me a column called price date2, group 1. I don't get the dialog box to select what type of grouping I want. It worked on another table I have but I am trying to create another pivot table and it won't work. I have taken out all the blank cells and even tried changing the format of the date. Can anyone help?
Thanks!!!!!

 

[NARAYANK991]

Hi Emma ,

Are you sure your dates are really dates ? This is what the following link has to say :

The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items.

http://www.contextures.com/xlPivot07.html#Dates

Narayan

 

[Emma]

I think I may have figured it out. I have a source page that I keep adding data to so when I selected it I selected rows that does not have information in them. This time I only selected the cells that have data in them and I was able to select the type of grouping I wanted. So that brings another question. Do I have to change my data source range every time I add data to new rows? Can't I select a broad range with blank cells for future data entry so I don't have to change my data source range in my pivot table to extend the range to cover new entries?

I thought I could just deselect the blank rows in by row label and have it work.

Thanks,

 

[NARAYANK991]

Hi ,

The ideal would be to convert your raw data into a table , so that you never need to specify the range ; specifying the table name e.g. Table1 will ensure that the pivot table always includes all data in the table , even as you add new rows.

The next best way would be to create a named range , say RawData , for your raw data , and make it dynamic e.g. in the Refers To box , you can have a formula such as :

=Sheet5!$G$7:INDEX(Sheet5!$S:$S,COUNTA(Sheet5!$G:$G)+6)

where your data is in columns G through S ; the column headers are in row 7 , and the row headers are in column G , and the rows 1 through 6 are blank. Now , in your pivot table , specify the source as RawData.

Narayan