Calculate cell row and column

B

Barb R.

I have a case where I have a pivot table one ROW designator with about 20
values. I also have about 10 values. What I want to do is have the data
listed as

ROW 1 Data value 1 Data value 2 Data value 3 instead of

Row 1 Data value1
Data value2
Data value3
Row 2 Data value1

etc.

Is there a way to do this that isn't tedious?

Thanks in advance,
Barb Reinhardt
 
B

Bernie Deitrick

Barb,

Drag the Data Value button to the top of the columns.

HTH,
Bernie
MS Excel MVP
 
B

Barb R.

Tried that. It didn't work. I have data for 10-12 subprograms summarized
as data for several metrics. I want to summarize each metric by subprogram.
If I put the metric at the top of the column, it summarizes it by value for
the metric.

Did I miss something?

I opted to use the "Transpose" function, but it's not as elegant as I'd like.

Barb Reinhardt
 
B

Bernie Deitrick

Barb,

If you could post a small table of your data, the result you get, and the
result you want, then it would help clarify what you mean.

HTH,
Bernie
MS Excel MVP
 
B

Barb R.

OK, here is an attempt at an example without real data
Metric 1 Metric 2 Metric 3 Metric 4
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3

I want to sum metric 1, 2, 3, etc. or each Group. My pivot table looks
like this:

Group 1 Metric1
Metric2
Metric3
Group 2 Metric1
Metric2
Metric3
Group 3 Metric1
Metric2
Metric3

And I want
Metric 1 Metric 2 Metric 3
Sum Sum Sum
Group 1
Group 2
Group 3
 
B

Bernie Deitrick

Barb,

You don't actually have a database: you have a cross-tab table. To get it
to work the way you want, you need to "un-cross-tab" your data, by using a
pivot table in a special way, described at:

http://j-walk.com/ss/excel/usertips/tip068.htm

Once you've done that, you can use a pivot table in a normal way to get the
result you want.

HTH,
Bernie
MS Excel MVP
 
B

Barb R.

I never said I had a database. I'll take a look and see what I can glean
from the link.

Thanks,
Barb Reinhardt
 
B

Bernie Deitrick

Barb,

Yes, you never said that you had a database, but I wanted to be clear in my
description, since pivot tables expect database style data tables. That's
why I asked for an example of the type of data that you had.

HTH,
Bernie
MS Excel MVP
 
Top