Excel I

Resources for the Faculty of Medicine Session for Microsoft Excel®



Overview

This session is for people who have been using Microsoft Excel® every day, perhaps for years. Many people are not yet using some of the most useful features because they are not easy to find or discover.

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 for others to enter data correctly with lookups and data validation.
  • Work with Tables and PivotTables

Duration: 6.5 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.
    • Logical Functions: IF() AND() OR()
    • Statistical Functions: SUM() SUMIF()
    • Lookup Functions: VLOOKUP()
  • Convert ranges to Excel Tables.
  • Use PivotTables to summarize large bodies of data.
  • Use Auditing Tools to understand workbooks and find problems.

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

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® Faculty of Medicine 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