Using Advanced Functions and Summarizing Data in Microsoft Excel

3 Hours

Course Overview

This instructor-led course covers more advanced formulas that include relative and absolute cell references. Incorporate advanced functions such as IF, VLOOKUP, and ISERROR into your formulas. Learn tools, such as the PivotTable, to analyze and summarize your data. It is geared for users who have experience with Microsoft Excel and need to learn these more intermediate/advanced features.

Course Objectives

Create formulas using relative and absolute cell references
Use the IF function to determine a result based on the criteria
Use the VLOOKUP function to retrieve data from a table
Use the ISERROR function to display something other than N/A
Design a PivotTable to analyze and summarize your data

Course Outline

Working with Statistical and Logical Functions

Understanding Absolute vs. Relative Cell References
Using the IF Function to Calculate Based on a Condition
Writing IF Statements
Nesting IF Statements
Using the COUNT Function to Count a List of Numbers
Using the COUNTA Function to Count Non-Empty Cells
Using the COUNTBLANK Function to Count Blank Cells
Using the COUNTIF Function to Count Based on a Condition
Using the COUNTIFS Function to Count Based on Multiple Conditions

Using Text Functions and Features

Using the Text to Columns Feature
Inserting Columns
Splitting Text over Multiple Columns
Using Text Functions
Using the CONCATENATE Function to Join Cells
Using the TRIM Function to Remove Extra Spaces
Using the UPPER, LOWER, and PROPER Functions to Change Case
Using the LEFT, MID, and, RIGHT Functions to Extract Characters
Using the Paste Values Feature

Creating VLOOKUP Formulas

Examining the VLOOKUP Function Syntax
Creating Lookup Tables
Creating Named Ranges
Writing VLOOKUP Formulas
Understanding Common Formula Error
Using the ISERROR Function to Hide Errors

Using PivotTable Reports to Summarize Data

Creating PivotTables to Summarize Data
Changing the PivotTable Field List View
Adding Fields
Grouping Items
Rearranging Fields within the Field List
Using Report Filters
Removing Fields
Changing Value Field Settings
Updating PivotTable with New Source Data

Cost and Delivery Format Options

We offer the above listed class as a private group class in the following delivery formats. Any two 3-hour Microsoft classes must be scheduled on the same day to receive pricing (9:00 am to 12:00 pm and 1:00 pm to 4:00 pm).

Private Group Class: Live Virtual
1-4 students	$850
5-8 students	$1000
9-12 students*	$1100
*$35 per additional student

Private Group Class: At Your Location
1-4 students	$1100
5-8 students	$1250
9-12 students*	$1350
*$35 per additional student

Private Group Training

Private group classes are cost effective when you have a group of students who need the same course. Volume discounts of 5% to 20% are available when five or more training days are scheduled. For classes conducted at your location, you are responsible for the classroom setup which should include:

A computer for each student (with the appropriate software installed), and
An instructor workstation connected to a visual display (e.g., projector, smartboard, etc.).

Customization

Softek can easily customize a training course that covers the specific topics you require and integrate examples relevant to your work environment. Course customization is billed at $150 per hour and covers time needed to meet, discuss, prepare, and incorporate any specific examples into the training program. Once we have a better idea of what you have in mind, we will be glad to provide a specific price quote for the customization.

Choose Your Learning Adventure: