Pivot tables

Delegates on our financial modelling courses like Excel pivot tables because the tables can help sort and categorise data.

Getting started with Excel pivot tables

Here is a worked example for download that will help get you started with Excel pivot tables.

First you need to create a table of data for analysis. Let’s imagine we want to analyse the expense claims for three employees (John, Paul, Ringo). Enter the three names into column A and copy down 10x i.e. to row 31. Let’s imagine the employees incur expenses in five categories: travel, petrol, food, materials and stationery. In column B enter the five expense categories and copy them down six times i.e. to row 31. Next, in column C we need some dummy data to analyse. In column C enter the values 1-7 and then copy this sequence down.

Starting a pivot table in Excel

Select the data you want to analyse (cells A1:C:31 in the Excel spreadsheet). On the “Insert” tab, under “Tables” select “Pivot table”. Select a location for the new pivot table e.g. cell I2. Click on “OK”.

Image showing how to construct a pivot table in Excel

Setting the pivot table up

In the “Pivot table field list” dialog box, select all the variables you are interested in (name, activity, amount). You will notice your pivot table appears. Next drag “activity” so that it appears as the column label. The row label should be “name”. “Amount” should appear in-between in the “Σ values” box. Note, if you click on the down arrow in the Σ values box and select “Value field settings” you will find you have access to other operators including for example “count”. click on “OK” and close the “Pivot table field list” and you will have a complete pivot table.

Image showing how to construct a pivot table in Excel financial modelling

Using an Excel pivot table in financial modelling

Now your pivot table is set up you can create simple links to it from other parts of your financial model. If your source data is updated, you can click into the pivot table, right mouse click and “refresh” to update the table for the new data.

Pivot table slicer

If you have one of the later versions of Excel e.g. 2010 you can add a “Slicer” to your pivot table. Click in your pivot table. On the “Pivot table tools” tab that appears, select “Options”, “Sort & filter” and select “Insert slicer”. Tick “name”. A button will appear containing the employee names, allowing you to select just the data that relates to individual employees.

Image demonstrating Excel's pivot table slicer

Pivot tables vs. Excel standard functions

Of course there is always more than one of solving a financial modelling problem in Excel! If you download the pivot tables example starting at cell F14 you can see how the same kind of problem has been solved using Excel’s Sumif function. There are a lot more ways of sorting data though, starting with Sumif’s bigger tougher older brother Sumifs and moving on to the super-hero of data picking and amalgamation Sumproduct. Some of our training course delegates like pivot tables though, because of the way they display and format Excel data.

About this free online training course material

You’re looking at free online course material on Excel functions extracted from our financial modelling course training.

 

 

...join the conversation...

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