Preserving Cell Formats on Update

Quick reference

• 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.