One way -

Create a "new" set of data that translates your 3 columns of data into 2 by
concatenating the Continent and Product columns. Assuming Continent is in
column A and Product in B, then

=A2&" - "&B2

will give you

Asia - Red

Then build your pivot table using the new, concatenated column instead of
the distinct Continent and Product columns


" wrote:

Hi all,

I was hoping you can help me with this question: how do I build a
pivot table which looks like a "group by" operation in a database?

Let's say I have this table:

Continent Product Sales
Asia Red 100
Asia Yellow 200
Asia Red 100
Asia Yellow 300
Europe Red 100
Europe Yellow 200
Europe Red 100
Europe Yellow 300


An Excel Pivot table will return this:

Row Labels Sum of Total sales
Asia 700
Red 200
Yellow 500
Europe 700
Red 200
Yellow 500
Grand Total 1400

whereas a statement like:

select Continent, Product, sum(Sales) as TotalSales
from MyTable
group by Continent, Product

in SQL will return:

Continent Product Total sales
Asia Red 200
Asia Yellow 500
Europe Red 200
Europe Yellow 500

Is there an easy way to obtain the latter in Excel?
Thanks!