Stop Sending Ugly Pivot Tables to Your Boss

For years in my corporate life, my PivotTables looked like well, this not exactly something you want to put in front of management. So whenever I needed to present this data, I would copy it out and format it on the grid separately. And honestly,

a lot of people on the team did the same thing. I had no idea that a PivotTable could actually look like this. There was no need for me to copy and paste and format this stuff separately.

like this. There was no need for me to copy and paste and format this stuff separately.

And this is a fully functioning PivotTable. The design is minimalistic. It's professional. We

have visual indicators that instantly show us what's over budget. And the

whole thing is completely dynamic. Had I known this, my life would have been a whole lot easier. So if you use PivotTables at work, you're going to want to see this.

So here's our data, and we want to create a budget report for our fast food chain. So we

have expenses for actual and budget. We have a couple of departments, a couple of categories, and our months currently go from Jan to June. Now, this is way too much information and data to read through. And this is exactly what PivotTables are for.

So, the current data is formatted as a Table. We can see it over here in Table Design. I'm going to go ahead and insert a PivotTable on a new Worksheet. Let's bring

Design. I'm going to go ahead and insert a PivotTable on a new Worksheet. Let's bring

over the PivotTable Fields. Now I want to add Department and Category to the Rows. Let's go ahead and add Budget and Actual as Values. Actually, I want it the

Rows. Let's go ahead and add Budget and Actual as Values. Actually, I want it the other way around. So I'm going to click and drag Actual and bring it before Budget. And I

want to add Month as a Filter. So I'm going to click and drag it to the Filters area.

Okay. So here is what we get. With Month being in the Filter, we're able to look at one month at a time. So, this is the classic PivotTable layout. Let's be honest, I mean, it's not that terrible, but it's not that great either. We're going to fix that.

One improvement though in Microsoft 365 that you might have noticed is that the source formatting pulls through. So notice these numbers already have the thousand separator because that's how

pulls through. So notice these numbers already have the thousand separator because that's how my data was formatted at source. Now this formatting pulls through, which means I don't have to adjust these if I want to keep this formatting. I'm fine with this. So let's keep it.

But there is one information that's missing. It would be good to see the variance so that we can see how much over or under budget we are. Let's add it to our PivotTable.

I'm going to go to PivotTable Analyze, Fields, Items, & Sets, and add a Calculated Field.

I'll call this Variance, and I'll go with Budget minus Actual, and OK.

So anytime we see a minus, it means that we're over budget, and these are expenses. So it's not good here to be over budget. Wherever we have a plus, we're under budget. Now, this is a good addition because it makes the report easier to read, easier to digest. But now, it's time to work on the layout.

And let's start with the changes that make the biggest impact. I'm going to go to Design, Blank Rows. Let's insert a blank line after each item. This gives your categories a bit

Blank Rows. Let's insert a blank line after each item. This gives your categories a bit more breathing space. Right? This is a lot easier to read like this.

Now you can also adjust the report layout and show it in Tabular Form, for example. This splits things a bit more. So notice we have Department and

for example. This splits things a bit more. So notice we have Department and Category in their own columns instead of nesting them. But I think the nested view works pretty well in our report. So I'm going to go back to Compact Form.

Now let's just remove some of the clutter. Let's go to the Analyze tab and turn off this Plus and Minus buttons. So, I don't need anyone to be able to expand or collapse these.

I just want to take them off. And I'm going to remove the Field Headers here.

Notice the Subtotals are on top. That's fine. I want to keep that. On the bottom, I have Grand Total, but I'm not so fond of Grand Total. I'm going to call it All Departments. And yes,

you can actually type whatever you want here. And when you refresh it, it's going to stay.

Now for the headers, no one wants to see Sum of Actual, Sum of Budget. So

let's replace this. The thing is that if I completely remove this and press Enter, we're going to get an error message that the field name already exists. Excel doesn't like it if we are using an identical name here that we have in our Field List. So the trick is to

add a space either before or after the name. So I'm going to add one before. Let's press

Tab to go to the next one and adjust this. And tap again and adjust this.

Okay, so this is already a lot cleaner. We can switch and take a look at Jan values only. Switch and take a look at Feb. But it still has that PivotTable look. Let's get rid of that.

