Excel 2007/2010 Intermediate 2

This course is for anyone who has been using Excel for budgets and managing information.

Aims

By the end of the course, you will be confident in using some of Excel’s more advanced features. You will be able to do more complex “what if” scenarios and use conditional formula and pivot tables to manipulate information.
Check Availability Download Course Outline

What you will learn

In particular you will be able to:

  • Move around a spreadsheet speedily using range names and use them in formula to make them easier to read and more accurate.
  • Use the auditing feature to check where the errors such as DIV?0 or NAME come from.
  • Create calculations based on single or multiple conditions. For example, calculate a discount for those over a certain age or ordering a high volume (IF with AND and OR).
  • Cross reference data using an Hlookup or Vlooukup function. For example, look up national insurance number to pull in personal information from another sheet or book.
  • Use Filters and Pivot Tables to help manipulate your data to find only the data you are interested in and make it easier to read.
  • Create macros to automate your work.
  • Assign a button on a toolbar to a macro to make it easier to use.

Requirements

Delegates must be confident in using Windows and a mouse. Additionally you must have used Excel before and be familiar with entering formula and using absolute cell references (see our Excel Essentials and Intermediate Outlines).

Timetable

Session 1

Recap, Partial Absolute Cell References, Using Paste Function, Good Design Principles, Range Names, Auditing Your Worksheet.

Session 2

Conditional Processing – Using the If Function with AND and OR, Using Vlookup or Hlookup to categporise and find an exact match.

Session 3

Recap, Advanced Filters, Pivot Tables.

Session 4

Introduction to Macros – Running and recording macros,  Assigning a macro to a button on a toolbar.
Check Availability Download Course Outline

The ‘Small Print’

The course will run from 10am to 4:45pm (light cold buffet 1pm to 1:45pm). Please be careful to arrive by 9.45am for registration and refreshments. The course will start precisely on time and it may not be possible to go back over material missed by late arrivals. If you arrive over 30 minutes late you may be asked to leave and attend another day.