Microsoft Excel Advanced (Private Group)

ExperTrain

Aims

This Microsoft Excel Advanced course is for attendees who want to expand their knowledge into some of the more advanced functions, analyse / summarise tables of data and automate common tasks.

Our Microsoft Excel course is delivered using the latest version available through Microsoft 365. This ensures that our demonstrations include the most up-to-date features and functions. While many of the concepts and techniques covered are applicable to earlier versions of Excel, there may be some differences in the user interface and available features. If you are using an older version of Excel, our trainers are happy to provide guidance on how to access similar functionalities within your version.

Objectives

‍On completion of this course, delegates will be able to:

• Use logical, lookup, statistical and error functions
• Use auditing tools
• Create range names and use range names in formulas
• Set restrictions on data entry using data validation
• Protect an Excel cell, worksheet, and workbook
• Automate common task by recording a basic macro
• Create, edit, format Pivot Tables and Pivot Charts

Course Content

Introduction and Objectives

Using Logical, Lookup and Statistical Functions

• Using the VLOOKUP / HLOOKUP functions
• Using IF, AND & OR function
• Using the IFERROR function
• Using COUNTIF & SUMIF

Using Auditing Tools

• Displaying precedents and dependents
• Removing arrows
• Evaluating a formula
• Setting manual & automatic calculation

Using Range Names

• Assigning names to ranges
• Using range names in formulas
• Editing and deleting named ranges

Using Data Validation

• Validating data using a data validation list
• Creating a custom input and error message
• Editing a validation list
• Removing data validation

Protecting Excel Data

• Protecting entire worksheet
• Protecting and unprotecting certain cells within a worksheet
• Protecting workbook structure

Introduction to Basic Macros

• What is a macro?
• How to record macros
• How to run a macro using the shortcut key and the run button
• Deleting a macro

Creating / Revising Pivot Tables and Pivot Charts

• Creating a Pivot Table
• Adding, moving, removing Pivot Table fields
• Formatting and structuring a Pivot Table
• Filtering Pivot Table items
• Changing the summary function
• Summarising data as percentages
• Refreshing a Pivot Table
• Changing the data source for a Pivot Table
• Adding a Pivot Table slicer
• Moving & deleting a Pivot Table
• Creating more than one pivot table on the same worksheet
• Creating a Pivot Chart
• Formatting a Pivot Chart

Hints and Tips

Best for confident Excel users who need to analyse data and build more robust spreadsheets for work. This course suits you if you want to get comfortable with advanced functions (including logical and lookup functions), auditing tools and features used for reporting and analysis.

Attendees who wish to attend this course must have a good knowledge of Excel and be competent at working with formulas using absolute cell references, use basic functions like sum, average, max, min, count and be able to write formulas across multiple sheets and multiple workbooks.

Microsoft Excel is a core skill across almost every industry. Skills from this course support roles such as Administrator, Office Manager, Finance Assistant, Data Administrator, Analyst and Project Coordinator. It also provides a foundation for advanced Excel, data analysis and Power BI.