Course Structure
This course is split over the following 5 modules:
Advanced Functions
- Name Manager, Naming Ranges and Using them in Formulas
- Formula Auditing
- SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
Working with Data and Real-Time Collaboration
- Advanced Relative & Absolute Cell References
- Restricting Cell Entries to Certain Data Types
- Data Validation Tools
- Real-Time Collaboration Features
Advanced Data Analysis and Presentation
- Using Sparklines
- Advanced PivotTables
- Grouping Data to analyse data by Year/Quarter/Month
- Summarise/calculations embedded in Pivot Tables
- Introduce, create, and manage Slicers
- Create and manage multiple PivotCharts
- Manipulating options in existing PivotCharts
- Multiple PivotTables linked to the same slicer
- Building a BI Dashboard
- Advanced Charting Techniques
Power Query
- Get Data
- Transform Data
- Load Data
- Create relationships between tables
Integration with other tools
- Integration with Power BI
- Using Excel with Power Automate
- Data Import and Export
Should Attend
Everyone can attend this course. However, an Intermediate Excel proficiency is advised.
Prerequisites
Applicants should have completed Excel Intermediate Level.
Assessment
There is no final assessment. Students will be guided during the course. At the end of the course, attendees will receive a Certificate of Attendance.
Key Features
Learning Outcomes
Advanced Functions. By the end of this module, participants will be able to:
- Understand how to create and manage named ranges and use them effectively in formulas to simplify complex calculations.
- Learn to trace and debug formulas to ensure accuracy and identify errors.
- Master the use of conditional functions to perform calculations based on specific criteria.
Working with Data and Real-Time Collaboration. By the end of this module, participants will be able to:
- Gain proficiency in using relative and absolute cell references to create dynamic and flexible formulas.
- Learn to use data validation tools to restrict cell entries and ensure data integrity.
- Understand how to apply various data validation rules to control the type of data entered into cells.
- Explore Excel’s real-time collaboration features to work efficiently with others on shared workbooks.
Advanced Data Analysis and Presentation. By the end of this module, participants will be able to:
- Learn to create and use sparklines to visualize data trends within a single cell.
- Gain expertise in creating and manipulating PivotTables to analyze large datasets.
- Understand how to group data in PivotTables to analyze it by different time periods.
- Learn to perform calculations and summarize data directly within PivotTables.
- Master the use of slicers to filter data in PivotTables and PivotCharts.
- Develop skills to create and manage multiple PivotCharts for data visualization.
- Learn to customize and manipulate options in existing PivotCharts to enhance data presentation.
- Understand how to link multiple PivotTables to a single slicer for synchronized filtering.
- Gain the ability to build interactive BI dashboards using Excel’s advanced features.
- Explore advanced charting techniques to create visually appealing and informative charts.
Power Query. By the end of this module, participants will be able to:
-
- Learn to use Power Query to import data from various sources.
- Understand how to transform and clean data using Power Query’s powerful tools.
- Master the process of loading transformed data into Excel for analysis.
- Learn to create and manage relationships between tables to build complex data models.
Integration with Other Tools. By the end of this module, participants will be able to:
-
- Explore how to integrate Excel with other tools such as Power BI and Power Automate for enhanced data analysis and automation.
- Understand how to use Excel with Power Automate to streamline workflows and automate processes.
- Gain proficiency in importing and exporting data between Excel and other applications.