Library:Workshop - Excel

Instructors

Jeremy Buhler, Assessment Librarian (jeremy.buhler@ubc.ca)
Jonathan Kift, MLIS Candidate

Purpose

Using a sample spreadsheet of anonymous Desk Tracker data, this hands-on workshop introduces some Excel functions for data manipulation, presentation, and analysis.

Outline

Introduction (5 min)
User interface tips (5 min)
Some helpful formulas (10 min)
Questions & Practice (5 min)
Pivot tables (20 min)
Questions & Practice (10 min)

Download sample spreadsheet

User interface tips

Footer summary

Located at the bottom left of the screen

Average: For numerical values, shows the average of the selected non-empty cells
Count: Shows the number of cells that are not empty in the selected range
Sum: For numerical values, shows the sum of the selected non-empty cells

Freeze rows/columns

In the sample spreadsheet, select row 18
From the View tab select Freeze panes
Choose Freeze panes

In general, Freeze panes will freeze all rows above the selected cell, and all columns left of the selected cell.

Data Filters

Place cursor anywhere in the table
From the Data tab select Filter
Arrows appear in each of the column headers; click them for table sorting and filtering options

Notice how the list of items in the filter provides a summary list of the types of elements in the column you are filtering.

Expanding the formula bar

If the formula you are working with gets too long, you can give yourself more "breathing room" by moving your mouse pointer to the bottom edge of the formula (until the cursor becomes a double-headed arrow), and dragging down.

Hiding/un-hiding rows and columns

Select rows 3 to 16
Right click highlighted rows and choose Unhide to expose the hidden table

Some helpful functions

=COUNTA()

Counts the number of cells that are not empty in a specified range
The formula in cell K16 counts all the entries in column G

=SUBTOTAL()

Counts the number of cells that are not empty in a specified range AND re-calculates according to filter settings
The formula =SUBTOTAL(3,G:G) in cell K17 counts all the visible entries in column G and updates when filters are applied

=COUNTIF()

Counts the number of cells in a range that meet the specified criteria
Type the following into cell B7 to count the number of Directional questions in column:
=COUNTIF(G:G,A7)
Use the Fill function to extend the formula to other question types (cells A7:A12)
There are three ways to fill cells:
1. Click and drag the small square in the lower-right corner of the selected cell. (You will see the cursor turn into a black + when it is in the right place.)
2. Double-click the small square in the lower-right corner of the selected cell. This will automatically fill downward.
3. Select the region you want to fill, and then choose Fill->Down from the Home tab. This will only fill the region you have selected, and can fill in other directions if you like.

=SUM() and arithmetical formulas

Adds up all the cells in a range.
Type the following into cell B13 to get a total for the column:
=SUM(B7:B12)

You can also create arithmetical formulas that don't contain functions at all.
Type the following into cell C7 to calculate the percent of the total:
=B7/B13

Notice how this is a decimal value, and not a fraction. Click the "%" symbol in the Number section of the Home tab to format it properly.
If you try to Fill this column, it won't work without a small change:
- Add a dollar sign to the formula like so:
  =B7/B$13
This forces the cell reference to stay on row 13. If we Fill now, it should work fine.

=TEXT()

Can be used to display a date/time field in another format; to show the month for each row in the sample file, type the following in cell C18:
=TEXT(B18,"mmm")
Use the Fill function to extend the formula to all cells in the column

Similarly, to show the day of the week for each row, add the following to cell D18:
=TEXT(B18,"ddd")
Use the Fill function to extend the formula to all cells in the column

Pivot Tables

Pivot tables can be a quick and flexible way to organize, visualize, and perform calculations on large data sets. They do not affect the source data and are excellent when you are looking at 2 or more dimensions of the data.

Creating a basic pivot table

Place cursor anywhere in the source data
From the Insert tab select Pivot table
Click OK. The default settings will create a pivot table in a new worksheet
Select the data to display by dragging items from the list on the right into the quadrants below:

drag Question type into the Row Labels quadrant
drag Question type into the Values quadrant

Calculations and displayed values

The example above displays a count for each question type but it's also possible to display percentages, % change, etc. In this example you will add a column showing percentage

Drag Question type into the Values quadrant AGAIN. There will be two columns showing totals
In the lower right Values quadrant, click the arrow on the second entry and select Value Field Settings
Switch to the Show Values As tab
From the drop-down list select % of Grand Total, then click OK

Pivot charts for visualization

Place cursor anywhere in the pivot table
From the Insert tab select Column; choose 2D Clustered Column(top left option)

The pivot table and chart are linked: changes to one affect the other which can be helpful for visual data exploration

Drag Desk into the Column Labels quadrant
Experiment by dragging other fields into Row or Column quadrants, switching from Row to Column, etc.

Changing chart type

Different chart types highlight different aspects of your data. Sometimes changing the chart type will expose a narrative that was previously hidden. To change an existing chart to a different type:

Right-click the chart area and select Change Chart Type
Choose the new type and click OK

Filters and slicers

Filters add another dimension to tables and charts by limiting the displayed data

Drag Month into the Report Filter quadrant
Experiment with filtering options using the drop-down arrow in cell B1

A more intuitive visual filter called a Slicer is available in the Windows version only

Place cursor anywhere in the pivot table
From the Insert tab select Slicer
Check Month and click OK