Excel Advanced Training
1 day (10:00 AM - 5:00 PM Eastern)
$250.00
Register for a live online class.
Details
Subjects Covered
Prerequisites
Setup Requirements
Details
Course Details
In this course, students will further build on the skills acquired in the Microsoft Excel Introduction and Intermediate courses. They will work with advanced functions and formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and will use advanced data filtering. They will apply advanced chart formatting options, and create more complex charts. They will work with PivotTables and PivotCharts, export and import data, and query external databases. Students will learn about the analytical features of Excel, such as Goal Seek, and how to create scenarios. Finally, they will run and record macros, and explore VBA code.
This course will help students prepare for both the Microsoft Office Specialist core-level exam and the Microsoft Office Specialist Expert exam for Excel. For comprehensive certification training, students should complete the Introduction, Intermediate, and Advanced courses for Excel.
Subjects Covered
Excel Advanced (2013, 2016, 2019, Office 365)
- Cell and Range Names
- Creating and Navigating to a Range Name
- Creating from Selection
- Applying Names
- Managing Names
- Using the Name Manager
- Advanced Functions
- Using Functions
- Nesting Functions
- Using Conditional Functions
- SUMIFS, COUNTIFS, and AVERAGEIFS
- Date and Time Functions
- The NOW and TODAY Functions
- The YEAR Function
- The DAYS360 Function
- The NETWORKDAYS Function
- The DATE Function
- Lookup Functions
- The HLOOKUP Function
- The VLOOKUP Function
- Financial Functions
- The PMT Function
- Advanced Data Management
- Validating Cell Entries
- Date Criteria in Data Validation Rules
- List Criteria in Data Validation Rules
- Validating Data
- Advanced Filtering
- Creating a Criteria Range
- Multiple Workbooks
- Using Multiple Workbooks
- Moving or Copying Worksheets Between Workbooks
- Arranging Multiple Windows
- Linking Workbooks
- Maintaining Workbook Links
- PivotTables and PivotCharts
- Working with PivotTables
- Adding Fields
- Using Fields to Filter Data
- Inserting Slicers
- Using Slicers to Filter Data
- Modifying Slicers
- Grouping PivotTable Data
- Moving and Refreshing Fields
- Inserting Calculated Fields
- Formatting PivotTables
- Conditional Formatting a PivotTables
- Using PivotCharts
- Analytical Tools
- Goal Seek
- Scenarios
- Scenario Summary Report
- Merging Scenarios
- Macros and Visual Basic
- Understanding Macros
- Changing Trust Center Macro Settings
- Creating Macros
- Recording Macros
- Saving Files with Macros
- Macro Buttons
- Working with VBA Code
- Copying Macros Between Workbooks
- Templates and Settings
- Application Settings
- Customizing the Ribbon
- Creating Custom Templates
- Protecting a Worksheet
- Protecting Parts of a Worksheet
- Protecting a Workbook
- Protecting Worksheets using Digital Signatures
- Sharing and Security
- Sharing a Workbook
- Merging Workbooks
- Tracking Changes
- Reviewing Workbook Changes
- Using Document Inspector
- Finalizing a Workbook
- Advanced Charting
- Changing Chart Scale
- Chart Formatting Options
- Formatting Data Points
- Creating Combination Charts
- Adding Trendlines
Prerequisites
Before Taking this Class
Excel Intermediate (2013, 2016, 2019, Office 365)Setup Requirements
Software/Setup For this Class
Microsoft Office 2013 or higher (2013, 2016, 2019, or Office 365)
Onsite Training
Do you have five (5) or more people needing this class and want us to deliver it at your location?