Locked lesson.
About this lesson
How to force a PivotTable to preserve cell formats and column widths when it is updated.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Preserving Cell Formats on Update.xlsx191.4 KB Preserving Cell Formats on Update - Completed.xlsx
191.5 KB
Quick reference
Topic
How to force a PivotTable to preserve cell formats and column widths when it is updated.
Where/when to use the technique
When you want Excel to stay true to all the formatting work you’ve done when the PivotTable is updated.
Instructions
Preserving Column Widths
- Right click the Pivot
- Choose PivotTable Options
- Uncheck the box next to “Autofit column widths on update”
Preserving Cell Formatting
- This option is enabled by default
- To turn this feature off, so that a pivot table resets it’s formatting
- Right click the Pivot
- Choose PivotTable Options
- Uncheck the box next to “Preserve cell formatting on update”
- 00:00 One of the things that drives me absolutely crazy with
- 00:04 PivotTables is after I have gone and
- 00:07 spend a whole bunch of time formatting them to get them just the way I want.
- 00:12 And then when I refresh it all of my formatting is trashed.
- 00:16 That's no fun at all.
- 00:17 Nobody likes that.
- 00:18 In this particular video, i'm going to show you how to control
- 00:21 which pieces are preserved and which pieces are tossed when you actually do
- 00:25 a PivotTable update to protect some of your hard work here.
- 00:29 The first thing i'm going to do is i'm going to select columns B through G, and
- 00:33 i'm just going to shorten these guys up a little bit.
- 00:36 And oh that's a little bit too much.
- 00:38 There we go.
- 00:39 I've got some nice column widths.
- 00:40 And I'm gonna go and center my headers.
- 00:43 And then maybe I'll go and highlight a couple of cells because I wanna go and
- 00:47 look into those.
- 00:48 So this is what somebody might do with their pivot.
- 00:51 And the next thing we know, we come along, we right click, we refresh the PivotTable.
- 00:55 And you can see that all of the highlighting for things that I used to
- 00:58 look at is still highlighted and all of my column widths changed.
- 01:02 Well that's no fun, I'd actually kind of like this to be the other way around.
- 01:05 So here's what we can do with this.
- 01:08 If we right click on the PivotTable, and we go down to PivotTable options
- 01:14 you'll notice that by default it's checked to auto-fit column widths on update.
- 01:20 So let's uncheck that and say OK.
- 01:23 And now when we go back here, and I'm going to resize my columns again.
- 01:29 And now, once again, I'll right click and refresh my PivotTable.
- 01:32 And you'll notice that my columns stay put.
- 01:35 Now I still got a bit of a challenge though with the highlighting,
- 01:39 it;s still here.
- 01:40 You'll notice, actually let's just take a quick look at how this works.
- 01:43 I've got certain cells highlighted that somebody wanted to look at, and if I were
- 01:46 to drag my class onto my PivotTable right now, placed above category.
- 01:50 You'll notice that those cells stay highlighted, the entrees here.
- 01:54 If I take class off the PivotTable, they stay highlighted as well.
- 01:59 And if I refresh my PivotTable, once again, they stick around.
- 02:02 Well, that's not ideal.
- 02:03 If somebody's done some mark-up on my pivot, I probably don't want to see that.
- 02:07 Let me see, let's go and
- 02:08 take a look with how we would go about actually fixing this.
- 02:12 If we right-click in the pivot, and we say PivotTable Options, you'll notice down
- 02:17 the very bottom there is an option to preserve cell formatting on update.
- 02:21 If we uncheck that box, and we say OK, the pivot is redrawn and
- 02:25 these things disappear.
- 02:27 Well let's see.
- 02:28 How effective was that?
- 02:30 Let's go highlight some different cells here.
- 02:32 We'll highlight these guys here, and let's say that we had somebody that came in and
- 02:37 they applied a different number format and
- 02:39 they've made some changes to the headers up at the top.
- 02:42 And why not? Let's go and
- 02:43 put some of these things in a red font or something.
- 02:45 So a whole bunch of different kind of stylistic changes.
- 02:49 So what happens now when I right click and refresh?
- 02:56 Every single one of those formatting changes that I just made is gone.
- 03:00 We've lost the highlighting that was over here.
- 03:03 The red font, all of the number formats.
- 03:05 That's one reason why we never apply number formats directly to a cell.
- 03:10 And our centering on our headers has gone as well.
- 03:13 So unfortunately we can't control the individual elements.
- 03:15 If it's formatting, it gets wiped if we change the setting.
- 03:19 So that's how we go about controlling the individual elements, with column widths.
- 03:24 And with formatting, setting ourselves up to not resize column widths but to clear
- 03:30 off all of the markup and formatting that people have applied to our PivotTable.
Lesson notes are only available for subscribers.