only. Switch and take a look at Feb. But it still has that PivotTable look. Let's get rid of that.

And this is the part that makes the biggest difference. We're going to be creating our own custom PivotTable Style. Now, I'm going to walk you through each step, but if you don't want to do it all from scratch, you can download my file from the link in the description. The Style is already built for you. You just need to copy it and paste it

description. The Style is already built for you. You just need to copy it and paste it into your own Workbooks. And I'm going to show you how to do that at the end of this video.

But if you want to understand how this works, and trust me, it's worth knowing, let's build it.

Let's go to the Design tab. And here you can select between different PivotTable Styles. So you could go with something more minimalistic like this one or this one. Actually,

Styles. So you could go with something more minimalistic like this one or this one. Actually,

I'm going to pick this one as my basis. So this is White, Pivot Style Light 1. Right.

This already looks pretty neat and clean, but we're going to customize it further.

And to do that, you just need to right mouse click and Duplicate. Up here,

you can give your Style a name. I'm going to call it XelPlus Style. And here you can see all the elements that are used in a PivotTable. That's the key for keeping everything dynamic because you're not formatting cells, you're formatting PivotTable elements.

And the ones in bold here mean that they already have some special formatting in place.

So the main one is the Whole Table. This defines how everything is formatted. And

then you can overwrite an element by selecting it from here. For example,

notice the Header Row. That one already has some formatting applied to it.

So I'm going to click on Format. We can see in Border, these are the borders currently applied. In Font, this is the color that's currently used. You can change these to fit

applied. In Font, this is the color that's currently used. You can change these to fit your needs. I'm going to keep the font color as is. But for Border,

your needs. I'm going to keep the font color as is. But for Border, I actually want to go with this black color. And I only want a border on the bottom. Right? I only want the border here. I don't want this border on top.

bottom. Right? I only want the border here. I don't want this border on top.

So, I'm actually going to get rid of everything. And then only click here to add a border on the bottom. Now, just to see how this looks, let's click on OK. And OK.

Now, notice nothing happened in the PivotTable. That's because the old Style that I selected is still applied. To apply this new Excel Plus Style, I'm going to go here, and you're going to see your custom Style here. Notice when I hover over it, we see the name. Select that. And now we can see that Style applied.

I'm actually going to go ahead and remove the Gridlines from the sheet just so that we can see our PivotTable pop more. Let's remove that.

I do want to make some other changes because notice here we have this empty row, but then we have the border on the bottom and on top here. I

find this to be a bit too much of a clutter. I want to remove that.

So, let's go back to Design. Just make sure you select your custom Style here, right click, and go and Modify this. Now, we're going to look for the Blank Row element. That must be somewhere right here. Currently doesn't have anything applied to it. So,

Row element. That must be somewhere right here. Currently doesn't have anything applied to it. So,

whatever formatting it has, it's coming from the Whole Table formatting.

We're going to go and Format it here. Under Borders,

I'm just going to get rid of everything. I don't want that. Let's click on OK.

And actually, another thing I want to do before we commit this is I want to adjust the Whole Table formatting. Let's go over to Font. Let's not go with this super dark color, but this one. And for Border, I am going to get rid of everything first. I don't want

those side borders. I do want the middle border here. And this color right now, that's pretty good. It's not too strong. So, let's add that in.

But for the vertical line, I actually want a thick white border. So, the thick one is here.

I'm going to switch to white. Now, I can't see where it is. It's a good thing I saw it before.

It's right here. Now, I'm going to add that as my vertical border. And this adds a very subtle white in between. And it breaks things up a little bit and makes things look really neat.

Now, we remove that one here. And you can notice here we have that subtle white break in between the border. So, this already looks a lot nicer.

Let me just insert a row here. Just insert a row on top so we can see the side borders are also gone.

Now comes my favorite part. But by the way, we cover lots of cool tricks that many people who use PivotTables every day don't know about in my Pivot Table Mastery course. There we will take you from beginner to saving a ton of time with PivotTables.

course. There we will take you from beginner to saving a ton of time with PivotTables.

You're also going to learn how to create PivotTables from multiple data sets and other real world scenarios that you're likely going to come across at work. If you're interested, check it out. Link is in the description.

at work. If you're interested, check it out. Link is in the description.

