repeat text on Excel Pivotable with duplicate rows


Please tell me how I would achieve the following;
I have the following data and pivottable, as per attached.

the example data
 the pivot table of what i currently have
This is the pivottable I require, (ignore the format change). Basically I require blank cells to be filled.

 pivot table what I need, ignore format change.

LVL 11
Who is Participating?
Rory ArchibaldCommented:
If you need to keep the pivot table as a pivot table, then you can't (unless you have Excel 2010).
If not, then copy and paste special-values the pivot table, select the column with the blanks in, press f5, click the Special... button and choose Blanks. Then type = and press the up arrow and press Ctrl+Enter.
thydzikAuthor Commented:
thanks for the reply, yes, I need to keep it as a pivot table.
Rory ArchibaldCommented:
Then you are out of luck unless you have Excel 2010, I'm afraid. The only alternative I can think of would be to copy the pivot table to another location and fill in the blanks there.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

thydzikAuthor Commented:
thanks, I will leave this open for a little while, and also try the manual approach.
Rory ArchibaldCommented:
It would be possible to automate the copying to another sheet for example if that would work?
thydzikAuthor Commented:
no need, I really like your proposed manual approach.
Rob HensonFinance AnalystCommented:
It can be done by formula but they would be in a separate table.

Assume Pivot Table is in area A3:E20

In G3 put formula:


Copy down and across to cover same size area as the Pivot Table

This is basically saying if the Field Name cell in the pivot is blank use the same as above, otherwise its a new field.

Rob H

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.