Microsoft Office 2013 Excel - Part 2

Microsoft Office 2013 Excel - Part 2


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.

Lesson 01 - Creating Advanced Formulas

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

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

Topic A: Create and Modify Tables

A Sample Table

The Create Table Dialog Box


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

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 Pivot Charts

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

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


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

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

Microsoft Office 2013 Excel - Part 2
tag icon Price $49.99
tag icon Instrutor 1
tag icon Duration 4h 24m
tag icon Lessons 7
tag icon Access 12 Months
Add to Cart