Book Description

Hated math in school, but know that it must have its uses?
Daunted by the idea of calculations? Or simply sick and tired
of the mundane sorting of information you’re asked to do in
your job?
You need Excel Formulas and Functions 2021—The
Intermediate Level!
Discover:
● A quick overview of the basic structures of Microsoft
Excel
● How to format and present your information in eye-
catching ways
● The key functions to help you avoid complex
calculations
And much, much more!
With the most up-to-date information on Microsoft Excel,
Excel Formulas and Functions 2021—The Intermediate Level
can guide you from disaster to master in a matter of days. So,
why wait any longer? Take this opportunity to improve your
productivity, efficiency, and financial understanding.
Learn:
● How and why to visualize your data
● Easy shortcuts for a household budget
● The way to determine if a loan is affordable
… along with many more tips and tricks to make your Excel
learning journey a quickly progressing pleasure, not a
torturous purgatory.
With step-by-step worked examples included to guide your
way and sample exercises with solutions to get hands-on
practice, this book is the complete package to have you
excelling with Excel in no time.
Excel Formulas and
Functions 2021—The
Intermediate Level

Step-by-Step Guide with

Examples for Intermediate
Level
Adam Ramirez
© Copyright 2021 - All rights reserved.
The content contained within this book may not be
reproduced, duplicated or transmitted without direct written
permission from the author or the publisher.
Under no circumstances will any blame or legal responsibility
be held against the publisher, or author, for any damages,
reparation, or monetary loss due to the information contained
within this book, either directly or indirectly.
Legal Notice:
This book is copyright protected. It is only for personal use.
You cannot amend, distribute, sell, use, quote or paraphrase
any part, or the content within this book, without the consent
of the author or publisher.
Disclaimer Notice:
Please note the information contained within this document is
for educational and entertainment purposes only. All effort has
been executed to present accurate, up to date, reliable,
complete information. No warranties of any kind are declared
or implied. Readers acknowledge that the author is not
engaged in the rendering of legal, financial, medical or
professional advice. The content within this book has been
derived from various sources. Please consult a licensed
professional before attempting any techniques outlined in this
book.
By reading this document, the reader agrees that under no
circumstances is the author responsible for any losses, direct
or indirect, that are incurred as a result of the use of the
information contained within this document, including, but not
limited to, errors, omissions, or inaccuracies.
Table of Contents
Introduction
Chapter 1: Refreshing the Basics
Workbooks
Worksheets
Rows
Columns
Cells
Ranges
Lists
Tables
Formulas
Functions
Names
Chapter 2: Data Formatting
Inserting Rows and Columns
Changing Row and Column Settings
Working With Specific Data Types
Hiding and Unhiding Data
Worksheet and Cell Protection
Summary
Chapter 3: Database Utilization
DATE
Dragging and Copying
EXACT
Filtering
FIND
HYPERLINK
IF
LOOKUP
LOWER
MATCH
PROPER
Sorting
SUBSTITUTE
UPPER
Summary
Chapter 4: Data Visualization
Pie Charts
Column and Bar Charts
Line Charts
Embedding Charts
Summary
Chapter 5: Pivot Tables
Pivot Charts
Summary
Chapter 6: Mathematical Functions
ABS
AVERAGEIF
AVERAGEIFS
EVEN
FACT
INT
MOD
POWER
PRODUCT
QUOTIENT
ROUND
SUMIF
SUMIFS
Summary
Chapter 7: Statistical Functions
COUNT
COUNTA
COUNTIF
COUNTIFS
CONFIDENCE
CORREL
FORECAST.LINEAR
MEDIAN
MIN
MODE.SNGL
STDEV.S
VAR
Summary
Chapter 8: Financial Functions
CUMIPMT
DB
FV
PMT
PV
RATE
Summary
Chapter 9: Combining Functions
Nested IF Functions
IFERROR
Summary
Chapter 10: Review
Exercise 1
Exercise 2
Exercise 3
Possible Solutions
Exercise 1
Exercise 2
Exercise 3
Summary
Conclusion
References
Introduction
Microsoft Excel is an incredibly versatile piece of software.
From tabulating customer data to complex engineering
equations, the uses of Excel have grown far and wide since its
initial development way back in 1987. In today’s IT-centric
business environment, a foundational knowledge of Excel is
becoming more and more crucial, and it is my goal with this
series of books to make gaining that knowledge a little bit
easier.
Though Excel tends to be associated with the financial sector,
there’s no need to have a master’s degree in accounting to
make use of its many capacities and benefits. In fact, all you
need is access to the software! With a few simple tools under
your belt, Microsoft Excel can be of use to many working
professionals, and indeed to people from all walks of life. It
can help you to arrange your personal finances—creating
household budgets, meal plans, shopping lists, and so on—or
it can help you to compile huge swathes of information such as
mailing lists for a business or charity. With over 400 built-in
functions to hand, Excel can even be the basis of intricate
scientific data analysis and weather forecasting. Best of all,
there’s not even a need to memorize any complicated
equations; the program itself does all of the heavy lifting.
Because of this, you don’t need to go through this book with a
pen and paper, studying each function, making flashcards to
test yourself, and so on. Excel will prompt you to enter the
necessary information every time you choose to use a
function. Your main concern is simply to become comfortable
in your use of the program and to understand what functions
and formulas can achieve and how they might be used.
Before learning how to use the functions of Excel, a
spreadsheet can feel like a useless compilation of numbers.
That misconception is, unfortunately, all too regular. The
common parlance surrounding Excel can also feel incredibly
intimidating, and many of us never venture beyond finding the
sum of a few numbers, or perhaps multiplying and dividing if
we’re feeling extra adventurous. This book is here to help you
to realize that Microsoft Excel is actually remarkably user-
friendly; you just have to have the confidence to use it!
Throughout this book, you will be guided through an
intermediate-level overview of the many functions and
formulas available to you through the use of Excel, why they
are so useful, and how exactly you can use them for yourself.
It is worth noting that this book is the second in my series of
Microsoft Excel-related materials, and therefore will assume
some basic knowledge of the program and its uses. However,
for your benefit, Chapter 1 of this volume includes a brief
refresher of these basics, both to review what has previously
been covered and to give new readers an idea of the level of
pre-existing know-how needed to embark on this intermediate
learning journey. Excel Formulas and Functions 2020 Basics
—Step-by-Step Guide with Examples for Beginners focused on
familiarization with the Excel program and navigation of the
user interface without getting into too much detail about how
to actually enter and use data within Excel. This book, on the
other hand, will dive into the nitty-gritty of using Excel in a
practical manner. This may seem like a lot of technical
mumbo-jumbo to begin with, but bear with me. The rewards
that you can reap with this knowledge make it worth plowing
through the difficult sections. With the foundational navigation
and vocabulary review in the first chapter behind you, you will
be more than capable of tackling everything you need within
these pages.
From this summarizing jumping-off point, we will delve into
how to format your data in the most effective way for the
purposes of your entry, and how to utilize, visualize, and
analyze this data as needed. Each of these topics will include
step-by-step explanations of how to implement each Excel
function. I highly recommend that you open up your Microsoft
Excel program and follow along with the uses of the functions
as you read. This book is not meant to be read from cover to
cover, all in one go, like a novel. It is a guidebook, meant to
map out each step of a journey that you take for yourself.
Though we won’t be going through questions and answers in a
hands-on way as you are introduced to these functions and
formulas, you will have the opportunity to practice your skills
in the final chapter of this book. Therefore, familiarizing
yourself with the use of each function covered throughout
these pages is the best way to ensure that you can carry this
knowledge with you as you move forward to reviewing your
skills and, eventually, using Excel for your own purposes.
You will learn about how to format an Excel worksheet for
specific data types, how to utilize database and reference
functions such as LOOKUP and IF, the method of transferring
data into various forms of charts and graphs, and the purpose
and creation of pivot tables, along with why each of these
tasks are useful and can increase your productivity in the
workplace. Furthermore, we will delve into the mathematical,
statistical, and financial functions embedded in the Excel
program. We will also take a look at how these many functions
can be combined within one another to achieve almost any
calculation you can imagine.
Finally, Chapter 10 will help you to put all of this theory into
practice by giving you an opportunity to apply this medley of
knowledge and skills in your own Excel spreadsheets. Here,
we will take a more participatory approach, and you will be
given a few sample situations where you can create your own
spreadsheet and analyze the data using the functions from the
previous chapters. I have, of course, also provided screenshots
of the ideal final worksheets to allow you to compare and
contrast with your own work. This practical opportunity to use
the information contained within this book will help to solidify
your learning and can be used as a refresher course when you
want to review all that you have learned. But, please don’t let
this chapter be the end of your journey with Excel. Put your
knowledge to good use!
Visualizing and quantifying data has become a vital skill in so
many lines of work, and this brief intermediate course will
bring you to a level of understanding that will enable most
day-to-day uses of Excel. You needn’t be an accountant,
analyst, or IT professional to make the most of the program.
Teachers can use Excel to monitor student grades or graph
information, making it more accessible to visual learners.
Sales representatives can make use of the statistical functions
to predict their future commission earnings. Freelancers can
keep track of costs and create invoices. Homemakers can trace
patterns in household expenditure, manage chore charts for
children, or monitor pantry and spice rack inventory. At a
personal level, Microsoft Excel can even help you to
determine your progress towards set goals, for instance in
fitness and nutrition. Regardless of your personal
circumstances, you can be sure that Excel can help you to
improve your understanding of trends and increase your
productivity. Those benefits don’t end with the conclusion of
this book.
Microsoft Excel can help you to achieve your goals
effectively, efficiently, and economically, and the more you
learn about this multifaceted program, the more that you can
make use of its many benefits. Tailor what you learn here to
your own life. Make the program work for you. Embrace
Excel as part of your life and let it do the grunt work of
tracking trends and completing calculations. You deserve to be
allowed and enabled to apply your energies where they are
most needed, and Microsoft Excel is the program to get you to
that point. As you will see throughout this book, that is the
beauty of Excel. It can be wholeheartedly adapted to your own
purposes and needs. Once equipped with a little know-how to
do so, the world—and Microsoft Excel—are your oyster. So,
let’s launch into that learning, shall we?
Chapter 1: Refreshing the Basics
Before diving into any of the complexities of formulas and
functions, it’s important that you get to grips with the Excel
work environment and the terminology involved in the use of
this program. These basics will lay the foundations for our
further learning in later chapters, so it’s vital that you
familiarize yourself with these concepts before progressing
further. If you are used to working with Excel or have just
finished reading Excel Formulas and Functions 2020 Basics,
this may all be old news. However, I do recommend at the
very least skim-reading this chapter to refresh your memory
before continuing with the rest of the book.
These fundamental aspects will be mentioned umpteen times
throughout the course of this book, and without a firm
foundation of these basics, you are sure to be lost. So, take
your time. Open a Microsoft Excel window while you read
through this chapter and ensure that you can recognize the
element to which each term refers. Take the opportunity to
apply the more practical elements once before continuing to
the following chapter. It will make your entire learning
journey much easier if you do so.

Workbooks
Within the Excel program, a workbook is your base
spreadsheet file. Similar to a document on Microsoft Word, the
workbook is the home of any given set of data within Excel.
Generally, we begin with a blank workbook, opened from the
home page of Excel. However, it is worth noting that
templates do exist for such things as invoices, timesheets, to-
do lists, and so on. These can be accessed below the option to
create a blank workbook, by clicking “File,” “New,” and
entering your desired topic in the search bar labeled “Search
for Online Templates.”

Worksheets
A worksheet is what is commonly referred to as a
“spreadsheet.” This is the area within which you actually enter
data within your workbook. Many worksheets can exist within
a single workbook file, but you can only have a single
worksheet active at a time within any one workbook.
Worksheets can be toggled between by navigating to the tabs
at the bottom left of the screen, or by using CTRL + PG UP to
activate the previous sheet and CTRL + PG DN to move to the
following sheet.

Rows
Rows run horizontally across the worksheet. They are
numbered down the side of the sheet, running from one to a
very high number.

Columns
Columns are the vertical counterpart of rows. They are
identified using letters of the alphabet running across the top
of the worksheet.

Cells
Cells are the specific areas in which you enter data. These are
the little rectangles you see throughout your worksheet. Each
cell is identified according to a combination of a row and a
column and, in order to use the functions and formulas
throughout this book, you will need to be comfortable with
identifying these cells by name. For instance, cell A1 is the
leftmost and uppermost cell in any worksheet. Cell C3 will be
in the third column and the third row.

Ranges
A range is any identified collection of cells. These are
identified using a colon (:). For instance, the range A1:E1 will
identify the five cells in row 1 from column A to column E.
Likewise, the range A1:C3 will demarcate a box of nine cells,
including the cells which are in the first three rows and the
first three columns.

Lists
A list is the set of data stored within a range of cells. In a list,
the first row and first column are usually designated to
descriptive labels, identifying the type of data contained in
each section. For instance, in the example below, the first
column is dedicated to classifying the month from which each
row of data arises, while the first row categorizes the type of
financial information contained within each column.

Tables
Tables are converted lists which contain a heading title and
include only a specific range of information. They are
automatically formatted and include a variety of tools only
available when working within a table. Tables also tend to be
the basis for creating charts and graphs, as you will see in
Chapter 4: Data Visualization.

Formulas
A formula is any sequence within a cell that is used to produce
a specific value. This value might be a reference to another
cell, a mathematical equation, or any other expression used to
automatically fill the cell. Formulas always begin with an
equal sign (=) and can be typed either directly into the cell
itself or into the formula bar located between the user interface
ribbon and the worksheet; the formula bar is preceded by a
box including the formula identifier “fx“.

Following the necessary equal sign, formulas are created using

five major components:
● One or more cell reference(s): The letter-number
combination(s) used to identify specific cells.
● Operators: The symbols and signs used to denote what
calculation should be done between the various cells
referenced. These include mathematical operators such as
+, -, *, and /, as well as reference operators such as the
colon (:) used to indicate a range of cells and commas (,)
used to separate multiple ranges—e.g. “A1:A3, C1:C3”
would indicate the first three cells in column A and the
first three cells in column C, but exclude column B
between them.
● Text or values: Directly typed in strings of information
surrounded by quotation marks (“…”).
● Functions: Explained below.
● Parentheses: Also known as brackets, these punctuation
marks are necessary to determine the order in which
calculations are completed, just as in mathematical
equations. Parentheses also allow functions to determine
the relevant information which they must take into
account as explained below.

Functions
Functions are formulas which are built into the Excel program.
These can be used to avoid typing out a long equation. For
instance, rather than finding the total of a group of cells by
typing “=A1+A2+A3+A4+A5+A6+A7+A8” the SUM
function allows us to simply write “=SUM(A1:A8).” It is vital
that each function starts with an equals sign to denote the
beginning of a calculation or operation. Parentheses must also
be used to open and close the cell range associated with each
function. Without these parentheses to identify the range
involved in each function, it is likely that you will receive a
cell error code.
Likewise, it is necessary that all pieces of information—
whether this be numbers, text, cell references, or something
else—included in a function are adequately separated and
indicated as discrete pieces of data.
For example:
=SUM(A1 A2 A3)
This is not a valid function. Excel will read all three cell
references as part of a single piece of information—also
known as an “argument.”
Instead, these references should be separated by commas, such
as in:
=SUM(A1, A2, A3)
Here, the function can operate effectively, knowing that it
needs to add the numerical information contained in the cells
A1, A2, and A3.

Names
Names are often given to entire rows or columns in Excel
worksheets as a means to identify the purpose of the data
contained within them. For example, a business’s sales and
sales returns book will need to include transaction dates,
account reference numbers, a narrative regarding each
transaction, gross totals, sales tax, delivery charges, etc. Each
of these groupings of data can become a named column within
an Excel worksheet. To name a column, simply select its letter
identifier at the top of the worksheet, and use the name box—
located at the bottom of the toolbar and to the left of the
formula bar—to type a column name. Press “Enter” (↲) on
your keyboard to save this newly typed name. This technique
can also be used to name a row by first selecting its numerical
identifier from the left of the worksheet before typing in the
name box.
Names are useful because they can be included in formulas.
For instance, SUM(Sales) would find the total of the
numerical data in the column or row named “Sales.”
Chapter 2: Data Formatting
Learning to format the data that you enter is a key aspect of
getting the most from your use of Microsoft Excel. Not only
will this allow you to present your information in an easily
understandable way, it will help you to protect crucial
information included in your workbook. By standardizing how
you present your information, in terms of headings, font sizes,
row and column widths, borders, background colors, and so
on, the data included becomes much more accessible to
readers other than yourself. This is particularly useful if you
are using Excel to present information to family members or a
supervisor at work.
For instance, cells that deal with financial information can be
formatted to automatically include a currency symbol and
round to two decimal places, which can help to present sales
data in an easily understandable way without you needing to
manually enter “$” or “€” in each individual cell. Likewise,
your worksheet can be formatted to present negative values in
red so that profits and losses can be distinguished at a glance.
No matter how you personally use Excel, formatting can help
you to make the most of the data that you enter in an easy and
effective way.

Inserting Rows and Columns

