Microsoft Excel 2013 Advanced

Duration: 1 Day
Costs: Open courses: From £245 + VAT
In-house: Price on application - request a quote >>
Aims: This course is designed for those delegates who wish to understand how to analyse and present their data more efficiently as well as automate common tasks.
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-requisites: 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.

Course Outline

Multi-Criteria Statistical Functions

  • Recap on SUMIF, COUNTIF, AVERAGEIF
  • Use SUMIFS, COUNTIFS, AVERAGEIFS functions

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

 

In-House Training

If you have a number of people in your organisation with the same training need, in-house training programmes are an ideal solution.

We pride ourselves on delivering training that is relevant to your business – ensuring staff see the benefits and how they can save time and work more efficiently.

We can deliver both tailored training sessions or the more traditional Foundation, Intermediate and Advanced levels.

Delivery Methods

Training can be delivered at your offices or we can help to select a suitable venue. We can deliver training in various different ways with the more popular being:

  • Group classroom training
  • 1-to-1 training sessions to address individual needs
  • Webinars/Online training

Laptop Hire

Laptop hire is available for most of our courses. We provide PC laptops with the appropriate software pre-loaded as well as projectors, mice and power extension leads.

Pre-Course Skills Assessments

Our focus is always on providing the most relevant training for each client. Before any course we offer pre-course assessments in order for us to ascertain your exact requirements, so you don't waste any time or money on training that isn't right for you and your business.

Course Materials

We provide electronic step by step training reference manuals and issue attendance certificates on completion of the training. That way once we finish, you'll still have everything you've learned at your fingertips.

Post Training Support

In order to make sure you get the best service and expertise from TrainingU, we offer unlimited lifetime post training support giving you peace of mind, should you need help with anything.

Open Course Dates

Dates Venue Offer Prices  
22 February 2018 St Albans  £195 £245 Book
Now
22 May 2018 St Albans   £245 Book
Now
2 August 2018 St Albans   £245 Book
Now
29 November 2018 St Albans  £195 £245 BookNow

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