Advanced Pivot Table Techniques (to achieve more in Excel)

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.