Inserting a row or column into an Excel worksheet can be a
particularly useful skill. If you realize that you have forgotten
a type of information that ought to be included within a data
set, being able to insert a row or column enables you to slot
data into the middle of a worksheet without needing to
manually move the information around it.
Some important knowledge to keep in mind is that a new row
will be inserted above the row of the currently selected cell. In
the case of a new column, it inserts to the left. These positions
will determine where you need to click in order to place your
new row or column as you wish.
Once you have decided where to place your new row or
column, there are then several ways to complete the insertion.
The quickest—though not necessarily most straightforward—
of these options is to use the keyboard shortcut CTRL + +.
Select the row below where you would like your new row or
the column to the right of your new column location, and then
press these two keys on your keyboard simultaneously to
insert the new row or column.
You also have the—potentially more user friendly—option of
using the insert tab located on the right of the toolbar of Excel,
or right-clicking any cell and using the insert option in the
drop-down menu. This will open a menu from which you can
insert an entire row or column, as shown below.
Changing Row and Column Settings
Once you have the desired number of rows and columns
inserted into your worksheet, you may find that their
formatting does not suit your purposes. For instance, if you
will be entering long sentences of text—such as customers’
full addresses—into a given column, that column may need to
be significantly wider. If the first row of the worksheet is used
to give headings to each column, you may find it useful to
make every entry in that row show in bold font. Similarly, if
data needs to be segregated into various sections or
calculations, it can be useful to change cell borders to reflect
this. Each of these is very easy to achieve in Excel.
To adjust the height of a row, hover your mouse over the lower
edge of the row’s identifier at the left side of the screen. When
a dual-sided arrow symbol appears ( ), click and use your
mouse to drag down the lower boundary of the row. This same
technique can be used to extend the width of a column by
extending its right boundary.
Additionally, clicking the format option in the toolbar also
provides you with ways of changing the row height. By
selecting “Row Height,” you can input a specific numerical
size for the chosen row(s). Conversely, clicking “AutoFit Row
Height” will automatically adjust the height of the selected
row to the text contained within the cells in that row. This
technique can likewise be used for column widths.

Adjusting the alignment of text in a row, column, range, or cell

is completed by selecting the cell(s) in question and using the
toolbar’s alignment section to apply a left, center, or right
horizontal alignment. However, it is important to note that in
Excel, you can also choose a top, center, or bottom vertical
alignment. This becomes particularly important in the case of
tall rows, where not every cell is filled with data to the same
extent. The worksheet below shows examples of how these
horizontal and vertical alignments display when combined.
The red oval denotes the area of the toolbar in which these
settings were chosen. As you will see, the selected cell (D2)
has a top vertical alignment chosen from the upper set of
alignments and a right horizontal alignment as chosen from the
lower set of alignments. These combine to align the text in the
top right corner of that cell. By clicking on the numerical row
identifier or alphabetic column identifier before choosing an
alignment, that alignment will be applied to the entire row or
column.

To change the border on a row, column, range or cell, select

the cell(s) you wish to change, right-click, and select “Format
Cells” from the drop-down menu. This will open a multi-
tabbed formatting window. By clicking the “Border” tab, you
will be presented with the many options available to you.
Don’t be intimidated, there is plenty to choose from but it
doesn’t need to be overwhelming.

The “Line” section of this window enables you to choose from

a variety of styles and colors of borders, while the “Border”
section determines the boundary on which you would like to
place this changed border. As you make your selections, a
preview will be shown to you within the “Border” section of
the window. As you can see above, a double red line around
all four boundaries of the range has been chosen. In practice,
when applied to all of column B, that looks like this:

Working With Specific Data Types

Excel also gives us the option to format cells to more easily
work with specific data types. For instance, when working
with financial math, we can format cells to automatically
round to two decimal places and display a currency symbol to
the left of the numerical data. This currency formatting also
gives us the option to automatically display negative values in
red. Likewise, we can make use of the time and date
formatting options to enter purely numerical data and have it
converted to a more easily understood configuration. Each of
these options is accessed through the “Number” section of the
toolbar, or the “Number” tab of the “Format Cells” window
accessed by right-clicking any cell(s).

Hiding and Unhiding Data

When working with a particularly data-heavy worksheet, it can
sometimes be overwhelming. It’s hard to keep track of what
information is relevant to your current work and what data is
simply confusing for the time being. However, in these cases,
you very rarely want to get rid of that information entirely.
What isn’t currently relevant may have a vital purpose in the
future, and even splitting this information off into its own
worksheet could cause complications later on. For this reason,
learning to hide and “unhide” certain groupings of data is a
vital skill.
To begin with, let’s take a look at hiding a row or column. As
usual when dealing with an entire row or column, the grouping
will be selected by clicking on its numerical or alphabetical
identifier to the side or top of the worksheet. If you wish to
choose more than one non-adjacent row or column, holding
down the CTRL key on your keyboard will allow you to click
and select multiple identifiers. Once the relevant data is
selected, there are two methods of hiding it.
Firstly, you may right-click and choose “Hide” from the drop-
down menu. The second option is to use the toolbar. By
selecting “Format” from within the “Cells” section of the
toolbar, a drop-down menu will also be activated. Within this
menu, there is a subsection titled “Visibility” from which you
can choose to hide or unhide rows, columns, and even the
entire worksheet. Once hidden, a thin double line will denote
the location of hidden rows or columns.
The process of unhiding these data groupings is very similar.
The thin double line demarcating the existence of hidden
information can be right-clicked, or the “Format” menu of the
“Cells” section of the toolbar may be used. Additionally, by
pressing CTRL + A, right-clicking, and choosing “Unhide,”
all hidden data sets will be revealed.
As mentioned above, even entire worksheets can be hidden
and unhidden for the purposes of simplifying your workspace.
Though this hides the worksheet from view, the data within
hidden worksheets can still be referenced in other worksheets,
for instance, if needed as part of a formula. Therefore, you do
not risk any additional complications to your calculations by
hiding a worksheet. To hide or unhide a worksheet, use the
“Format” menu in the “Cells” section of the toolbar.

Worksheet and Cell Protection

Protecting your entered data becomes especially crucial if your
worksheet can be edited by several people or if for any reason
you need to share the workbook with others. Protecting
sections of an Excel workbook prevents any other user from
editing, moving, or deleting that specific section. This
protection is implemented using passwords at either the cell,
worksheet, or workbook level, and within each of these levels,
there are several options available as to the level and type of
protection. The options can also be combined to create
simultaneous levels of protection, dependent on your needs.
To protect a workbook, one such option is to password encrypt
the entire file. This will mean that anyone attempting to gain
access to the workbook will be prompted to enter a password
before being able to view it. However, once this test has been
passed, the viewer will still have editing rights over the
workbook. It is also important to note that Excel does not have
a forgotten password facility, so it will be vital for you to
remember the password set to encrypt the file. If this seems
like the correct choice for your purposes, a password can be
set for the workbook by opening the “File” menu located at the
top left corner of the Excel window, above the toolbar. From
here, select “Info,” followed by the “Protect Workbook”
button. This will engage another menu. Choose “Encrypt with
Password” to open the “Encrypt Document” window. Within
this window, type your chosen password and press “OK.” You
will then be prompted to re-enter and confirm your chosen
password. Once you have pressed “OK” a second time, you
will be brought back to the workbook. However, once
closed/exited, the password will need to be entered to regain
access to the workbook. To remove a password from a
previously encrypted file, you need only follow these same
steps, but leave the password entry bar blank.
If you are content to have your spreadsheet workbook viewed
but would like to protect it from any changes, it is possible to
create a read-only file. Though editing can be enabled by
viewers, sending your file in a read-only format serves as a
warning to proceed with care before making any changes.
Similarly to the password encryption option, the ability to
make a workbook open as a read-only file can be accessed
through the “Protect Workbook” button located in the “Info”
section of the “File” menu. Select “Always Open Read-Only.”
When activated, this ensures that anyone who opens the file is
met with a pop-up stating, “The author would like you to open
[Workbook File Name] as read-only unless you need to make
changes.” To remove the read-only file opening, simply repeat
the above steps, which will switch this setting off.
The final option for workbook-level protection is to protect its
structure. This involves setting a password which will prevent
unauthorized viewers from adding, removing, renaming, or
moving worksheets within the workbook. In this case, the
workbook can still be opened by anyone—unless you choose
to also password encrypt the entire workbook—but its most
foundational structural elements cannot be changed. This
option is also available under the “Protect Workbook” button.
Select “Protect Workbook Structure,” enter, and confirm your
chosen password to activate this structural protection. This can
be undone in the “Review” tab, located parallel to “File,” by
choosing “Protect Workbook” and entering the password in the
“Unprotect Workbook” pop-up window. Once again, it’s
important to remember your password! If your password is
lost, so is your power to change the structure of the workbook.
Within the workbook, you can also choose to protect
individual worksheets from the editing, formatting, and
deletion of content. This is accessed in the “Review” tab,
located as specified above. Within the “Protect” section of the
“Review” tab toolbar, click “Protect Sheet.” This opens a pop-
up window which not only allows you to enter your chosen
password, but also include or exclude elements of the
worksheet to remain unaffected by the protection, as shown
below.
Any selection boxes marked with a check mark will remain
available outside of the password protection. Any actions left
unchecked will require password entry to be accessed. Once
your selections are made and your password is entered, click
“OK” At this point, you will be prompted to re-enter and
confirm your chosen password. To remove worksheet-level
protections, select “Unprotect Sheet” in the Review tab toolbar
and enter the password.
The final protection option we will be covering is that of
specific cells. This can be of use when you have spent quite a
while building a given formula and really don’t want to see it
disturbed. Likewise, certain data may be crucial to formulas
located elsewhere in the workbook, and therefore should not
be changed even by those who have access to the rest of the
file.
Protecting a single cell or a small range of cells is simple
enough. By right-clicking on the cell(s) in question and
selecting “Format Cells” to open the cell formatting window,
we can gain access to the “Protection” tab of this window.
Within this tab, you can choose to activate the options for
locking or hiding the chosen cell, which is confirmed by
clicking “OK” at the bottom right of the window. You will
then need to activate worksheet protection as specified above.
This protection will then be applied specifically to your chosen
cell or small range of cells.
If you should wish to protect a large range of cells, the method
is not quite as straightforward. Unfortunately, this is one task
in which Microsoft Excel is not the user-friendly piece of
software that you have come to know. This is because of the
necessity to use the program to lock the entire worksheet but
unlock the cells that you are okay with having edited. As a
result, in order to protect a combination of specific cells, you
need to begin, counterintuitively, by selecting all of the cells,
ranges, rows, or columns which you don’t want to protect. I
realize how backwards that sounds, but bear with me! Once
you have chosen the cells that do not need protection, use the
Format Cells window to uncheck the locked option and
continue by locking the remainder of the worksheet following
the worksheet-specific instructions above.

Summary
Each of the techniques covered in this chapter is a crucial part
of formatting your data to get the most from it. Laying out
your information correctly allows a household budget to look
like a household budget, a class participation registry to
present clearly and neatly, and a professional document to be
structured and safeguarded according to data protection and
regulation requirements. Knowing how to arrange your
worksheet effectively allows for simple and efficient data
entry. But, more importantly, it also allows you to more easily
make use of that data.
Having a bunch of numbers thrown into a worksheet with as
little finesse as an elephant finger painting will only make any
calculations involving that information energy-draining and
time-consuming. Formatting your worksheet in an easily
understandable way, on the other hand, prevents the need to
memorize the relevance of figures or manually search for
applicable information. Therefore, I highly recommend that
you take the time to open an Excel worksheet of your own and
practice each of these skills before continuing to the following
chapter. Know that you know how to enter your data
appropriately, it’s time to get to the good stuff: using it!
Chapter 3: Database Utilization
Entering information into a worksheet and presenting it in an
easily understandable way is a great start, but let’s be real.
That’s not what Excel is best at. The beauty of Microsoft
Excel is its ability to use the information within its workbooks.
Long gone are the days of plugging numbers into a calculator
and handwriting equations. Thankfully, so too are the days of
singing the alphabet song in our heads to figure out the order
in which to compile our data; I know I can’t be the only one
that felt silly doing that. Yet, we haven’t quite reached the era
where we are all comfortable and competent with the
replacement for that system. This chapter endeavors to bring
you one step closer to that sense of ease and expertise.
We have a tendency to think of the functions in Excel as being
purely mathematical and, as you will see in the later chapters
of this book, many of them are. But not all! Many are instead
built-in operations to help us search for, compare, and
reference information contained within worksheets or larger
databases. It is vital that you have at least an underlying
understanding of these kinds of functions, particularly as you
extend your Excel learning journey to encompass larger and
larger blocks of data.
Likewise, there are a few fundamental tips and tricks for using
your data and functions, in general, that can help you to work
more effectively and efficiently on any number of projects
within Excel. This chapter will give you an overview of these
kinds of components.

DATE
Often, in registers of a business’s sales, the year, month, and
day elements of the date of sale will be listed in separate
columns. This will allow users of the worksheet to highlight or
use, for instance, all of the information pertaining to a specific
year, as is necessary when compiling the company’s financial
statements. However, in many circumstances, we will also
wish to have a column which compiles each of these three
columns into a single, complete date. This would allow us to
filter and use all information prior to or after a certain date or
event, which can be particularly useful for consolidating the
accounts of merged businesses. This rationale can also be
extended to personal and household uses such as budgeting
and loans, as you will see in later chapters.
Thankfully, we need not manually enter the entire date by
reading each entry along a row and typing it out in full, the
DATE function is here to save us from that mundanity. To use
the DATE function, we will implement the structure as
follows:
=DATE(year, month, day)
Keep in mind that each of these pieces of information—known
as arguments when contained within a function—need not be a
numerical value; they can instead be a cell reference to where
this information is held.
Take, for instance, the worksheet below:

This uses the function:

=DATE(C2, B2, A2) to compile the Day, Month, and Year
columns into an entire date.

Dragging and Copying

The above DATE function may not seem as though it will save
you much time, given that you need to enter the function and
select the cell references to use as arguments. You might be
thinking, “But I could just type the date so much faster! Why
don’t I just do that?” And I have an answer for you! Setting up
the first function or two in a selection of neighboring cells is
only the beginning. Once you have set up a function that will
be used repeatedly across a row or down a column, such as the
DATE function in cell D2 above, it is a simple matter to apply
the same function to as many neighboring cells as you see fit.
Once my DATE function is operational, I can select cell D2
and hover over its bottom right corner, where there is a small
green square indicated by the red circle in the image below.
When hovering over this square, I am presented with a solid
black plus sign as my cursor.

By clicking this square and dragging downwards, I can then

apply the DATE function to each cell in column D. It will
automatically convert the required arguments for each row,
entering the correct complete date without the need to reenter
the function, for any neighboring cells in column D to which it
is dragged.
This is how, within seconds of finishing cell D2, you can have
a worksheet that looks as follows.
The highlighted cells in the image above are those to which
the DATE function was dragged.
This method of continuing the use of a function or formula
among neighboring cells can be applied in a myriad of
settings.
There are, however, several key issues to keep in mind when
using this trick. If your cell is filled with information that is
usually part of an ordered set—e.g. a day of the week, a month
of the year, etc.—dragging and copying that single will cause
neighboring cells to be filled with a continuation of that set.
For instance, dragging and copying a cell into which is entered
“Monday” will cause the neighboring cells to become
“Tuesday,” “Wednesday,” “Thursday,” and so on.
There is, thankfully, a way to avoid this if you simply have a
lot of information occurring on a Monday. By entering
“Monday” into the first two relevant cells, and highlighting
them both before selecting the green square to drag and copy,
you can give Excel the hint that you do not want to continue
with Tuesday. This will then allow you to copy only
“Monday” into as many neighboring cells as you like.

Likewise, as you have seen above with the DATE function,

dragging and copying most often automatically updates the
cell references included in a function for the row or column
along which it is dragged. It is, however, possible to avoid
this.
In order to keep a section of a cell reference—its alphabetical
or numerical portion—fixed, we simply place a dollar sign ($)
before that part of the cell reference in our first usage of the
function prior to copying. Therefore, if we wish to ensure that
an argument always refers to column B, but are okay with the
numerical row identifier changing, the cell reference in the
first use of the function could be “$B2.” If, on the other hand,
the column in which relevant information is placed does
change, but not the row, we might instead use “B$2.”
In some cases, both elements of a cell reference argument will
need to be fixed. You will see an example of this in practice in
Chapter 8: Financial Functions. In these circumstances, we
simply place a dollar sign before both the alphabetical and
numerical components of a cell reference, e.g. “$A$1.”
With these tips in mind, dragging and copying information to
neighboring cells should save you a lot of time.

EXACT
The EXACT function is used to compare two or more strings
of text. Imagine for instance that a business wants to compare
the address of a customer entered into a mailing list, with that
pulled from a database, the EXACT function might be used.
The name of this function is not an exaggeration; it really will
only match text strings that are exactly the same, meaning that
it is sensitive to case, punctuation, and spacing. Therefore,
“101 S. Blank St.” will return a FALSE response when
compared against “101 s blank st,” “101 s. Blank St.,” or “101
S Blank St.” using the EXACT function.
For this reason, EXACT is more commonly used to compare
case-sensitive data, such as passwords, or unpunctuated data,
such as product or item codes. The structure for the use of this
function is a simple one.
=EXACT(text1, text2, text3…)
Each of the arguments can be either a string of text contained
by quotation marks or a reference to a cell.

Filtering
Filtering is an opportunity to take control of your data without
manually needing to manipulate it into place. Suppose that you
keep an inventory of the foods in your pantry. If you wanted to
check what types of beans you have, you could, of course,
scroll through your data, counting butter beans, black beans,
pinto beans, kidney beans, and so on, manually. But wouldn’t
it be easier to simply filter the list to show only the beans?
Similarly, a business with a huge mailing list may wish to
target a promotion that requires in-person presence for only
those with local addresses; this is an opportunity for filtration.
Filtering allows you to choose a subset of your data according
to your own conditions. The remainder of the data is
temporarily hidden from view, enabling you to work
specifically with the subset until you once again unfilter the
information.
To implement a filter, highlight a cell in the list that you wish
to filter, and select the “Sort & Filter” option in the “Editing”
section to the right of the toolbar. Choose “Filter” from the
drop-down menu. This will cause a small gray arrow to appear
to the right of the column heading cell. Click this to determine
the filtration settings. Within the drop-down menu that
appears, you need only deselect the data types that you wish to
filter out of view and click OK. There is also an option to
select or deselect all data types to save you time if you with
you only include or exclude a small selection.
It is also possible to design your own filtration criteria outside
of the preordained categories of the list. For instance, you may
wish to filter out any sales of less than $100. This can be done
using either the “Text Filters” or “Number Filters” option
within the column heading drop-down filtration menu. From
here, you will be offered several further filter options, or can
select “Custom Filter” at the bottom of the list. Simply enter
your criteria into the pop-up window and press “OK” to
engage.
To remove the filter, navigate back to the “Sort & Filter” drop-
down menu and again click the now highlighted “Filter”
option to disengage the filtration system.
Activating and deactivating filters can be an amazing way of
working with smaller subsets of data without having to copy
or transfer anything to another worksheet. As you progress
with learning further functions, this ability will enable you to
complete complex calculations on subsets of a large database
in a very short space of time.

