Register Now      202-747-5000
Road Map To Learning

Choose Your Learning Adventure:

Select from our standard “off-the-shelf” classes or build a course to target your learning goals. Ready to learn new skills? Contact Softek today!

Using Advanced Functions and Summarizing Data in Microsoft Excel

3 Hours

Course Overview

This instructor-led course covers more advanced formulas, including relative and absolute cell references. Students will learn how to use statistical, logical, and text functions. Additionally, they will learn how PivotTables can be used to analyze and summarize data. This course is geared towards users who have experience with Microsoft Excel and need to learn these 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 SWITCH function to display results
  • Use the TEXTJOIN function to join cells
  • 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
  • Using the IFS Function for More Than Two Outcomes
  • Using the SWITCH Function to Display Results
  • Comparing the IFS and SWITCH Functions
  • 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 the TEXTJOIN Function to Join Cells
  • Accelerating Data Insertion with Flash Fill
  • Using the LEFT, MID, and RIGHT Functions to Extract Characters
  • Using the Paste Values Feature

Using PivotTable Reports to Summarize Data

  • Changing the PivotTable Field List View
  • Adding Fields
  • Grouping Data
  • 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.).


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.

Why Choose Softek?

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