Library:Workshop - Excel

From UBC Wiki
Jump to: navigation, search

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

  1. In the sample spreadsheet, select row 18
  2. From the View tab select Freeze panes
  3. 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

  1. Place cursor anywhere in the table
  2. From the Data tab select Filter
  3. 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

  1. Select rows 3 to 16
  2. 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

  1. Place cursor anywhere in the source data
  2. From the Insert tab select Pivot table
  3. Click OK. The default settings will create a pivot table in a new worksheet
  4. 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

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


Pivot charts for visualization

  1. Place cursor anywhere in the pivot table
  2. 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

  1. Drag Desk into the Column Labels quadrant
  2. 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:

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


Filters and slicers

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

  1. Drag Month into the Report Filter quadrant
  2. 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

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