FIND
The FIND function is used to locate where a given substring of
text occurs within another string or range of strings of text.
The answer is returned as a number signifying the position at
which the first character of the string occurs. That sounds a bit
complicated in theory, so let’s look at a practical example.
If the string of text “Microsoft Excel” is in cell A1, and we
wish to know how far into that string the word “Excel” begins,
we can use the function:
=FIND(“Excel”, A1)
This will return an answer of 11. This means that the “E” at
the beginning of “Excel” is the 11th character in the string of
text “Microsoft Excel.”
The general structure of the use of the FIND function is:
=FIND(find_text, within_text, starting_position)
The first argument, find_text, is the substring which we are
searching for. The within_text argument denotes the larger
string of text throughout which we are searching. The final
argument, starting_position, is optional, hence its omission
from the earlier example. This argument allows you to choose
where in the string you would like to search from. For
example:
=FIND(“o”, A1, 6)
This function would skip the first “o” present in the string
“Microsoft Excel” by beginning its search at the 6th character,
and would return a value of 7, signifying the position of the
next “o” as the 7th character.
It is important to note that the FIND function is also case- and
punctuation-sensitive. Therefore, not only must your search
criteria be appropriately capitalized and punctuated, you will
need to remain mindful that spaces will be included in the
character count.

HYPERLINK
As would be expected, the HYPERLINK function enables you
to embed clickable links in your Excel worksheet. This can be
useful if you are referencing external data and need to declare
your source.
The structure for the use of this function is:
=HYPERLINK(link_location, link_name)
The link_location argument should contain the URL for the
website that you wish to include. The link_name is an optional
argument, but I recommend its use as it enables you to include
a shorter clickable title on the embedded hyperlink.
For instance,
“http://www.websitename.com/a_really_long_link_with_lots_
of_numbers123456789_and_symbols%#:~:%.pdf” is a long
and unwieldy title to include in a single cell. We can instead
include this link and call it “Click Here For More Info” using:
=HYPERLINK(“http://www.websitename.com/a_really_long_
link_with_lots_of_numbers123456789_and_symbols%#:~:%.
pdf”, “Click Here For More Info”)
This will present the link much more tidily and explain to
external readers why the link has been included.

IF
IF statements were briefly covered in Excel Formulas and
Functions 2020 Basics and, as you saw there, are used to test a
piece of information against a logical operation. IF functions
have two potential outcomes: the value assigned if the test
returns as true and the value assigned if the test returns as
false. Therefore, their general structure is:
=IF(logical_test, value_if_true, value_if_false)
These kinds of functions can be applied to many situations.
For instance, if you are tracking your diet and exercise in an
Excel worksheet, you may wish to allow yourself 10% more
calories on the days where you exercise for an hour or more.
The function for your daily caloric allowance might look like:
=IF(Exercise>59, AvgAllowance*1.1, AvgAllowance)
IF functions can also be combined to create more complex
logical tests. For example, we may wish to increase your daily
caloric allowance by 15% if you exercised for an hour or more
at the gym, wish to increase it by 10% if you exercised for an
hour or more at home, or increase it by 5% if you completed
any form of exercise for less than an hour. These more
complex functions will be covered in a later chapter.

LOOKUP
The LOOKUP function searches either a row or a column for a
value that matches its search criteria. Before using this
function, it is advised that you sort your data into ascending
order as described later in this chapter. For now, let us look at
the structure of the LOOKUP function:
=LOOKUP(lookup_value, lookup_vector, result_vector)
The lookup_value is the information for which we are
searching. This can be a number, a string of text, a cell
reference, etc. The lookup_vector argument should contain the
data that we wish to search. This data can only be one-
dimensional, i.e. spread across a single row or column, and
composed entirely of neighboring cells. The result_vector is an
optional argument and denotes the list of data from which we
would like to pull our return value. For instance, if we are
looking up Seán’s monthly sales, we would be searching the
employee column for Seán’s name, but would wish to return a
value from the monthly sales column. It is important to note
that the result vector must be the same length as the lookup
vector in order for the function to operate.
If you wish to search a two-dimensional range of cells using
the LOOKUP function, this is possible, but the arguments are
slightly different.
=LOOKUP(lookup_value, array)
When searching a range of cells across a selection of rows and
columns, it is not possible to specify a separate return array.
Therefore, the resulting value will be the value present in the
last cell of the same row or column as the discovered lookup
value.

LOWER
The LOWER function is used to convert a given string of text
to its strictly lowercase version. This can be useful for data
most commonly presented all in lowercase, such as email
addresses. The structure for the use of this function is quite
simple as it only has one argument, the string of text in
question.
=LOWER(text)
Since the LOWER function deals exclusively with text,
numerical characters and punctuation will remain unaffected
by its application. Therefore, you need not fear receiving an
error code if your text argument includes numbers and
punctuation; the function will simply ignore them.

MATCH
The MATCH function is similar to the FIND function in that it
searches for a given value and returns its position. However,
unlike the FIND function, MATCH does not search a single
cell or string of text. Instead, it searches a range of cells, or
“array.” The structure for the use of the MATCH function is:
=MATCH(lookup_value, lookup_array, match_type)
The lookup_value argument is, of course, the criteria that we
are searching for. In a list of groceries, this might be
“bananas.” The lookup_array is the range of cells that we wish
to search for our lookup_value, e.g. A:A, which denotes all of
column A. The match_type argument is optional and has three
potential values. This is another area in which the MATCH
function operates differently to FIND. When the match type is
set to equal one, if the function cannot find an exact match to
the lookup value, it will return the position of the closest
match below the lookup value. If this option is used, please
ensure that the range of cells being searched are sorted in
ascending order, as this will impact what is decided to be the
closest match. This is the match type that the function will
default to when the argument is omitted, so it is best to sort
your list anyway. How to do this is covered later in this
chapter.
If the match type argument is set to equal zero, an error will be
returned if an exact match cannot be found. This is the only
match type in which the order of the array being searched is
irrelevant. If the match type is set to equal -1, the function will
return the closest match above the lookup value. Therefore, the
array of cells being searched should be in descending order.

PROPER
The PROPER function is used to capitalize the first letter in
each word in a string of text. This is often used by businesses
to ensure that their customer names are appropriately
capitalized before any communications are sent. For example:
=PROPER(“john a. smith”)
This function will return “John A. Smith.” More commonly,
the argument for the PROPER function is a cell reference.
This will allow the function to be dragged and copied along
entire sections of the worksheet.
PROPER also converts all other letters in the text string to
lowercase, so the common internet-joke typing style of
capitalizing every second alphabetic character, such as in “aRe
YoU oKaY?” can be easily converted to “Are You Okay?”
One issue to keep in mind, however, is that Excel reads any
letter occurring after an apostrophe (‘) as the start of a new
word. This is great if you are trying to capitalize Irish and
Scottish last names like O’Hara, but not so ideal if the text in
question ends up reading, “Mike’S Butchers.” So, when using
the PROPER function, tread carefully where apostrophes are
concerned.

Sorting
Sorting allows us to maintain a view of all data while
reordering it into a more logical or easy to follow arrangement.
Most often, data is sorted in alphabetical order, if text, or from
smallest to largest, if numerical. But these are by no means
your only options.
To implement a data sort, click the “Sort & Filter” option in
the “Editing” section of the “Home” toolbar. This will activate
a drop-down menu which will provide you with the common
options mentioned above—which can, of course, be clicked if
appropriate—as well as a third option, “Custom Sort.”
Clicking this third option will engage a pop-up window for
you to determine your sort type. You will need to choose the
data set you wish to sort according to—the options are usually
your column headings; the data type that you wish you sort
based on, e.g. the value contained in each cell, the font color,
etc.; and the order in which to sort, e.g. alphabetical.
One of the major benefits of choosing to custom sort is that
you can enact more than one level of sorting simultaneously.
If, for instance, you wished to filter an employee payroll first
by employee name and then by employee pay rate, this can be
achieved simply by clicking the “Add Level” option at the top
of the pop-up window and filling in the three sort criteria—
data set, data type, and sort order—as usual. This might look
like:
Click “OK” to confirm and activate your sort criteria.
It is also worthwhile to note that you can both filter and sort
data at the same time, enabling even more precise usage of
subsets of data.

SUBSTITUTE
The SUBSTITUTE function replaces text string(s). This is
regularly useful if the data imported from an external source is
in an incompatible format for the operations we have in mind.
The structure for using this function is:
=SUBSTITUTE(text, old_text, new_text, instance_num)
The text argument refers to the cell or range of cells in
question. The old_text argument is, obviously, the text string
that we wish to replace. As with all text strings used as part of
a function, ensure that you surround this argument with
quotation marks during use. The new_text is the replacement
text that we wish to substitute in place of the old text. The
instance_num is the only optional argument in this function. It
can be used to specify a specific occurrence of the old_text
argument which we wish to replace, e.g. “1” would cause the
function to only replace the first instance in which the old text
occurs. If this argument is omitted, it is assumed that we wish
to replace all instances of the old text.
Bear in mind that the SUBSTITUTE function is case-sensitive,
so replacements will only be implemented where the old text
is exactly the same as the old_text argument.

UPPER
Just like its LOWER counterpart, UPPER converts a string of
text to contain entirely capital, or uppercase, letters. For
instance:
=UPPER(“stop”)
This function will return the word “STOP” entirely in capital
letters. More commonly, the argument of the function is a
reference to a cell in which a text string is contained, rather
than the text itself.
Keep in mind that the UPPER function deals solely with text
and, therefore, will ignore numbers and punctuation present
within the text argument in question.

Summary
Learning to search and manipulate data within a worksheet is a
key aspect of getting to grips with Microsoft Excel. While
these functions may seem nonsensical while working with
small amounts of data, they can truly be a game changer when
managing larger databases and trying to meet deadlines. The
functions, tips, and tricks covered in this chapter will allow
you to easily handle large sets of data. You have learned how
to format various text strings, search and replace groups of
information, and filter and sort your data to make it more
understandable and easier to work with. Now, it’s time to take
a look at how to convert that data to a visually appealing form.
Chapter 4: Data Visualization
Once you have made use of your data and formatted it
appropriately, it is often very worthwhile to implement a
visualization technique. Within Excel, your data can be
presented in tables, as you saw in Excel Formulas and
Functions 2020 Basics—Step-by-Step Guide with Examples
for Beginners. However, tables are only the tip of the iceberg.
It is also possible to show your data in a selection of eye-
catching and easily understandable charts and graphs. From
showing survey results in a pie chart to graphing student
grades in a scatter plot, there really is no end to the uses you
can make of Excel’s chart functions.
Yet, before you learn how to create each of these charts and
graphs, it is important that you understand the purposes and
uses of each form. While a bar chart might be a great option to
display categorized data such as total sales for specific product
types in a given fiscal year, it won’t be nearly so effective for
continuous data such as the weekly cumulative sales of a
single product. Hence, we need to learn what chart to use
when.
Likewise, it is also crucial that you learn the foundational dos
and don’ts of both charts in general and each specific chart
type. For instance, I cannot stress enough the vital need to
label your axes. An axis (plural: axes) is the vertical or
horizontal line on a graph, which is referenced to determine
the type and scale of the data presented. Without axis labels,
your chart may look very aesthetically pleasing, but the
information used will have no point of reference, and thus, be
useless. Similarly, while 3D versions of graphs might sound
cool in theory, studies have shown that they detract from
reader understanding, and therefore, are often best avoided
(Schonlau & Peters, 2012).
So, let’s dive into the motives, means, and methods of the
ways to visualize your data!
Pie Charts
Pie charts are a circular form of data visualization. They are
divided into various slices, with the width of each slice
representing the proportion of data attributable to a given
category. For this reason, the data used needs to be easily
categorizable. So, for example, a pie chart can be used to
represent the data collected if 75 students were asked what
their favorite subject was at school. Suppose the answers were
as follows: English–25 students, Math–25 students, History–
10 students, Science–10 students, Art–5 students. Visualizing
the data would look like this:

Imagine needing to draw that manually. You would need to

determine the proportion of survey results attributable to each
of English, Math, History, Science, and Art—33⅓%, 33⅓%,
13⅓%, 13⅓%, and 6⅔% respectively—before converting
these proportions into either radians or degrees and sketching
the chart using a compass and a protractor. Thankfully,
Microsoft Excel does away with all of that manual work.
By entering the data into your worksheet, you have the option
to automatically insert a pie chart. This is done by first
selecting all of the relevant cells of data. If the data you wish
to include is not side by side, hold CTRL on your keyboard to
continue highlighting other cells. Once the necessary data is
selected, continue by clicking the “Insert” tab above the
toolbar and selecting the symbol that resembles a pie chart in
the “Charts” section of the toolbar. Choose the leftmost option
in the “2D Pie” section of the drop-down menu to create the
type of pie chart seen above. This will create the most simple
form of pie chart.
Though thankfully Excel will draw the chart for us, our work
is not yet complete. As you will see below, the chart created is
rather bare.

To edit the chart title, simply double click “Chart Title” and
begin typing. Click away from the chart to confirm your
changes.
As shown, the 2D pie chart option will automatically create a
legend for the colored slices located below the chart. However,
you have the power to change this to display elsewhere, or to
label each slice individually as done in the first pie chart
image. This is achieved by navigating to the “Chart Design”
tab located above the toolbar, clicking “Quick Layout” on the
left of the toolbar, and selecting your desired presentation from
the drop-down menu.
Pie charts are excellent for comparing proportions of data, as
we can easily understand what slice is the biggest or smallest
at a quick glance. However, pie charts do have their limitations
and should not be chosen in certain situations. For instance,
the pie chart above cannot display subjects that zero students
chose as their favorite, and thus it may lead people unfamiliar
with the school to assume that English, Math, History,
Science, and Art were the only subjects on offer. Likewise, pie
charts cannot display negative values and therefore cannot be
used, for instance, to show proportional profits and losses
made on a selection of products.
Additionally, it is common practice to only use pie charts for
up to six categories of data. With any more than six slices, pie
charts can become difficult to read, particularly if several of
the slices are quite small. Therefore, it is important to remain
mindful of the needs of your data visualization project before
selecting a specific type of chart. Remember, we still have
plenty of other options when a pie chart isn’t ideal!

Column and Bar Charts

One such option that can be used for categorized data, when a
pie chart is not an ideal solution, is a column or bar chart. The
types of charts use rectangular bars of varying heights—in the
case of column charts—or lengths—in the case of bar charts—
to visualize quantities of categorized data.
To continue with our pie chart example, imagine, for instance,
that the favorite subjects survey actually questioned 100
students. For the purposes of a pie chart, you may decide to
only display the subjects that five or more students chose as
their favorite. However, using a column or bar chart, we can
more comprehensively display the results as follows.
In these forms, we can easily read that 25 students chose
English as their favorite subject, whereas only five chose art.
Rather than comparing proportions as we would in a pie chart,
we can instead view the numerical total of students who chose
each subject.
To create a chart of this type, enter and select your data just as
you would for creating a pie chart, navigate to the “Charts”
section of the “Insert” tab, and choose either “2D Column” or
“2D Bar” from the drop-down column and bar chart menu. A
column chart displays vertical rectangles, as in the first image
above, whereas a bar chart uses horizontal rectangles, as in the
second image. Once again, though this will present the data
automatically, it will leave us with a rather skeletal-looking
chart to begin with. Edit the chart title as explained in the “Pie
Charts” section of this chapter.
Additionally, you will need to label one or more of the axes.
Having increments of five running along one axis is useless
without telling the chart viewer what those increments of five
refer to; in this case, the number of students who chose each
subject. To create an axis title, select the chart, click the “Chart
Design” tab above the toolbar, and select “Add Chart
Element” from the toolbar. This will engage a drop-down
menu from which you can select “Axis Titles” and choose to
label either the vertical or horizontal axis.
Just as with any form of data visualization, column and bar
charts have their strengths and their weaknesses. They are
wonderfully easy to interpret and are accessible to a wide
audience since many people have encountered them at some
point in their education. However, the discrete categorization
of the data can fail to show important interrelations between
data sets and thus cause bar and column charts to be
misleading. Furthermore, with more than 15 categories, bar
and column charts can get cluttered and difficult to interpret.
Likewise, if there are exponential differences between the size
of data points—e.g. if 20,000 students loved English but only
two ranked Ancient Greek as their favorite—quantifying your
axes effectively can become problematic. Each of these
limitations in turn creates an opportunity for the visualization
of data to be manipulated in favor of one perspective or
another, and therefore, as with any chart, it is vital that readers
are given enough information to decipher the data contained
within the chart. Additional chart elements such as labels,
legends, and gridlines can be helpful in achieving this.
Gridlines are background elements in a chart which can be
added to achieve a graph-paper-like look, which will help
readers to determine exactly where certain bars of columns
end. They can be included using the “Add Chart Element”
drop-down menu in the “Chart Design” tab.
Take the time now to create your own column or bar chart and
experiment with the various design elements before moving
forward. This will help you to internalize the uses and
limitations of this kind of chart.

