One of the most used features in Excel are
pivot tables. They save us time and effort because they do the calculations for us. And
I have many videos on the channel about them. But if you want to save even more time,
make sure you're going to check out these 10 Pivot Table Pro Tips that I'm going to
share with you in a second. What I'd like you to do is tell me which tip is your
favorite. Mine is number four. Let's go! Tip #1: The Fast Pivot. So, I have here
a table with salespeople, the order date, and the sales. And I'm going to create a final pivot table
that looks like this with formatted numbers. Can you believe that I can create that from here to
here with just two clicks? Yeah, two clicks. So, check this out. I'm going to click on Recommended
Tables, which is in the Insert tab, and scroll down to see what we want: Salesperson by Sum
of Sales. That's the second click. Two clicks to find a result. Yeah, I did kind of lie because
I was already on the Insert tab. You're probably going to be in the Home tab. So, in theory, it's
going to need three clicks. Just sounded cooler. Next tip is to adjust your pivot
table fields, tools, settings. So, there's a gear icon here that not too many people
explore. But there are two useful features hidden in there. Number one is this one right here.
If you have a lot of columns in your data set, it's much easier to locate the column if you
sort these A to Z. If, at any point in time, you want to go back to the data source order,
you can do that by adjusting your selection. The second useful feature is the way you define
the layout of these boxes. This is the layout I prefer, but it's not the default layout.
The default layout is this one right here, and I find these two squashed. I prefer to
change my view and work with this layout. So, be aware that there are different layouts
of this. Pick the one that you like best. Next tip is to use the timeline. So, the timeline is really helpful whenever you work
with dates in your data. So here we have order date. We could add a timeline to this by going
to PivotTable Analyze and inserting a timeline. You're going to see any date fields in here. I
just have one called order date. So, I'm going to place a checkmark and click on OK. And that's
it. I have a timeline added to my pivot table, so I can adjust my selections here and take a look
at my data changing. You also have more options. You could change your selection to view quarters
and adjust some settings from the timeline menu. You can, for example, change the style. And if you
want to remove that border around the timeline, which is something I always do, you can do that.
Just right-mouse click on one of these designs, select Duplicate, go to Format, Border,
and remove that border. And then you can give this a name. Click on OK. Let's go and
select it, and that border is gone. You can then make adjustments to your pivot table and
arrange these so it looks more professional. Next tip, and this was really cool, is to use
a pivot table without showing the pivot table. Here's what I mean. I've created a sample
report here with formulas. Some are coming from different sheets, and what I want to achieve
is to use a pivot table slicer to control this, so that for each sales manager, I can click on
this button and my data updates automatically. How am I using a pivot table slicer on something
that's not a pivot table? Here's a secret. I'm using a hidden pivot table. Here's the big reveal.
I'm going to unhide these cells. That's my hidden pivot table. All it has is the salespersons in
the row. I then went ahead and added a pivot table slicer by going to PivotTable Analyze and
inserting a slicer, and I selected Salesperson and clicked on OK. And I got my slicer. Just adjusted
the settings to make it look like this. Now, how are these referencing this? Well, they're
not referencing the buttons directly, but they're referencing this one. So, notice here the formula
is just referencing this, and these ones here are referencing this as well. Now, what happens if
you have multi-selected more people? Well, this formula currently is just picking up the first
person, but you could put safeguards in place to count the number of items here. And if it's more
than one, you put a specific message here or you use other formulas to get the result of the entire
selection. Up to you how you want to handle this. Tip #5: Only show the top or bottom
values. So, let's say I want to create a quarterly report for the salespeople, but I
don't want to show all the salespersons by quarter. I want to show the top three. I can
easily do that using Top N. First of all, we need to add the quarters in there. We don't
have quarters in our original source data, but it doesn't matter because we have something
better. We have the date. At the moment, I grab the date and drop it here in rows. Notice what
happens. I get categories for years, quarters, and months. Now, I'm going to remove what I don't
need. I don't need the order date. I don't need the months. I just want to keep the years and
quarters. Notice this report gets far too long. One thing I want to do is to get rid of these
subtotals. I'm just going to right-mouse click on this and remove the checkmark for the subtotal.
Now, we also want to remove these collapse and expansion buttons. You can easily do that by
going to PivotTable Analyze and just toggling this plus-minus buttons off. Now, let's restrict this
to the top three. We're going to click on this dropdown, go to Value Filters, down here you're
going to see Top N. Now, if you wanted it the other way around, you wanted to show the bottom,
the worst-performing people, select Bottom. In this case, we're going to go with Top. Let's
change this to three. We want this by sale. So, that's my sales column. If you have more value
fields, you'll be able to make your selection from here. We're going to click on OK. And now
we have the top three sales managers per quarter. Next tip, type over fields to correct them. So,
for example, let's say my manager doesn't want to see Qtr1, they want to see Quarter 1. You can
make corrections directly in your pivot table, and notice it gets replicated to the other fields
as well. Now, of course, it's best to make your corrections in your source data, but sometimes
you can't do that. You receive it from a system, but you want it corrected in your pivot table.
You can do that. You might be thinking, "Well, can I just correct the numbers?" No, you
can't. If you try to, you get this pop-up. Tip #7: Repeating labels versus
centering them. So, depending on your needs, let's say you need to send this off to someone
who wants to create a data table and upload it to assist them. You will want to have
these quarters repeated and the years repeated. There is a setting for that. If you
go to PivotTable Design under Report Layout, you can select Repeat All Item Labels, and that
does the job. If you only want it for a specific column, you can right-mouse click, go to Field
Settings, and only for the settings of this field, you can repeat the item labels or choose not to
repeat them in this case. Now, on the other hand, if you want to design a nice layout for reporting,
you might want to center these labels instead. Right-mouse click somewhere on your pivot table,
go to PivotTable Options, and place a checkmark for Merge and Center Cells with Labels,
and you get this nice layout for reporting. Next tip is custom sort by dragging or typing.
So, let's say here for the salespersons, I want to have a specific sort. I want to have
Justin and Jack on top because I want to keep my eye on them. One way I can do this is just to
grab this field and drag and drop it here. So, I'm just using my mouse here. There's no keyboard.
Another way you can do this is just by typing. So, notice this says Bob. I can just type
"Jack" Potter. When I press Enter, Bob shifts one cell down. And that doesn't just
happen in this instance but in all instances. Next tip, calculate the difference from the
previous period. So, let's say I want to get the change to the previous month here, or maybe I'm
interested in getting the change to the previous year. How can I do that? All we need to do is grab
our base value field, which is sales in this case, drag and drop it a second time to the pivot table.
Then right-mouse click, go to Show Values As, and select Percentage Difference From.
Now, we get to pick the base field. So, let's say in this case, I want to get the
difference from the previous month. So, my base field is months. My base item is not February,
but instead, I'm going to select Previous. So, this means the previous period that is visible
here. Now, one thing to keep in mind is that this doesn't have any inbuilt time intelligence.
By "previous," it doesn't mean the previous month. It means the previous cell. In this case,
it happens to be the previous month. But if you're sorting everything the other way around, you
have to be mindful of that. When I click on OK, I get the change to the previous month. What if I
wanted it for the previous year? I'm going to go drag sales a second time in here, right-mouse
click, Show Values As, Percentage Difference From. This time, my base field is for years, and my
base item is Previous. I'm going to click on OK, and I get the change to the previous year.
I can, of course, rename these as needed. Tip #10 is to disable double-click. So,
if you're in a pivot table and you want to quickly view the details of any fields, you can
double-click on a value field, and it's going to create a separate page with all the details. But
you might not want this behavior to happen. There is a way you can turn this off. Right-mouse
click, go to PivotTable Options, under Data, remove the checkmark for Enable Show Details,
and click on OK. Now, when you double-click, nothing happens while this happens. Okay, so I hope you enjoyed these tips. Before you leave, don't forget to tell me which one is your favorite. And don't forget to check if you're also subscribed to the channel because if not, do consider subscribing. Thank you for being here, thank you for watching and I'm going to catch you in the next video.
