Microsoft Excel
Advanced
Overview
Clearly, you use Excel a lot in your role. Otherwise, you wouldn’t be taking this course. By now, you’re already familiar with Excel, its functions and formulas, a lot of its features and functionality, and its powerful data analysis tools. You are likely called upon to analyse and report on data frequently, work in collaboration with others to deliver actionable organisational intelligence and keep and maintain workbooks for all manner of purposes. At this level of use and collaboration, you have also likely encountered your fair share of issues and challenges. You’re too busy, though, to waste time scouring over workbooks to resolve issues or to perform repetitive, monotonous tasks. You need to know how to get Excel to do more for you so you can focus on what’s important: staying ahead of the competition. That’s exactly what this course aims to help you do. This course is designed for students who already have foundational and intermediate knowledge and skills in Excel and who wish to begin taking advantage of some of the higher-level functionality in Excel to analyse and present data.
Course Objective
Upon successful completion of this courses and will be able to perform advanced data analysis, collaborate on workbooks with other users, and automate workbook functionality.
You will:
- Perform advanced data analysis functions.
- Work with multiple worksheets and workbooks simultaneously.
- Share and protect workbooks.
- Automate and protect workbooks.
- Automate workbooks functionality.
- Use advanced analysing functions.
- Analyse data by using PivotTables, Slicers, and PivotChart.
- Use automated analysis tools.
Who Should Attend
This course is intended for students who are experienced Excel users and have a desire or need to advance their skills in working with some of the more advanced Excel features. Students will likely need to troubleshoot large, complex workbooks, automate repetitive tasks, engage in collaborative partnerships involving workbook data, construct complex Excel functions, and use those functions to perform rigorous analysis of extensive, complex datasets.
Prerequisites
Some familiarity with statistical concepts (mean, median, mode, standard deviation) and data analysis principles is highly recommended. While some advanced courses might review these concepts, it’s beneficial to have a foundation.
Training Calendar
Intake
Duration
Program Fees
Module
Module 1 - Advanced Functions and Formula
Topic A: TEXT Functions
- LEFT, MID and RIGHT Functions
- LEN and TRIM Functions
- UPPER, LOWER, and PROPER Functions
- CONCATENATE Function
- Text concatenation with the AMPERSAND (&)
- Using Flash Fill (Excel 2013 and above)
Topic B: Advanced Logical Functions
- AND, OR Functions
Topic C: Advanced Date and Time Functions
- DAYS
- NETWORKDAYS, NETWORKDAYS.INTL
- WORKDAY, WORKDAY.INTL
- EOMONTH and DATEDIF Function
Topic D: Advanced Lookup Functions
- INDEX Function
- MATCH Function
Module 2 - Working with Multiple Workbooks/Worksheets
Topic A: Linking Data Between Worksheets and Workbooks
- Linking Data with Paste Special Command
Topic B: 3D Reference
- Creating 3-D formula
Topic C: Consolidate Data
- Data Consolidation
Module 3 - Automating Workbook Functionality
Topic A: Data Validation
- The data Validation Dialog Box
- Data Validation Criteria
- Input Messages and Error Alerts
- Range Names
- Indirect Function
Module 4 - Protecting Data
Topic A: Password Protecting an Excel File
- Protect a Workbook from being Opened or Modified
- Unprotect a Workbook from Password
Topic B: Password Protecting a Workbook
- Protect Workbook Structure
- Unprotect Workbook Structure
Topic C: Password Protecting a Worksheet
- Using Worksheet Protection
- Unprotect a Worksheet
Topic D: Protecting Range
- Allow Edit Ranges
Module 5 - Auditing Worksheets
Topic A: Trace Cells
- Precedents
- Dependents
- Remove Arrow
- Show & Hide Formula
- Error Checking
- Evaluate Formula
Module 6 - Advanced Analysing Functions
Topic A: Calculation for 1 Criteria
- SUMIF, AVERAGEIF, COUNTIF
Topic B: Calculation for 2 or More Criteria
- SUMIFS, AVERAGEIFS, COUNTIFS
Module 7 - Using Automated Analysis Tools
Topic A: Performing What-If Analysis
- Goal Seek
- Data Tables
- Scenario Manager
- Creating a Scenario Summary Report
Topic B: Financial Functions
- PMT
Module 8 - Analyse Dataset with PivotTable
Topic A: Create PivotTable
- Transactional Data
- Field area of PivotTable
- Grouping data (date, text, number)
- Report layout using Desing Tab
Topic B: Present Data with PivotChart
- PivotChart fields Using Automatic Subtotals
Topic C: Filter Data Using Slicers
- Insert Slicers
- Insert Timeline
- Report connection
Module 9 - Presenting Your Data Visually
Topic A: Use Advanced Setting for Chart
- Dual axis
- Trendlines
Topic B: Sparklines
- Line
- Column
- Win/Loss
Module 10 - Importing Data
Topic A: Importing Text
- Methods of Importing Text Files
- The Text Import Wizard
Topic B: Import Data
- Get Data Tab
- Import Text/CSV File
FAQs
General Questions:
Q: What is a Microsoft Excel Advanced course
A: It’s a course that delves into the most sophisticated features and techniques of Excel, empowering users to perform complex data analysis, build robust models, automate tasks with VBA, and create custom solutions.
Q: Who is this course for?
A: This course is designed for users who have a solid foundation in intermediate Excel skills and are looking to become true Excel experts. It’s ideal for professionals who need to work with large datasets, perform in-depth analysis, and develop custom tools.
Q: What are the prerequisites for this course?
A: You should have a strong understanding of intermediate Excel concepts, including advanced functions, PivotTables, data analysis tools, and charting. Some familiarity with basic programming logic or statistics can be beneficial, but check the course description for specifics.
Q: What software do I need?
A: You will need Microsoft Excel installed on your computer. The specific version (e.g., 2016, 2019, 365) might be specified by the training provider.
Q: How is the course structured?
A: The course structure varies. It might involve in-person workshops, live online sessions, self-paced online modules, or a combination. Check the course details.
Q: How long is the course?
A: The duration varies depending on the scope and format. It can range from a few days to several weeks. Check the course details.
Q: Will I receive a certificate upon completion?
A: Yes.
Program Content & Skills:
Q: What specific topics are covered in the course?
A: Typical topics include advanced data analysis (statistical analysis, data modeling, what-if analysis, forecasting), VBA programming (fundamentals, custom functions, automating tasks, user forms), power features (Power Pivot, Power Query, advanced data visualization), integration with other applications, and best practices. Check the syllabus for a detailed list.
Q: Will I learn about VBA in this course?
A: Yes, VBA programming is a core component of most advanced Excel courses.
Q: Will I learn about Power Pivot and Power Query?
A: Many advanced Excel courses will cover Power Pivot and Power Query for working with large datasets and data transformation. Check the syllabus.
Q: Will I work on real-world projects and case studies?
A: A good advanced course will include hands-on projects and case studies to help you apply the concepts.
Submit your interest today !
