Excel Pivot Table Tutorial

Slice and Dice your Excel Data like a Magician

excel pivot table tutorial Excel Pivot Table Tutorial

Excel Pivot Table Tutorial


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 011 Excel Pivot Table Tutorial

Creating the Pivot Table from Existing Data

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

excel pivot table tutorial 02 Excel Pivot Table Tutorial

The Create Pivot Table window will open.

excel pivot table tutorial 03 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

Select Value Field Settings from the menu.

The Value Field Settings menu will open.

excel pivot table tutorial 07 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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

You will see the pivot table

excel pivot table tutorial 09 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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 Excel Pivot Table Tutorial

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.

 

[tubepress]

Did you Like this Article?

Sign up for free updates from VitaminCM.com?

mail subscribe1 Excel Pivot Table Tutorial

youtube 1281 Excel Pivot Table Tutorial

twitter follow1 Excel Pivot Table Tutorial

See some of the other places where you can connect with me out there in the wilds of the internets.

Comments

  1. By Sobe

    Reply

  2. By Naeem

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Fatal error: Exception thrown without a stack frame in Unknown on line 0