Microsoft Excel Advanced Level 1
Microsoft Excel training course in Melbourne and across Australia. Face-to-face Excel training or Live online Excel training courses available.
The Excel Advanced course aims to give participants a comprehensive overview of Excel’s Formulas, Functions, Tables, PivotTables and PivotCharts in order to create comprehensive reports from databases.
What you'll Learn:
A single cell or a range of cells can be named to simplify references in formulas and functions.
You'll learn how to create Names, use Names in formulas and functions and Manage Names.
The fancy formatting options for tables are just the beginning, what makes tables excellent to work with, it the way they interact with Formulas, Functions, PivotTables, Data Validation and a host of other features.
Understanding Excel's Functions is an import aspect of using Excel to it's full potential. The advanced functions covered in this course aims to help you understand function structure and requirements which can be applied to any function.
Advanced Conditional Formatting
Conditional formats are a valuable method for error checking of data entries and highlighting
discrepancies between numbers.
For example, when a cell entry exceeds a specified value or is outside a particular range, the user can be alerted by seeing the value displayed in a different colour.
Learn about Advanced techniques for Sorting, Filtering and Summarising Data with the SUBTOTAL function.
PivotTables and PivotCharts
A PivotTable is an interactive table that quickly summarises, or cross-tabulates, large amounts of data from lists or tables. Field headers can be dragged to various regions within the PivotTable structure with complete flexibility to create a report.
Once a PivotTable is populated with data it can be rearranged, charted, formatted, filtered, calculated, summarised and broken up into individual pages.
Macros (VBA for Excel)
When the same actions or tasks are repeatedly performed in documents, it might be time to create a macro. A macro is a recording of each command and action performed to complete a task. Then, whenever a task needs to be carried out in a document, the macro could be run instead.
- Live instructor led training
- Comprehensive training manual
- Training certificate
Previous experience using Microsoft Excel at an Intermediate level is an essential prerequisite for this course.
- Naming cells
- Naming ranges
- Using Names in formulas and functions
- Creating Tables
- Table Components and Features
- Table Formatting
- Formulas and Functions in Tables
- Naming Tables and using Table names in Formulas and Functions
- Using Slicers
- Function Structure
- IF function
- AND / OR functions
- Nested functions
- VLOOKUP function
- XLOOKUP function (Office 365)
- IS functions
- Advanced Sorting & Filtering
- SUBTOTAL function
- Subtotals Feature
- Constructing PivotTables
- Data fields
- Hiding and showing field details
- Creating data groups
- Displaying Source Data
- PivotTable Options
- Formatting PivotTables
- Comparative Calculations
- Calculated fields
- Data Consolidation with PivotTables
- Creating a PivotChart
- Reorganising Chart Fields
- Filtering Chart Fields
- Using Slicers on Charts
- Recording Macros
- Running Macros
- Assigning Macros to Worksheet Objects
- Customising the Ribbon