Let's say my source data looks like this:

Brand   Model   Color   Region    Sales
Ford    Focus   Black   N/A       23
Ford    Focus   White   N/A       2
Nissan  Leaf    Black   N/A       12
Nissan  Leaf    White   N/A       6
Nissan  Pulsar  Black   N/A       3
Nissan  Pulsar  White   N/A       4
Ford    Focus   N/A     US        15
Ford    Focus   N/A     EU        10
Nissan  Leaf    N/A     US        5
Nissan  Leaf    N/A     EU        13
Nissan  Pulsar  N/A     US        1
Nissan  Pulsar  N/A     EU        6

I would like a pivot table that looked like this:

                   Color          Region
               Black   White     US    EU
  Focus           23       2     15    10
  Leaf            12       6      5    13
  Pulsar           3       4      1     6    

Is that possible? The only thing I can achieve is this:

                  Black         White            N/A
               US   EU   N/A  US  EU  N/A   US   EU   N/A
  Focus                   23            2   15   10
  Leaf                    12            6    5   13
  Pulsar                   3            4    1    6    

which is pretty ugly.

Problem seems to be that I'm not sure it's possible to have two variables in columns which are not hierarchically related. I could create two different pivot tables, onw wich brand/model/color and another with brand/model/region, but in my real case (that's a simple mockup) there're about 6-7 common variables, and having two huge tables is pretty overkill and not very pleasant for looking at data.

Thanks in advance for any help!


Solution below. It does look a bit too specific for what a pivot table can provide. I threw this together in 10min, so a more complicated one might take longer, but might be pretty difficult to maintain if your source changes a lot.

  • I filled in Columns G and H, which are just a concatenation of your A-->D columns (G2= "=A2&B2&C2&D2"), and a copy of the total sales figure.
  • Next I typed up the output table (what's in grey in I14--> S18)
  • Then used the formula K16= "=IFERROR(VLOOKUP($I16&$J16&K$14&K$15,$G$2:$H$13,2,FALSE),"") " in K16 which pulls out the concatenation for the new table you want, and does a vlookup on our new table.
  • You could clean it up a bit having use Black, White, N/A in the top row, and Nissan in the column only once, by changing the formula a bit. I did it this way so I could just dray and drop the cell.

Let me know if you want me to upload the excel file somewhere for you to look at.

Excel solution

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.