KeyLogic Blog: BI/Information Visualization

Learn more about the advances and expertise our KeyLogic team brings to their respective fields.

Converting Data to Useful Information – A Basic Pivot Table “How To”

Sep 11, 2013

Has anyone ever handed you a spreadsheet of data and asked you to make sense of all the numbers?  One of the more beneficial features within Excel is the pivot table.  This function makes it possible to rearrange and summarize data into informative tables with a few clicks of your mouse. 

This function is especially helpful when it comes to financial data.  The example below is a fictional depiction of subcontractor payments made to various companies across multiple work breakdown structure (WBS) codes. 

By looking at this presentation, it is difficult to determine the total amount paid to each company, the specific amounts paid to Company E for each WBS, the total amounts paid to Company E for each active purchase order, or the voucher amounts for Company E.  Converting the data to a pivot table will aid in your analysis of the information. 

Creating Your Pivot Table

In order to create a pivot table, arrange the data in a table format with column and row headings.  (Sample data has been provided to assist you.  Click on the icon to the left and a spreadsheet will open. ) Highlight the Excelrows and columns of your data table, including the header row but excluding the total row.  With the cells highlighted, click on the Insert Ribbon; then, click on the Insert Pivot Table Icon.  The Create Pivot Table dialogue box will appear.  Choose the data that you want to analyze will give you the option to Select a table or range or Use an external data source.  Since you preselected the data to be included, the Select a table or range radio button should be highlighted; verify that the data range you selected is consistent with the data range identified in the Table/Range box.

Choose where you want the Pivot Table report to be placed – New Worksheet or Existing Location.  The default location is a new worksheet.   If you choose to place your report on the existing sheet, identify the location for the report.  Click OK and a new sheet will appear.   

When the sheet appears, you will see two boxes, the actual Pivot Table (entitled PivotTable1 as a default) and the Pivot Table Field List as illustrated to the left.  To begin building your report, Choose fields from the Pivot Table Field List box.  Select items to be included in your pivot table and the placement of each.  As you begin to add items to the Pivot Table Field List, the Pivot Table Report will begin to form.  (In the event the Pivot Table Field List “disappears” from your screen, simply click in the Pivot Table box and the Field List will “re-appear”.)

 Report Filter gives you the opportunity to add additional filters to the information in your pivot table.  This option is beneficial for a more in-depth pivot table than the information presented in this example. 

The selection of a Column or Row Label will determine the layout of your pivot table. 

The value of items to be determined should be placed in the Σ Values box.  Placement of an item in this box will determine the mathematical functions performed on the data, i.e. sum, average, count, etc.  If the function you are trying to achieve is not the default function, click on the drop down arrow and select the Value Field Settings.  Select the summarize value field by option you are trying to achieve and click OK.     

Once you select all of the desired information, your pivot table is ready for formatting.  The color of a pivot table is basic blue; however, you can personalize the font and background color in the same manner, as you would normally modify a spreadsheet.     

The option to filter your data is included in each row or column by selecting the drop down arrow and choosing the data you wish to see.  This function is the same as the normal filter function for any Excel spreadsheet. 

Modifying your data

It is possible to revise your data after the creation of your pivot table.  Once you have the changes incorporated into your data sheet, return to your pivot table.  Right Click on the pivot table and select the Refresh option.  The data in your pivot table should update with the new information.

Examples

The following pivot tables are examples of the scenarios presented above. 

          

All of these examples are possible from the same set of data.  In most cases, the best way to learn pivot tables is to start playing with a sample data set.  Experiment with different scenarios until you achieve the desired result.  

Questions about Pivot Tables? Ask below.



Written by: Jenny Friend

You are not allowed to post comments.