Microsoft Office 2010 Excel - Part 3

Microsoft Office 2010 Excel - Part 3


Course Overview

This course builds on the skills and concepts taught in Excel 2010: Intermediate. Students will work with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and database functions such as DSUM. They will learn how to work with PivotTables and PivotCharts, how to import and export data, and how to query external databases. Finally, students will learn about the analytical features of Excel (such as Goal Seek and Solver), running and recording macros, SmartArt graphics, and conditional formatting with graphics.

Course Introduction 

Course Introduction 

Unit 01 - Logical & Statistical Functions 

Topic A: Logical Functions

The IF Function

Demo - A-1: Using the IF Function

Demo - A-2: Using a Formula to Apply Conditional Formatting

OR, AND, and NOT Functions

Demo - A-3: Using OR, AND, and NOT as Nested Functions

Nested IF Functions

Demo - A-4: Using Nested IF Functions

The IFERROR Function

Demo - A-5: Using the IFERROR Function

Topic B: Math and Statistical Functions

The SUMIF Function

Demo - B-1: Using SUMIF

The COUNTIF Function

Demo - B-2: Using COUNTIF

The AVERAGEIF Function

Demo - B-3: Using AVERAGEIF



The ROUND Function

Evaluate Formula Dialog Box

Demo - B-5: Using ROUND

Unit 01 Review

Unit 02 - Financial & Date Functions 

Topic A: Financial Functions

The PMT Function

Demo - A-1: Using the PMT Function

Topic B: Date and Time Functions

Date Functions

Demo - B-1: Using Date Functions

Calculating Time

Demo - B-2: Using Time Functions

Topic C: Array Formulas

Array Formulas 

Creating an Array Formula

Demo - C-1: Using an Array Formula

Applying Arrays to Functions

Demo - C-2: Applying Arrays to Functions

Modify an Array Formula

Demo - C-3: Modifying the Array Formula

Topic D: Displaying and Printing Formulas

Displaying Formulas in Cells

Hide Formulas from Users

Show Hidden Formulas

Demo - D-1: Showing, Printing, and Hiding Formulas

Automatic Recalculation

Edit Iteration Calculation Options

Demo - D-2: Setting Calculation Options

Unit 02 Review

Unit 03 - Lookups and Data Tables

Topic A: Using Lookup Functions

The HLOOKUP Function

The VLOOKUP Function

Demo - A-1: Examining VLOOKUP

VLOOKUP for Exact Matches

Demo - A-2: Using VLOOKUP to Find an Exact Match

VLOOKUP for Approximate Matches

Demo - A-3: Using VLOOKUP to Find an Approximate Match

HLOOKUP for Exact Matches

Demo - A-4: Using HLOOKUP to Find Exact Matches

HLOOKUP for Approximate Matches

Demo - A-5: Using HLOOKUP to Find Approximate Matches

Topic B: Using MATCH and INDEX

The MATCH Function

Demo - B-1: Using the MATCH Function

The INDEX Function

Demo - B-2: Using the INDEX Function

Topic C: Creating Data Tables

One-Variable Data Tables

Demo - C-1: Creating a One-variable Data Table

Two-Variable Data Tables

Demo - C-2: Creating a Two-variable Data Table

Unit 03 Review

Unit 04 - Advanced Data Management

Topic A: Validating Cell Entries

Data Validation

Demo - A-1: Observing Data Validation

Setting Data Validation Rules

Demo - A-2: Setting Up Data Validation

Using Date Criteria

Demo - A-3: Setting Date and List Validations

Topic B: Exploring Database Functions

Structure of Database Functions

Demo - B-1: Examining the Structure of Database Functions


Demo - B-2: Using the DSUM Function 

Unit 04 Review

Unit 05 - Exporting and Importing

Topic A: Exporting and Importing Text Files

Using the Save As Command

Demo - A-1: Exporting Excel Data to a Text File

Importing Data

Demo - A-2: Importing Data from a Text File into a Workbook

The Text Import Wizard

Converting Text to Columns

Demo - A-3: Converting Text to Columns

Removing Duplicates

Demo - A-4: Removing Duplicate Records

Topic B: Exporting and Importing XML Data

The XML Maps Dialog Box

The XML Source Pane

Importing XML Data

Exporting Data to an XML File

Deleting XML Maps

Topic C: Querying External Databases

Using Microsoft Query

Demo - C-1: Getting External Data from Microsoft Query

Web Query

Retrieving Data from a Web Page

Demo - C-2: Using a Web Query to Get Data from the Web

Unit 05 Review

Unit 06 - Analytical Tools

Topic A: Goal Seek and Solver

Using the Goal Seek Utility

Demo - A-1: Using Goal Seek to Solve for a Single Variable

Activating Add-Ins

The Add-Ins Dialog Box

The Solver Parameters Dialog Box

Demo - A-3: Using Solver to Solve for Multiple Variables

Topic B: The Analysis ToolPak

Analysis ToolPak

Using the Sampling Analysis Tool

Demo - B-1: Using the Sampling Analysis Tool

Topic C: Scenarios

Creating a Scenario

Demo - C-1: Creating Scenarios

Add a Scenario Manager Button

Merging Scenario

A Sample Scenario Summary

Unit 06 Review

Unit 07 - Macros and Custom Functions

Topic A: Running and Recording a Macro

Running Macros

Demo - A-1: Running a Macro 

Recording Macros

Demo - A-2: Recording a Macro

Assigning Macros to Buttons

Demo - A-3: Assigning a Macro to a Button

Add Macro Buttons to the Ribbon

Insert a Button

Change Button Properties

Demo - A-4: Inserting a Macro Button

Create Auto_Open Macro

Demo - A-5: Creating an Auto_Open Macro

Topic B: Working with VBA Code

VBA Code

Observing VBA Code

Demo - B-1: Observing a VBA Code Module

Example of Editing VBA Code

Demo - B-2: Editing VBA Code

Topic C: Creating Functions

Function Procedures

Creating a Custom Function

Demo - C-1: Creating a Custom Function

Unit 07 Review

Microsoft Office 2010 Excel - Part 3
tag icon Price $49.99
tag icon Instrutor 2
tag icon Duration 3h 20m
tag icon Lessons 7
tag icon Access 12 Months
Add to Cart