Mastering Excel® Functions

and Formulas
PARTICIPANT WORKBOOK

www.skillpath.com
All trademarks are the property of their respective owners.
SkillPath claims no ownership interest in the trademarks. © SkillPath®. All rights reserved.
Introduction
Learning to master Excel® provides essential skills that will aid your team’s productivity and provide tools for
effective communication of numbers. You can also use Excel® to track your compliance requirements.

When you understand how to collect and disseminate vast quantities of data with Excel®, your value to your
team grows in quantum leaps.

Properly configured and maintained, Excel® has the capacity to make you more productive and communicate
more effectively because the data that you manage is essential to long-term corporate success.

In short, Excel® is an application used to manage and analyze vast quantities of data — numerical, categorical
and ordinal.

SkillTip: Numerical data has meaning as a measurement and is quantitative. Categorical data identifies
characteristics (qualitative). Ordinal data can be either numerical or categorical but has a special pattern — like
dates or days of the week.

As you’ll see in today’s lesson, it’s not data input (numerical, categorical or ordinal) — it’s about the output your
workbook provides and the knowledge it’s able to transfer.

Speaking of knowledge transfer…we’ve got a lot to cover, so let’s get started!

Course Overview
This multipart course is designed to provide compact explanations and relevant examples of the essential
elements of mastering Microsoft Excel® — a program that enables an array of mathematical and analytical
solutions from simple formulas to complex PivotTable reports that condense thousands of rows of data into
meaningful information.

In this course, you’ll go beyond basic “how-to” material and “step-by-step” instructions to best practices for
designing professional-grade workbooks complete with numbers, text, formulas and functions.

Prerequisites
To fully benefit from this course, it’s important for participants to:

• Be comfortable inputting data and writing simple formulas.

• Understand absolute and relative cell references.
• Be familiar with basic functions SUM, IF, AVERAGE.
• Have built basic PivotTable reports.

v694_200109 Mastering Excel® Functions and Formulas 03

Learning objectives
At the end of this course, you’ll be able to:

• Implement essential shortcuts, conditional formatting, functions and tables to build sheets quickly.
• Design and configure form controls to automate your workbook.
• Utilize data analysis and data mining tools.
• Develop effective charts and graphs.
Notes to readers
Throughout this workbook, you’ll see study aids that will help you master Microsoft Excel®.

• SkillSteps: The fundamental steps to get to a feature

• SkillTips: Special guidelines for becoming a Power User
• Strategies: Techniques for mastering Microsoft Excel®

SkillTip: This workbook was written using Microsoft Excel® for Office 365 MSO Version 1904 Build 11601.20144
Click-to-Run Monthly Channel.

04 Mastering Excel® Functions and Formulas v694_200109

How to Write Formulas With Relative
and Absolute Cell References
The heartbeat of Microsoft Excel® is the formula. A formula calculates values based upon parameters. By default,
Excel® will recalculate formulas as soon as you complete them and press ENTER.

Formulas begin with an “=” symbol and are comprised of numbers, text or functions.

Examples of formulas:

• = 10 + 10 calculates the sum of two numbers

• =A1 + B1 calculates the sum of the values in two cells
• = C1 * .90 calculates the product of the value in a cell and .90
• =Sum(A1:A10) calculates the total of numbers in a range of cells
SkillTip: In general, don’t put values into formulas. Put cell references. Formulas with values are more difficult to
update and maintain than those with cell references.

Strategy: Typing is trouble. When you want to refer to a cell in a formula, select the cell with the mouse rather
than try to type its address.

