Skip to content
This official Microsoft course trains you in the advanced use of Excel, teaching you how to create, manage, and distribute professional spreadsheets for various purposes. You will learn how to customize the Excel environment depending on the project.
About This Course
This official Microsoft course will provide attendees with the knowledge necessary to develop advanced skills in the creation, management, and distribution of professional spreadsheets for a variety of specialized purposes and situations. Participants will learn how to customize Excel environments to meet specific project needs and improve productivity by demonstrating the correct application of the core Excel features at an expert level and completing tasks independently.
This course is oriented towards preparation for the official Microsoft Office Specialist certification: Excel Expert (Microsoft 365 Apps).
-
Duration
60h (Online)
Objectives
At the end of the course, participants will be able to expertly manage documents in Microsoft Excel, customize them according to specific needs, collaborate efficiently on projects, and use advanced functions to create professional Excel documents.
- During the course, attendees will learn to:
- Manage workbook options and configurations.
- Manage and format data.
- Create advanced formulas and macros.
- Manage advanced charts and tables.
Program
- Module 1: Manage workbooks.
- Copy macros between workbooks.
- Reference data in other workbooks.
- Enable macros in a workbook.
- Manage versions of the workbook.
- Module 2: Prepare workbooks for collaboration.
- Restrict editing.
- Protect spreadsheets and cell ranges.
- Protect the structure of the workbook.
- Configure formula calculation options.
- Module 3: Fill in cells based on existing data.
- Fill cells using Quick Fill.
- Fill cells using advanced Series Fill options.
- Generate numerical data using RANDARRAY().
- Configure formula calculation options.
- Module 4: Format and validate data.
- Create custom number formats.
- Configure data validation.
- Group and ungroup data.
- Calculate data by inserting subtotals and totals.
- Delete duplicate records.
- Module 5: Apply conditional formatting and advanced filtering.
- Create custom conditional formatting rules.
- Create conditional formatting rules that use formulas.
- Manage conditional formatting rules.
- Module 6: Perform logical operations on formulas.
- Perform logical operations using nested functions including the functions IF(), IFS(), SWITCH(), SUMIF(), AVERAGEIF(),COUNTIF(), SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(),MINIFS(), AND(), OR(), NOT() and LET().
- Module 7: Search for data using functions.
- Find data using the XLOOKUP(), VLOOKUP(), HLOOKUP(), MATCH() and INDEX() functions.
- Module 8: Use advanced date and time functions.
- Reference date and time using the NOW() and TODAY() functions.
- Calculate dates using the WEEKDAY() and WORKDAY() functions.
- Module 9: Perform data analysis.
- Summarize data from multiple ranges using the Consolidate function.
- Perform scenario analysis using Goal Seek and Scenario Manager.
- Predict data using the AND(), IF(), and NPER() functions.
- Calculate financial data using the PMT() function.
- Filter data using FILTER().
- Sort data using SORTBY().
- Module 10: Troubleshooting Formulas.
- Track precedence and dependence.
- Monitor cells and formulas using the Observation Window.
- Validate formulas using error checking rules.
- Evaluate formula.
- Module 11: Create and modify simple macros.
- Record simple macros.
- Name simple macros.
- Edit simple macros.
- Module 12: Create and modify advanced graphics.
- Create and modify dual-axis graphics.
- Create and modify charts, including Charts of Box and Mustaches, Combos, Funnel, Histogram, Trend, and Waterfall.
- Module 13: Create and modify Pivot Tables.
- Create Pivot Tables.
- Modify selections and field options.
- Create slicers.
- Group PivotTable data.
- Add calculated fields.
- Configure value field settings.
- Module 14: Create and modify Pivot Charts.
- Create Dynamic Graphics.
- Manipulate options in existing PivotCharts.
- Apply styles to Pivot Charts.
- Delve into the details of Dynamic Charts.
