Excel Pivot Table - Repeating Lables

[Guy F]

Hi, I’m new to the forum, hope there are some pivot table gurus
that can help me.

When doing a pivot table, row labels that pertain to multiple rows are
only shown once. Anyway to have the row labels repeated for each row
that it pertains to?

Simple example, if I want to show revenue by state and by product, and
there are 3 products for each state, my pivot table would have 3
columns: Column 1) State, 2) product, and 3) revenue. Revenue is the
data column. For each state, there would be 3 rows for each product,
the state name is only shown on the first row, with the other 2 rows
blank. Any way to make the state repeated in each row?

I know MS is just trying to make is easy to read, but it is pain when
you want to use this information in a database function, which requires
all information for each record. You have to manually copy and past the
information down for each row/record. You can click on
“Data”, then “PivotTable and PivotChart
Report…”, then “Options”. From here, you can
choose “Merge Labels”. This will merge the 3 rows with the
state name, but this does not help either. It seems, intuitively, that
they would have a “Repeat Labels” option there.

Any comments on the would be appreciated. By the way, I’m using
excel 2002.

 

[Guest]

Hi,

Create a helper column. Let's call it State1. Type "1" and copy it down all
the way down to the bottom of last row. Drag this helper column first in your
row selection of Pivot table, then state and product. This way it will list
all the state in Pivit Table and hide your helper column.

Or you can use this helper column to write vlookup formula which will refer
to a table that you will create (to some remote location on a sheet) like CA
in one cloumn corresponde to 1 in second column. And go down the list of many
state that you have.

Hope this help you.

Dinesh