Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have a spread sheet that looks something like this...

Excel Data

Now, I'd like to create a column chart that has 3 series, one for each country. Then, I want series for each category, but I want to plot the total, not each individual order total. So, something like this (excuse the horrible artwork)...

Excel Chart

The data label placement isn't all the important, the key is that for each Category (Bikes and Clothes) I chart the total for each country, not individual values from the "Order Total" column.

Is this possible? Is it possible to do the same idea, but to switch Country and Category around?

share|improve this question
// , Thanks for the question! I'm still learning the use cases for Pivot Tables, so I think it's relevant to the tags you used. Maybe consider adding the tag "pivot-tables"? – Nathan Basanese Feb 8 at 22:50
up vote 4 down vote accepted

Pivot tables and pivot charts are perfect for this purpose. They'll let you summarise your data into a table and chart and to easily reorganise both of them.

share|improve this answer
Yes, but i'd really like a regular chart unfortunately. – Chaulky Jan 6 '11 at 0:55
@Chaulky: I don't think it can be done any other way. If there is an alterative you'll probably find it to be so complicated as to outweigh any disadvantages you perceive pivot charts having over normal charts. Hopefully some can enlighten us both. :) – Mike Fitzpatrick Jan 6 '11 at 1:14
You can make a regular chart from a pivot table. Start by selecting a blank cell that doesn't touch any data and insert the chart, then use the Edit Data dialog to add the series one at a time. See Making Regular Charts from Pivot Tables, – Jon Peltier Jun 7 at 12:05

You may want an array formula (I found it by looking for "double sumif()") which essentially lets you specify more than one criteria for the IF part.

See this page for more information:

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .