How to Pivot a Usage Report

Preparing data for pivoting

1. Request csv report for desired month—information on how to to obtain a csv report can be found here

2. Open attachment in Excel

3. Insert column between columns A and B

Screen_Shot_2017-11-30_at_11.28.09_AM.png

4. Select column A. Click Data>Text to Columns

  • Select Delimited, click Next >
  • Check Space, click Next >, and then Finish

5. Delete column B

Pivoting queries by user

6. Select all data in raw data tab, insert pivot table. Select Fields as shown:

Screen_Shot_2017-11-30_at_11.52.14_AM.png

7. To calculate % of queries run as Identity Check, add this formula to cell K5: =SUM(F5:G5)/J5

  • Fill down

8. Add conditional formatting as desired

  • In this case, Data Bars were added to column J and Color Scale was added to Column K

Pivoting queries by week

9. Select all data, insert pivot table. Select Fields as shown:

Screen_Shot_2017-11-30_at_11.54.42_AM.png

10. Right click the date heading in cell B4, select Group…

  • Confirm the range of dates desired with Starting at and Ending at dates
  • Under By… select Days
  • For Number of Days type 7, then click OK

11. Sort the report by most active users by clicking the filter arrow in cell A4

  • Select Descending and
  • Sort by Count of User
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request
Powered by Zendesk