Page Comparison

...

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
Image Modified
Select “New Query” then “From File” and “From Folder”. (Note: This image does not reflect the current file path.)
Image Modified
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.
Image Modified

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.
Image Modified

Format Data

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

In the pop-up window, select "Table 0" for the door numbers and date/times accessed to appear. Click OK.
Image Modified
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.
Image Modified
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.
Image Modified
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".
Image Modified
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".
Image RemovedImage Added
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.

...

In the tool bar at the top, click Insert.
Make sure all three columns are highlighted and select "Pivot Table".
Image RemovedImage Added
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.
Image RemovedImage Added
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).
Image Added
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.
Image RemovedImage Added
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.
Image Added
Working with "Range" again, click and drag "Door" to the "Rows" box. This will now appear on the spreadsheet:
Image RemovedImage Added
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.
Hover Image Added
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".
Image Added
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", then enter . 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.
Image Added
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 is being close to the minimum but not quite where it needs to be.
NowFinally, click and drag Event/Name to the Values box. This means Event/Name will be in 2 boxes. This is what the final settings should look like (if you haven't checked "Date":
Image RemovedHowever, in the Values box, it will change to "Count of Event/Name."