Line Charts
Unlike column and bar charts, line charts are used to represent
continuous data, i.e. data that can take on any numerical value,
not only whole integers. For example, height is a form of
continuous data, since someone may be 5′9.6321″ tall. The
universe hasn’t forced us all to stop growing at precise inch-
high increments, after all.
Line charts also connect individual data points using segments
of straight line. The horizontal axis is often used to denote the
progression of time, while the vertical axis represents some
form of progress. This is why line charts are very often used in
business. They are capable of tracking information such as
sales, share price, dividends, exchange rates, and so on, over
time, and can even be used to predict trends in consumer
behavior. But the potential of line charts does not simply begin
and end in the finance sector. This kind of graph can be useful
for tracking personal progress also.
By rating your mood between one and 10 each day, you could
then graph the change in your overall demeanor over time to
assess factors in mood changes, such as seasonal affective
disorder or the effectiveness of a new antidepressant. By
tracking the number of cigarettes smoked each day, we can
graph the effectiveness of a tapered quitting technique. In
cases such as these, a line graph often looks something like
this:

A patient with a chronic pain condition who may need to track

their day-to-day pain while starting a new treatment may find
a line chart useful. In this case, column A in an Excel
worksheet might be named “Date,” while column B could be
“Pain Level Out of 10.” By selecting the cells over a two week
range, navigating to the “Line or Area Chart” drop-down
menu in the “Insert” tab, and selecting “2D Line” section,
something like the following can be created.
The beauty of line charts is that in addition to charting the
progress of one measurement over time, we can compare this
trend with others through the addition of a second line. Say,
for instance, that our imaginary chronic pain patient wanted to
see if their pain levels were truly changing due to their new
treatment, or if instead they were simply in less pain when
they engaged in less activity. In this case, they might add a
third data set to their visualization. By labeling column C
“Activity Level Out of 10,” they can then plot a second line on
their chart, which would look something like the following.

Here we can see that though pain level does roughly correlate
with activity level early in the new treatment regime, the
decreased levels of pain in week two are not due to reduced
activity, and therefore the treatment must be helping. Without
this extra line, we weren’t getting the full story from the data
presented. However, be careful not to over clutter your chart
with lines either, as this can get confusing.
Moreover, with line charts, in particular, it is of the utmost
importance that you label your axes and provide a legend
explaining the relevance of each color line. This can be done
using the “Add Chart Element” drop-down menu.
Thankfully, in the example above, both of our data sets were
being measured in the range of one to ten. However this will
not always be the case, and when you progress to dual-axis
line graphs, it will be imperative that you are in good habits
when it comes to labels and legends.
Beyond this issue, some common missteps regarding line
charts include forcing your graph to have a zero baseline,
meaning that your y-axis (the vertical one) begins at zero.
While a zero baseline is useful in many cases, just as it was in
our pain scale example, it can sometimes hinder the effective
visualization of your data.
Take, for instance, the example of a currency exchange rate.
One U.S. dollar will never be worth zero euros. In fact, in the
recent past, this value has only fluctuated between 0.811 and
0.859. Therefore, it would be far more effective to keep our y-
axis between 0.8 and 0.9 rather than between zero and one.
This can be implemented by right-clicking on the y-axis of
your chart and selecting “Format Axis” from the drop-down
menu. This will open an “Axis Options” menu in which you
can adjust the bounds of the axis to fit your chosen maximum
and minimum values.
Another key factor to keep in mind is to use contrasting colors
if you are inserting more than one line in the chart, as this will
keep it easy to read and interpret. Remember that colorblind
viewers may not be able to distinguish between red and green,
or between various shades of the same color, so it is best to
steer clear of these combinations. The colors of your lines can
be altered using the “Change Colors” drop-down menu in the
“Chart Design” tab.

Embedding Charts
Now that you understand when and how to create charts using
Microsoft Excel, the next step is to make use of these data
visualizations. Your charts are not merely trapped in your
Excel workbook, but can be linked and embedded elsewhere.
For example, if you are writing a research paper and would
like to include a graph of survey results, you can use Excel to
auto-create your data visualization before transferring the chart
across to your Word document.
More than a simple screenshot or image, embedding an Excel
chart into a Word document allows the chart to automatically
update when any changes are made to the data in Excel,
without requiring you to retransfer the chart itself. As long as
your files remain saved in the same folder, the data entered
into your Excel worksheet will keep the embedded chart in
sync, preventing out-of-date information from making its way
into your final report.
This is achieved in the “Insert” tab of Microsoft Word rather
than being operated from Excel. To embed your chart, select
“Object” from the “Text” group on the toolbar. This will open
a pop-up window with two tabs. Choose the “Create from
File” tab and click “Browse” to search your computer’s files to
find the relevant Excel workbook. Once chosen, click “Insert”
to confirm your selection.
In order to ensure that the chart will automatically update, you
will need to check the box marked “Link to File” before
clicking “OK” to embed the chart and close the pop-up
window.
Once embedded, you can even edit the Excel worksheet
information from Word by double clicking on the chart; just be
sure to save your changes in both Excel and Word.

Summary
Graphs and charts are a hugely influential part of data
presentation in the modern world. They allow users with little
expertise in mathematical analysis to intuitively understand the
relationships and trends present within sets of information.
Moreover, even for people who are confident in their
mathematical ability, charts are a convenient method of
summarizing large sets of data.
For these reasons, and more, charts have been shown to be
43% more effective at convincing people of a given point than
statistics alone (Tal & Wansink, 2014). So, perhaps I should
have shown that information in a graph to convince you to
persevere with learning how to insert charts!
Whether it’s convincing your spouse to take on their fair share
of the housework or leading a sales pitch for your company,
visualizing your data is the way to go if you want to get others
on your side. In this chapter, you saw the uses and limitations
of pie charts, bar and column charts, and line graphs. You also
learned how to embed charts into Word documents. These
skills will be a hugely beneficial tool in your belt as you
progress with using Microsoft Excel in your daily life.
Chapter 5: Pivot Tables
Pivot tables are one of Microsoft Excel’s most underutilized
functions, but their value should not be underestimated
because of this! These interactive tables have the power to
take your data usage to the next level. Pivot tables’ largest
power is flexibility. They can rearrange your data as needed,
allowing crucial information to be highlighted or calculated in
a matter of seconds. Because of this, they are used to extract
vital data from large sets, to categorize and sort data, to filter
and summarize a pre-existing table, and so on, to enable
educated decision-making both at a private and corporate
level.
While a standard—or “flat”—table separates entered
information into rows and columns, a pivot table has the
capability to “see” the data entered as its own category. These
fact fields allow us to aggregate the data in previously unseen
ways, and it is this characteristic that enables these tables to
“pivot” and arrange the entered data as needed.
Pivot tables are most useful when the information being
processed includes duplicate entries and numerical data. For
instance, if every household purchase we make can be
categorized as housing, transportation, food, utilities, or other,
and we wish to track the numerical data related to our monthly
spending, a pivot table may be an ideal choice.
Pivot tables are created from your average worksheets, so the
first step in creating a pivot table is to enter your data into your
worksheet as usual. A key difference, however, is that creating
pivot tables works best when you do not utilize row headings.
Instead, enter all data in categorized columns with distinct
titles across the first row. This will ensure that your pivot table
can actually pivot—i.e. rearrange the data—when created.
Apart from the first row, which will be dedicated to the titles
of the columns, each row thereafter should include related data
in each column. For instance, all of the data related to a single
household purchase should be entered in the same row and not
combined with information relating to a separate purchase.
Once you are sure that your data is entered purely in labeled
columns and that all information in a row is interrelated, I
highly recommend that you convert this list to a table as
covered in Excel Formulas and Functions 2020 Basics—Step-
by-Step Guide with Examples for Beginners. This will not only
make referencing the data significantly simpler, but it will also
mean that if additional rows or columns are needed later, they
can be added to the table without needing to update the range
of cells referenced in the pivot table. Once the data has been
tabulated, navigate to the “Insert” tab. At the leftmost edge of
the toolbar, select “PivotTable.” This will activate a “Create
PivotTable” pop-up window. Highlight the range of data
required from your worksheet, ensuring that this data is
accurately represented in the “Table/Range” bar. This should
look something like “Table1” or “‘Pivot tables’!$A$1:$C$21.”
Next, you will need to select a location into which your pivot
table will be created. This can be done either in a new
worksheet or in a chosen cell of the existing worksheet,
according to your preference. Your completed “Create
PivotTable” window will look something like the following.

Click “OK” to confirm your selections and move to the next

step in creating a pivot table.
At this point, you have chosen your data, but not determined
how you would like to arrange it. To progress to fully creating
the pivot table, you will need to configure the data to your
needs before the table will be created and displayed. To the
right of the program window, a “PivotTable Fields” menu will
open. Here you will see displayed the various categories of
data from the range that you selected to include. Each of these
categories—that is, each of the columns of data that you have
entered—can be added or removed from the pivot table report.
This is why it is so vital to title your columns, as without
adequate titles, you will have no way to know what data you
are configuring to present in what way.
You have the power to decide whether to display each
category of data as a row, column, value, or filter, thus cross-
referencing your simple, flat table into a pivot table. What data
you choose to include and where will determine the structure
of the created pivot table. Rows and columns function as
normal to determine the horizontal and vertical layouts of the
data. The data grouped under these classifications are usually
non-numerical. The values section of a pivot table generally
includes the data categories that are numerical, and thus those
that may need calculations. By selecting a certain category as a
value, our pivot table will automatically sum the numerical
total in each column. Filters are used to change what data is
displayed once a pivot table is created. In practice, this means
that pivot tables are flexible even after creation, as a chosen
filter will apply to the entire table.
For instance, if we were to create a pivot table from a
worksheet including the dates and totals of household
spending as categorized above, we might use the dates as
rows, the spending amounts as values, and the types of
spending as a filter. Don’t forget to correctly format the
columns that display financial data using the “Number”
section of the “Home” toolbar to select the appropriate
currency and decimalization. When these choices have been
made, it will mean that if the filter is set to “All” at the top of
the pivot table, it will display as follows.
As you can see, this has aggregated spending data from the
same dates to show day-by-day, as well as total, monthly
spending. If, however, we use the “Spending Type” drop-down
menu to filter the pivot table to show only the food data, it
would present as follows.

This allows us to see, in a short matter of clicks, that $542.60

was spent on food in the month of January. If we were to
calculate this sum using functions and formulas, it would be
far more complicated and time-consuming. Furthermore, if
you need to see the calculations behind any aggregated figure,
you need only right-click on the relevant cell in the pivot table
and select “Show Details.” This will create a flat table in a
new worksheet of the same workbook, detailing the individual
data points that went into creating the aggregated number. Be
sure to label this newly created sheet to avoid confusion later.
The rows, columns, values, and filters can be adjusted at any
time by selecting the pivot table, navigating to the “PivotTable
Analyze” tab, and selecting “Field List” from the “Show”
section of the toolbar.
In this case, we might instead choose to denote the spending
type as a column, leaving rows and values as is. This creates
the following pivot table.

Here we can view not only the total spending per date, but the
total monthly spend on each category all at once. As you can
see, the possibilities for the display of pivot tables truly are
endless.
A key tip to remember is that you will need to refresh your
pivot table using the “Data” section of the “PivotTable
Analyze” tab if any relevant data in your worksheet is updated.

Pivot Charts
In addition to the flexible pivot tables that can be created in
Excel, you also have the option to include pivot charts. These
are charts created from pivot tables, which are equally flexible.
Pivot charts can be inserted using the “Tools” section of the
toolbar in the “PivotTable Analyze” tab. By clicking
“PivotChart,” you will be presented with a window from
which you can choose the chart type you desire. It is important
to keep in mind the dos and don’ts of the various chart types
learned in Chapter 4: Data Visualization.
The pivot chart will be created based on the current display of
your data within the pivot table, so it is also vital that you
ensure the information is categorized appropriately for the way
you would like to display data in a chart. For instance, if we
would like to create a chart based on the total monthly
spending on each of the five categories—housing, transport,
utilities, food, and other—it will be necessary for our pivot
chart to be formatted accordingly. This can be done by setting
the spending dates as a filter over the pivot chart, setting to
“All” to include the entire month, choosing spending type as a
row field, and leaving the spending amounts as values. In this
configuration, the pivot table will present the total monthly
spending for each category.
From here, we can choose to insert a pivot chart. If you would
like to display the proportional amounts of spending, a pie
chart may be the best choice. After you have clicked “Pivot
Chart,” select “Pie” from the “Insert Chart” window and click
“OK” to insert the pivot chart. This should then be labeled and
formatted according to the instructions in the previous chapter.
The chart inserted will be adjustable, just as with a pivot table.
Using the gray drop-down menus, the data included can be
filtered. In this example, that means that the pie chart can
show proportional spending throughout the entire month or for
a specific date or range of dates. Check the box at the bottom
of the drop-down menu to be able to select a range of dates to
include in the chart.
Similarly, we may wish to visualize the numerical totals of
spending per category rather than the share of spending
attributable to each classification. In this case, a bar or column
chart would be a good choice. These can also be selected from
the “Insert Chart” window and should be labeled and
formatted according to the guidelines in the previous chapter.
Remain mindful of the boundaries of your axes and not over-
cluttering the chart with too many columns or bars.
Pivot tables and charts are a great option for large data sets
from which many data reports may need to be derived. Their
flexible nature enables us to easily segment the data according
to a wide variety of variables and needs, and being able to
create multiple charts from the same data set makes them ideal
for creating easily accessible reports and presentations which
need to be understood by all. They can also be created from
external data sources, as well as internal worksheet data,
which we will cover in the next volume of this series. This
means that you are not restricted to working purely with data
that you have entered into a worksheet yourself, and it allows
for more specialization in data processing in the workplace.
However, pivot tables and charts do have their limitations, and
these should be taken into consideration before deciding on
their use. Creating these pivot tables and charts can be a time-
consuming venture to begin with, though always keep in mind
whether this time is an investment which will save you more
time later. Furthermore, they do not automatically update in
the way that embedded charts do. If your source data changes,
you will need to manually refresh the pivot chart or table in
order to sync these alterations. This is remarkably easy to
forget and can cause misrepresentations of the data if
forgotten.
Likewise, if any additional source data is added outside of a
table, the range of data referenced in the creation of the pivot
table will need to be updated before it can be reflected within
the table. This is done by clicking “Change Data Source” in
the “Data” section of the toolbar inside the “PivotTable
Analyze” tab. Once the “Change PivotTable Data Source”
window opens, highlight the new range of cells to be included
and click “OK” to confirm.
Summary
Pivot tables and charts are becoming more and more utilized
in the workplace, and have a wide variety of personal uses.
Because of this, I highly recommend that you take some time
now to open your own Microsoft Excel program and create a
pivot table. Play with it. Make mistakes and end up with a
pivot table the length of the continental United States if you
have to, but take the time to familiarize yourself with the very
many ways that data can be rearranged and displayed using
pivot tables and charts. Your future self will thank you for it.
Chapter 6: Mathematical Functions
Thus far, you’ve learned a lot about the entry and presentation
of predefined data. You’ve learned how to search for and
reference a database, and how to format the information in
eye-catching ways. But that’s not what Excel is known for, is
it? The true meat and bones of Excel are what you can do with
that entered data! We want to know more than what can
simply be entered automatically. We want to calculate
averages, determine totals, and actually work with the
information that we include in an Excel workbook. Digging
our teeth into those kinds of calculations requires the use of
functions.
Functions in Microsoft Excel are predefined formulas that are
built into the software to simplify the use of a certain
calculation or task. These shorthands can be used to save time
and increase your productivity when working on your
spreadsheets. As the name implies, mathematical functions are
the more calculation-based capacities included in Excel. They
allow you to know what information you need without needing
to know how to get it. Therefore, you—thankfully—won’t
need to remember your courses from high school to make use
of mathematical functions! In fact, Excel even does us the
favor of reminding us how to use functions by prompting the
order in which information should be entered. These pieces of
information are referred to as the arguments of the functions,
and you will see their structure explained below.
In the first book of this series, you were given a concise
introduction to a few of Excel’s mathematical functions,
including SUM, AVERAGE, and SQRT. With these functions,
their arguments often included either a number, a list of
numbers separated by commas, or a range of cells. In this
chapter, we will take a look at several higher-level calculations
that are also simplified using the inbuilt functions of Excel and
which—in some cases—include more complex groupings of
arguments. A crucial piece of information to remember is that
every function will need an equal (=) sign before it in order to
be used.

ABS
Imagine that you have taken out a $1,000 loan from a bank. In
theory, that amount of money should now be represented as a
negative number, given that it is a debt. When you make a
monthly payment of, say, $75, you are using a positive number
to work that debt back up to $0, i.e. paying off the loan.
However, for many of us, those signs complicate issues. Am I
subtracting 75, even though it’s a positive number? How do I
make sure that I don’t end up accidentally calculating my new
debt as $1075 this month, $1150 next month, and so on? The
answer is absolute values.
The absolute value of a number is simply its value regardless
of positive or negative signs. When taking the absolute value
of an addition/subtraction formula, such as ,
the order and signs of the calculation can be ignored.
ABS(-1000+75) has the exact same value as ABS(75-1000),
which in turn has the exact same value as ABS(1000-75). In
all cases, your answer is 925: the value of your new debt after
your monthly payment.
=ABS() is simply used to get the non-negative, signless value
of any numerical data. Any cell, formula, or function can be
listed within the parentheses enclosing the ABS function. But
as with all functions, be careful to close every set of
parentheses correctly. We will cover more on this crucial step
in Chapter 9: Combining Functions.

