Excel 2019, Level 3 - Advanced
Versions 2013, 2016, 2019, Office 365, & Online are also available.
Course Outline
Lesson 1 - Importing & Exporting Data
- Importing External Data into Excel
- Importing Text Data into Excel
- Converting Text to Columns
- Removing Duplicate Rows of Data
- Importing Data from a Database
- Linking to Another File
- Linking & Embedding Objects
- Exporting Data from Excel
- Publishing Worksheets & Workbooks to the Web
- Creating Web Queries
Lesson 2 - Formatting Numbers
- Creating Custom Number Formats
- Using Conditional Formatting
- Applying Conditional Formatting Based on Top/Bottom Rules
- Applying Specialized Conditional Formatting
- Creating Your Own Formatting Rules
- Managing Conditional Formatting
- Clearing Conditional Formatting
Lesson 3 - Working with Ranges
- Naming a Range
- Using a Named Range
- Managing Range Names
- Using the Vlookup Function
Lesson 4 - Working with Macros
- Creating a Macro
- Running a Macro
- Editing a Macro
- Saving a Workbook with Macros
- Opening a Workbook with Macros
- Adding a Macro to the Quick Access Toolbar
Lesson 5 - Data Analysis Tools
- Tracing Formula Precedents
- Tracing Cell Dependents
- Tracing and Fixing Errors
- Error Checking a Worksheet
- Creating a PivotTable
- Rearranging a PivotTable
- Setting PivotTable Options
- Formatting a PivotTable
- Filtering PivotTable Data with Slicers
- Filtering PivotTable Data Inline
- Creating Custom Filters
- Filtering PivotTable Data Using Timeline
- Creating a PivotTable
Lesson 6 - Summarizing Data
- Adding SubTotals to a List
- Nesting SubTotals
- Applying Advanced Filters
- Adding Group and Outline Criteria to Ranges
- Using Data Validation
- Previewing Data Using Quick Analysis
Lesson 7 - Analyzing Your Data
- Using Goal Seek
- Using Solver
- Creating & Displaying Scenarios
- Using Data Tables
- Forecasting Future Values
Lesson 8 - Workgroup Collaboration
- Locking/Unlocking Cells in a Worksheet
- Protecting a Worksheet
- Showing or Hiding Formulas
- Protecting a Workbook
- Encrypting a Workbook
- Marking a Workbook as Final
- Inspecting Workbooks
- Checking Document Compatibility
- Checking Document Accessibility
- Sharing & Co-Authoring an Excel Workbook