How do I repeat headings in tabular pivot table layout - Excel 2007

hi Folks
Is there a way to quickly copy repeating headings in the tabular layout of pivot tables in Excel 2007. I know it can be done in 2010 but is there any way to do it in Excel 2007. I've tried MrExcel's trick of using F5 (blank cells), =, arrow up and Ctrl and Enter. It doesn't work on pivot table headings alas...
Any suggestions?
Thanks.
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
Click inside your PivotTable > Right click > Pivot Table Option > Select the Printing Tab


 PTimg1
0
Rob HensonFinance AnalystCommented:
@yo bee - I think the question is regarding displaying repeated headings on screen and not printing.

The equivalent in 2010 is on the Pivot Table Tools additional Ribbon, Design Tab, Report layout, Repeat Item labels. I believe this feature came in with 2010.

agwalsh - please confirm my assumption.
0
yo_beeDirector of Information TechnologyCommented:
After I posted I looked back at the question and realized it might be that.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

agwalshAuthor Commented:
@Rob Henson - yes you are right. It's on screen. And yes I know it's in 2010 but alas, this person has 2007 and is not likely to be upgrading to 2010 any time soon :-(
So given all that...any suggestions :-)
0
Rob HensonFinance AnalystCommented:
As I thought, the link below to MS Website confirms that Repeat Labels was new in 2010.

http://office.microsoft.com/en-gb/excel-help/what-s-new-changes-in-pivottables-HA010359479.aspx#_Toc256087896

2007 doesn't have the feature but I do know of a couple of workarounds.

Option 1 - Copy the table and paste values into a separate sheet. You can then use the method mentioned in the question to populate blanks.

Option 2 - Set up a replica table alongside the Pivot with IF statements to populate the replica table. For example assuming Pivot table in columns A to F and replica in columns K to O, the following in column K will populate the blank cell equivalents for column A:

=IF(A5="",K4,A5)

In other words, if the cell in the table is blank use what you have above otherwise use the table value.

Copy across to column O and down as far as required for the table.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
Gives two clearly explained workarounds to a problem solved in Excel 2010. Thank :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.