Grouping Data in Pivot Tables in Excel

has followed Excel's instructions, but still could not group date (into
month, year, etc) from a column of date in a table. Message 'Cannot group
that selection' even though the whole column (cells) had been reformated in
date format. Please help.

I am having an issue with getting a Pivot Table to display data how I would like to report on it. The column labels are fine, and when I put one field into the row label section, I see data how I would like to. When I add another field to the Row Label section, it nests this within the first question, where I really just want to keep them grouped seperately by field and display in one table. I think the graphics below will help explain this better.


Pivot Table with 1 field for the row & column sections, which is fine

pivot1.jpg


When I add the second field in, the fields 'nest', whereas I would like them to 'group':


pivot2.jpg


Ideally, I would like them to stay grouped by each field, similiar to below (This is all hardcoded, I just wanted to show my ideal output):


pivot3.jpg


I have been banging my head against this for hours now, and would really appreciate any help. Thanks!

Hi All,

I have a spreadsheeet in work that I have inherited. On this worksheet we have a list of customers with their various details in the column headers such as name, telephone number, email and so on.

Now the tricky part is we have a several column headers for the areas they work in. To simplify what I am trying to explain. Let us say they are states. So for example a customer could work in NY, CA, CO, MT and so on. Now this is usually yes or a no. For yes we simply put an 'X' in the customer row for each state they work in. So if a customer works in New York and California then we would put an 'X' under both of those columns.

This works fine, however if I now want to run a privot report to see how many states one of these customers work in then it becomes tricky because each state has its own column heading and therefore a field.

How can I group a range of column headers and call them say 'States' and then from that run a pivot table report to find out what states my customers work in and have a cout of this also?

I am using Excel 2007.

Thanks in advance for any help offered.

Hello everybody!

In a pivot table, I am reporting the results of a survey question "What percentage of potential business is lost to ...xxxxxxx" and the respondent answers with a percentage. Since it is an open response that can have up to 100 different answers, I chose to use the Group function to show the responses 0-10%, 11-20%, etc. Unfortunately Excel lables the groups 0-0.1, 0.1-0.2, 0.2-0.3, etc. I have toyed with the pivot table and field properties for hours but can't get the pivot table to display the results the way I want them to be displayed.

Tips?

Hey Guys

I have one that stumped me! I am using 2003 excel and I need to create a summary on aging stock. The data that I get is in CSV format imported as text into excel. It has the dates like this: yyyy-mm-dd. I tried to group the dates to be monthly. But excel PIV returns this message: Can not group selection. So I did some research and noticed blanks. So I created a dummy date to replace the blanks, eg: 1950-01-01. eg:=IF(M2=" ","1950-01-01",M2).

Now it still will not let me group the dates? So I checked the formats and it is all in TXT format (check). I tried to change the date to the numeric version "DATEVALUE(W2)" and the PIV will still not group it? I can't find a way to have it group the dates that I have on my data. Is there something that I am missing here?

Any help will be much appreciated!!!