Cell-level Formatting in a Pivot Table

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.