Koerner Library

Resources for the Koerner Library Session for Microsoft Excel®



Overview

This session is for people who are using Microsoft Excel® on a regular basis and who want to use the software as a tool to get things done. Some of the most useful features are not easy to find so we will uncover the mindset behind the user interface to make things discoverable.
The afternoon session will be more specifically for people who manipulate large bodies of data (thousands, and tens of thousands of rows of data).

Course Description

We will:

  • Do a review of the basics including the fluent user interface.
  • Learn tricks to speed copy/paste operations with Autofill, Format Painter, and other quick ways to boost your productivity with Excel.
  • Explore functions in terms of how they work, and focus on certain functions that are relevant to your work.
  • Learn to make it easy connect data from one place to another using lookups.
  • Work with Tables and PivotTables

Duration: 6.0 hours

Objectives

By the end of this session you will be able to

  • Use the Office 2010 Fluent User Interface effectively (Ribbon, Quick Access Toolbar, Tabs and Contextual Tabs)
  • Think like a Worksheet Designer and consider the people who will be using your worksheets (Users)
  • Use Autofill to extend known sequences, and to copy and paste cell contents and formulas.
  • Navigate Large Worksheets easily using the options on the View Tab to control what you see.
  • Use Conditional Formatting to get Excel to highlight important information automatically.
  • Understand Functions at a high level, and be able to use the Function Tool to find and use functions easily.
  • Use Names (named cells and ranges) to simplify your work and make it easier to understand your formulas.
  • Work with large bodies of data
    • Convert ranges to Excel Tables
    • Sort, Filter data
    • Use PivotTables to summarize large bodies of data.
    • Copy large bodies data that meet specific criteria
    • Optional – link two tables of data using a shared key

Prerequisites

Some prior experience with Excel is strongly recommended. You don’t have to have been designing complex workbooks, but you it would be very helpful if you have ben using Excel for data entry and creating simple worksheets.

Printed Material

You will have a workbook for reference and to add your own notes.

Computer Equipment

The sessions are conducted in a computer lab, and you will have a computer provided for your use.

Sample Files

We will use these sample files during our session. These will be available after the session.

Sample Files (right click, open in new window).

Click on any file to preview it in Excel on the Web, but if you want to work with it, use the download button to copy it to your local hard drive before attempting work with the file.

Or

download all the files as compressed folder

Instructional Methods

  • Hands-on labs
  • Discussion
  • Lecture

Course Materials

  • Web site
  • Course notes
  • Printed copies of on-screen lecture notes available on request during lectures

No Recording or Pictures in the Lab

Please note: Taking pictures or video during the course sessions is not permitted. All the course materials and lecture notes are available in physical form and on the web.

Sample Files

Sample Files

Click on any file to preview it in Excel on the Web, but if you want to work with it, use the download button to copy it to your local hard drive before attempting work with the file.

Or

download all the files as compressed folder

Reflection

How are we doing?
metacognition

See the last page of your notes: Things I really want to remember.

Backup Support Site

Microsoft Excel® Koerner Library Support site

Warm Up Basic Skills

What you saw when it was done

Image:Firstxlsxa.png

What you typed to make it happen

Image:Firstxlsxb.png

Quick Access Toolbar

Quick Access Toolbar
Customize the Quick Access Toolbar


Overview

Decision Support Worksheet

  • Design considerations
  • Formulas
  • Relative and Absolute cell addresses
  • Functions: Sum, Max, If
  • Conditional Formatting

Workshop notes

Discussion

Image:Decide Workbook 01.jpg

Weighting

Image:Decide Workbook 02.jpg

Formulas

Image:Decide Workbook 03.jpg

G13: =G12/MAX($G$12:$I$12)

Copy / Paste

Image:Decide Workbook 03.jpg

Percentages
G13: 

=G12/MAX($G$12:$I$12)

IF
Version 1 “Winner”
G14: 

=IF(G13=100%,"Winner","")

Winner / Loser

Image:Decide Workbook 05.jpg

=IF(G13=100%,"Winner",IF(G13=MIN($G$13:$I$13),"Loser",""))

Conditional

Image:Decide Workbook 06.jpg

Copy / Paste 2

IF
Version 1 “Winner” 

=IF(G13=100%,"Winner","")

Version 2 (Winners and Losers) 

=IF(G13=100%,"Winner",IF(G13=MIN($G$13:$I$13),"Loser",""))

Rank 

=RANK(G13,$G$13:$I$13)

Choose 

=CHOOSE(G15,"First","Second","Third")
Cut & Paste

Data1

Age

Birthdate is in cell J2

=INT((NOW()-J2)/365.24)

PivotTable

Month

Returns the Month as Text (not a date formatted as Month)

Cell C2 contains a date

=TEXT(C2,"mmm")

Year

Returns the Year as Text (not a date formatted as Year)

=TEXT(C2,"yyyy")

Week

=WEEKNUM(C2)

Tables

There are many built-in features that you can apply when you convert a range into a Table. Select a single cell in the range you want to convert, then choose the Insert Tab, then click the Table command.

In the example below we will convert an existing table (with a lot of formatting), so we will have to do some work before hand to make things work better. If there were no formatting and all the column headings were in a single row, we wouldn’t have to do any preparation.

Image:ConvertToTableFOM.jpg

References

PivotTables

Here is a quick video of creating a PivotTable based on this file:

File: PivotBowlingScores.xlsx

link to video

Start with a range or table that has unique column headings in a single row at the top

Image:ExcelInsertPivotTable01.jpg

The PivotTable opens in a new worksheet

Image:ExcelInsertPivotTable02.jpg

Click Fields (right side of the screen) from the field list for Row Labels and Values
Double click any summary number to see a new sheet with the drill-down details

Image:ExcelInsertPivotTable03.jpg

Try setting a Field as a Column Label

Image:ExcelInsertPivotTable04.jpg

Try rearranging the Row Labels and Column Labels

Image:ExcelInsertPivotTable05.jpg

Image:ExcelInsertPivotTable06.jpg

Pivot Chart Example

Image:Excel Pivot Chart 01.png

Pivot Cheque Register

Note: To make this example work, we need cells that contain Month and Year

G2:
=TEXT(C2,"mmm")
 
H2:
=TEXT(C2,"yyyy")

Image:Excel Pivot0201.png

Image:Excel Pivot0202.png

Faculty of Medicine Examples
Example 1 (counting instances) Raw Data

Image:FOMPivot01a.jpg

Pivot Table

Image:FOMPivot01b.jpg

Example 2 (money) Raw Data

Image:FOMPivot02a.jpg

Pivot Table

Image:FOMPivot02b.jpg

 

Link to this Table

Copy this link to your clipboard

https://fms-nquery.finance.ubc.ca/oldstuff/IHRIS/Departmentcodes.htm

In Excel – choose the Data Tab and Import from Web, then paste the link in to the box.

Or

  1. Follow the link above
  2. Right Click within the table of data
  3. Click Export to Excel

 

Resources