About the Course
Microsoft Excel Advanced aims to strengthen the data analysis, reporting, and automation skills of experienced Excel users. The training covers complex formulas, advanced data tools, PivotTable applications, and recorded macros to help manage business processes faster and more effectively.
Target Audience
Users with intermediate Excel knowledge
Professionals performing intensive data analysis and reporting
Finance, accounting, human resources, and operations teams
Users looking to build automation and advanced analytics in Excel
Learning Outcomes
Use complex formulas and nested functions effectively
Build advanced reports with PivotTables and PivotCharts
Analyze large datasets using built-in data management tools
Automate repetitive tasks with recorded macros
Produce advanced, professional Excel solutions for business processes
Curriculum
Module 1 – Tables and Data Management
Using the Data Ribbon
Sorting Tables and Custom Sorting (Sort / Custom Sort)
Applying Auto Filter
Filtering Tables with Advanced Filter
Data Consolidation (Data Consolidate)
Importing External Data (Import E.Data)
Subtotals (Subtotal)
Removing Duplicates (Remove Duplicates)
Converting Text to Columns (Text to Columns)
Flash Fill
Using Data Validation
Module 2 – Conditional Formatting
Applying Conditional Formatting to Text, Numbers, and Dates
Working with Data Bars, Color Scales, and Icon Sets
Defining Custom Rules (New Rule)
Editing and Deleting Rules (Manage Rules)
Module 3 – Functions
Lookup and Reference Functions
VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH
Logical Functions
IF, Nested IF, AND, OR, IFERROR
Text Functions
UPPER, LOWER, LEFT, RIGHT, CONCATENATE, MID, LEN, TEXT, SEARCH
Conditional Aggregation Functions
SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, SUMPRODUCT
Database Functions
DSUM, DAVERAGE, DMAX, DMIN, DCOUNT
Module 4 – Working with Excel Tables
Creating Dynamic Excel Tables
Using Dynamic Table Features
Using the Total Row Feature
Creating Defined Names
Using the Name Manager
Module 5 – PivotTables & PivotCharts
Creating a PivotTable
Modifying a PivotTable
Refreshing PivotTables
Updating the Data Source Range
Using Report Filter Pages
Relationships Between Tables
Report Connections
Inserting Slicers and Timelines
Creating a PivotChart
PivotChart Options
Module 6 – Security
Protecting Workbooks, Worksheets, and Cells
Securing Workbook Structure
Encrypting Excel Files with Passwords
Permission Settings
Module 7 – Recorded Macros
Recording a Macro
Running a Recorded Macro
Editing a Recorded Macro
Module 8 – Tips and Tricks
Keyboard Shortcuts
Practical Techniques in Functions and Data Analysis
Other Courses in This Category
-
Basic MS Project
Foundational Microsoft Project training covering project scheduling, Work Breakdown Structure (WBS), task dependencies (FS, SS, FF), resource assignment, and Gantt Chart tracking.
- Live Course
- 8 modules
-
Advanced MS Project
Advanced Microsoft Project training covering Critical Path analysis, resource leveling, cost and budget variance tracking, and Earned Value (EV, PV, AC) performance analysis.
- Live Course
- 8 modules
-
Excel VBA Training
Automate Microsoft Excel with VBA: macro recording, Excel Object Model programming, custom functions, interactive UserForms, error handling, and Access/SQL Server integration.
- Live Course
- 9 modules
-
Excel Expert Level
Expert-level Microsoft Excel training covering array formulas, XLOOKUP, INDEX/MATCH, advanced PivotTables, Goal Seek, Solver, What-If Analysis, form controls, and Power Query.
- Live Course
- 8 modules
-
Microsoft Word, PowerPoint and Project for knowledge workers and project teams: long-form corporate document templates, Slide Master and data-driven executive decks, WBS, critical path (CPM) and resource scheduling.
- Live Course
- 5 modules
-
Basic Excel
Foundational Microsoft Excel training covering interface navigation, cell and table formatting, worksheet management, conditional formatting, charts, and core formulas.
- Live Course
- 9 modules
Get in touch
