Register Now      202-747-5000

Using Advanced Functions and Summarizing Data in Microsoft Excel

365 | 2019 | 2016
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/Investment

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).

Public Course at Softek Private Course at Your Office Private Course at Softek
This class is $500 flat fee up to 8 students $575 flat fee up to 8 students
only offered $550 flat fee up to 12 students $625 flat fee up to 12 students
for private groups. $35 per additional student $35 per additional student

Private Group Training

Private group courses are cost effective when you have a group of students who need the same course. Pricing is based on the maximum number of students anticipated per day per course. Volume discounts of 5% to 20% are available when five or more training days are scheduled. For classes conducted at your office, you are responsible for the classroom setup, including student computers, instructor station, LCD projector, and software installation. For courses conducted at Softek, we take care of classroom setup.

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 $125 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. There is a 2-hour minimum fee for customization.

Why Choose Softek?

  • Live, in-person training
  • Instructors with 10+ years of experience
  • Detailed training guide
  • Certificate of completion
  • Money-back guarantee