Microsoft Excel 2016, Intermediate

Creating Advanced Formulas

  • Apply Range Names
  • Range Names
  • Adding Range Names Using the Name Box
  • Adding Range Names Using the New Name Dialog Box
  • Editing a Range Name and Deleting a Range Name
  • Using Range Names in Formulas

Use Specialized Functions

  • Function Categories
  • The Excel Function Reference
  • Function Syntax
  • Function Entry Dialog Boxes
  • Using Nested Functions
  • Automatic Workbook Calculations
  • Showing and Hiding Formulas
  • Enabling Iterative Calculations

Analyzing Data with Logical and Lookup Functions

Use Text Functions

  • Text Functions
  • The LEFT and RIGHT Functions
  • The MID Function
  • The LEN Function
  • The TRIM Function
  • The UPPER, LOWER, and PROPER Functions
  • The CONCATENATE Function
  • The TRANSPOSE Function

Use Logical Functions

  • Logical Functions
  • Logical Operators
  • The AND Function
  • The OR Function
  • The IF Function

Use Lookup Functions

  • Lookup Functions
    • The LOOKUP Function
    • The VLOOKUP Function
    • The HLOOKUP Function
  • Use Date Functions
    • The TODAY Function
    • The NOW Function
    • Serializing Dates and Times with Functions
  • Use Financial Functions
    • The IPMT Function
    • The PPMT Function
    • The NPV Function
    • The FV Function

Organizing Worksheet Data with Tables

Create and Modify Tables

  • Tables
  • Table Components
  • The Create Table Dialog Box
  • The Table Tools – Design Contextual Tab
  • Styles and Quick Style Sets
  • Customizing Row Display
  • Table Modification Options

Sort and Filter Data

  • The Difference Between Sorting and Filtering
  • Sorting Data
  • Advanced Filtering
  • Filter Operators
  • Removing Duplicate Values

Use Subtotal and Database Functions to Calculate Data

  • SUBTOTAL Functions
  • The Subtotal Dialog Box
  • Summary Functions in Tables
  • Database Functions

Visualizing Data with Charts

  • Create Charts
  • Charts
  • Chart Types
  • Chart Insertion Methods
  • Resizing and Moving the Chart
  • Adding Additional Data
  • Switching Between Rows and Columns

Modify and Format Charts

  • The Difference Between Modifying and Formatting
    • Chart Elements
    • Minimize Extraneous Chart Elements
    • The Chart Tools Contextual Tabs
    • Formatting the Chart with a Style
    • Adding a Legend to the Chart
  • Create a Trendline
    • Trendlines
    • Types of Trendlines
    • Adding a Trendline
    • The Format Trendline Task Pane
  • Create Advanced Charts
    • Dual Axis Charts
    • Creating Custom Chart Templates

Viewing Chart Animations