What is a Pivot Table?

Spending too much time analyzing data?

PivotTables automatically sort raw data into a table or spreadsheet, displaying the results on a separate table showing the summarized data. PivotTables can save you a lot of time, work and give you insights into your data that you may have missed or is difficult to discover.

PivotTables are a powerful tool in Excel that delivers analytical power and allows the user to summarize raw data allowing for effortless data analysis and powerful project management.

How to Create a Pivot Table in Excel

1. Click on the “REPORTS” tab and go into your “REPORTS > Custom Reports Page”. Select the custom report which you want to analyze and select a date range you wish to view and click “View Report”.
   .
   
   .
2. Once the report generates in the top left-hand corner ensure that “More Details” is set to “Show Tasks & Time” Then in the top right-hand corner click “Export: XML” then “Export”.
   .
   
   .
3. Open up a blank excel document and drag the downloaded file into the document and a pop-up message should appear as default select “As an XML table” and click “OK”.
   .
   
   .
4. Copy all data on the Excel document and open the “notepad application” on your computer and paste it in. Then copy all the data from the notepad and paste this data into a blank excel document. Doing this step allows us to get all data without formatting.
   .
5. Click on the “Insert tab” on the top and then “Table” and “OK”.
   .
   
   .
6. Once the data is in table format ensure a table cell is selected and in the insert tab again, press PivotTable and OK. Note: If a Microsoft Excel pop up appears about creating a schema. Press OK again.
   .
   ..
   ..
   
   .
7. Use rows and values to generate what you want to do your analysis on.
   .
   Note: ensure you drag in your variables for Values. Here I have selected Title, Task Name, User and dragged in Time Spent to analyze how much time was spent on each project task.
   .
   
   .
8. To ensure you show total hours in the Pivot Table
   In the bottom right corner left click on the Count of Time Spent variable then select Value Field Settings.
   .
   Next, change the selection to Sum Next click on Number Format and select Custom and then in the type box type in or select the text to [h]: mm then press OK.
   .
   
   .
   With the help of Pivot Tables, you then are able to effortlessly summarize and analyze large amounts of data clearly.
   .
   The final table should look something like this.
   .
   .

Got a Question?

If you have any questions about project management reports get in touch with our ProWorkflow Support Team.