Analyzing Grad Commons Research Rooms audits (PIVOT edition)

As part of space management and policy enforcement, usage data from the electronic locks is gathered and analyzed. If you haven't already done so, begin by Gathering Lock Audit Data. You will take the data and extract it to create a Pivot Table using Microsoft Excel. How to do this is outlined below.

Helpful Tip: Save your work regularly. Occasionally (often) an error will occur that will not allow you to save your work. If you have been working on this for awhile, you will lose all of your work. Don't do that. The first few times you do this, it will take time so save often. (Save yourself the tears.)

Load data into Microsoft Excel

Open a new Microsoft Excel sheet
Click on the “Data” tab
Select “New Query” then “From File” and “From Folder”. (Note: This image does not reflect the current file path.)
Click Browse.
Select This PC < Shared Network Space LEAD < Shared <Library Spaces Planning & Mgmt< Rooms < Research Room Audits < XXXX Audit Data< Most Recent file
Click OK, then OK again.
Another box will appear – just click Load at the bottom.

Allow the rows to load. Depending on how big the file is, it may take a few seconds. You can watch the progress on the right side of the screen.

Format Data

On the tool bar at the top of the screen under “Query Tools”, click “Query”
Select Edit on the far left
The far left column is labeled "Content". Click on the box with the downward arrows.

In the pop-up window, select "Table 0" for the door numbers and date/times accessed to appear. Click OK.
Right click on the Date column. In the drop down menu, select Transform and click “Date Only”. This will remove the time stamps, which will potentially skew the data counts.
In the Event/Name column, click on the down arrow for the menu.
In the pop-up window, select “Load More” at the bottom of the window to see all the event name options.
You will now have a much longer list. Individually select only the research rooms. We do not analyze data from any other entry. Then click "OK".
Delete the Function and Source Name columns by right clicking and selecting “Remove”. The only columns you need to keep are Door, Date, and Event/Name.
Once you are finished making your edits, click on Close and Load in the upper left hand corner. This will take some time to load so be patient.
In the upper tool bar, under “Table Tools” click Design. Highlight the three remaining columns and click "Remove Duplicates" under "Table Tools".
There will be a pop-up window. Make sure all three columns are selected (Door, Date, Event/Name). Also leave "My data has headers" selected. Click OK. Excel will tell you how many duplicates were removed. Click OK again.
Click the downward arrow in the Event/Name column and one last time check to make sure only Grad Commons research rooms are selected. Unselect anything that is not a Grad Commons research room. Click OK.

Create a Pivot Table

In the tool bar at the top, click Insert.
Make sure all three columns are highlighted and select "Pivot Table".
A pop up will appear to select the table or range, which you should’ve already highlighted. Be sure to chedk the box to "Add this data to the Data Model" and click OK.
On the right will be a box labeled "PivotTable Fields". It will have one large box (Range) and 4 smaller boxes (Filters, Columns, Rows, and Values).
In "Range", click and drag Event/Name to the "Filters" box.
This will appear in the spreadsheet. Click on the downward arrow to open the drop down menu.
Check the box at the bottom labeled "Select Multiple Items". A checked box will appear next to the word "All". Click the + next to "All".
All of the rooms should have a check mark next to them. Uncheck any you do not want to include. Click OK.
Working with "Range" again, click and drag "Door" to the "Rows" box. This will now appear on the spreadsheet:
Click on the downward arrow next to "Row Labels" to open the drop down menu
Uncheck "Blank" then click OK.
Again, working with "Range" check the box next to "Date". When you do this, several more items will appear. See the image below. They will also appear in the "Columns" box, but you aren't going to do anything with the Columns box so just ignore it.
In the Range box, hover the cursor over "Date (Year)". A downward arrow will appear to the right. Click on this arrow.
A pop up window will appear similar to what you worked with in previous steps. Select the appropriate year and make sure all other years are unchecked. Then hit "OK".
Click and drag "Date (Year)" from "Range" to "Rows" and put it below "Door" in that box. When you do this, it will disappear from the "Columns" box.
Repeat this process with "Date (Month)", selecting only the relevant months.
Uncheck "Date (Quarter)" because you don't need it.
Hover the cursor over "Date" in the "Range" box and again, click the downward arrow.
This time, select "Date Filters". Choose "After" and there will be a pop-up. Enter the day before the first day of data you want. So if Jan. 2 is the first day of data you want, input Jan. 1.
Click and drag "Date" to the "Rows" box.
**Important Note** You don't have to use Date but it can help you see patterns such as a room's usage being close to the minimum but not quite where it needs to be.
Finally, click and drag Event/Name to the Values box. This means Event/Name will be in 2 boxes. However, in the Values box, it will change to "Count of Event/Name."

Final Results

When you are finished, your Pivot Table Fields on the left side of the screen should look like this:

Your Pivot table should look roughly like this:

You can collapse or expand rows by clicking on the + and - boxes next to the labels. If you collapse a field (say January for example), it will collapse January for ALL the rows (but not February). This can be helpful when you want to look at specific data or reduce the number of rows you need to scroll through.

Save your work as "Year-Month-Date Usage" in the Audit Analysis Reports Folder.