AVERAGEIF
As covered previously, the AVERAGE function enables one to
find the mathematical mean of a data set in Excel. Likewise,
the IF function enables us to stipulate a condition which, if
met, implements one course of action and, if unmet, puts a
different process into action. AVERAGEIF combines these
two functions. This allows you to separate out the data that
you would like to get the average of, using an if-like criterion.
For instance, if all household spending is tracked in a
worksheet and categorized as one of housing, utilities,
transportation, food, or other, you may wish to know how
much your average spend on transportation is. This might, for
instance, be the average amount of money you spend on gas
every time you top up your car. In the worksheet below, this
calculation would look like:
=AVERAGEIF(B2:B21, “Transportation”, C2:C21)

Every AVERAGEIF function usage will follow this basic

structure:
=AVERAGEIF(range, criteria, average_range)
Range refers to the cells over which you wish to exercise the
condition of the if statement. In our example above, the range
is our spending type categories. The criteria argument of the
function is the condition which must be met in order for the
data to be included in the average calculation. In our example,
this is the text “Transportation.” However, this might just as
easily be a number like 1603—which might refer to a specific
invoice, customer, etc.—or a mathematical expression such as
“<20.” Keep in mind that if your condition is anything other
than a simple integer or cell reference, it will need to be
surrounded by quotation (””) marks.
The average_range argument of the AVERAGEIF function is,
in fact, optional. If your first chosen range contains the data
that you wish to average, the final argument is not necessary,
and can be left blank. However, more often, we are testing the
condition against one portion of our data set—in this case, the
spending type—while finding the average of another—the
spending amount. Therefore, we list the cell range of the
spending amounts as our average_range argument.
If, rather than finding the average spend on transportation, we
instead wished to determine the average spend less than $400
—excluding housing costs and the largest utility bill—our
function would instead be:
=AVERAGEIF(C2:C21, “<400”)
This function has no average_range argument, as the initially
included range is the data we wish to average.

AVERAGEIFS
The AVERAGEIFS function allows us to be even more choosy
about the data that we include in our mathematical mean
calculation by including more criteria. For example, building
on the example above, imagine that we not only track
household spending according to various categories, we also
know whether each spend was made by Lisa, Frank, or if they
split the cost between them. This is tracked in column D,
leaving all other information as in the previous image.
Lisa and Frank are planning their budget for next month, and
to aid their decision making, they would like to know how
much each of them spends on an average miscellaneous
purchase. To determine Frank’s miscellaneous spending, our
function would be:
=AVERAGEIFS(C2:C21, D2:D21, “Frank”, B2:B21, “Other”)
The structure of the arguments is:
=AVERAGEIFS(average_range, criteria1_range, criteria1,
criteria2_range, criteria2…)
This can be extended to include up to 127 criteria and their
associated ranges. In AVERAGEIFS functions, the
average_range argument is no longer optional and therefore
must be specified. The criteria1_range is the range of data to
which the first condition applies. In this case, we want to know
which purchases are Frank’s, so we include the range of cells
which lists who made each purchase and use “Frank” as the
criteria. Likewise, we also need to determine which of Frank’s
purchases fall into the “Other” category, and this is achieved
using the final two arguments of the above function.
To determine Lisa’s average “Other” spend, we would simply
need to substitute her name for Frank’s in criteria1 to get:
=AVERAGEIFS(C2:C21, D2:D21, “Lisa”, B2:B21, “Other”)

EVEN
The EVEN function is used to round numbers to an even
integer (whole number). This rounding always occurs away
from the number zero, meaning that positive numbers are
rounded up while negative numbers are rounded down.
Therefore, 0.05 would round upwards to 2 rather than 1 as it is
uneven, and -2.1 would round down to -4.
This function is particularly useful for financial analysts who
may need to eliminate digits of the least significance while
maintaining the approximate value.
The use of the EVEN function requires only one argument: a
number or reference to a cell containing numerical
information. For example:
=EVEN(C6)

FACT
The factorial of a whole number is that number multiplied by
every whole number between itself and one. This calculation
allows us to determine how many combinations of a given
selection of choices can be made. For instance, the amount of
different ways that a deck of 52 cards can be shuffled is
calculated by finding 52 factorial, i.e.
52*51*50*49*48….*3*2*1.
Therefore, factorials are an important mathematical operation
when it comes to probability. For instance, say that a
restaurant offers 10 different side dishes. Customers can order
three different sides together for $10. Since each side dish
costs a different amount for the restaurant to prepare, they will
need to know the likelihood of a customer ordering the three
most expensive sides all at once, which would cause the
business to lose money.
In this case, we need to know how many combinations of three
sides can be created from the 10 options. This is calculated by:
=FACT(10)/FACT(10-3)
This gives us 720 combinations of sides that can be ordered.
Therefore, the business runs a one in 720 risk that a customer
will use the $10 deal to order the most expensive side dishes,
causing them to lose money.

INT
The INT function rounds numbers down to their nearest lower
whole number. This means that positive numbers will round
towards zero, e.g. 4.7 will round to 4, while negative numbers
will round away from zero, e.g. -11.6 will round to -12.
This requires only one argument: a number or the reference to
a cell containing numerical data. For example:
=INT(C12)

MOD
The MOD function in Excel calculates division. However,
unlike standard division, the MOD function returns the
remainder as its answer. So, for instance, if a teacher prints
280 worksheets, they may need to know how many they will
have left after handing them out equally among 23 students.
This can be calculated using:
=MOD(280,23)
This returns the answer that the teacher will have four
remaining worksheets that can be saved for use with another
class.
The number to be divided is always the first argument,
followed by the dividing number. You will always know if you
have made a mistake in the order of the arguments, because
your divisor will be returned as the answer. For example:
=MOD(23,280)
This returns 23 as its answer, since 280 cannot divide 23 even
once, and therefore all of 23 is the remainder.

POWER
If you need to calculated five to the power of six, you could, of
course, manually type into the formula bar:
=5*5*5*5*5*5
But the POWER function has been built into EXCEL to make
this much easier on us, and to help us to avoid miscounting
those many multiples of the same number. Instead, this can be
typed as:
=POWER(5,6)
This is a particularly useful function for anyone in scientific
fields, as many phenomena occur exponentially. Food safety
officers may need to calculate the rate of food spoilage,
ecologists may be interested in the rate of human population
growth, and most recently, epidemiologists have used
POWER-like functions to track trends in the spread of
COVID-19.
The POWER function also has some very advantageous
household uses, such as calculating the compound interest
costs of various personal loan interest rates.

PRODUCT
The PRODUCT function multiplies a supplied list of numbers
or range of cells containing numerical data. It can compute up
to 255 arguments per use of the function. An example of the
use of such a function is:
=PRODUCT(A1:A7)
This will calculate the product of the numbers in cells A1, A2,
A3, A4, A5, A6, and A7. Any text entries within this range
will be ignored, but text typed directly into the function
arguments will result in an error and should be avoided.

QUOTIENT
The QUOTIENT function is the division counterpart of
PRODUCT. However, QUOTIENT will only return the whole-
number portion of the calculated division, excluding any
remainders or decimal portions. MOD, as explained above,
can be used to determine the remainder as an integer, if
needed.
The structure for the use of a QUOTIENT function is as
follows:
=QUOTIENT(numerator, denominator)
The numerator is the number which needs to be divided. The
denominator is the number which is doing the dividing. So, for
instance, in practice this might look like:
=QUOTIENT(280,23)
The above will return the answer 12, ignoring the remainder of
4, or the entire answer of 12.1739…

ROUND
The ROUND function is used to round numbers up or down
according to the usual mathematical rules, where a digit of five
or more rounds up and a digit of four or less rounds down.
Additionally, the ROUND function allows the user to choose
the extent of rounding applied, i.e. how many decimal places.
The general structure for the use of the ROUND function is:
=ROUND(number, number_of_digits)
The number argument is, of course, the number in question or
the reference to a cell containing numerical data. The
number_of_digits argument is what allows for flexibility in the
use of this function. Take, for instance, the number 1653.9217,
and assume that this number is entered in cell A1 of our
worksheet.
=ROUND(A1, 3)
The function above uses three as the number_of_digits
argument, which gives us 1653.922. This was rounded up
since the last digit, seven, is more than five.
=ROUND(A1, 1)
This rounds to 1653.9, rounded down given that the number in
the second decimal place, two, is less than five.
=ROUND(A1, 0)
The above function returns 1654. But, the options do not stop
there. We can continue to round! If we use a negative integer
as our number_of_digits argument, we can specify the number
of digits to the left of the decimal point that we wish to round.
=ROUND(A1, -1)
By using one as our second argument, the function will return
the answer 1650, having rounded the digit in the “ones” down
since it was below five.
=ROUND(A1, -3)
This will return an answer of 2000 as the digit in the
“hundreds” column was above five.
=ROUND(A1, -4)
Finally, this returns 0, since the digit in the thousands column
was a one and was rounded down. Any number_of_digits
argument lower than -4 will, in this case, continue to round our
original number to 0 since it only had four numbers to the left
of the decimal point.

SUMIF
Like all IF-style functions, SUMIF allows us to implement a
conditional filter on a range of data before deciding on a
subrange to submit for addition.
Take, for instance, our household spending worksheet from
before. Lisa and Frank may wish to know how much each of
them individually spent in the month of January. This could
help them to more fairly divide household expenses in the
future or may be part of a larger calculation to track spending
trends across a variety of months. Rather than individually
selecting each cell that relates to Frank’s spending or Lisa’s
spending within the worksheet, they can simply implement a
SUMIF function.
The general structure for the use of SUMIF is:
=SUMIF(range, criteria, sum_range)
As with our use of AVERAGEIF functions, range and criteria
are vital arguments. These tell us what set of cells we are
applying a given condition to. The sum_range argument is
needed only if the cells containing the numerical data to be
added are different from the range of cells to which the criteria
is being applied.
In the case of our household spending example, we do indeed
need the sum_range argument. We will be filtering the data
according to who spent the money, but we can’t add the word
“Frank” to itself over and over again, now can we? We need to
tell the SUMIF function that we wish to add the numerical
data in a different set of cells. Therefore, we can filter the
“Who” column range using the criteria “Frank” and use the
“Spending Amount” column as our sum_range to create the
function:
=SUMIF(D2:D21, “Frank”, C2:C21)
Don’t forget that your criteria argument can just as easily be a
mathematical expression. So, we could choose to add all
spending amounts below $50 using the function:
=SUMIF(C2:C21, “<50”)
Likewise, our criteria could just as easily relate to the date of
the spending. So, to find the total spent in the first two weeks
of January 2021 from our worksheet, we can use the function:
=SUMIF(A2:A21, “<15/01/2021”,C2:C21)

SUMIFS
Just as with its AVERAGE counterpart, the SUMIFS function
allows the application of more than one condition to an
addition calculation. As expected, the structure for this
function is:
=SUMIFS(sum_range, criteria_range1, criteria1,
citeria_range2, criteria 2…)

Summary
In this chapter, we explored how to apply various
mathematical functions to data pre-entered into an Excel
spreadsheet. These functions have a wide variety of uses and
limitations, though some common themes run throughout. It is
vital that you remember to begin any use of a function with an
equal (=) sign. Likewise, you must always separate arguments
using a comma (,) and never forget to close your parentheses.
With these basic rules in place, mathematical functions can
simplify everything from household expenditure calculations
to complex scientific enquiries. The most important thing is to
use them and become comfortable with their application. Since
every function has its own specific use and structure, I suggest
that you take the time to implement at least one of every type
of function in an Excel Worksheet before progressing to the
next chapter. Not only will you be building on this prior
knowledge later in Chapter 9: Combining Functions, but you
will be ensuring that the information is more likely to stay in
your mind by practicing it for yourself. Good luck! I’ll meet
you back at the next chapter!
Chapter 7: Statistical Functions
Mathematical functions, such as those in the previous chapter,
enable us to calculate information such as totals, averages, and
so on. These are useful for discrete data comparison, but they
are limited in how often they can be applied for tracking
trends. This is where statistics come in. Statistical functions
apply more complex mathematical operations to collections of
data to analyze the relations between those numbers.
Technically, AVERAGE is a statistical function, as the
mathematical mean relates each individual piece of data to the
others in its grouping. However, as this is so commonly used
as a statistic, it is generally considered mathematical in origin.
As you will see as we progress through this chapter, other
statistics can also be applied to a variety of situations; they just
aren’t as universally known.
Statistics are used by everyone from commonplace decision
makers to weather forecasters, and are a vital part of any
journey towards confidence and competence with Microsoft
Excel. Businesses use statistics to determine if they are
making the correct choices in the products that they sell;
architects, engineers, and builders use statistics to confirm the
durability of designed projects before beginning a build; and
heads of households can use them to calculate trends in
expenditure. The uses of statistics are as far-reaching as your
imagination will stretch. So, let’s dive in!

COUNT
The COUNT function is used to determine the number of cells
in a range that contain numerical information. This may be
used to determine the number of individual transactions
carried out by a household, the amount of sales completed by a
business, and so on.
Teachers can even use this to track student absences by
recording a present student with an alphabetic entry, such as
“P,” and an absent student with a numeric entry, such as “0.”
By applying the COUNT function, this teacher can then allow
Excel to do the work of tracking how often each student was
absent.
To continue our household expenses example from the
previous chapter, we will use the worksheet:

To determine the total number of transactions for the month,

we can use the function:
=COUNT(C:C)
This includes the entire C column as our cell range to be
counted, and will ensure that even if additional expenditure
rows are added retrospectively, our COUNT function will
automatically update to include them.

COUNTA
The COUNTA function counts all non-empty cells. Therefore,
rather than just including numerical data, as the COUNT
function does, COUNTA includes numbers, dates, text, logical
values, error values, spaces, and so on, as cells which contain
data. This is why this function is sometimes referred to as the
“COUNT IF not blank” function.
The structure to use COUNTA is as follows.
=COUNTA(value1, value2, …)
With any COUNT-type function, up to 233 additional
arguments to be included in the count can be entered inside the
parentheses, as long as they are separated by commas.

COUNTIF
The COUNTIF function allows us to set a criteria with which
to filter the information that we wish to count. This can be
applied to both numeric and non-numeric information, just as
with the COUNTA function.
The structure for the use of COUNTIF is:
=COUNTIF(range, criteria)
Therefore, if we wanted to know how many household
expenditures occurred on the first of January, we could use the
function:
=COUNTIF(A:A, “01/01/2021”)
Likewise, to count the number of transactions below $20, we
might use:
=COUNTIF(C2:C21, “<20”)

COUNTIFS
Just as with any IFS-type of function, COUNTIFS allows us to
include more than a single condition through which to filter
the data which will be counted.
This used with the structure:
=COUNTIFS(criteria1_range, criteria1, criteria2_range,
criteria2…)
This function is capable of including up to 127 criteria and
their associated ranges.
If, for instance, we wished to count how many transactions
occurring in the first two weeks of January were for amounts
less than $100, COUNTIFS can be implemented using cell
referencing from the worksheet above:
=COUNTIFS(A2:A21, “<15/01/2021”, C2:C21, “<100”)
CONFIDENCE
A confidence interval is a representation of the certainty we
have in an estimated value. It lets us know how accurate an
average calculated from a sample of data truly is.
For instance, if a restaurant takes a sample of 20 single-
customer bills and finds with a 95% confidence that customers
seated alone generally spend $15.75 with a standard deviation
of 11.4—we’ll talk more about standard deviations later, so
don’t worry too much about this value for now—then the
confidence interval is $5. This means that there is only a 5%
chance that the average spend of all individually seated
customers is lower than $10.75 or higher than $20.75.
Therefore, the business can plan accordingly.
Manually calculating the confidence interval for a set of data
is rather laborious. We would need to determine the sample
size, the mean, the standard deviation, and look up a z-value
for our given confidence level, all before multiplying,
dividing, and making use of a square root. Thankfully, Excel
greatly simplifies this for us.
The CONFIDENCE function uses the structure:
=CONFIDENCE(significance, standard_deviation, size)

The significance is always equal to .

Therefore, if we know that the confidence level is 95%, as
above, the significance to be included in the function is 0.05.
We will be learning to calculate standard deviation further into
the chapter, as well as how to incorporate various functions
within one another in Chapter 9: Combining Functions, but for
now let us assume that you have been given the standard
deviation, as above, of 11.4.
The size argument refers to how many data points are part of
this calculation. Of course, this can be determined using the
COUNT function, but for now, let us use the number 20 itself
as the argument. Our function then looks like:
=CONFIDENCE(0.05, 11.4, 20)
You can also use a cell reference for each of your arguments if
you have previously entered or calculated significance,
standard deviation, or sample size within your Excel
worksheet. Remember that each argument will need to be a
numerical value greater than zero for the function to work.

CORREL
The correlation between two data sets is an indication of how
interrelated the data in each set is. For instance, ice cream
sales are strongly correlated with rises in temperature.
However, it’s important to remember that correlation does not
imply causation. Murder rates also rise in correlation with ice
cream sales, but the underlying cause is increased agitation
due to heat, not that all murderers love ice cream… though
maybe they do! If you know any murderers, be sure to ask
them for me.
Nevertheless, when mindful of cause and effect, correlation
can be an extremely useful measure. Financial analysts in
particular make use of correlation quite a bit. For instance,
knowing the correlation between the price of an individual
stock and a market index can help traders to predict share
prices. Likewise, understanding how much your heating bill is
correlated to seasonal weather changes can help you to plan
your household budget much more effectively during the
winter. This is where the CORREL function comes in. It is
structured as:
=CORREL(array1, array2)
It is important that the first array be the range of cells which
include the independent data set, e.g. the weather, which
cannot be controlled. The second array is the range of cells
containing the dependent variable—in this instance, our
energy usage to heat the house. Both arrays should contain the
same number of data points, as they should be capable of
pairing up to create a line chart as in Chapter 4: Data
Visualization.

