Topic outline

  • Overview

    Collapse all Expand all

    Module One: Manage Workbook Options and Settings

    • Manage Workbooks
      • Save a workbook as a template
      • Copy macros between workbooks
      • Mange Document Versions
      • Reference data in another workbook
      • Reference data by using structured references
      • Enable macros in a workbook
      • Display hidden ribbon tabs
    • Manage Workbook Review
      • Restrict editing
      • Protect a worksheet
      • Configure formula calculation options
      • Protect workbook structure
      • Mange workbook versions
      • Encrypt workbooks with a password
    Module Two: Apply Custom Data Formats and Layouts
    • Apply Custom Data Formats and Validation
      • Create custom number formats
      • Populate cells by using advanced Fill Series options
      • Configure data validation
    • Apply Advanced Conditional Formatting and Filtering
      • Create custom conditional formatting rules
      • Create conditional formatting rules that use formulas
      • Manage conditional formatting rules
    • Create and Modify Custom Workbook Elements
      • Create custom color formats
      • Create and modify cell types
      • Create and modify custom themes
      • Create and modify simply macros
      • Insert and configure form controls
    • Prepare a Workbook for Internationalization
      • Display data in multiple international formats
      • Apply international currency formats
      • Manage multiple options for +Body and +Heading fonts
    Module Three: Create Advanced Formulas
    • Apply Functions in Formulas
      • Perform logical operations by using AND, OR, and NOT functions
      • Perform logical operations by using nested functions
      • Perform statistical operations by using SUMIFS, AVERAGEIFS, AND COUNTIFS functions
    • Look up data using Functions
      • Look up data by using the VLOOKUP
      • Look up data by using the HLOOKUP function
      • Look up data by using the MATCH function
      • Look up data by using the INDEX function
    • Apply Advanced Date and Time Functions
      • Reference the date and time by using the NOW and TODAY functions
      • Serialize numbers by using date and time functions
    • Perform Data Analysis and Business Intelligence
      • Import, transform, combine, display, and connect to data
      • Consolidate data
      • Perform what-if analysis by using Goal Seek and Scenario Manager
      • Use cube functions to get data out of the Excel data model
      • Calculate data by using financial functions
    • Troubleshoot Formulas
      • Trace precedence and dependence
      • Monitor cells and formulas by using the Watch Window
      • Validate formulas by using error checking values
      • Evaluate formulas
      • Calculate data by using financial functions
    • Define Named Ranges and Objects
      • Name cells
      • Name data ranges
      • Name tables
      • Mange named ranges and objects

    Module Four: Create Advanced Charts and Tables

    • Create Advanced Charts
      • Add trend lines to charts
      • Create dual axis charts
      • Save a chart as a template
    • Create and Manage Pivot Tables
      • Create PivotTables
      • Modify field selections and options
      • Create slicers
      • Group PivotTable data
      • Reference data in a PivotTable by suing the GETPRIVOTDATA function
      • Add calculated fields
      • Format data
    • Create and Manage PivotCharts
      • Create PivotCharts
      • Manipulate options in existing PivotCharts
      • Apply styles to PivotCharts
      • Apply Styles to PivotCharts
      • Manipulate options in existing PivotCharts
      • Apply styles to PivotCharts
      • Drill down into PivotChart details

  • Topic 1