Graduation Sale - 20% off! Get the skills they don’t teach in college - Graduation Sale - 20% off

GoSkills
Help Sign up Share
Back to course

Preserving Cell Formats on Update

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
191.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”
Login to download
  • 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.

Value Field Formats
04m:30s
PivotTable Styles
03m:14s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2022 GoSkills Ltd. Skills for career advancement