FORECAST.LINEAR
Of course, one of the most commonly known purposes of
forecasting is in meteorology. I certainly check the weather
forecast before planning any outdoor activities. In financial
modeling, forecasting is used to predict, for example, future
expenses based on past expense amounts at various levels of
production.
To expand on our previous example, let’s say we have all of
our heating expenditure from the previous winter compiled
into a worksheet with temperatures in column A and kilowatt
heating usage in column B. If we know from local weather
forecasting that next week is set to be an average of 40 ℉
(4.5 ℃ ), we can also forecast our heating usage for the
upcoming week. This might look something like the
following.
=FORECAST.LINEAR(40, B2:B14, A2:A14)
Here, the first argument is the “x” value for which we want to
forecast a “y.” We want to determine the kilowatt heating
usage when the temperature is 40℉, and therefore, 40 is our
“x” value. The second argument is our known y-values. In this
case, that is the known weekly heating usages, in kilowatts,
from last winter. The third and final argument is our known x-
values; the weekly average temperatures from last winter. It is
vital that these last two arguments are entered in the correct
order, so remember that you are trying to sandwich your
unknown value type with the known value type. Therefore, it
goes:
=FORECAST.LINEAR(x, known_ys, known_xs)
It is also vital that all of your known x-values are measured in
the same unit as your forecasting “x”—in this case, degrees
Fahrenheit—and that all your known y-values are in the same
unit as you desire for the returned answer—here, kilowatts.
As with the CORREL function, each array—known_ys and
known_xs—will need to be of the same length.

MEDIAN
The median of a data set is another statistical piece of
information similar to the average. As you know, the average
of a group of numerical data is calculated by addition and
division, which means that it can be skewed by values much
higher or much lower than the bulk of the information,
particularly in small sample sizes. The average (also known as
“mean”) is also often not a real data point. For example, the
average number of children per family is 1.8, but no one is
having 0.8 of a child. The median, on the other hand, is the
actual data point which separates the data set in half, i.e. half
of all data in the set falls above it while half falls below it. If
there is an even number of data points, the median will fall
equally between the two middle measurements.
This form of data analysis is particularly useful when working
with large data sets that vary greatly. For instance, take a look
at income inequality in the United States. If we were to find
the average annual individual income, it falls at $62,518.13.
That sounds pretty good. Many people reading that figure
would be inclined to believe that everyone in the U.S. must be
reasonably well-off. However, if we look at median individual
income, we see that it is only $43,206.00. That means that
50% of people in the U.S. earn less than $43,206/year. Clearly,
the average is skewed by the very high earners at the top of the
income scale. Here, median individual income gives us a
better insight into the actual state of affairs.
To calculate the median of any set of information in Excel, the
structure is:
=MEDIAN(number1, number2, …)
You can, of course, also use cell ranges rather than
individually entering each number to be included in the
function. Empty cells, as well as logical and text values, are
automatically ignored by the median function and need not be
excluded from a cell range reference.

MIN
Just like the MAX function covered in the first book of this
series, the MIN function does exactly what you would assume;
it finds the minimum numerical value in a range of data. This
can be useful for determining the most and least expensive
utility billing periods, for calculating depreciation schedules,
for determining the spread of student grades, and much more.
One can either enter numbers directly into the function, or use
a range, such as:
=MIN(B1:B75)

MODE.SNGL
The mode of any group of data is the value which occurs most
often within that data set. The MODE.SNGL function in Excel
will return the most frequently occurring numerical data in a
list of numbers or a range of cells. If, for instance, products are
numerically coded, a business might use the MODE.SNGL
function to calculate which product is sold most often,
according to sales records stored in an Excel worksheet.
However, the function will ignore non-numerical data. To use
this function on a column named “Temp,” the function would
look like this:
=MODE.SNGL(Temp)

STDEV.S
Standard deviation is a measurement of how much a set of
data varies in its spread, relative to the average of the set. The
lower the standard deviation, the closer all data points are to
the average or mean. The larger the standard deviation, the
more spread out the information is. For instance, cans of soda
have quite low standard deviation in their sizes. They most
often hold 12 fl oz, and for quality assurance and legal
purposes, the volume of liquid will not vary largely from that
measurement.
Standard deviations are not only used in quality control
settings, but also as part of forecasting, in insurance and
actuarial sciences, to determine effectiveness of marketing
campaigns, to decide on the reliability of a survey or poll, and
so on. But, manually, this is a rather awkward calculation to
make. Thankfully, in Excel we need only use the STDEV.S
function. You need only enter the relevant numerical data or a
range of cells as your argument(s) and press “Enter” For
example:
=STDEV.S(B:B)
This will find the standard deviation of column B. The
function will ignore text and logical values.

VAR
Variance is another measurement of the spread between data
points in a set of information. It measures how far each data
point is likely to be from the next data point in the group. This
representation of data dispersion is often used by stock brokers
to determine market volatility. It is also a crucial part of the
construction industry, as a large variance in materials size or
quality could cause unsafe building sites. Variance can also be
used for as simple a scenario as how long it takes you to drive
to work. By knowing the spread of your various travel times,
you can determine the safest margin of time needed to get to
work on time, without consistently forcing yourself to get out
of bed much too early for fear of being late.
To calculate variance manually, from scratch, is complicated.
But if you already have the standard deviation, you need only
square that value to find the variance.
If you do not have the standard deviation, however, and want
to move straight to finding the variance, VAR is the function
for you. Just as with the STDEV.S function, the argument(s)
for the function will be a list of numbers, or a range of cells,
such as:
=VAR(C12:C50)

Summary
Statistical functions in Excel can be used to help track and
predict trends in data. They can be used by everyone from
high-level financial planners to those of us who just want a
few extra minutes to snooze in the morning. How you choose
to implement them in your daily life is completely up to you.
In this chapter, we covered some of the foundational functions
needed for this kind of day-to-day use of statistics, as well as
an introduction to a few slightly higher-level measurements
such as standard deviation and variance. Doctors use statistical
measurements to determine the effectiveness of new
medications, retailers decide what to sell in their stores based
on trends in consumer purchasing patterns, and each of us
subconsciously makes use of statistics every time we weigh
the pros and cons of any decision. When we wonder how
likely we are to succeed at a given task, and form a mental
comparison with previous attempts at similar tasks, this is
essentially a rough-estimate statistical calculation. Therefore,
you are already using statistics in your life; you only need to
decide in what circumstances you would like to formalize
these calculations to enable more fact-based decision-making.
As you determine how you want to use Excel, you can tailor
your use and future learning of functions to meet your needs.
Chapter 8: Financial Functions
Though many of us have a tendency to associate Microsoft
Excel with the financial trade, as you’ve seen, many of the
functions and formulas which can be used in the program are
applicable to a huge variety of purposes. But, of course, there
are those functions which are more appropriately used for the
calculation of accounting records, economic trends, and so on.
This is not at all to say that these functions should only be
used by accountants and economists. It is a sad fact of the
education system that few of us leave school with the financial
literacy to adequately navigate adult responsibilities and needs
such as savings, pensions, personal loans, and so on. It is this
lack of knowledge that leaves so many people vulnerable to
high-interest payday loans, buy-now pay-later schemes, and
inefficient multi-level marketing “entrepreneurship.”
Combined with the statistical knowledge from the previous
chapter, we can even calculate the true losses invoked by
buying a lottery ticket every week when the odds are stacked
against you, compared with the interest that could be earned if
that money was invested each week instead.
For this reason, I urge you to stick with this chapter even if
you feel that it doesn’t apply to you. I hope that with this book,
I can do my own small part to help you to waylay those risks
and navigate those responsibilities, all without having to invest
huge amounts of time or effort to learn mathematical or
accountancy-based skills.

CUMIPMT
The CUMIPMT function is used to determine the cumulative,
or successively added, interest paid on a loan or earned on an
investment over a certain period of time. This calculation can
be done retrospectively to determine what has already been
paid or earned, but it can also be treated as a form of
forecasting and used to determine the cost of loan over time,
or the potential return on a fixed rate investment.
The structure to be used for this function is:
=CUMIPMT(rate, number_periods, present_value,
starting_period, ending_period, type)
The rate argument refers to the interest rate applied per period.
This is generally stated on the paperwork for any loan, savings
account, or investment. If your interest rate is stated as an
Annual Percentage Rate (APR), this will need to be divided by
the annual number of payment periods. For instance, a 6%
APR loan paid monthly will have a 0.5% interest rate per
period (6%/12). For the purposes of the function, this is
divided by 100 and written in decimal form, rather than
percentage form, as 0.005. It is vital that you remember to
convert your interest rate to a per-period interest rate, or the
entire calculation will be incorrect.
The number_periods argument is how many payment periods
the loan or investment will be divided across. If, for instance,
you take out a two-year loan and make a payment every
month, this number will be 24.
The present_value argument refers to the value of the loan or
investment at the beginning of the first period in question. If
our two-year loan is for $10,000, the present value of the loan
at the beginning of period one is simply $10,000.
The starting_period is any whole number between one and the
number of periods determined for the second argument.
However, remember that if you don’t start at the first period,
you will need to adjust for the present value of the
loan/investment. We will be learning more about how to do
this further into this chapter.
The ending_period argument should also be any number
between one and the total number of periods. Of course, it will
need to be greater than or equal to the number chosen as your
starting period.
Finally, the type argument determines when the interest is
paid. If the interest is paid—whether from or to you—at the
start of the period, the type argument is set to “0.” If, on the
other hand, the interest is paid at the end of the period, the type
argument should instead be “1.”
Let us assume that our two-year loan’s interest is paid at the
end of the period and its details are entered into an Excel
worksheet as follows:

We can then use the CUMIPMT function to find the total

interest paid in the first year.
=CUMIPMT(B6, B7, B2, B8, B9, 1)
This returns -$415.97. Interest paid on a loan will always be
returned as a negative, since it is an outgoing payment from
the customer to their loan provider.
Remember that this is not the total value of payments that will
need to be made in year one, just the interest. Interest is the
cost of borrowing the loaned money from your loan provider,
but you also need to pay back the principal—initially
borrowed amount—of the loan. The PMT function covered
later in this chapter will help you to determine the entire
payment amount.

DB
The DB function is used to calculate the declining balance of a
depreciating asset. Depreciation is the process by which assets
lose their value over time. Many of us will have heard the
phrase, “As soon as you drive a new car off the dealership lot,
you’ve lost money.” That lost money is depreciation. Once an
asset is not “new” anymore, you can only sell it as “used,”
which will earn you less money back than you paid for the
asset to begin with. Depreciation hits an asset most severely at
the beginning of its life cycle, but once it is older, it will lose
less value each period. This is because with each period, the
asset will lose a percentage of its value, causing that value to
decrease over time, hence the name “declining balance.” As
the balance declines, so too does the depreciation charged
against the asset reduce.
Depreciation is used by any business that uses property, plant,
or equipment. A restaurant’s ovens will depreciate. A pottery
artist’s kiln will depreciate. Farmers’ milking equipment will
depreciate. It’s important that each of these businesses knows
how to account for that in their records and understands the
current value of their assets if they choose to sell them. What’s
more, international accounting law dictates that this must be
recorded in a business’s annual accounts.
It can also be helpful to track depreciation of your own
personal assets so that you understand when it is more
efficient not to sink any further costs into fixing something. If
you can calculate that your old dishwasher is losing value by
$50 every month, it hardly makes sense to spend $200 on a
repair, particularly if that repair won’t patch it up for long. You
would be better off washing dishes by hand for a while and
putting that money towards a new (to you) machine, if
possible.
The structure for using the DB function is:
=DB(cost, scrap_value, life, period, month)
The cost argument should be filled with the numerical
information or cell reference to the initial amount paid for the
item at the date of purchase. Scrap_value refers to the amount
of money for which the asset can be sold when it is no longer
useful. Scrap value may, in many cases, be $0. The life
argument is the number of periods for which the asset will be
depreciated. If a piece of machinery at a factory has a useful
life of five years, we use “5” as the life argument.
The period argument refers to the period we are currently
calculating. Take our imaginary piece of machinery. If it was
bought in January 2018, is depreciated monthly (including the
month of purchase), and we are currently calculating for
January 2022, we are on period four. The month argument is
optional. If left blank, the DB function will assume that annual
depreciation occurs over 12 months. However, this is not the
accounting policy of every business, and therefore, if
depreciation is applied on a bimonthly basis, the month
argument should be included and reduced to six.
Let us take our imaginary factory machinery. If it was bought
in January 2018, for $100,000, has a scrap value of $5,000
dollars, and a useful life of five years, we can calculate the
depreciation for the fourth year of use of the asset as:
=DB(100000, 5000, 5, 4)
Calculated for each period, this can be compiled in a
worksheet like so:

For cell B7, the function is used as:

=DB($B$3, $B$4, $B$5, B2)
Note the dollar ($) signs used on the cell references for cost,
scrap_value, and life, these set those references as fixed as
discussed in Chapter 3: Database Utilization. This means that I
only needed to drag the bottom right corner of that cell across
to F7 rather than typing out a DB function for each cell in row
7.
The remaining value of the asset is simply the value at the
beginning of the period minus depreciation. In period one this
is:
=B3-B7
In period 2:
=B8-C7
In period 3:
=C8-D7
From there, highlight cells C8 and D8 and drag the bottom
right to allow Excel to extrapolate the pattern and
automatically calculate the remaining values for cells E8 and
F8. Don’t forget to utilize these little tricks as you work your
way through any and all repeated calculations. They are a huge
time saver.

FV
The FV function enables us to calculate the future value of an
investment with a fixed interest rate. Let’s say that you decide
to invest $1,000 into a 10-year savings bond that offers an
Annual Equivalent Rate (AER) of 1%. To calculate the future
value of this investment in Excel, the function would look like:
=FV(0.01, 10, , 1000)
Note the blank argument; this will be explained below. This
returns a future value of $1,104.62.
The FV function has five possible arguments.
=FV(rate, number_periods, period_payment, present_value,
type)
However, only three of these arguments need be included for
the calculation to complete. If an argument is skipped and
further arguments occur after the space where it would have
been, it is necessary to include a blank-space argument in
order for the function to keep track of the meaning of different
variables.
The rate argument is, of course, the interest rate per period.
This is a vital argument that cannot be skipped. Here, this was
divided by 100 to convert from a percentage to a decimal.
Though that is not strictly necessary for the FV as it will
recognize percentages, not every function will recognize
percentages, so converting to decimal representation is a good
habit to get into. If your interest rate is given as an annual
figure, but interest is paid monthly, don’t forget to divide by
12.
The number_periods argument is, clearly, the amount of
periods across which interest is paid. This is also a crucial
argument that cannot be skipped. For the example above, I
assumed that interest was paid on the 10-year savings bond
annually. However, this will not always be the case.
Then, we reach the area of the function in which we have a bit
of flexibility regarding the arguments to include. The
period_payment refers to a specific numerical payment made
each period. These kinds of payments are available on certain
forms of company shares, mutual funds, or savings schemes,
but in general are unusual in investments and more common
for loans. For this reason, this argument is often omitted.
However, if it is omitted, the next argument absolutely must be
included.
The present_value argument represents the current value of the
investment at the time of investment. If omitted, it is assumed
to be zero and the period_payment argument must be used.
Finally, the type argument determines when interest is paid,
just as with the CUMIPMT function earlier in this chapter. A
“0” denotes that this occurs at the beginning of the period,
while a “1” represents this occurring at the end of the period. It
is not necessary to specify either.
The FV function can also be used to determine the future value
of a loan that is being paid off. However, in this case, the
period_payment value will need to be a negative number.

PMT
The PMT function enables you to calculate the total payment
—including principal and interest—paid per period of a fixed
interest rate loan. This will enable you to determine whether or
not you can afford the repayments necessary to pay off the
loan over a given number of periods.
The structure necessary for the use of the PMT function is:
=PMT(rate, number_payments, present_value, future_value,
type)
The rate is, as usual, the interest per period. The
number_payments is, quite self-explanatorily, how many
payments the repayment of the loan is split over. For instance,
a four-year loan repaid monthly has 28 payments. A two-year
loan repaid weekly would have 104 payments. The
present_value argument is the principal sum of money
borrowed.
The future_value argument is optional. This is the level to
which we wish to reduce the loan with our payments. If left
blank or omitted, it is assumed that we wish to pay off the loan
entirely to a balance of $0.
The type argument is also optional and indicates the day-count
basis to be used in the calculation. At this level, we will omit
this argument and allow Excel to default to its standard
settings.
Therefore, to calculate the payment amount needed to pay off
a $5,000 personal loan in two years at 7% APR using monthly
payments, our function would look like:
=PMT(0.00583, 24, 5000)
This shows us that we need to be capable of repaying
$223.85/month before committing to this loan schedule.
If instead, we decide to pay off the loan over three years:
=PMT(0.00583, 36, 5000)
We need to make monthly payments of $154.38. Though this
leads us to pay more interest in the long run, it might be more
accessible on a lower monthly income.

PV
The PV function calculates the present value of a loan or
investment. When working with money across spans of time,
the value of that money is variable. This is not related to
inflation, but rather due to the fact that having money now is
“better” than having the promise of money in the future. If you
have money in the present, you can use it, invest it, and so on.
But if you have to wait to receive that money, you are paying
the “opportunity cost” of not having it right now, i.e. you can’t
use it or invest it.
This concept becomes very important when calculating the
periodic values of assets and liabilities, such as those we have
seen earlier in this chapter. In the following chapter, we will
learn how to combine functions so as to use this PV function
as part of a larger calculation. For now, let’s take a look at the
arguments included in the PV function itself.
=PV(rate, number_periods, period_payment, future_value,
type)
As you can see, the PV function follows a familiar argument
layout. Rate is the periodic interest rate, located first as usual.
The number of periods is included as the second argument.
The periodic payment is the fixed payment per period, as can
be calculated using the PMT function above. The future_value
is once again optional and is assumed to be zero if omitted.
The only major difference is that, in this case, the binary
settings for type are reversed. This argument should be set to
“0” if payments occur at the end of the period and “1” if
payments are made at the beginning of the period. If omitted,
Excel assumes that payments are made at the period end.
As such, the calculation of the present value of an investment
at 1% periodic interest across five periods, with payments of
$250/period made at the end of the period would be calculated
using:
=PV(0.01, 5, 250)
Don’t forget that, in practice, each argument can be a cell
reference rather than a number.

RATE
The RATE function is used to calculate the interest rate
charged on a loan. It can also be used to determine the interest
rate that would need to be guaranteed to reach a specific saved
amount on a potential investment.
This can be useful to help you to decode those shopping
channel advertisements that claim you can get a product for
one lump sum or a number of “low, low monthly
installments!” Are those monthly installments really worth it if
they’re charging you a huge interest rate for the benefit of
being able to pay off the cost slowly? This is what’s often
referred to as a “poverty tax,” since people with higher income
levels will be able to afford the lump sum, while those with
less income end up paying more for the same product in the
long run (Kaplan, 2009). Financial literacy in this area can
help you to avoid the deeper of these sorts of pitfalls.
The structure for the RATE function is:
=RATE(number_periods, period_payment, present_value,
future_value, type, estimate)
The first two arguments you are most likely very familiar with
by now. However, it is important to note that in the case of
loans, the period_payment argument will need to be a negative
number for the RATE function.
Present value represents the present value of all future
payments to be made. This is the overall worth of the product,
the initial amount put into an investment, and it can be
calculated using the PV function. The future_value argument
is optional as it is generally zero for loans, and if this is the
case, it can be left blank or omitted. However, for investments,
the future_value argument represents the desired final
outcome, i.e. the amount of money we hope to have made at
the end of all periods of the investment.
The type argument should be set to “0” for payments
occurring at period end or “1” for payments occurring at
period start. This argument is optional and, if left blank or
omitted, payments will be assumed to occur at the end of the
period.
The estimate argument is also optional. It is your opportunity
to include your best guess at the interest rate, which will give
the function a level at which to begin its calculations, speeding
up and simplifying the function. If omitted, the estimate is
assumed to be 10% (0.1).
To calculate the interest rate on a product worth $1000 paid off
with 12 monthly payments of $110, the RATE function might
be used as follows:
=RATE(12, -110, 1000, , , 0.03)
Note the blank spaces left for the future_value and type
arguments when we wish to omit them but include a guess. As
it turns out, the interest on this deferred payment scheme is
5%.

Summary
As you have seen throughout this chapter, the financial
functions capable within Excel can be useful to people in all
walks of life, not merely accountants and economists. From
interpreting sales pitches to determining the affordability of
loan payments, the functions covered in this chapter provide
an introduction to the financial understanding that can be
gained through the use of Excel functions. This is by no means
the end of the financial story, however. Microsoft Excel
contains over 400 functions in total, and over 50 of these relate
specifically to monetary matters. The key at this stage is to
understand the usefulness of the six functions covered here.
Familiarizing yourself with the arguments commonly included
in financial functions will prove fundamental whether your
Excel journey progresses only to the end of this book or to an
accountancy degree. Remember that Excel will prompt you of
the order in which to enter the arguments, so you needn’t
spend hours memorizing the structure of each function.
Instead, the crucial part is to understand when and how to use
each of the financial functions to provide yourself with the
type of information that is most useful to you.
Chapter 9: Combining Functions
Functions are wonderful tools that help us to simplify any
number of complex calculations. However, that simplification
is somewhat defeated if we are required to enter heaps of
individual points of data in order to use the functions.
Likewise, typing the information directly into a function rather
than using cell references is a time-consuming effort which
only proves itself useful to that singular function. Because of
this, it is often extremely useful to combine or “nest” functions
within one another. To do this, we simply use one function as
an argument in another. Only the first—outermost—function
will need an equals sign preceding it to operate. This is simply
to denote that a function or formula is being used in a given
cell.
For example, we might be working with our worksheet of
household expenses for January 2021 as below.

Let’s say we wanted to find, to the nearest $100, the total

