Excel Advanced

Resources for the Faculty of Medicine Session for Microsoft Excel®
Advanced Session

This site is to provide links and resources that are not in the course notes. Most of the documentation for what we will be doing is in those notes or in the sample files that we will be using.


Overview

This session is for people who need to understand some of the more advanced aspects of Microsoft Excel®. This will include aspects of automation where Excel can help you to analyse your data and even reverse-engineer your results, while saving sets of assumptions along the way. You will also create user defined functions and macros to automate routine tasks.

Note: This course relies heavily on concepts and skills introduced in the Excel Introduction for the Faculty of Medicine.

Course Description

We will:

  • Use some of the advanced options available in the Back Stage View (File Tab)
  • Learn more tricks with Autofill, Copy/Paste, Insert with keystroke and mouse combinations and other quick ways to boost your productivity with Excel.
  • Explore advanced functions that read data and generate results based on specific conditions.[examples: IF, SUMIF, ISNA]
  • Secure your worksheets and workbooks
  • Create Macros
  • Optional: and start to work with Visual Basic for Applications (VBA)
  • Expand and extend your previous Work with Tables and PivotTables

Duration: 6.5 hours

Objectives

By the end of this session you will be able to

  • Use built-in Excel tools to audit your worksheets and to understand how they work and to detect errors.
  • Use What-If Analysis to do Goal-Seeking and use the Solver Add-in
  • Create Scenarios both manually and automatically to store multiple sets of assumptions within a single workbook
  • Create Custom Lists to be used with AutoFill and custom sorting.
  • Learn the Security Model (Office 2010) that governs Excel documents
  • Create and work with Excel Templates.
  • Build Macros and execute them with buttons on the worksheet.
  • Optional: Read simple Visual Basic scripts (this is a conceptual introduction)
  • Create User Defined Functions with Visual Basic.
    • Logical Functions: SUMIF()
    • Lookup Functions: VLOOKUP() MATCH() INDEX()
  • Use PivotTables to summarize large bodies of data.

Prerequisites

This course relies heavily on concepts and skills introduced in the Excel Introduction for the Faculty of Medicine.

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

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

Security

Slideshow: Office 2010 Security: Protecting your files


Microsoft Office Video: Protect worksheets and workbooks

Video

MS_Office Video: Protect worksheets and workbooks

Macros

Video: Create and run macros in Excel 2010

Video: Save time by creating and running macros in Excel 2010 video 1 of 4

User Defined Functions (VBA)

Code for User Defined function

Code for User Defined Function Fahrenheit

Function Fahrenheit(CelsiusDegrees)
'Returns degrees Fahrenheit for given degrees Celsius
Fahrenheit = CelsiusDegrees * 9 / 5 + 32
End Function

Visual Basic for Applications VBA

Details

VBA from Balance and Signing Auth Lookup - Sheet 5 SpeedChart Balance Check

VBA from Balance and Signing Auth Lookup – Sheet 5 SpeedChart Balance Check


Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "A1:A100" '<== change to suit
On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Target.Formula = UCase(Target.Formula)

End If

ws_exit:

Application.EnableEvents = True

End Sub


Cut & Paste Code and Examples

Details

 

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

details

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



Resources