Excel Level 2

Learning Objectives

In this course, you will create advanced formulas, utilize and maintain database records, utilize Named Ranges in your formulas and visualize your data in Charts. You will:

• Enter, edit, and format data to create workbooks

• Work with Named Ranges

• Use IF, OR, PMT, VLOOKUP and XLOOKUP functions

• Display relationships between formulas and cells using formula auditing tools

• Sort and filter data

• Organize worksheet data with tables

• Visualize data with charts

Course Outline

Section 1: Working with Range Names

• What are Range Names?

• Apply Range Names using the Name Box

• Editing Range Names

• Using Range Names in Formulas

Section 2: Using Specialized Functions

• Function Categories

• The Excel Function Reference

• Function Syntax

• Function Entry Dialog Boxes

• Using Nested Functions

• Automatic Workbook Calculations

Section 3: Analyzing Data with Logical and Lookup Functions 

• The IF Function

• The AND Function

• The OR Function

• The LOOKUP Function

• The VLOOKUP and HLOOKUP Function

• The XLOOKUP Function

Section 4: Using Financial Functions

• The PMT Function

• The FV Function

Section 5: Use Date Functions

• The TODAY Function

• The NOW Function

• Serialize dates and times with functions

Section 6: Auditing Worksheets

• The Trace Cells Feature

• Show and Hide Formulas

• Resolve Errors in Formulas

• Using the Watch Window 

Section 7: Organizing Worksheet Data with Tables

• What Are Tables?

• Table Components

• The Create Table Dialog Box

• The Table Tools – Design Contextual Tab

• Table Styles

• Customize Row Display

• Table Modification Options

Section 8: Sorting and Filtering Data

• The Difference Between Sorting and Filtering

• Sorting Data

• Filtering Data with Autofilters

• Creating Advanced Filters

• Filter Operators

• Removing Duplicate Values

Section 9: Using Subtotal and Database Functions to Calculate Data

• SUBTOTAL Functions

• The Subtotal Dialog Box

Section 10: Visualizing Data with Charts

Section 10.1: Inserting Charts 

• Chart Types

• Create Charts

• Chart Insertion Methods

• Resizing and Moving the Chart

• Adding Additional Data

• Switching Between Rows and Columns

Section 10.2: Modifying and Formatting Charts

• Chart Elements

• The Chart Tools Contextual Tabs

• Format the Chart with a Style

• Add a Legend to the Chart

• Add Data Labels to Charts

• Create Custom Chart Templates

Section 11: Managing Themes

• About Themes

• Customize Themes

Section 12: Adding Trendlines

• Types of Trendlines

• Add a Trendline

• Format a Trendline

Section 13: Create Sparklines

• What is a Sparkline?

• Types of Sparklines

• The Sparkline Tools – Design Tab