Excel Pivot Table Tutorial

Excel Pivot Table Tutorial

Slice and Dice your Excel Data like a Magician

Excel Pivot Table Tutorial
Excel Pivot Table Tutorial

Note: There is a new Advanced Excel Pivot Table Tutorial here.

Excel spreadsheets are handy tools for tracking and analyzing data. However, they do have some limitations. If you build a spreadsheet in a certain layout, it will often take a fair amount of time and work to provide alternate views of the data.

That’s where Pivot Tables come to the rescue. Pivot Tables allow you to take a large block of Excel data and slice & dice it in numerous useful ways. The best thing is that you don’t ever have to risk damaging your source data to produce these alternate views.

The spreadsheet below tracks product sales by day for a Fruit stand. We’ll see how a Pivot table can give us lots of different insights into this data.

Excel pivot table tutorial 01

Creating the Pivot Table from Existing Data

Go to the Insert tab and click the Pivot Table button.

Excel pivot table tutorial 02

The Create Pivot Table window will open.

Excel pivot table tutorial 03

You can pull  in data from an external source if you want, but typically you will just use the data in the sheet you are on.

You can also put the pivot table in the existing sheet or in a new one. It’s a little bit cleaner to put the pivot table in its own tab.

Click the OK button.

Your Pivot Table will open in a new tab. There will be no data displayed, but all of the fields in your original tab will be displayed in the Pivot Table Field List on the right side of the screen. You can arrange these fields in numerous useful combinations.

Excel pivot table tutorial 04

Drag the fields into their buckets at the bottom of the Pivot Table Field List.

Your Pivot Table’s data will be displayed based on your settings. The image below shows where the fields appear on the actual table.

Excel pivot table tutorial 05

The values in your table may need to have a particular number formatting. This data is money, so I’m going to set the format to Currency.

Click the Sum of Sales field in the Values box.

A menu will appear.

Excel pivot table tutorial 06

Select Value Field Settings from the menu.

The Value Field Settings menu will open.

Excel pivot table tutorial 07

You can change the Values to other formulas (count, average, etc.) if you need to.

Click the Number Format button.

The Format Cells menu will open.

Excel pivot table tutorial 08

Select Currency from the Category  list and click the OK button.

You will see the pivot table

Excel pivot table tutorial 09

Filtering and Sorting Your Data

The data in all of the fields in your new Pivot Table can be filtered and sorted to display or hide any data. Use the dropdown buttons on any field to pull up the Filter options.

Excel pivot table tutorial 10

You can sort the values of a field A to Z or Z to A or use more advanced options. Let’s see the Filtering options.

You will see all of the values for the selected field. You can check and uncheck any combination of values to show or hide those values. The field below contains all of the products in listed. I only want to see results for Apples and Oranges.

Excel pivot table tutorial 11

Check the values that you want to see and click the OK button.

Now you will only see those values (Apples and Oranges) displayed. You will also see the Filter icon displayed on the dropdown button.

Excel pivot table tutorial 12

Rearranging (Pivoting) Your data

You can completely rearrange the layout of the data on report by dragging the fields around the areas of the Pivot Table Fields List in the lower right corner of the screen. I currently have the report displaying Products down the rows and Days across the columns. I want the switch it so that the Days go down the rows and the Products go across the columns.

Excel pivot table tutorial 13

Drag the Day field into the Row Labels box and drag the Products field into the Column Labels field and click the Updated button.

Now you can see the report updated to the new layout.

Excel pivot table tutorial 14

Nesting Fields of Data

You can “nest” one field inside of another field in a pivot table. If you drag more than one field into one of the report areas the lower field will be grouped inside of the higher one.

Let’s see what it looks like if we move the Origin field above the Products field in the Column Labels area.

Excel pivot table tutorial 15

Drag Origin over and above Products and click the OK button.

You will see the values in the Origin field (Imported and Local) with the Imported or Local products grouped below.

Excel pivot table tutorial 16

You can drag the Products field ABOVE the Origin field to reverse the grouping.

Now you will see any Imported and Local data grouped inside of each Product.

Excel pivot table tutorial 17

This technique works the same way on the Row Labels.

Pulling in New Data

If you go back to the original worksheet where your source data is contained, you can make changes to the underlying data. You can have these changes reflected on your Pivot Table very easily.

Go back to the Pivot Table and right click in one of the cells.

An option menu will open.

Excel pivot table tutorial 18

Select Refresh.

The data in your pivot table will update to reflect any changes made in the underlying source data.

See the Video Tutorial for Using Excel Pivot Tables

The following video shows how to create and manipulate a Pivot Table in Microsoft Excel.

Did you Like this Article?

5 thoughts on “Excel Pivot Table Tutorial

  1. Great tutorial — the only suggestion I have is that you should provide a file with the data readily available so users can follow the tutorial step-by-step. Other than that, great work! 🙂

  2. you guys wont believe what happen to me today because of this Excel function…. I was just unsuccessful for a job because of this S**T. i can believe that is so easy and I had forgot that this was called PIVOT TABLE. on the moment of the interview I just completely forgot.?

  3. Sir, this tutorial was very helpful. Is there any function in excel where i can combine the duplicate data. I tried consolidate function and its almost what i wanted but instead of sum/average, is there any function that can just combine/merge the data… something like below.
    This is my data..
    Model Location
    12050 A101
    12051 A102
    12053 A103
    12050 A103
    12051 A201

    And i want it to be like this.
    Model Location
    12050 A101, A103
    12051 A102, A201
    12053 A103

    Thanks.

Leave a Reply

Back To Top