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!!!


If I ungroup a grouped field then VBA throws an error so I need a way to test if a field in a pivot table is grouped.

ie:

if pf is grouped then
pf.ungroup
endif

anyone ?
Thankd


Hi folks,
I'm new to this pivot table stuff. I have a Date column in Row, and then Footage in Values (summed). What I would like to do is sort Date by week without making groups and then renaming them. From what I've read, I need to use the Group Field function in PivotTable Tools > Options, but I can't click on it. I opened both Date and Footage and unchecked all the blank values, as many people have suggested, but I still can't use Group Field for some reason.

Any assistance would be greatly appreciated.


Hi,
I have the attached Pivot table.
When i create this pivot table, it groups some of the "A# and Name"s.
e.g.
"A277003 / VICTORIA WILSON"
"A444302 / YASEMIN KELES"

Is there for the pivot table to not group by the A# & Name and to list the A# and names on each row...

Please see Output sheet to see exactly how I would like it?... But i would like this to be done in the pivot table so i can create a macro..... to do this..

The ones which are bold have been done manually..

hope you can help.
Thank you


I am using Excel 2003 and Windows XP. My issue is that I can't rename a group within a Pivot Table I'm using. Other groups within the level can be renamed, but the one I am having trouble with just goes back to it's original name (e.g., Group6) or something like that. If I enter a leading apostrpohe, it will take the new name, but the apostrope shows.

I've tried un-grouping and re-grouping everything, checking to make sure the data was all the same format and a variety of other things, but nothing works. This has happened to me intermittently in different spreadsheets. Why can't this one group be re-named when other groups within the sub-group can be re-named?


Hello,

I would like to remove "Group Fields" I had created in some Pivot Tables.

When some field values are grouped in a PT, Excel creates an "Group Field" for this purpose.
These "group fields" become members of the "Pivot table fields list".
Now that I created these grouping values in my database, I would like to remove these "group fields" from my pivot tables.

My motivation is performance.
I have more than 50 PT, all based on large datasets.
I keep them together in one big excel file for convenience.
I have the feeling that the "group field" are deteriorating sometimes the performance.
Today, Excel has been crashing several time when saving the file, therefore, I want to get it slimmer.

Would you know how I could remove the "group fields", at least to check if it works better.

Thanks


I have range of dates by day from December 08 through to current date.

I have created a pivot table that groups these by month.

Now I see Jan, Feb, Dec which is wrong as I wish to Show Dec 08 followed by Jan 09 and then Feb 09.

Is there a way to sort these simply? I have tried manual sort but am not able to drag and drop.

Any ideas - help?!


I have raw data showing activity throughout the day. Each entry is time stamped. I would like to create a pivot table and group it in 5 minute intervals throughout the day. This would create time blocks that i would include in a chart. I would prefer a solution that works with 2003.

Thanks in advance



Got a tricky one, I want to create a pivot table starting with a column of percentages on the left, so for example the first column of the table will be:

25% (Count)
33% (Count)
50% (Count)

The idea being to count how many 50%s there are, how many 25%s etc. Problem is, all of my numbers aren't integers, so I actually have 25.1%, 25.14%, 25.6% etc which leaves my pivot table like this:

25%
25%
25%
25%
33%
33% etc then a count of all the specifics. Is there a way to get the pivot table to ignore everything after the decimal place?

I then want more info to work out some averages of all the values attached to those initial percentages. This works fine, however on some I am doing calculations with zero, giving me "#DIV/0!" and consequently the same value in the pivot table. Is there a way I can exclude that value from counting for just that particular average?

Cheers,


Hello guys
I am looking for some assistance to make pivot tables with multiple data values against a control item.
A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.

Hope somebody could help me out.

Regards,

Hi

I am challenged/very frustrated with my pivot table which will not group dates (returns an error message).

In reading other posted questions, I see that my problem is caused by empty date cells. Is there a way around this?

My spreadsheet will be used daily to input Invoice details - part of which is the date of invoice. If I were to select 'refresh data', this would not be pulling through any new data as it wouldn't be in the range. I had made my range A1:K1500 to incorporate future entries, but then I have empty date cells as to date there are only 200 entries.

Hope this makes sense!! Would really appreciate any suggestions.

Many thanks
Lyn




Hi,

I have a Pivot Table that's referencing a SQL server query as the Data Connection. The query is pretty straightforward. I just want to get a list of the usernames and the date the accounts were created.

SELECT
[Accounts].[Username],
convert(datetime,[Accounts].[DateCreated]) AS [DateCreated]
FROM
[Accounts]

I can get this working in a pivot table, but I want to group the dates by a range so I can see how many accounts were created in the month of May. This is where I'm stuck. The group/ungroup options are disabled.

Untitled.png

What I've tried:

1. I've made sure that the column is formatted by date
2. If I copy the data in the table to another worksheet and create a pivot table all works fine. I'm able to group my dates.

Is it possible that grouping and ungrouping aren't available when pulling data from a SQL query?