Help formatting pivot table

[Alanna 10]

Hi there,


I know its probaly a common question however I need help with formatting my pivot table.


I have set up my lines/fills/font and accounting but when I refresh it dosnt stay. I have the Preserve cell formatting on update clicked on but it still changes.


Please help - its for an invoice and needs to look tidy.

 

[Luke M]

Try reading these 2 links. SHould help you out:

http://www.contextures.com/xlfaqPivot.html#FormatLost

http://www.contextures.com/excel-pivot-table-format.html#keep


If you need more robustness, you'll need to record a macro of you applying the formatting changes, and then setup an event macro so that everytime the PT gets refreshed, the formatting macro runs and re-applies all your changes. This is obivously the trickier route, so hopefully the other methods will work.

 

[Alanna 10]

Thanks, no those links didnt help. Can you help me with the Macro option?

 

[Luke M]

I'll give it a try. Let's start by having you record the macro of you doing all the changes. Then, post the macro here, and we'll work on cleaning it up and putting it in the event macro.

 

[Alanna 10]

Ok so sorry im very very new to Macros, I dont even know what they are?

Also not sure how to post/attach anything

 

[Luke M]

Here's step by step.

1. Refresh your table so that all formatting is reset.

2. Start recording a macro. (Hit Alt+t, m, r)

3. Give the macro a simple name, and start recording.

4. Make all your formatting changes.

5. Stop recording the macro. You should have seen a new toolbar pop-up with the VCR Stop symbol, or it will appear in bottom left corner.

6. Right click on the sheet tab, Select "view code". This will open the Visual Basic Explorer (VBE)

7. On left hand side, you should see a project naviagor (like windows explorer). Find your project, and then go to Modules and click on Module 1.

8. Your macro that you just recorded should be here. Select the entire thing, copy.

9. Paste the macro directly to this thread. If you want to preserve formatting, put a backtick(`) at beginning and end of macro. The backtick is the key above Tab.