🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

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
191.8 KB
Conditional Formatting on PivotTables - Completed.xlsx
192.2 KB

Quick reference

Topic

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 we're going to look at conditional formatting and
  • 00:07 how it can add a little bit of business intelligence to our pivot tables.
  • 00:12 All conditional formatting is accessed through the Home tab
  • 00:16 by going to Conditional Formatting.
  • 00:18 And to apply conditional format to a pivot table is very similar to the way we would
  • 00:22 do it with a range except that there's a couple of other
  • 00:25 idiosyncracies that we have to be aware of.
  • 00:27 So the first thing we're gonna do is we're gonna select somewhere inside our
  • 00:31 pivot table.
  • 00:32 And we'll go to Conditional Formatting, and
  • 00:34 I'm going to go to Top Bottom Rules and I'm gonna select the Top 10 Items.
  • 00:40 And for some strange reason, Microsoft decided the default for
  • 00:44 top ten should be light red.
  • 00:46 Personally, I think, it should be green cuz that means good.
  • 00:49 So we're gonna say that the Top 10 Items, we're gonna fill green with green and
  • 00:53 we're gonna say, OK.
  • 00:55 You'll notice that it has not given me the top ten,
  • 00:58 it hasn't even given me the top one, it's just highlighted a single cell.
  • 01:02 But it's thrown up this funky little window beside it.
  • 01:04 And if I go and click on that, it actually asks me would I like to see it for
  • 01:10 all cells showing sales dollar values?
  • 01:13 Well sure.
  • 01:15 And you'll notice now that it's highlighted the top ten values, but
  • 01:18 they're all in my totals, and that is hardly what I'm looking for.
  • 01:22 I'm looking for the information inside the values areas for my top ten.
  • 01:25 That's gonna be much more useful to me.
  • 01:27 I know I know the totals are gonna be big.
  • 01:29 So I'm gonna go back to this little window again,
  • 01:31 and I'm gonna change this to the very bottom item.
  • 01:33 And this is what I usually use when I'm setting up my pivot table items.
  • 01:37 What it asks is, all cells showing the Sales $ values
  • 01:42 where both a Category and a Week have been applied.
  • 01:45 So here's a category, here's the weeks.
  • 01:47 So it's everything in the middle areas, not the subtotals.
  • 01:51 And that reaches through all of the different areas, both with alcohol and
  • 01:55 with food, and it highlights the top ten items for me, and that's kind of cool.
  • 02:00 But what if I wanted to go and see something else,
  • 02:04 like maybe I wanted to apply a different conditional format to show me which
  • 02:07 the biggest numbers were going across the subtotals.
  • 02:11 Well, let's go to Conditional Formatting > Top bottom Rules > Top 10 Items.
  • 02:15 I'm going to go in this case with the top three, I don't think I need all top ten.
  • 02:19 And I'll do them in a slightly different color just so that we can tell them apart.
  • 02:23 Yellow fill with dark yellow text, and say OK.
  • 02:26 And once again it's applied it to a single cell.
  • 02:30 If I click on this and say all cells, it's gonna go through the entire table,
  • 02:35 which will pick up the totals because they're the largest in the entire thing.
  • 02:40 That's not ideal, or all cells showing Sales $ values for Class and Week.
  • 02:46 So this is now class where the previous ones were the category.
  • 02:50 So we'll say OK.
  • 02:52 And you'll notice the top three values are down here.
  • 02:55 Now this is great but, that's all as good as you having this little icon.
  • 03:01 When you click somewhere else, it's still showing here.
  • 03:03 But as soon as I go and put something else away into a cell,
  • 03:08 you'll notice that no matter where I click, that icon's gone.
  • 03:12 So how do I modify this pivot table?
  • 03:14 Well, here's the other alternative.
  • 03:16 We can go to Conditional Formatting and we select a cell somewhere in the pivot
  • 03:19 table, go to Gonditional Formatting and go to Manage Rules.
  • 03:24 When we get in there we have the option to show us the formatting rules for
  • 03:28 this pivot table, those are the ones that we want.
  • 03:31 And that gives us a list of all of the different rules that we have here.
  • 03:34 So we can click on the rule, and say Edit Rule.
  • 03:39 And once we're in there, does this look familiar?
  • 03:41 It's applying this rule as its target to B12.
  • 03:44 That's where we started,
  • 03:45 that's why the marching ants are walking around this particular cell right now.
  • 03:49 It also gives the same three options that were in that little dialogue box that came
  • 03:53 up on the top right hand side.
  • 03:54 So If you prefer to work with a full-size window,
  • 03:57 you don't have to ever use that little dialog.
  • 03:59 You can apply the conditional formatting rule and then go into manage rules and
  • 04:02 come into the full-size window.
  • 04:04 And within here, you can do all kinds of different interesting formats.
  • 04:08 We can even change this out if we wanted a darker green with a white font,
  • 04:13 for example, to make something that's a little bit different.
  • 04:16 We can completely do this and say OK and
  • 04:19 apply and get a slightly different style on our pivot table as well.
  • 04:24 Conditional formats are a whole another topic,
  • 04:26 its the key piece that we wanna be aware of here is how to apply them and
  • 04:30 how to apply them to different areas on the pivot table.
  • 04:33 And that whole discussion, all revolves around what happens in here.
  • 04:39 Are we selecting all cells or are we only selecting the ones that are in
  • 04:44 a subtotaled or in a values area by choosing the last option?

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