amount of January bills that were split between both Lisa and
Frank. Rather than carrying out a separate SUMIF calculation
followed by a ROUND function, both functions can be
completed in conjunction with one another as follows.
=ROUND(SUMIF(D2:D21, “Split”, C2:C21), -2)
Notice how the SUMIF function is completely enclosed within
the ROUND function. This will cause Excel to complete the
SUMIF first, as its value is needed as an argument in the
ROUND function.
The SUMIF finds the total of all spending amounts where the
“Who” column (D) is labeled “Split.” The ROUND function
then takes this total and rounds it to the nearest hundred, as
specified by its second argument according to the details in
Chapter 6: Mathematical Functions.
It is crucial that each function’s parentheses are individually
opened and closed. Without each nested function being closed,
Excel has no way of knowing which arguments belong to
which function and will, most likely, return an error. For
example:
=ROUND(SUMIF(D2:D21, “Split”, C2:C21, -2)
This is not a valid nested function. Firstly, one closing
parentheses is missing. Therefore, one of the nested functions
is incomplete. Secondly, a result of this omission is that the
SUMIF function now has too many arguments. Remember, a
SUMIF should be structured SUMIF(range, criteria,
sum_range), and even the sum_range argument is optional.
Therefore, the accidental inclusion of the “-2” argument will
cause this function to fail before the ROUND function is even
attempted.
Keep in mind that for every function, you should have an
opening “(” and closing “)” parenthesis, also known as
“brackets.” As your uses of functions grow more and more
nested, it is vital that you keep track of these pairings.
For instance:
=QUOTIENT(ROUND(SUMIF(D2:D21, “Split”, C2:C21),
-2), 2)
This function is dividing our previous function in two to
determine roughly how much Lisa and Frank each spent on
split costs in January 2021, assuming a 50:50 ratio of shared
expenditure. This figure might, for instance, be needed to
decide on household and personal budgets for February 2021.
Notice how we have three functions in use, and therefore we
must have three pairs of parentheses.
Likewise, it is easy to forget to place commas between
arguments when nesting functions, but it is just as vital as the
use of parentheses. The use of commas enables Excel to
differentiate between each argument of a function. Therefore,
even if that argument is another function, that comma is still
needed. Note how after the first two closing parentheses, a
comma is used before continuing the next argument of the
function one layer out. Imagine that we instead used:
=QUOTIENT(ROUND(SUMIF(D2:D21, “Split”, C2:C21) -2)
2)
This is not a valid function. Here, everything is read as a
single argument of the QUOTIENT function due to the lack of
commas.
This point becomes particularly important to remember when
a nested function is not the first argument in the function one
layer outside it. In these cases, there is the opportunity to
forget the comma at both sides of the internal function!
Suppose that we have only the following information about a
personal loan:

If we wanted to calculate the total interest which would be

paid in year one, our combined function might look like:
=CUMIPMT((B2/B3), B4, PV((B2/B3), B4, B5), 1, 12, 0)
Here, I have calculated the present value of the loan using a
nested PV function. This function is separated from the other
arguments, not only by its bracket but by the commas at either
side.
Notice that mathematical operations can also become
arguments as I’ve done here, dividing B2 by B3 to get the per
period interest rate. This could also be achieved using the
QUOTIENT function. These operations also need to be
contained within their own parentheses and separated from
other arguments with commas. I know that all of this opening
and closing of brackets can feel daunting, but stick with it. It
really does save time in the long run, particularly when the
nesting becomes more complex.
For instance, an outer function’s arguments may be entirely
comprised by other functions. This might happen, for example,
if a math teacher has several freshman classes and wishes to
know the average top grade across all of freshman year. This is
completed in the image below.

As you can see, the function used is:

=AVERAGE(MAX(A:A), MAX(B:B), MAX(C:C),
MAX(D:D))
Here, each argument of the AVERAGE function is itself a
function. The MAX functions are used to find the highest
score in each freshman class by being applied to the full length
of each column; it will automatically ignore blank or non-
numeric cells. Then the average/mean of those highest grades
is computed.
This is just one example of where a number of functions may
be combined at the same level. That is to say that each MAX
function opens and closes its parentheses before another MAX
function begins. Therefore, each of these is an argument in the
AVERAGE function at the same level.
However, it is possible to combine functions within one
another to an even greater extent, creating more than the two
layers demonstrated above.

Nested IF Functions
One of the most common uses of nested functions is in the
value_if_false argument of IF functions. To remind you, the
structure used with an IF function is:
=IF(logical_test, value_if_true, value_if_false)
We can use combined IF functions to place layers of
conditions on the values assigned to the cell in question.
For instance, if a teacher had an Excel workbook of the
percentage scores of each student in each of the classes that
they taught, they might wish to use nested IF functions to
convert these grades from numerical percentages to the letter
grades that are more commonly sent home on report cards. In
that case, assuming “Score” as a stand in for the cell reference
of a student’s percentage grade, the combined function could
be:
=IF(Score>89, “A”, IF(Score>79, “B”, IF(Score>69,“C”,
IF(Score>59, “D”, “F”))))
To break this down, let us begin at the outermost IF function.
If a student’s percentage score is 90% or above, they will
receive an A. But what happens if their score is 89% or lower?
In that case, Excel will continue to use the second outermost
IF statement. Here, if a student’s score is greater than 79%
they will receive a B. Remember, we would have already
assigned an A if they had scored higher than 89%, so a B
grade will only be given if a student’s score is between 80 and
89 percent.
If the student has not met the criteria for a “true” value in
either of the first two IF functions, we proceed to the third. If
their score is 70% or greater, they will receive a C. If not, the
innermost IF function comes into play. If the student’s score is
greater than 59%—i.e. 60–69%—they will receive a D, and if
not, they will receive an F. In this way, nested IF functions can
be used to create a system of hierarchical filters and outcomes.
Take a moment to review how the parentheses and commas
are used in this example.
Nested IF statements can almost always also be used in the
reverse order, provided that we alter our logic argument
slightly. For instance, with the grading system above, rather
than beginning with As, then testing for Bs, then Cs, etc, we
could instead begin by assigning a failing grade if needed and
work our way up.
=IF(Score<60, “F”, IF(Score<70, “D”, IF(Score<80, “C”,
IF(Score<90, “B”, “A”))))
This achieves the same grading system in the reverse order of
IF functions. Now we are testing the student’s percentage
score to check if they are below the cut-off for the next-highest
grade bracket. If they are below this value, they receive the
current level IF function’s true value, and if they are equal to
or above this number, the next level of IF function is engaged.
Notice how the logic argument had to swap from a “greater
than” operation to a “less than” operation. This is due to the
progression of the statements at each level. If we were to try,
in the first IF function, to use something like, “Score>59, D,”
then every student who received a grade 60–100% would
receive a D without the core being further filtered.
When you use nested IF statements such as these, be sure to
test them with a variety of values to ensure that they perform
as anticipated before implementing them across the worksheet.

IFERROR
The IFERROR function allows you to replace Excel’s standard
error returns with a customized string of text. For this reason,
it is often used as the outermost layer of a complex nested
function. The structure for use of this function is:
=IFERROR(value, value_if_error)
For example, if a business is trying to determine how much
profit per person is made on each project, it may have profit in
column A and the number of staff directly involved in column
B. However, if some of the business’s products are created or
distributed in a fully automated process, this number may be
zero. Rather than allowing these situations to return an error
code—since dividing by zero is impossible—an IFERROR
function can be used as follows:
=IFERROR(QUOTIENT(A2,B2), “Automated process”)

Summary
Combining or nesting functions is a versatile method of
completing complex calculations or logical functions.
However, a few common mistakes are made when this
combination is implemented in practice. You need to
remember to open and close a set of parentheses for each
function used so that Excel understands what arguments
belong to what function. Likewise, it is vital that you
remember to separate arguments within a function with
commas, regardless of whether the argument is a number, a
cell reference, or yet another function.
Don’t beat yourself up if your nested functions receive a lot of
error messages and pop-up windows while you are
familiarizing yourself with this way of thinking. At first, it can
be a bit confusing to determine exactly how Excel reads these
combinations and figure out how to implement nested
functions to receive your desired result. Stick with it. If you
follow the key rules and remember to test your functions with
a variety of values, the possibilities are endless, making them
more than worth the effort.
Nested functions will allow you to tailor the functions you
have already learned to a wider variety of purposes. So, keep
going!
Chapter 10: Review
Reading about how to use Microsoft Excel is a good place to
start, but it cannot be where your learning journey ends. There
is no substitute in this process for just sitting down and
working through some examples. Only hands-on learning can
really help you to absorb the true uses of Excel and build that
sense of competency that is necessary to feel comfortable
using what you have learned in your day-to-day life. In fact, a
study by Higher Education researchers in 2015 found that
practical learning is over 40 times more effective than purely
theoretical learning when it comes to knowledge retention
(Ekwueme et al., 2015)!
For this reason, this chapter has been included to help you to
get to grips with using Microsoft Excel for yourself. Below,
you will find three practical exercises for you to try out. Each
of these provides some basic information to get you started,
along with step-by-step details of the requirements of the
exercise. As you will see, the guidance that you are given will
decrease with each exercise so that you can practice figuring
out how to implement various calculations yourself.
At the end of this chapter, you will find images of a potential
solution for each exercise. However, keep in mind that each
Excel user will have their own personal preferences regarding
layout, formatting, and function usage. For this reason, my
sample solutions may look slightly different than the finished
product you come to on your own. This does not mean that
you have done anything wrong! Check that the figures and
charts calculated and created are similar, go back and check
anything that is definitely wrong, and most importantly, be
kind to yourself. Learning any new skill can be daunting,
learning one that is heavily number- and software-based even
more so, so everyone will make mistakes. Just keep practicing.

Exercise 1
CompSupp4U, a computer supplies business, is trying to
decide whether they can afford to take out a $100,000 loan to
extend their warehouse. The management of the company
wants to know that they can afford repayments on the loan,
even if their sales do not immediately rise as a result of the
increased space. Therefore, they are basing the decision about
the loan on the sales information from June 2021.
CompSupp4U sold 10 product types in June 2021. Each of
these products was categorized as an A, B, or C product. Type
A products were sold at a 5% mark-up from cost price. Type B
products were sold at a 10% mark-up, and type C at a 15%
mark-up. Additionally, a sales tax of 21% was levied on all
products.
The table below shows the information that we know thus far
about CompSupp4U’s June sales:

What we know about the proposed loan is summarized as

follows:

Management is willing to sign off on the loan, provided that

the monthly payments would be worth less than 41% of profits
from June 2021.
Create an Excel worksheet in which you will:
A. Calculate profit for June 2021.
B. Determine the monthly payment which would be due on
the 15-year loan.
C. Assess whether or not CompSupp4U can afford the loan.
Guidelines:
● Open a new Excel workbook and save it as
“CompSupp4U.”
● Title the first worksheet “June 2021 Sales.”
● Create a second worksheet titled “Loan.”
● Transfer the known information into each worksheet.
Adjust the formatting of the cells and columns that
contain monetary amounts using the drop-down menu in
the “Number” section of the toolbar.
● In the “June 2021 Sales” worksheet, title a column as
“Mark-Up.” Use nested IF and EXACT statements to
assign the appropriate mark-up to each product type.
Don’t forget to convert the percentage to decimal form to
simplify later multiplication. Drag and copy the first
row’s function to the remaining product types.
● In a column titled “Sales Price,” determine the price per
item by adding one to the mark-up and then multiplying
this number by the cost price. Drag and copy the first use
of the function or formula down the column to the other
product types. Format this column for the appropriate
currency.
● In a column titled “Total,” calculate the total sales
amount for each product by multiplying sales price by
quantity. Remember to format this column for currency.
● Using a SUM function, find the subtotal of all sales.
Label this figure using the neighboring cell.
● Calculate the sales tax applied by multiplying the
subtotal by the sales tax rate. Don’t forget to convert the
tax rate to a decimal. Label this figure using the
neighboring cell.
● Using a SUM function, find the total sales including tax.
Label this figure using the neighboring cell.
● In a column titled “Total Cost,” determine the output cost
on each type of product by multiplying cost price by
quantity. Drag and copy the first use of this function or
formula down the column.
● Using a SUM function, calculate the total cost for all
products sold. Label it.
● Calculate profit by subtracting total cost from total sales,
and label your answer.
● Move to the “Loan” worksheet. Calculate the number of
payment periods across which the loan is spread by
multiplying the number of years by 12. Label your
answer using a neighboring cell.
● Use a function or formula to calculate the interest rate
per period by dividing the APR by 12. Don’t forget to
convert to a decimal to simplify later use of this figure.
● Use the PMT function to calculate the monthly payment
needed to repay the loan.
● Determine what percentage of June 2021 profits the loan
payments would be by dividing the payment amount by
the profit calculated in the “June 2021 Sales” worksheet.
Don’t forget to format the cell for a percentage value.
● Use an IF statement to determine whether or not this
meets management’s condition for the loan.

Exercise 2
Benny’s Greengrocers, a small business with eight employees,
has decided to calculate their payroll using Microsoft Excel,
beginning in the first week of January 2021. The employees
are named Annie, Bob, Catherine, Delilah, Ed, Francis, Gwen,
and Harriet, and they are all paid weekly.
Annie and Delilah are teenagers who work there part-time
after school. They each work 15 hours a week and are paid
$7.25 per hour. Bob, Catherine, Ed, and Francis each have
flexible hours to work around their childcare needs. They earn
$10.80 per hour, and in the first week of January 2021, they
worked 20, 23.5, 36, and 27 hours, respectively. Gwen and
Harriet are the only full-time employees. They each work 40
hours at the greengrocers every week and are paid the same
hourly wage as the other adults. From their gross pay, the
company must deduct a 6.2% social security tax.
Create an Excel worksheet in which you will compute:
A. Each employee’s net pay
B. The average net pay of employees who work more than
21 hours per week
C. A pie chart showing the proportion of total pay going to
each employee
Guidelines:
● Create a new Excel workbook and save it with the title
“Payroll.”
● In the first row, enter the column headings “Employee
Number,” “Employee,” “Hourly Wage,” “Hours
Worked,” “Gross Pay,” “Tax,” and “Net Pay.” Ensure that
all text in the first row is in bold.
● In the next row of the “Employee Number” column,
enter “BG001” followed by “BG002” in the cell below.
Highlight these cells and drag and copy this information
until you have an employee number for each worker.
● Enter the employee’s names in the “Employee” column.
Since this is the first week of an official payroll, don’t
worry about which employee number is associated with
which worker.
● Continue to enter all of the information provided above.
● Format the “Hourly Wage,” “Gross Pay,” “Tax,” and
“Net Pay” columns for accounting, using the toolbar.
Adjust for currency if needed.
● Using a formula or function, calculate gross pay as hours
worked multiplied by hourly rate. Remember to drag and
copy this formula or function down the column.
● Calculate tax as tax rate multiplied by gross pay. Drag
and copy this operation down the column.
● Compute net pay for each employee as gross pay minus
tax.
● Use an AVERAGEIF function to find the average net pay
of the employees who worked more than 21 hours that
week.
● Insert a pie chart of gross pay per employee. Edit the title
of this chart to read, “Proportional Pay Per Employee”
and ensure that the legend shows which employee is
represented by which slice of the pie chart.
● Save your work.

Exercise 3
The International Book Company (TIBC) is a franchise which
requires all locations to keep sales records of the types of
books sold. This information is fed back to headquarters and
determines the types of special offers that are enacted in the
following calendar year.
For this reason, the Dallas branch of TIBC has decided to
implement this sales record through Microsoft Excel. For each
book sold, a record is kept of the date of sale, the type of book
sold, the number of books sold, the type of customer, and the
price before and after sales tax.
Books can be classified as realistic fiction, fantasy, general
non-fiction, or textbooks. If more than 50 textbooks are sold at
once, the customer is assumed to be an educational business
and is charged sales tax at 1%; otherwise, sales tax is charged
at 3%. In the records, books of separate types are always
recorded separately, even if their sale occurs in the same
transaction.
For simplicity of record keeping, fiction of any kind is priced
at $7/book, general non-fiction at $10/book, and textbooks at
$20/book.
Assume that the image below shows information about the
sales for week one of January 2021 that has been transferred in
from a database which automatically updates when sales are
processed.
Create an Excel workbook in which you will:
A. Complete the remainder of the empty cells above,
making use of functions and formulas wherever possible.
B. Create a pivot table currently showing the total books
sold per genre/type across all dates.
C. Create a pivot chart in 2D column style which shows the
total books sold per type, excluding textbooks.
Guidelines:
● Open a new workbook and save it as “Jan 2021 Sales.”
● Title your worksheet “Week 1.”
● Enter the provided information according to the image
above, remembering to format columns according to the
type of information that they contain.
● Using nested IF statements, allocate the appropriate price
per item to each row of sales.
● Using an appropriate formula or function, calculate gross
price as price per item times number of books.
● Using an appropriate function, allocate the appropriate
customer type to each sale.
● Using an appropriate function, calculate sales tax as
gross price multiplied by the appropriate rate of sales tax
based on the customer type.
● Implement a function or formula to find net price as
gross price plus sales tax.
● Once complete, convert this array of cells to a standard
(flat) table.
● Create a pivot table, using “Date” as a filter field, “Type
of Book” as a row field, and “No. Books” as a value field.
● Create a pivot column chart, remembering to exclude
textbooks. Format the chart appropriately.

Possible Solutions

Exercise 1

In this solution, the mark-up for each product was assigned

using the function:
=IF(EXACT(“A”, C3), 0.05, IF(EXACT(“B”, C3), 0.1, 0.15))
This first tests the category cell for the value “A” and assigns a
5% if found. The 5% mark-up has been divided by 100 to
decimalize the value for future multiplication. If “A” is not
found, the second EXACT function within the nested IF
statement checks for “B.” If found, a 10% mark-up is applied,
and if not—since there are only three possible values for
category, and two have already been tested for—the final
mark-up percentage, 15%, is returned. This was then dragged
and copied to the remainder of the relevant cells as detailed in
the exercise guidelines.
Sales price was calculated, in the first row, using:
=(1+E3)*B3
One must be added to the mark-up percentage to ensure that
we are calculating the entire sales price—in the case of the
first item type, 105% of cost—rather than the mark-up alone.
This calculation could just as easily be achieved using:
=PRODUCT((1+E3), B3)
This, however, requires more careful use of parentheses.
The total price is similarly calculated, in the uppermost
instance, using:
=F3*D3
Once again, a PRODUCT function could implement the same
calculation. Provided that your figures are correct, the method
by which you achieve those numbers is merely personal
choice.
The subtotal of sales was calculated using:
=SUM(G3:G12)
As you can see, this includes all of the relevant per-product
totals. It is vital that these results be labeled, given that they
are different from what is implied by the column heading. This
need for labeling is also why total cost is separated from total
sales by a largely blank column, but we’ll get to that.
Sales tax was calculated using:
=G14*0.21
The tax rate given in the exercise details was 21%, but this
must be converted to a decimal for the purposes of
multiplication. This was done by dividing by 100. Though I
did this in my head, you might have chosen to implement this
division as a nested QUOTIENT function which might look
like:
=PRODUCT(G14, QUOTIENT(21, 100))
Neither option is “more correct.”
Total sales was then calculated using:
=SUM(G14:G15)
Given that there are only two cells involved in this sum
function, you might have instead chosen to instead use a
formula including the addition operator (+). This might look
like:
=G14+G15
Total cost, for the first product type, was determined as:
=PRODUCT(B3, D3)
A formula could have been used to achieve the same result
which might have been:
=B3*D3
As usual, this should have been dragged and copied to the
remaining relevant cells.
The total cost for all products was calculated using a SUM
function as specified in the exercise guidelines. In this
solution, that SUM function was:
=SUM(I3:I12)
Profit was then calculated using the formula:
=G14-I14
This concluded the necessary requirements for section A of the
exercise.

Having transferred to working on the “Loan” worksheet, the

total number of periods for the loan was calculated using:
=B3*12
A function for the same equation could have been:
=PRODUCT(B3, 12)
The interest per period was determined using:
=(B2/12)/100
This might also have been achieved using nested QUOTIENT
functions such as:
=QUOTIENT(QUOTIENT(B2, 12), 100)
If this was your chosen method, you would have needed to be
mindful of your use of parentheses and commas.
Payment per period was calculated in this possible solution as:
=PMT(B5, B4, B1)
Refer to Chapter 8: Financial Functions for further details
regarding the structure and use of the PMT function.
Payment per period was expressed as a percentage of monthly
profit using:
=ABS(B6/‘June 2021’!I15)
Note the way that a cell from another sheet in the workbook is
referenced, using an exclamation point (!). Thankfully, this
does not need to be remembered and typed out, as simply
navigating to the sheet and clicking the relevant cell will enter
it into your function.
The ABS function above was used for the purposes of
excluding the minus sign which proceeds the monthly
payment amount. Furthermore, the cell was formatted for the
inclusion of a percentage according to the guidelines in the
exercise.
Finally, the management approval or disapproval was
determined, in this solution, using:
=IF(B8>40%, “No”, “Yes”)
Therefore, if the payment per period expressed as a percentage
of profit had been more than 40%, management would not
have approved. Given that this criteria was not met,
management did give their approval and the function returned
a “Yes” value.

Exercise 2
In this solution, the gross pay for each employee was
calculated by first creating a function for Annie’s pay and then
dragging and copying this function down the neighboring
cells. The function used was:
=PRODUCT(C2,D2)
This could just as easily be calculated using the mathematical
operator “*.”
Tax was calculated in a similar manner using:
=PRODUCT(E2, (6.2/100))
The division by 100 was necessary to convert the taxation rate
from a percentage to a decimal. This is an easy mistake to
make, but also an easy one to recognize as without this
adjustment the employees would be taxed more than their
entire gross pay—thankfully an impossibility!
In this solution, net pay was calculated in the first row by:
=(E2-F2)
This was, of course, dragged and copied down the remainder
of the relevant cells in column G. Strictly speaking, the
parentheses are not necessary here, but I find it a good habit to
contain any operation within parentheses to simplify my
thought process when it comes to more complicated formulas
and functions.
The average pay of employees who worked less than 21 hours
for that week was calculated using AVERAGEIF, as specified
in the exercise guidelines. In this solution, this looked like:
=AVERAGEIF(D2:D9, “>21”, G2:G9)
The pie chart was compiled according to the guidelines found
in Chapter 4: Data Visualization.

Exercise 3

In the flat table section of this solution, various IF and

EXACT functions were used. Remember, these are merely
possible solutions, so if you have achieved the same numerical
answers in a different way, that’s okay.
To allocate the price per item, the function used in the first row
of data was:
=IF(EXACT(B3, “Realistic Fiction”), 7, IF(EXACT(B3,
“Fantasy”), 7, IF(EXACT(B3, “Gen Non-Fic”), 10, 20)))
To break this down, we are first testing to see if the cell in the
Type of Book column contains the text string “Realistic
Fiction.” Since we are assuming that this information is pulled
from an automatically generated database, we can use the
EXACT function in this column without fear of interfering
typos or invalid entries. If the book type entered in cell B3 is
indeed “Realistic Fiction,” then a price per item of $7 is
entered. If this is not the case, we continue to test with another
(nested) IF statement. If the type of book is “Fantasy,” a price
per item of $7 is applied. If not, we proceed to the innermost
IF function. If the book type is listed as “Gen Non-Fic,” the
price applied is $10. If not, since there are only four possible
genres and we have already tested for three, we can assume
that any remaining books must be textbooks and apply a
$20/book price.
To determine the gross price, a simple formula is used, and
then dragged and copied to the rest of the column:
=(C3*D3)
Keep in mind that this could also be calculated using a
PRODUCT function.
To apply the appropriate customer type, nested IF and EXACT
statements were once again used.
=IF(EXACT(B3, “Textbook”), IF(D3>49, “Business”,
“Private”), “Private”)
Here, the inner IF statement is used in the value_if_true
argument, and therefore the value_if_false argument is
duplicated in each IF function. This is because, we are using
the innermost IF statement to apply a second criteria to the
logic test rather than creating a hierarchical test. This
combined function checks that a sale was both of textbooks
and of a quantity above 49 before applying the value_if_true
argument, “Business.” In all other cases, “Private” will be
applied.
This test can, of course, be reversed, testing for each of the
three private customer genres, then testing for textbooks,
followed by a test for quantity. You could even put the
quantity criteria first, and test the genre after; the choice is
yours. You might not even have used an IF statement but
instead decided to filter the list to show only the relevant
textbook data. Once again, double check your answers and
only worry about the method if something has gone wrong.
In this exercise, unlike in Exercise 1, we do not have the
luxury of a standard tax rate. Therefore, before implementing
sales tax, we need to determine at what rate to apply this levy.
Since the worksheet format presented in the details of the
exercise did not provide us with a separate column in which to
enter a function for the sales tax rate, this rate must also be
multiplied by the gross price before being entered into the
appropriate column. In this solution this has been achieved in
the first row using:
=(E3*IF(EXACT(F3, “Private”), 0.03, 0.01))
To break this down, since we only have two possible tax rates,
only one IF function is needed. If our customer type is private,
a 3% tax is levied; this is converted to a decimal form for the
purposes of multiplying with the gross price in cell E3. If the
customer is not private, they must be a business, in which case
we are using the 1% tax rate. This is again converted to a
decimal for the purpose of multiplication.
Net price is simply gross price plus sales tax. In this solution,
this was calculated in the first row using:
=SUM(E3,G3)
This was then dragged and copied to all relevant neighboring
cells. Keep in mind that this could just as easily be achieved
using an addition operator (+).

The pivot table is inserted using the field details given in the
exercise and as detailed in Chapter 5: Pivot Tables.

The pivot chart is also implemented using the exercise

guidelines and the details in the previously mentioned chapter.
Additionally, the total number of textbooks sold is excluded
using the drop-down menu of the x-axis. Within this list of
options, “Textbooks” can be unchecked to be removed from
the chart. Remember that the chart needs a title, and the y-axis
needs to be labeled in order for the scale of the graph to be
appropriately read. Without this label, we might be led to
believe that the store sold 11,000 realistic fiction books rather
than 11. Scale and labels are crucial.

Summary
The exercises included in this chapter allowed you to review
and practice a variety of the formulas and functions covered
throughout this book in a practical, real-world simulation.
They were not intended to be a test, so if you struggled, that’s
okay. Take the time to read back over the chapter sections
relating to the areas in which you had difficulty. Review the
possible solutions included and attempt the exercises again.
The goal here is progress, not perfection. We all need learning
opportunities through which to grow and progress. If things
still aren’t making sense, seek out more information, make use
of the many Excel templates and tutorials available online, and
keep coming back to these exercises until they make more
sense. Your future self will thank you for your perseverance.
Conclusion
Microsoft Excel has become a larger and larger part of the
business world as society in general leans further and further
into our dependence on technology. But what is often forgotten
with regard to Excel is the huge benefits it has had in the daily
lives of so many non-business-savvy individuals. I hope that
this book has helped to show you how this can be the case.
Throughout this book, we’ve seen the ways that Excel can be
used by homemakers, teachers, construction workers, and
scientists, and in pretty much every career path or lifestyle
possible. There really is no walk of life where the potential to
implement Microsoft Excel has not grown and developed in
the more than 35 years since the program was first launched.
Despite this, there still tends to be a sense of dread
surrounding learning how to use it. We all assume that you
“can’t teach an old dog new tricks,” so we persevere with old
habits when new technology comes along, but there comes a
point at which it is time to accept the changing nature of the
environment around us. The fact that you have reached the
conclusion of this book shows that you have reached that
point, and I commend you for getting this far. You deserve a
pat on the back for taking the—often daunting—leap into
learning a new skill.
You have observed throughout the chapters of this book that
Microsoft Excel is by no means singularly for business people.
You have learned the ways in which your data can be
effectively formatted for easy or protected access; you have
mastered the means to searching, filtering, and summarizing
large sets of data—including those ever convincing visual
options; and you have gained a solid footing in the use of pivot
tables and charts to achieve all of these goals in a flexible
manner.
What’s more, you now have an extensive intermediate-level
knowledge of the functions available in Microsoft Excel
across mathematical, statistical, and financial calculations, as
well as the ways in which these can be combined with
reference functions to achieve complex calculations,
hierarchical condition criteria, and easily understood error
messages. Beyond this, you have even practiced these skills in
a hands-on manner through the exercises in the final chapter.
Let me reassure you, these achievements are not to be sniffed
at. It’s okay if you have struggled, ended up confused, or
closed this book and walked away at times. You’ve made it
here, and you should be proud of yourself.
Now, the time has come to put that knowledge into action—to
go out into your daily life and start to use your knowledge of
Microsoft Excel in the real world. Start a household budget.
Sell your crafts online. Use charts in your next sales pitch at
work. Make educated investments. Determine the correlation
between your kid’s sugar intake and their energy levels, or
simply use it to keep track of your shopping list. Whatever you
choose, put your new knowledge to use.
Many of us have heard the saying “use it, or lose it!” When it
comes to many skills learned in adulthood, this is sadly the
case. You have put the effort into familiarizing yourself with
this knowledge; don’t allow it to slip to the cobwebbed
recesses of your mind.
You put in the time, you put in the work, you deserve to reap
the rewards. So, implement your Excel skills in whatever way
you see fit and enjoy the productivity, the knowledge, and the
confidence that comes with them. Experiment with the
templates available in Excel to find your personal preferences
of how to format your data. Tailor the knowledge you have
gained within the pages to the circumstances of your own life.
Download the Microsoft Excel app to your phone to access
important data, lists, and charts on the go to make educated
financial decisions in the blink of an eye. These examples are
only the tip of the iceberg.
Plan your holiday itinerary and budget; create to-do lists,
planners, reports or schedules. No matter your life
circumstances, the possibilities for the use of Microsoft Excel
are endless. It’s simply a matter of having the bravery to make
the necessary attempt. And you have it! You’ve shown time
and time again throughout this book that you have it. So, make
use of it. Apply that same gumption that got you to pick up
this book to your daily life, and excel with Excel!
I hope that you will join me in the next book of this series to
get to grips with the more advanced uses of formulas and
functions in Microsoft Excel. See you then!
References
Ekwueme, C., Ekon, E., & Ezenwa-Nebife, D. (2015). The
Impact of Hands-On-Approach on Student Academic
Performance in Basic Science and Mathematics. Higher
Education Studies, 5(6), 47.
https://doi.org/10.5539/hes.v5n6p47
Kaplan, G. (2009). The Poor Pay More. Robert Wood Johnson
Foundation.
Schonlau, M., & Peters, E. (2012). Comprehension of Graphs
and Tables Depend on the Task: Empirical Evidence from
Two Web-Based Studies. Statistics, Politics, And Policy,
3(2). https://doi.org/10.1515/2151-7509.1054
Tal, A., & Wansink, B. (2014). Blinded with science: Trivial
graphs and formulas increase ad persuasiveness and belief
in product efficacy. Public Understanding Of Science, 25(1),
117-125. https://doi.org/10.1177/0963662514549688