Course Overview
This course teaches students about working with functions, working with lists, analyzing
data, visualizing data with charts, and using PivotTables and PivotCharts.
Course Introduction 3m
Course Introduction
Chapter 01 – Working with Functions 1h 13m
Topic A: Work with Ranges
Cell and Range Names
Names and the Name Box
The New Name Dialog Box
The Create from Selection Command
The Name Manager Dialog Box
Demo – Naming and Editing Ranges
Cell and Range Names in Formulas
The Use in Formula Command Method
The Formula AutoComplete Method
Demo – Using Defined Names in a Formula
Topic B: Use Specialized Functions
The Excel Function Reference
Function Syntax
Date Calculations
Demo – Locating and Using Specialized Functions
Topic C: Work with Logical Functions
Comparison Operators
The IF Function
The AND Function
The OR Function
The NOT Function
Demo – Working with Logical Functions
Nesting
Nested Function Syntax
Demo – Combining Functions
Topic D: Work with Date & Time Functions
The TODAY Function
The NOW Function
The DATE Function
The NETWORKDAYS Function
The WEEKDAY Function
The WORKDAY Function
The ISOWEEKNUM Function
Demo – Work with Date & Time Functions
Topic E: Work with Text Functions
The LEFT Function
The RIGHT Function
Convert Text to Columns Wizard
The MID Function
The CONCATENATE Function
Text Concatenation with the Ampersand
The UPPER Function
The LOWER Function
The PROPER Function
Demo – Working with Text Functions
Chapter 01 Review
Chapter 02 – Working with Lists 38m
Topic A: Sort Data
Sorting
Multiple Column/Row Sorting
Quick Sorts
The Sort Dialog Box
The Sort Options Dialog Box
Demo – Sorting Data
Topic B: Filter Data
Filtering
AutoFilters
The Custom AutoFilter Dialog Box
Advanced Filtering
The Criteria Range
Demo – Filtering Data
Topic C: Query Data with Database Functions
Database Functions
Database Function Syntax
Demo – Using Database Functions
Topic D: Outline and Subtotal Data
Outlines
The SUBTOTAL Function
The Subtotals Feature
The Subtotal Dialog Box
Demo – Using Subtotals to Summarize Data
Chapter 02 Review
Chapter 03 – Analyzing Data 28m
Topic A: Create and Modify Tables
Tables
Table Components
The Create Table Dialog Box
The Table Tools Design Contextual Tab
Table Styles and Quick Styles
The New Table Style Dialog Box
Quick Analysis
Demo – Creating and Modifying Tables
Demo – Using Summary Functions in Tables
Topic B: Apply Intermediate Conditional Formatting
The New Formatting Rule Dialog Box
The Conditional Formatting Rules Manager Dialog Box
Rule Precedence
Demo – Applying Intermediate Conditional Formatting
Topic C: Apply Advanced Conditional Formatting
The Use a Formula to Determine Which Cells to Format Rule
Demo – Using Logical Functions to Apply Conditional Formatting
Chapter 03 Review
Chapter 04 – Visualizing Data with Charts 38m
Topic A: Create Charts
Charts
Chart Basics
Chart Types
Recommended Charts
The Insert Chart Dialog Box
Demo – Creating Charts
Topic B: Modify and Format Charts
Chart Modifications
Chart Formatting
Chart Elements
The Design Tab
The Format Tab
The Format Task Pane
The Chart Tools Buttons
The Select Data Source Dialog Box
Demo – Modifying and Formatting Charts
Topic C: Use Advanced Chart Features
Dual-Axis Charts
Trendlines
The Format Trendline Task Pane
Demo – Creating a Dual-Axis Chart
Chart Templates
Demo – Creating a Chart Template
Chapter 04 Review
Chapter 05 – Using PivotTables and PivotCharts 45m
Topic A: Create a PivotTable
Pivoting
PivotTables
Transactional Data
The Create PivotTable Dialog Box
The PivotTable Fields Task Pane
Demo – Creating a PivotTable
Topic B: Analyze PivotTable Data
The Analyze Tab
The Design Tab
The Value Field Settings Dialog Box
SUM Function Only
Percentage of Total
Difference From Option
The GETPIVOTDATA Function
Demo – Analyzing PivotTable Data
Topic C: Present Data with PivotCharts
PivotCharts
The PivotChart Fields Task Pane
PivotChart Filters
Demo – Presenting Data with PivotCharts
Topic D: Filter Data by Using Timelines and Slicers
Slicers
The Insert Slicers Dialog Box
The Slicer Tools Contextual Tab
The Report Connections Dialog Box
Timelines
The Insert Timelines Dialog Box
The Timeline Tools Contextual Tab
Demo – Filtering Data by Using Slicers and Timelines
Chapter 05 Review
Course Closure
Total Duration: 3h 45m