Course Overview
This course provides students with the knowledge and skills to advance their data analysis skills
and abilities through the application of advanced Excel 2013 functionality such as advanced
formula creation, tables, charts, PivotTables and PivotCharts.
Course Introduction 1m
Course Introduction
Lesson 01 – Creating Advanced Formulas 30m
Topic A: Apply Range Names
Worksheet without Range Names
Worksheet with Range Names
Excel 2013’s Save Options
Where to Access Saved Versions
Demo 1-1: Adding Range Names
Demo 1-2 & 1-3: Editing and Deleting a Range Name
Demo 1-4: Using Range Names in a Formula
Topic B: Use Specialized Functions
The Function Library
Logical Functions
Statistical Functions
Syntax of the AVERAGE Function
The Insert Function Dialog Box
The Function Arguments Dialog Box
Demo 1-5: Locating Functions by Using the Function Reference
Demo 1-6: Using Functions
Lesson 01 Review
Lesson 02 – Analyzing Data with Logical and Lookup Functions 26m
Topic A: Leverage Questions and Testing to Write Formulas
Topic B: Use Logical and Lookup Functions to Find Answers to Questions
Logical Functions
Demo 2-1: Working with Logical Functions
Demo 2-2: Combining IF and AND Functions
The VLOOKUP Function
The HLOOKUP Function
Demo 2-3: Working with Lookup Functions
Lesson 02 Review
Lesson 03 – Organizing Worksheet Data with Tables 1h 1m
Topic A: Create and Modify Tables
A Sample Table
The Create Table Dialog Box
The TABLE TOOLS DESIGN Contextual Tab
Demo 3-1: Converting a Range to a Table
Topic B: Sort and Filter Data
Data Sorted on a Single Column
Data Sorted on Multiple Columns
Filtered and Sorted Data
Advanced Filter with Three Criteria
Demo 3-2: Sorting Data and Removing Duplicate Records
Demo 3-3: Filtering Records
Topic C: Use Summary and Database Functions to Calculate Data
The SUBTOTAL Function
Demo 3-4: Using Subtotals with Range Data
Summary Functions in Table Total Row: Example 1
Summary Functions in Table Total Row: Example 2
Compare SUM to DSUM
Demo 3-5: Using Summary Functions in Tables
Demo 3-6: Using Database Functions
Lesson 03 Review
Lesson 04 – Visualizing Data with Charts 50m
Topic A: Create Charts
Data in a Table
Data in a Chart
The Insert Chart Dialog Box
Demo 4-1: Creating Charts
Topic B: Modify and Format Charts
Demo 4-2: Modifying Charts
Demo 4-3: Formatting Charts
Topic C: Create a Trendline
Trendlines Forecasting Out Two Years
The Format Trendline Dialog Box
Demo 4-4: Creating a Trendline
Topic D: Create Advanced Charts
A Dual-Axes Chart
Demo 4-5: Creating a Dual-Axes Chart
Lesson 04 Review
Lesson 05 – Analyzing Data with PivotTables, Slicers, and PivotCharts 40m
Topic A: Create a PivotTable
A Standard Table
A PivotTable
The PivotTable Field List Pane
The Summarize Values By Options
Summarizing Values by Using the MIN Function
The Show Values As Options
Showing Values As a Percent of the Grand Total
Combining Summarize and Show (Example 1)
Combining Summarize and Show (Example 2)
Demo 5-1: Creating PivotTables
Topic B: Filter Data by Using Slicers
Slicers and a PivotTable
Demo 5-2: Adding Slicers
Topic C: Analyze Data by Using PivotCharts
Slicers and a PivotChart
Demo 5-3: Creating a PivotChart
Lesson 05 Review
Lesson 06 – Inserting Graphics 27m
Topic A: Insert and Modify Graphic Objects
The INSERT Tab’s Illustration Group
Types of Shapes
Demo 6-1: Inserting Pictures and Clip Art
Topic B: Layer and Group Graphic Objects
Layers
A Hidden Layer
The Same Layers in a New Stacking Order
Three Graphic Objects, No Grouping
Three Graphic Objects in One Group of Three
Demo 6-2: Inserting and Grouping Shapes
Topic C: Incorporate SmartArt
The Choose a SmartArt Graphic Dialog Box
Demo 6-3: Incorporating SmartArt
Lesson 06 Review
Lesson 07 – Enhancing Workbooks 29m
Topic A: Customizing Workbooks
A Comment
Demo 7-1: Inserting Comments and Hyperlinks
Topic B: Manage Themes
Built-in Themes
Demo 7-2: Applying and Editing a Theme
Topic C: Create and Use Templates
An Expense Report Template
Demo 7-3: Creating a Template
Topic D: Protect Files
File Protection Options
The REVIEW Tab’s Changes Group
The Protect Sheet Dialog Box
The Protect Structure and Windows Dialog Box
Demo 7-4: Protecting a Worksheet and a Workbook
Lesson 07 Review
Course Closure
Total Duration: 4h 24m