GoSkills
Help Sign up Share
Back to course

Conditional Formatting on PivotTables

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Basics of applying conditional formats to a PivotTable.

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.

Conditional Formatting on PivotTables.xlsx
199.2 KB
Conditional Formatting on PivotTables - Completed.xlsx
199.8 KB

Quick reference

Conditional Formatting on PivotTables

Basics of applying conditional formats to a PivotTable.

Where/when to use the technique

When you’d like to add some context-sensitive formatting to a PivotTable.

Instructions

Creating a conditional format

  • Click on the column you wish to format
  • Go to Home > Conditional Formatting
  • Pick the appropriate rule
  • Click the little icon to the top right of the cell to expand the format options
  • Make a choice of how you’d like to apply it:
    • Selected cells
    • All cells in the table (includes totals and subtotals)
    • All cells showing “x” values for “y” and “z” (values excluding totals and subtotals)

Modifying a conditional format

  • Select a cell that holds the format to be modified
  • Go to Home > Conditional Formatting > Manage Rules
  • Select the rule to modify and click Edit

Clearing a conditional format

  • Select a cell that holds the format to be removed
  • Go to Home > Conditional Formatting > Manage Rules
  • Select the rule to modify and click Delete
Login to download
  • 00:04 In this video,
  • 00:05 we're going to see if we can add a little bit of intelligence to our pivot tables.
  • 00:09 The challenge that we've got right now is that when we go and
  • 00:11 we take a look at this pivot table, I'm just going to zoom out a little bit so
  • 00:15 we can see a little bit more information.
  • 00:17 There's a lot of numbers here.
  • 00:19 But the challenge is that doesn't really provide us a lot of insight,
  • 00:21 I'd like to see which my top values are.
  • 00:24 But I don't want to filter the pivot table.
  • 00:26 What I really want to do is highlight them,
  • 00:27 so I'm going to use conditional formatting to actually make that happen.
  • 00:31 Now, here's the thing.
  • 00:32 I'm going to go and click on a single cell, doesn't matter which one really.
  • 00:35 But I'm going to start in the top left-hand corner because that's where I
  • 00:38 always start my conditional formats from, just makes it easier to read them and
  • 00:41 maintain them later.
  • 00:42 I'm going to go to Conditional Formatting > Top/Bottom Rules,
  • 00:45 we're going to take the Top 10 Items.
  • 00:48 Now I'm not sure why Microsoft felt that the top ten should be highlighted in red.
  • 00:51 Generally, we kind of look at this and say well, good things are green, so for sales,
  • 00:55 we're going to go with Green Fill with Dark Green Text.
  • 00:58 And when I say OK, you'll notice that it actually applies it not to the top
  • 01:03 ten items in the pivot table, but it actually applies to a single cell, and
  • 01:07 this is a little bit weird.
  • 01:08 It also pops up this funky little widget beside it.
  • 01:12 And if I go and click on this, it gives me some formatting options.
  • 01:15 And it says would you like to apply it to the selected cell, and
  • 01:17 that's what happens by default.
  • 01:18 So when you're working with a range,
  • 01:21 you select the entire range that you want to deal with.
  • 01:23 On a pivot table, we start with a single cell, but
  • 01:27 we've gotta toggle with this little piece here.
  • 01:29 So we don't want it for the single cell.
  • 01:31 Let's try this All showing Sales $ values.
  • 01:34 And when we click on this, it now highlights all of the values in our totals
  • 01:38 and subtotals and you think well, okay, that's weird.
  • 01:41 I kind of knew that my totals and subtotals were going to be the biggest.
  • 01:43 That's not really what I want.
  • 01:45 So let's go back and try and change this again.
  • 01:47 We'll change it this time to All cells showing Sales $ values for
  • 01:51 Category and Week.
  • 01:52 And what is this actually talking about?
  • 01:55 It says, I want you to look at this cell and
  • 01:57 see whether it's been filtered by category and by week.
  • 02:00 The grand totals and the subtotals,
  • 02:01 they don't actually fall into that classification.
  • 02:04 It has to have a specific single line item for either my row or for my column.
  • 02:08 And at that point, it actually now applies nicely to show me my top ten items,
  • 02:12 which is great.
  • 02:14 But what if I do want to filter and
  • 02:16 I want to find out what my top three subtotals are?
  • 02:19 Well, let's try again.
  • 02:21 We'll set up a new rule, Conditional Formatting > Top/Bottom Rules > Top 10.
  • 02:26 And we're going to go and this time, it doesn't make sense to actually figure out
  • 02:29 all ten because there's only ten categories here, so let's go with three.
  • 02:33 And we're going to go with a Yellow Fill, just to make it different and
  • 02:37 we'll say OK.
  • 02:38 And once again, it applies it to a single cell.
  • 02:41 So we'll go back and we'll say, all right, let's go with All again.
  • 02:45 Wait, no, that's not the right one.
  • 02:47 So let's go to All showing the Class and Week.
  • 02:50 Generally, what I find when you're trying to apply a conditional format to a pivot
  • 02:54 table, you want to apply it to the last item on this list.
  • 02:57 Unless you've got averages, then going across the entire thing makes sense.
  • 03:00 But if it's specific values, it usually wants to be the last one.
  • 03:04 At that point, you can that these three are the biggest sellers that we actually
  • 03:08 have, so that's good to know.
  • 03:11 Now, this is great because the pivot table formats will stick as we collapse
  • 03:15 different parts of our pivot table.
  • 03:17 The green obviously goes away because it's not needed at this point.
  • 03:20 And when I expand things, it brings them back, so
  • 03:22 everything's still good there, it all sticks and collapses nicely.
  • 03:25 But the problem is, what if I now decide that I want to reconfigure this
  • 03:29 conditional formatting rule?
  • 03:31 Even if I go back and click the cell,
  • 03:33 I've lost the widget that allows me to change it.
  • 03:35 No, what do I do?
  • 03:37 Well, the answer at this point is we actually go to Conditional Formatting and
  • 03:42 we go to Manage Rules.
  • 03:43 If you ever need to click clear rules, by the way, you can do it here, you can clear
  • 03:47 them from the entire pivot table, or the entire sheet, or wipe them all out.
  • 03:50 But we just want to change one.
  • 03:51 So we're going to go to Manage Rules, which will pop up this dialog.
  • 03:55 When I select my top three, all values, I can now say Edit.
  • 03:59 And what you'll see is that here's all the options from the little widget, so
  • 04:03 they're all there, you don't lose them.
  • 04:04 If you prefer to work in a full-size dialog, you can just come straight into
  • 04:08 Manage Rules after you set it up and configure it here.
  • 04:10 The nice thing is we can also make modifications to our conditional format.
  • 04:14 So maybe I say, hey, I really want to go with actually a nice dark green fill.
  • 04:18 And I want to have a white text on it that's in bold for these guys.
  • 04:23 I can totally set that up to make a custom conditional format
  • 04:26 that looks absolutely stellar like that and there we go.
  • 04:29 So that's the basics of how we actually use conditional formatting on
  • 04:33 a pivot table.
  • 04:34 Conditional formatting is an entire topic all on its own.
  • 04:36 It's something we do teach in GoSkills Dashboarding course, but
  • 04:39 to apply it to a pivot table, you need to know those quick little tricks.

Lesson notes are only available for subscribers.

Custom PivotTable Styles
05m:16s
Basic Sorting
03m:24s
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

© 2023 GoSkills Ltd. Skills for career advancement