Microsoft Excel Advanced

Excel is a powerhouse of tools for data management and analysis. This 2-day advanced training will focus on improving your efficiency using Excel’s advanced features, such as Power Query, Pivot Tables, and macros. Participants will learn to clean, transform, visualize, and automate data for more impactful reporting, dashboards, and business analysis.

By the end of this course, participants will:

1. Master advanced Excel features to manipulate and analyze data.
2. Automate data workflows and tasks using macros and Power Query.
3. Create and format professional dashboards and reports with PivotTables and charts.
4. Use advanced formulas for dynamic reporting and data modelling.
5. Generate and share reports with minimum maintenance.

DAY 1: Excel Fundamentals & Power Query

Module 1: Introduction to Excel Advanced Features

1. Recap of basic Excel functionalities.
2. Overview of advanced features for the course.

Module 2: Working with Tables

1. Using Tables for structured data management.
2. Customizing table styles and adding calculated columns.
3. Using table references in formulas.

Module 3: Power Query Basics

1. Introduction to Power Query for data cleaning and transformation.
2. Connecting to data sources (Excel, CSV, web).
3. Filtering, sorting, and transforming data (e.g., removing duplicates, changing data types).
4. Creating calculated columns in Power Query.

Module 4: Advanced Data Manipulation

1. Auto Filter & Advanced Filter for data analysis.
2. Setting up data validation rules and error handling.
3. Applying conditional formatting with custom formulas.

Module 5: Essential Functions for Dashboard Design

1. SUMIF, SUMIFS, COUNTIF, COUNTIFS.
2. SEARCH, FIND.
3. Filter Function.
4. IF, AND, OR, NOT logic.
5. INDEX, MATCH, VLOOKUP or XLOOKUP.
6. INDIRECT and new AI functions.

Hands-On Practice

1. Use Power Query to clean and transform a dataset.
2. Apply data validation and conditional formatting to real-world business data.

DAY 2: Pivot Tables, Macros & Advanced Dashboards

Module 6: Pivot Tables & Pivot Charts (Excel Dashboard Creation)

1. Introduction to Pivot Tables.
2. Using Pivot Tables for summarizing data.
3. Sorting, grouping, and filtering data in PivotTables.
4. Creating Pivot Charts for dynamic visualization.
5. Using slicers and timeline filters.

Module 7: Power Query

1. Transforming multiple tables with Power Query (Merging and Appending queries).
2. Setting up Power Query for automatic data refresh.
3. Applying transformations for better data analysis and reporting.

Module 8: What-If Analysis

1. Using Goal Seek for forecasting and planning.
2. Setting up Data Tables for different scenario analyses.
3. Introduction to Solver for optimization.

Module 9: Macros and VBA

1. Introduction to macros and VBA for automating tasks.
2. Recording and running macros.
3. Editing macros in the Visual Basic Editor.
4. Creating macro buttons for automation.

Module 10: Advanced Charting Techniques

1. Customizing chart elements and creating interactive charts.
2. Creating Speedometer, Gantt, and Thermometer charts.
3. Dynamic charts that update automatically with data changes.

Final Hands-On Project

1. Build a complete Excel dashboard using:
   1. Pivot Tables, charts, and Power Query.
   2. Macros for task automation.

We’d happy to help.
Feel free to talk to us today, however most suitable for you.

Mobile call: +603 – 7493 6868

WhatsApp: +6018 – 380 5878

Email: enquiry@apexitraining.com.my

Website: www.apexitraining.com.my