Microsoft Excel Advanced

Microsoft Excel Advanced

Duration:
1 Day
Aims:

This course is aimed at regular users of Excel who need to broaden their knowledge and develop a structured approach to building more complex formulas. The course also covers many tools and features to help them develop effective worksheet models.

Objectives:

On completion of this course, attendees will be able to: create a variety of advanced statistical, logical and lookup functions; further analyse data with PivotTables; create multiple conditional formats; use data tools to 'clean up' data lists; apply data entry rules and restrictions; apply worksheet/workbook protection; record a basic macro (note: VBA programming is not covered on this course).

Pre-Requistites:

Before attending this course, attendees should have either participated in our Excel Intermediate course or have equivalent knowledge. Attendees of this Advanced course must already be confident with: writing formulas; the basics of IF, SUMIF, COUNTIF, LOOKUP functions; creating a basic PivotTable; the basics of the Conditional Formatting feature.

Versions

Courses are delivered in 2007, 2010, 2013, 2016.

What will I learn?
Multi-Criteria Statistical Functions
  • - Recap on SUMIF, COUNTIF, AVERAGEIF
  • - Use SUMIFS, COUNTIFS, AVERAGEIFS
Logical Functions
  • - Recap on Single IF Statement
  • - Nested IF statements
  • - Combine IF statements with AND, OR, NOT functions
  • - IFERROR & ISNA
Further VLOOKUP Function
  • - Nest VLOOKUPs with IF function
Formula Auditing
  • - Use worksheet auditing tools
  • - Trace dependent and precedent cells
  • - Check for errors
More on Conditional formatting
  • - Manage multiple conditional formats
  • - Logical expressions in Conditional Formatting
Use Data Tools to manipulate data
  • - Split text to columns
  • - Use Text Functions to change display of data
  • - Remove duplicates
Further PivotTables
  • - Manually group items
  • - Automatically Group Date items
  • - Create calculated fields
  • - Create calculated items
  • - Reference cells within a PivotTable
  • - Set PivotTable options
PivotTable - Data Model
  • - Create a PivotTable from multiple Excel tables
Data Validation
  • - Types of validation
  • - Setup rules for data entry
  • - Highlight invalid data
  • - Create drop down lists
  • - Display help and error messages
Protection
  • - Apply cell, worksheet, and workbook
  • - Password protect a file
Introduction to the Macro recorder
  • - Macro security
  • - Record a Macro
  • - Assign a Macro to a button, shortcut key and to the Quick Access Bar
  • - Note: This course does not cover VBA programming
Open course dates
Keep me informed of future dates and offers Keep me informed
Dates Venue Offer Prices  
22 February 2018 St Albans   £245 Fully Booked
22 May 2018 St Albans   £245 Book
Now
2 August 2018 St Albans   £245 Book
Now
29 November 2018 St Albans  £195 £245 Book
Now

We offer with all our public scheduled courses:

  • Free pre-course skills assessment
  • Lunch and refreshments
  • Course reference guide
  • Attendance certificate
  • Lifetime post training support
  • Small groups of up to 8 people

Courses start at 9:30am and finish at 4:30pm

Related courses
Next Available Course Date

22nd May 2018

Venue

St Albans

Price

£245

Book now View more dates
Run this
course at
your offices
Request a quote
which course
Which level do you need?
Take our online skills assessment
Find out more
Speak to a friendly advisor

call

0333 344 6630

or visit our

Online Chat
Trustpilot