Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Non-nested rows/columns in a pivot table?
Is it somehow possible to not have rows/columns nested?
Say, for example, that one field is 'colour' and another is 'year'. Is it possible to have one column with 'colour' and another with 'year' rather than a nested 'years per colour' or 'coulour per year' format? I realize that it will give some problems with the sum rationale but it would sometimes be useful nonetheless. Thanks Henrik |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Non-nested rows/columns in a pivot table?
Hi Henrik
Do you mean that you have both Colour and Year as row items? If so, drag one of them to be a Column item instead, or drag one of them to be a Page item. -- Regards Roger Govier "Henrik" wrote in message ... Is it somehow possible to not have rows/columns nested? Say, for example, that one field is 'colour' and another is 'year'. Is it possible to have one column with 'colour' and another with 'year' rather than a nested 'years per colour' or 'coulour per year' format? I realize that it will give some problems with the sum rationale but it would sometimes be useful nonetheless. Thanks Henrik |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Non-nested rows/columns in a pivot table?
Hi Roger
There would be other rows already, and you would have the intersections displayed anyway, wouldn't you. I'm looking for something like (not sure if the table will come out all right): Colour Year Total Blue Red 2005 2006 Audi 50 25 60 15 75 BMW 20 25 15 30 45 Total 70 50 75 45 120 What I don't want is 'blue cars in 2005 / 2006', etc. Henrik "Roger Govier" wrote: Hi Henrik Do you mean that you have both Colour and Year as row items? If so, drag one of them to be a Column item instead, or drag one of them to be a Page item. -- Regards Roger Govier "Henrik" wrote in message ... Is it somehow possible to not have rows/columns nested? Say, for example, that one field is 'colour' and another is 'year'. Is it possible to have one column with 'colour' and another with 'year' rather than a nested 'years per colour' or 'coulour per year' format? I realize that it will give some problems with the sum rationale but it would sometimes be useful nonetheless. Thanks Henrik |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Non-nested rows/columns in a pivot table?
Hi Henrik
I don't think you can manage that with a PT. You could drag one of the 3 fields to a page area, and therefore show the other 2 as Row and Column values for that particular page selection, or for All. Assuming your data is set out in columns A:D as Make Colour Number Year If you then set out your table headings in G1:J1 as Red, Blue, 2005, 2006 and F2:F3 as Audi, BMW then the following Sumproduct formulae G2 =SUMPRODUCT(($B$2:$B$100=G$1)*($A$2:$A$100=$F2)*($ C$2:$C$100)) copied to H2, and then G2:H2 copied down to G3 I2 =SUMPRODUCT(($D$2:$D$11=I$1)*($A$2:$A$11=$F2)*($C$ 2:$C$11)) copied in a similar manner will produce the summary you want. Change ranges to suit -- Regards Roger Govier "Henrik" wrote in message ... Hi Roger There would be other rows already, and you would have the intersections displayed anyway, wouldn't you. I'm looking for something like (not sure if the table will come out all right): Colour Year Total Blue Red 2005 2006 Audi 50 25 60 15 75 BMW 20 25 15 30 45 Total 70 50 75 45 120 What I don't want is 'blue cars in 2005 / 2006', etc. Henrik "Roger Govier" wrote: Hi Henrik Do you mean that you have both Colour and Year as row items? If so, drag one of them to be a Column item instead, or drag one of them to be a Page item. -- Regards Roger Govier "Henrik" wrote in message ... Is it somehow possible to not have rows/columns nested? Say, for example, that one field is 'colour' and another is 'year'. Is it possible to have one column with 'colour' and another with 'year' rather than a nested 'years per colour' or 'coulour per year' format? I realize that it will give some problems with the sum rationale but it would sometimes be useful nonetheless. Thanks Henrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I use nested IF using OR in a pivot table? | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
how to delete/clean out the row list in pivot table | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) |