Categories

User's Tags

What are Pivot Tables and how to make the most of them?

  • Pivot tables are a type of data visualisation tool found in many common spreadsheet software packages such as Microsoft Excel.They are a great way to summarise a large amount of data, but they can also help you analyse and explore the data further.  

    In this blog we will look at how charities can use pivot tables to make the most of their Localgiving data. However, pivot tables can be used to look at any spreadsheet data you have.

    Localgiving members can use pivot tables to:

    • Find out the total given by each donor

    • Look at the total raised each month

    • Compare the total raised from monthly donations and one-time donations

    • Find out how much is outstanding to you

    Sound useful? Read on!

    I will show you some of the basic functions of the PivotTable tool available in MS Excel.

    I have used Excel 2010, so if you are running a different version some of the options might be in a different place.

    The first step is to download a report of the information you’d like to look at from your interface.

     

    Go to the “My Donations” tab of your interface.  Click “Reports” from the left hand green menu.

    Then you can select the data you would like to analyse using either one of the predefined periods or using a custom date range. I decided to look at data from the 1st quarter of this year (01/01/2016 – 31/03/2016).

    Once the report has downloaded, open it in Excel and select the data to be included in the PivotTable.

    Then click on the “Insert” tab on the ribbon and click on “PivotTable” on the far left.

    A pop up should appear showing the data you have selected and asking whether you want to put the PivotTable in a new or existing worksheet – I recommend choosing a new worksheet so that your PivotTable doesn’t get mixed up with other information.

    Click “OK” and you should be taken to a new worksheet that looks something like this:

     

    On the right hand side of the screen you can see the area where your PivotTable will appear, and the left hand side of the screen has the PivotTable field list where you can choose which data will appear in your PivotTable.

    You can start to create a PivotTable by dragging the fields listed into one of the 4 quadrants below. Start by dragging “Donation ID” into the “Rows” quadrant and “Amount” into the values quadrant. This will give you a simple PivotTable listing all the donations you have received and the total amount raised from each one (i.e. including the donation, Gift Aid and match funding).

     

     

    You could alternatively drag “Supporter name” into the “Row Labels” quadrant, rather than “Donation ID” to produce a list of how much each donor has donated (including Gift Aid and match funding).

     

     

    Analyse your data further using calculated fields

    So far, this is interesting, but not that much more useful than the original report you downloaded; using calculated fields is one way to analyse your data in more detail.

    Calculated fields allow you to use a formula incorporating other fields in your data set.

    For example you might want to find out the net amount received from each donation made to your group (donation + Gift Aid + match funding minus any fees).

    To start adding a calculated field to your PivotTable; make sure you have selected one of the cells in your existing PivotTable, then click on “Options” in the PivotTable Tools section of the ribbon (far right). Then click on “Fields, Items & Sets”. A drop down menu should appear, select the first option – “Calculated Field”. 

     

     The “Insert Calculated Field” pop up should then appear. Here you can name your new field (I’ve called ours “Net amount”) and then create the calculation for your field.

    To calculate the net amount from each donation put your cursor in the formula box and type "=". Then double click on each of the field you want to include in the calculation – i.e. Amount, Payment Provider Fee, Donation Commission & Gift Aid Commission, adding a "+" sign between each one. Finally, click OK to add your new calculated field.

    Your new field should now be included in your PivotTable as an extra column. If it doesn’t appear automatically, you can easily add it by dragging and dropping the new field into the Values quadrant.

    Using Filters to summarise your data

    You can also use PivotTables to filter your data. For example to see the amounts that are still due to be paid, drag the “Payment Status” field into the “Report Filter” quadrant of the PivotTable Field List.

    Select the drop down arrow next to the filter and select “Processing” to see just amounts that are still outstanding.

     

    You can also use more than one filter at a time. To see just the donation payments that are outstanding you can also drag the “Type” field to the “Report Filter” quadrant and use the drop down arrow to select donation.

    You should now be able to perform some basic tasks when it comes to PivotTables:

    • Create a PivotTable in MS Excel

    • Use calculated fields to analyse your data

    • Use filters to summarise your data

    There are so many more ways to utilise PivotTables, far more than I can fit into one blog post, but hopefully today’s introduction will get you started and from here you can explore more functions, and be a data analysing wizard in no time at all.