MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cell-level Formatting in a Pivot Table


August 27, 2018 - by Bill Jelen

Cell-level Formatting in a Pivot Table

Pivot tables now support cell-level formatting. Office 365 version Version 9029.2xxxx or later has the feature.

Let's say your manager wants to highlight Diane's sales of Apple. You can right-click that cell and choose Format Cells..., or simply format the cell using the formatting tools on the Home tab.

Apply formatting to a cell in a pivot table
Apply formatting to a cell in a pivot table

When you pivot the table (in this case, swapping rows and columns), the formatting moves with the cell: it is attached to Diane & Apple.

Pivot, and  the formatting persists
Pivot, and the formatting persists

What happens if you remove Diane and put her back? If you remove Diane with a Filter or Slicer...


Remove Diane by using the Slicer
Remove Diane by using the Slicer

...and then bring her back, the formatting will stay attached to Diane:

After Diane is filtered back into the table, the formatting sticks.
After Diane is filtered back into the table, the formatting sticks.

However, the experience is different if you remove the field from the pivot table. In the following figure, I dragged Product out of the pivot table. The red cell apparently requires both Diane and Apple, as the red is gone...

Remove either Product or Name from the pivot table and the formatting is lost.
Remove either Product or Name from the pivot table and the formatting is lost.

When I add Product back in, Excel has forgotten the formatting:

The formatting is forgotten when a field is removed from the pivot table
The formatting is forgotten when a field is removed from the pivot table

Of course, you can apply different formatting to different cells.

Here is another cool feature: the formatting will expand. In this figure, the pivot table has only product. Three products are in color.

Apply formatting to three products.
Apply formatting to three products.

When you add a new field to the rows area, the color expands:

Add a new row field, and the color expands
Add a new row field, and the color expands

Or - add the new field to the column area and the color also expands:

The color is applied to the product level and expands as you add new fields
The color is applied to the product level and expands as you add new fields

Watch Video

Video Transcript

Learn Excel for MrExcel Podcast, Episode 2232: Format One Cell in a Pivot Table.

If you like what you see in this video, please subscribe and ring that bell.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. This new feature added to Office 365, version 9029.2xxxx or later.

I want to create a pivot table from here, Insert, PivotTable. Let's go to an existing worksheet right here so we can see it, click OK. We will put Products down the side, Names across the top, Sales right, and for whatever reason-- who knows why-- my manager is really obsessed about Gary selling Guava, right? He wants to see that sell. So I'm going to choose that cell and right click, and Format Cells, and we will choose green with a white font for that. Click OK. Alright, now, normally when you apply that formatting, that formatting is going to be lost the next refresh, or whatever. So let's just come back here and we will find a Gary Guava. Change them from 520 to 1520 and come back and refresh the pivot table and BAM! it updates.

Alright, now, let's do some more things Let's take Name and Pivot Name down to the rows area and scroll down, and Gary Guava remains formatted. I don't know how they're doing this. Were they storing this information? We'll take product and move it back across the top. Gary Guava still remains formatted. What if I remove it from the pivot table? I'm going to remove it first, using a filter. So, here we'll insert a slicer based on group. Click OK. Now, one of these groups has Gary, and one doesn't, so I'll choose Blue Team, Gary goes away; now I want to choose Red Team, Gary's going to come back and they still remember it. Wow. That is really, really cool. How the heck are they doing it? What are they storing that I can't imagine? Now, the overhead associated with this. Alright, so we put it down the side here. Gary, Guava. Now, what happens if we take product completely out? Alright? Goes away. Put product back? Goes away. So the formatting persists as long as that cell remains in the PivotTable-- remains in the PivotTable.

I guess the other big test here-- the test that I didn't practice before the video, so we really have no idea-- if we say all Red Team Apple-- let's do that, red apple-- Red Apple is going to be formatted, well, of course, in red. Why not? So Red Apple is formatted, and then I add something new like Name here. That persists, so it'll expand-- that thing will get bigger. So, now, once I assigned Red Apple to be red, well then it continues to expand. This is pretty cool. And of course, it's not just one cell you can do as many as as you need, right? So if I want all Gary Guavas to be green, click OK, and then change the pivot table around a little bit so the Apple stays red and the Gary Guava is green.

The other test is, do we have to right-click-- do we have to right-click-- and do format cells or can we just simply apply a format and, magically, it'll work. Alright, so, here we have Red Team, Gary, Date. Let's take the product out. So, Red Team and Gary, I'm going to format Red Team, Gary, and Blue with a white font. See that accessibility checker we talked about the other day-- it's got me. I always think about whether or not I'm going to get dinged on accessibility, so I try and choose colors that have a high contrast. Alright, so I didn't do that by right-clicking and format cells. I just applied the format.

And now let's try and pivot. So, I move Group over here, Name over here, and Gary Red Team continues to work, right? So it's not just that they added format cells, it's so that anytime you format a single cell in a pivot table, that will now travel-- travel with that cell-- and that was added to Office 365, version 9029.2xxxxx or later.

That feature, in my new book, Microsoft Excel 2019. Click that "I" on the top, right-hand corner to learn more about the book.

Alright, you want to format a single cell in a pivot table? If you Office 365, right-click the cell and Format Cell, or even just, heck, apply the Format, the formatting will stick even after pivoting or filtering or adding more data. The place it'll go away, though, is when you remove one of those fields from the pivot table. To download the workbook from today's video, to try this out, visit the URL in the YouTube description.

Well, hey, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: cell-level-formatting-in-a-pivot-table.xlsx

This is one of those features that people probably just expected to work the whole time. Now that it is working, it will not even be noticed that there was once a time that this never worked.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Don’t use */-+ in the SUM Function"

Title Photo: Alondra Olivas on Unsplash


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.