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.xlsx199.3 KB Preserving Cell Formats on Update - Completed.xlsx
199.3 KB
Quick reference
Preserving Cell Formats on Update
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 PivotTable
- 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 PivotTable
- Choose PivotTable Options
- Uncheck the box next to “Preserve cell formatting on update”
- 00:04 One of the things that drives me absolutely crazy with PivotTables is when
- 00:08 I've gone and
- 00:08 spent a whole bunch of time formatting them to get them just the way I want.
- 00:13 And then when I click the Refresh button,
- 00:15 all my formatting is trashed, that's no fun at all, nobody likes that.
- 00:18 And in this particular video, I'm going to show you how to control which pieces
- 00:22 are preserved and which pieces get tossed when you do an update.
- 00:26 And this'll hopefully help you protect some of your hard work.
- 00:28 So let's first demonstrate the issue here, I'm going to go and
- 00:31 resize these columns here, from B through G.
- 00:34 And I'll set them up to about, I don't know, a 16 width, somewhere around there,
- 00:37 so that looks nice.
- 00:39 And now I'm going to go and
- 00:40 highlight some of the key things that I might want to look into.
- 00:42 So these cells here, I might want to go and investigate why are these ones blank,
- 00:46 and why are these ones at zero?
- 00:48 And there we go, I've got everything set up the way I want.
- 00:51 At this point, I can go and refresh my data, Data, Refresh All.
- 00:55 And you'll notice that the formatting sticks,
- 00:57 which I kind of don't really want to see.
- 00:59 Because what if I had formatted this and next month comes along?
- 01:02 I don't want to see the yellow stick around, and
- 01:04 all my column widths have been re-shrunk, which isn't what I want either.
- 01:07 I actually kind of wished this worked the other way around.
- 01:11 So how can I actually take control of this, well, here's how it works.
- 01:14 What we're going to do is we're going to right-click on our PivotTable, and
- 01:17 we're going to go to PivotTable Options.
- 01:19 And the first thing that I want to show you here is this Autofit column widths
- 01:22 on update.
- 01:23 So I'm going to uncheck that box and say,
- 01:25 OK, nothing really looks like it's all that different.
- 01:29 But now what I'm going to do is I'm going to resize my PivotTable here,
- 01:32 we'll get back to around the 16 mark, there we are.
- 01:35 And now when I go Refresh All, you'll notice that I can do this as many times as
- 01:39 I want and those columns will stay put, widthwise, which is pretty nice.
- 01:44 Now, what about this formatting, well, it's set up to work this way because,
- 01:48 at this point, I could actually drag Class onto my PivotTable.
- 01:51 And you'll notice that I've got Canned Beer, Coolers/Ciders, and
- 01:53 my Food Modifiers highlighted.
- 01:55 Look what happens when I put Class onto my PivotTable.
- 01:58 It keeps the highlighting in context with those cells, so
- 02:01 that's actually really kind of cool in a lot of ways.
- 02:04 But the key thing is I've highlighted it at this month end.
- 02:07 And when I go and refresh next month end,
- 02:08 I really want to start with a fresh, clean PivotTable.
- 02:11 So here's the deal, we can go right-click, PivotTable Options, and
- 02:15 we can choose to uncheck Preserve cell formatting on update.
- 02:19 And when we do that, it's immediately going to wipe out those colors.
- 02:22 Now, no big deal, we wouldn't normally set this up as we're going like this.
- 02:26 But I can now go and highlight these guys and say, all right, let's take a look.
- 02:29 We'll highlight those, everything looks good,
- 02:32 and when I hit Data, Refresh All, they go away.
- 02:35 So next month, I can highlight these ones that I need to look into, there we are.
- 02:39 And when I right-click and Refresh, that's going to go and reset as well.
- 02:44 So this is pretty handy because it gives us the ability through those options to
- 02:48 actually lockdown those columns widths.
- 02:50 They're actually set to expand by default so that you don't end up with hash marks
- 02:54 in your cell and the number gets too big for the cell.
- 02:57 But ideally, here we can set it up to look exactly the way we want, and
- 03:01 the columns will stay put on refresh.
- 03:03 And that nice little highlighting, we can actually make it go away so
- 03:06 we can do our next reconciliation next month from a clean point.
Lesson notes are only available for subscribers.