SkillTip: If you have an existing spreadsheet and wish to display the formulas, use the Show Formulas button in
the Ribbon or press CTRL + `.

Often, you can write a single formula and then copy it to neighboring cells to save the time it would have taken
you to rewrite the formula.

Copying formulas is not always as simple as it seems because sometimes reusing a formula may require relative
cell references other times it might require absolute cell references.

Cell references
There are four types of cell references:

REFERENCE TYPE EXAMPLE

Relative A1

Absolute $A$1

Mixed (Column fixed) $A1

Mixed (Row fixed) A$1

Use these different reference types in the appropriate scenarios.

v694_200109 Mastering Excel® Functions and Formulas 05

SkillTip: While editing a formula, you can modify a cell address from one reference type to another by pressing
F4. (On Excel® for Mac, it’s Command + T.)

Relative cell references

If the formula is written with relative cell references, the cell references will update to reflect the new cell to which
they’ve been copied.

To copy a formula from one cell to another:

• Use Copy and Paste

• Use the Fill handle
• Home > Editing > Fill
In all instances, your objective is to type as little as possible.

Absolute references
Sometimes you don’t want a cell reference to change when you copy a formula. You need the cell addresses to
use absolute references.

Cell references with dollar symbols before the column letter and row number are absolute and will not update
when a formula is copied.

06 Mastering Excel® Functions and Formulas v694_200109

New Topic: Modern Excel®, Dynamic Arrays and Spill Ranges
Modern Excel® or Dynamic-Array Excel® are terms that are being used to describe the newest versions of Excel®
because they support Dynamic Arrays.

What are Dynamic Arrays?

They are a new calculation engine that support simpler formulas that “spill” to remaining cells.

For example, if you wanted to create a sum from two columns, you could write a single formula in a cell that
would “spill” to the remaining rows.

New Topic: Quick Analysis for Formulas

and Crosstab Formulas
Quick analysis
The Quick Analysis Tool (CTRL + Q) provides an easy method to create calculations. The tool gives you some
standard calculations you can apply to a range of cells.

v694_200109 Mastering Excel® Functions and Formulas 07

Other quick methods to build a formula (ALT + =)
In addition to Quick Analysis, you can also build formulas with shortcuts like ALT + =.

In the example, we’ll build a crosstab report with subtotals at the end of each row and at the bottom of
each column.

Best practices when building a quick crosstab report

It’s not a great idea to daisy-chain formulas together. It’s best to calculate totals directly from the source
numbers, not from subtotals.

When you crate a checksum calculation to compare your indirect calculation with a direct one, you can build a
logical formula with the “=” key that return TRUE if the numbers match and FALSE if they don’t.

08 Mastering Excel® Functions and Formulas v694_200109

The Basics of Functions
Excel® provides 248 functions divided into 14 categories that are available via the Formulas tab on the Ribbon.

Function are built-in formulas that carry out set calculations and require you to provide arguments. Arguments
are any input you give to a function (as in a range of cells.) Arguments are always surrounded by parenthesis and
individual arguments are separated by commas.

Functions can be inserted into a formula by typing their name. However, you’ll have to know the name of the
function or research the appropriate one.

Here are some examples of functions:

• SUM: to calculate a total

• NETWORKDAYS: to calculate number of workdays between two dates
• PMT: to calculate a loan payment
• VLOOKUP: to look up a value in a list
• CONCATENATE: to combine text values
• IF: to return a value if true and another if false
• RANDBETWEEN: returns a random number between two numbers
• CELL: returns information including the name of the current file
To learn the names of functions, you can use the Insert Function button on the Formulas tab and search for the
function that matches your need.

v694_200109 Mastering Excel® Functions and Formulas 09

How to calculate days to go with NETWORKDAYS
Do you need to calculate the number of working days until a deadline? Consider NETWORKDAYS.

Why you should build formulas with cell references, rather than fixed values: CONVERT
It’s always easier, as depicted above, to update a worksheet when formulas refer to cells (with relative and
absolute cell references) rather than values. In the above example, you can switch column calculations by
swapping labels, not swapping columns.

Use CONVERT as an example to understand this principle.

As mentioned earlier, try to avoid formulas daisy-chained to other formulas.

10 Mastering Excel® Functions and Formulas v694_200109

The Wonderful SUMPRODUCT Function That Combines
Two Other Functions

Text functions

v694_200109 Mastering Excel® Functions and Formulas 11

Some of the most common and valuable text functions include:
• UPPER • RIGHT
• LOWER • MID
• PROPER • CONCAT
• LEFT
You can use text functions to create calculations based on cells that contain text, change their appearance or
truncate their content.

Flash Fill can make text functions obsolete

Use the new TEXTJOIN to generate a list of emails

12 Mastering Excel® Functions and Formulas v694_200109

The IFs functions

The family of IF functions is based on combinations of values and conditions.

The list of IF functions includes:

• IF • AVERAGEIF • COUNTIFS
• SUMIF • AVERAGEIFS • MAXIFS
• SUMIFS • COUNTIF • MINIFS

They often make it easy to create crosstab reports when using mixed cell references.

The formulas perform calculations conditionally — based on whether a condition is TRUE or FALSE.

v694_200109 Mastering Excel® Functions and Formulas 13

COUNTIFS, SUMIFS, AVERAGEIFS
When you need to calculate sum or count combinations of entries — like names and regions, use the COUNTIFS
function or the SUMIFS function.

Like CONVERT, they can be written with cell references to the top row and the left-most column to calculate the
values that meet those two criteria.

Rather than write elaborate formulas, you can build a single multipurpose formula that can be copied to a cross-
section of cells.

SkillSteps: The arguments for COUNTIFS look like:

=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2] …)

For example, if you need to count the number of employees broken down by department and region, you could
write a formula like: =COUNTIFS($C:$C,$K4,$B:$B,L$3).

If you wanted to total the amount of salary paid to employees broken down by department and region, you
could write a formula like: =SUMIFS($G:$G,$C:$C,$K11,$B:$B,L$10).

SkillSteps: The arguments for SUMIFS look like:

=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SkillTip: When using sheets with only raw data, you can select the entire columns as criteria ranges, rather than
selecting only certain rows.

In addition to these functions, Excel® provides a powerful tool that consolidates large data sets.

14 Mastering Excel® Functions and Formulas v694_200109

How to Use VLOOKUP, INDEX and MATCH
The purpose of VLOOKUP is to match a value in a remote column and return a “looked up” value. For example,
“look up an employee ID number and return their first and last name.”

VLOOKUP requires four arguments of information:

1. What are you looking up?

2. Where are you looking the value up?

3. Which column of the lookup range contains the resulting value?

4. Do you want to allow approximate matches?

The drawbacks to VLOOKUP:

The solution to this inefficiency is to
• The value you are returning MUST be in a divide up the pieces of VLOOKUP into
column to the right of the lookup value. INDEX and MATCH.
• The lookup value must be in the first column of • INDEX defines the range of cells
the lookup range. with the results you need.
• The formula must contain the entire lookup • MATCH identifies the row number
range (and if the range is many columns wide, from the INDEX range that you
this is a large amount of data to store in every specifically need.
cell using the same VLOOKUP formula).

Here is an example of using VLOOKUP and then delivering the same results with INDEX and MATCH.

v694_200109 Mastering Excel® Functions and Formulas 15

SkillTip: As data in a workbook grows, the usage of VLOOKUP can slow down the workbook. If this is the case,
consider replacing VLOOKUPs with INDEX and MATCH.

SkillTip: The consolidation feature matches column and row headers from the supporting sheets to generate
the results. Your ranges need to have consistent headings for this to work.

Once you’ve successfully combined the data, Excel® displays the results in an outline format.

16 Mastering Excel® Functions and Formulas v694_200109

skillpath.com

MASTERING EXCEL® FUNCTIONS AND FORMULAS 1/20