Excel Level 3

Excel 2019 Level 3 – Advanced

Excel 2019 Level 3 – Advanced. Versions 2013, 2016, 2019, Office 365, & Online are also available. This course covers Microsoft Office Specialist Program exam objectives to help you prepare for the Excel 2019 Level 3.

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
• How to Trace and Fix 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