Okay, so here's the really cool way of bringing attention to what matters most in your reports, and it's one of my favorite tricks, and it doesn't use Conditional Formatting. We're

going to visualize the Variance data so it's easier to spot how things are going.

I'm going to bring Variance a second time to the reports. I'm going to click and drag it all the way down to the end. Now, for this version, we're going to right click, go to Number Format, go to Custom.

Now, if you aren't familiar with Custom Number Formatting, the trick is that you can decide how positive numbers should be formatted, how negative numbers should be formatted, and then how zeros and text can be formatted, and you separate these with a semicolon.

Now, let's say for positive numbers, I want to get a green symbol. So,

I'm going to bring up the Windows emojis, but I'm actually going to go all the way here to symbols, and I want to go with this box here. So, I'm going to add that in.

Now, I want this to be green. So, in square brackets, I'll add green. Okay. So this is how positive numbers are going to be formatted.

Now we can decide how negative numbers can be formatted after that semicolon.

I'm just going to copy this and paste it in here, but instead of green, I want red. And

last is how zeros can be formatted. I'm just going to leave it blank. So zeros are hidden.

Now, by the way, if you're using Excel in another language, you want to replace green and red with whatever that is in your language.

So, I'm going to OK this. And instantly, we get really nice, neat looking icons added to our PivotTable report.

Now, I definitely don't want to show this header. So,

I'm going to remove it and add a space because we do need something over here.

And instantly we can see where we have overspent and where we have underspent.

One thing though I prefer to improve here is that I'm not so fond of this green color here. It's just far too bright for me. So let's right click, go to Number Format. You can go with a different color. And my favorite is Color 43. And for red,

I actually prefer another red, a darker one. And that's Color 53.

Now, let's just add this. And that's the result that we get.

Now, if you're wondering what color codes are available, I've also included that here in this Workbook.

Now, we're currently looking at February numbers.

Let's switch our view to Jan. And everything updates automatically.

But there are just a few final things that we want to do to make this report ready.

Let's right click, go to PivotTable Options, and uncheck AutoFit Column Widths on Update.

This makes sure that your column width stay the same every time you refresh the PivotTable. Also,

make sure you have a check to Preserve Cell Formatting on Update.

Now, if your report has empty cells and you don't want to show blanks, but instead you want to show zeros, you can adjust that over here. And if you end up with error values because, let's say you're calculating percentage change and sometimes you're dividing by zero, you can just show blank for error values.

Okay. So there are some settings here that you can update. So let's go with that and then adjust the column widths to make sure all the numbers fit properly.

Okay. So now let's check May. And this is beautiful.

So, we ended up going from this to this. Same data,

same PivotTable underneath, but completely different impression.

And let's actually test it. What happens if we add a department that didn't exist before? Let's go over to budget data. I'm just going to scroll down here. I have

before? Let's go over to budget data. I'm just going to scroll down here. I have

some extra values. I'm going to cut this, and let's go and add it below the Table.

Okay. So, we can see our Table expanded. Let's go over to our beautiful report here. I added a new category for Research and Development. I'm going to right click

here. I added a new category for Research and Development. I'm going to right click and Refresh. And it pops up here. And my calculation and icon are added as well.

and Refresh. And it pops up here. And my calculation and icon are added as well.

Now, one last thing. This specific custom Style that we just created here in Design lives inside your Workbook. So, if you want to use it in another file, here's the trick.

Copy the sheet to your other file. So, I'm going to right mouse click, Move or Copy, Create a Copy. Now, select your other file, or in this case, I'm just going to copy to a new book and OK. Now in this other file, because we've copied over the PivotTable that has that design,

and OK. Now in this other file, because we've copied over the PivotTable that has that design, you're going to find the custom Design available. Now you're going to go ahead and delete that PivotTable, right? You don't want to move that PivotTable or copy it to your other

sheet. You're going to delete it. When you delete it, the custom PivotTable Style is going to stay.

sheet. You're going to delete it. When you delete it, the custom PivotTable Style is going to stay.

So when you create a new PivotTable in this file, you're going to see that custom Style.

Okay? So remember, you can also just download my practice file from the description and then copy it from there.

Now, if you found this video helpful, you know what to do. I'm going to see you in the next video.