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”.
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.
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.
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.