Free Excel
|||

How to use Pivot Tables

Creating Data Models in PowerPivot

Pivot tables are a way to consolidate a lot of data quickly and easily. You don't have to be an expert in Excel to use them. This simple step by step method which you can use on a blank spreadsheet.

You can either create the data for the table yourself or you can download the Excel File should you want to skip the boring bit.

Pivot Table excel download file

Excel multiple worksheets

With Excel open enter the following headings:

Company Name First Name Last Name Product Sold Date Sold Qty Sold Unit Price Total 

Enter the following for details so that your table looks like the following:

Company Name First Name Last Name Product Sold Date Sold Qty Sold Unit Price Total 
Computer Tutoring John Smith Excel Training 01/02/2014 36  £   340.00  £  12,240.00
ACME Joshua Fields Rocket Boots 02/02/2014 51  £   500.00  £  25,500.00
Toothbrush R Us Daisy Dunk Electric Toothbrush 03/02/2014 32  £     80.00  £    2,560.00
Candle Sales Janet Johnson Vanilla Scented Candle 04/02/2014 1234  £        4.00  £    4,936.00
Amp Guitars Harold Horace Electro Accoustic 05/02/2014 69  £   230.00  £  15,870.00
Computer Tutoring John Smith InDesign Training 06/02/2014 79  £   231.00  £  18,249.00
ACME Joshua Fields Time Machine 07/02/2014 17  £   232.00  £    3,944.00
Toothbrush R Us Daisy Dunk Dental Floss 08/02/2014 34  £   233.00  £    7,922.00
Candle Sales Janet Johnson Keep awake Candle 09/02/2014 32  £   234.00  £    7,488.00
Amp Guitars Harold Horace Classic Spanish 10/02/2014 11  £   235.00  £    2,585.00

Once completed then auto fill the data down to row 2000.
(Highlight all of the data except the first row then autofill down to row 2000.)

Using the most click to select any cell within the data.

From the Ribbon select Insert - Pivot Table.

In the create Pivot Table dialog box you'll see the selected range of date. It is this data from which we shall make a pivot table.

Also note that the New Worksheet radio button is selected. This means that the pivot table will be created on a new sheet.

Click the OK button to create the pivot table.

You can now see a blank pivot table. On the right side of the screen is the area you'll use to add fields to your pivot table. You need to add fields to your pivot table to get any use out of it.

First thing we'll find out is how much money we made in total.

From the pivot table fields section on the right drag the total field down to values section.
(You can also check the checkbox to add the Total field and the field should be added to the Values section. However this is not a guarantee so try to get into the habit of dragging the fields down to the Values section).

The values section will run a function calculation on any field that's dragged into it. Now we dragged the Total field in the sum() function will run. However you dragged a field with text data or any data that's not a number Excel will use a count() function.

Hopefully the sum of total should now be 20256215. Don't worry if your numbers are a little out the idea here is that you understand the concept of Pivot Tables.

First thing you may notice is that the number isn't formatted as currency and you'd be right. Even though we formatted the previous sheet as currency the formatting hasn't been brought across. This is quite normal but let's rectify the problem.

In the values section click on the little drop down arrow to the right of Sum of Total then click Value Field settings.

This Value Field Settings box is very handy. With this box you can change the formatting among a number of other things.

Let's change the number format by clicking on the Number Format button.
(You'll find it on the bottom left of the value field settings box).

Click Accounting from the category list.
(Accounting formatting generally looks a lot more professional that currency).

Click on OK then OK again to see the change to the accounting format.

Great so now we want to see exactly how much money we made per product.

Drag the Product Sold field down to the ROWS section.
(Dragging the Product Sold field to ther rows section allows the products to appear on the left of the pivot table.)

Now you can see how much each product made but now let's have a look at just who bought them.

Drag Company Name down and place it above Product Sold.
(This may be a little fiddley however if you keep holding the mouse button until you see a black line appear above Product Sold. Be patient it will appear eventually).

There you go a basic pivot table. You can use these techniques with any data however please remember:

  1. There must be no rows missing in the data. You can, of course, have bits missing. For example should you not have the first or last name. But you must have at least one bit of information in the row for the Pivot Table to work.
  2. Ensure that the first row contains column headers. That is information that will name your data. Pivot Tables don't use row letters or number to classify each column it uses the names in row one so ensure that you have them.
  3. The data in pivot tables is linked however it doesn't automatically update. To do this you can use the mouse to right click on the pivot table and choose Refresh. You can also find the refresh button on the Ribbon.