Today you learn some advanced
pivot table tricks in Excel. In case you're brand new to pivot tables, make sure you watch this video first. It also explains with an example why pivot tables are so great. Today we're going to
take it a step further and cover some advanced tricks that some of which I
came across a lot later. I'm curious what you think. And if you already knew all of these, let me know in the comments. Let's jump in. (upbeat music) Tip number one, adjust
field list settings. To show you what that is, let's quickly insert a pivot table. So I have here some sales data for different products,
customers and companies. I'm going to go to insert,
tables, pivot table. This data here is already formatted as an Excel table called table sales. I'm going to insert a pivot
table in a new worksheet and click on okay. This is the default view you have when you create pivot tables,
but you can change that. If you go to tools here, you can select field section
and area section side by side. This is the view I prefer to have because it's easier to organize things. You can also adjust
how you see the fields. You can sort them A to Z or sort them in the data source order. This is really helpful, especially if you have a lot of fields. Tip number two, create multiple
reports with one click. So here I want to create
sales product reports for each single customer. I can do that with one click. Let's set up the first report. I want product in the rows and
sales in the values column. I'm going to add the
customer to my filter. Before I do that one click, let's update the formatting of this. I want to add 1000 separator to this and I also want to sort
this in descending order. So right mouse click,
sort, largest to smallest. Now comes the part with one click because I want to create different reports for each single customer. Currently, I have four of these. Instead of doing this manually, I'm going to go to pivot
table settings here, go to options and show
report, filter pages. This brings up anything
that's in the filter, it's customer name, click on okay and I get multiple
reports for each customer. One is for Aida, then
Delicia, Erma, and Leibher. Each of these are to different
customers in this dropdown. Double-click to view details. What I forgot to show in
my first pivot table video, was the ability to
double-click on a number. So in case you're wondering what makes up a specific
number in the pivot table, all you have to do is double-click on it and you're going to
get to all the details. The sum of sales USD here is 84,580. If I switch back to the pivot
table, that's our number. You get the details with a double-click. Add data bars to your pivot tables. Here I have the different
products and the sum of sales. Let's say I want to
show these as data bars. Just go to home, conditional formatting,
data bars, more rules. Here's the important selection. Instead of selected cells, you want to go with all cells showing in the header of your pivot table column. This is what's going to make you dynamic. All based on the size of your pivot table, format style is data bar. If you only want to show the bar and you don't want to show the numbers, place a check mark here adjust the color, and click on okay. Now we have the data bars sear. We can replace this. I'm just going to add a space. Now here's something
you have to be careful. When I right mouse click and refresh this, everything is going to collapse because the default
setting of pivot tables is to make the column with dynamic. You'd want to change that in this case. So I'm going to go to pivot table analyze, go-to pivot table options and uncheck auto-fill
column with an update. Now I'm going to expand
this to the size I want. When I right mouse click and refresh, it's not going to collapse. In case you also want
to see the sales values in a separate column, you can just add it again
to your pivot table. Adjust the title, update the
formatting, and you're done. Number five, create calculated fields. To add a calculated field
to your pivot table, go to pivot table analyze,
under calculations, you're going to see calculated field. Here you can give your fields a name. I'm going to call mine adjusted sales. Now that formula can involve
any of your field names. In this case, I'm going to
create a simple calculation that's going to take my sales USD, so all I have to do is double-click it to see it in the formula bar. And I'm going to multiply
this with one plus 10%. So I'm going to increase it by 10%. Once you're done, click on add and you're going to see the new field right here, click on okay. That's my adjusted sales field. Now that I have a new
calculated fields in here, I'm able to use it in my other reports. To remove a calculated fields, go back to calculated fields options, go to the drop down here. You're going to see your
calculated field selected and then click on delete and okay. Calculate the difference between columns. In this case my source data has information for June and July only. And I want to calculate
the sales difference between these two months. First of all, I'm going to add
document date to my columns. This is automatically going to
give me a grouping for months, and I'm going to be able to
drill down into the exact dates. Well, I don't want that drill down. So I'm just going to kick
out the document date and leave it with months. Now we're going to you
calculate the difference between the two. Well, I can add the
sales USD a second time to the values field. And then I'm going to right mouse
click on the second instance, show values as difference from. So notice we also have
percentage difference from, I'm going to go with difference from. Here's important to pick
the right base field. It says product description,
which is right here. That's not what I want. I want to calculate the difference from my July and June columns. I'm going to change that
selection to months, base item is previous. Now this doesn't mean the previous month. It means the previous column. In this example it happens to
be the previous month as well. So that's fine click on okay. I get my difference. All I need to do, is
update my titles here. Call this difference. Tip number seven, smart
custom number formatting. An alternative to conditional
formatting in pivot tables, is to use custom formatting. So right mouse click, go to number format. This ensures that your
formatting is dynamic based on the pivot table size, go to custom and add
your smart formatting. So in this case I'm going to use symbols for positive and negative numbers. Use the shortcut key Windows
dot to bring up the symbols. You can also copy and paste symbols from the clipboard into this box. I'll just search for the up arrow. That's what I want to show
if the number is positive, then add a semi-colon. Now, if the number is negative,
I want to show a down arrow. I'm also going to add color
coding to the down arrow, go with red in square brackets. For the up arrow, I'm going to add the code color 10 and put this in square brackets. And okay, now I've disguised
my numbers as symbols. This is dynamic. If I add customer name to this, everything updates accordingly. Number eight, create your
own custom groupings. Let's say I want to split
these product description into three separate groups. I want to have a women,
men and other group. To create that, just select
your different products, right mouse click and group. You get a second field that also shows up on your pivot table that you can rename this as you like. I'm going to call mine main category and call this group one that
was automatically created the men group. Now I want to add a women group as well. So I'm just going to select
the different groups here, right mouse click and group, rename this to women. The rest is something that
I want to group in other. I'm just going to select
these, hold them control select the rest, right
mouse click and group and call this other. Now you can also arrange
these the way you want. I'm going to grab other and bring
it and drop it to the bottom. Now you have this new category
available as a new field that you can use in your reports. Number nine, group dates as you need. In this case, I want to take
a look at the sales by date. So I'm going to bring
document date to the rows. Now it automatically brings
in the months as well. And it would have brought in the years if I had data for different years. In this case I just have data
for these two separate months. In case I just want to
see the document date I can get rid of the months. But what if I want to have
my own custom grouping? Just right mouse click, go to group, and you can decide if you want to have your groups based on
quarters instead of months or based on days. So let's say I want to take
a look at my sales data every five days. I'm going to add a five day
interval to these, click on okay. And I've created my own
custom date grouping. Tip number 10, add a
timeline to your pivot table. To add a timeline, go
to pivot table analyze and insert timeline. So in the previous video that I did, we took a look at insert slicer. If you're curious how this works make sure you check out that video. Now we're going to go ahead
and insert this timeline. This is going to give you a list of fields that are date fields. I just have one, it's the document date, click on okay. Now I immediately get a
timeline that I can use when you select a month, notice the data changes and
is restricted for that month. You also have the ability
to select multiple months. You can switch your view
from months to quarters, years or even just days. This way you can take a
look at a specific day or select multiple days. Whenever you want to remove your timeline, you just have to delete it. So this wraps up my favorite
pivot table tips and tricks that are more on the advanced side. If you have tips of your own, please share these in the comments. Many thanks for watching. I hope you found this useful. Don't forget to hit that
thumbs up if you did. And also if you're new to this channel welcome and subscribe before you leave. And I'm going to see
you in the next video. (upbeat music)
