MICROSOFT EXAM OBJECTIVES COVERED IN THIS CHAPTER:
AVERAGE
()
, MAX()
, MIN()
, and SUM()
functionsCOUNT()
, COUNTA()
, and COUNTBLANK()
functionsIF()
functionRIGHT()
, LEFT()
, and MID()
functionsUPPER()
, LOWER()
, and LEN()
functionsCONCAT(
) and TEXTJOIN()
functionsWhen you type a formula in a cell, Excel makes it easy to refer to specific cells and ranges. You can add one of three types of references: relative, absolute, and mixed. As you'll see in this chapter, Excel also allows you to refer to a cell range and a table within a workbook.
Next, I will show you how to perform simple calculations and operations using built‐in calculation commands that you type into the Formula Bar. These include calculating the average, minimum, maximum, and sum of a group of cells that contain numbers, counting cells in selected cells or a range, and performing conditional operations with the IF()
function.
Finally, you'll learn how to format and modify text in a cell by using a variety of functions. This includes how to return one or more characters at the right, left, or midpoint area with a text string; change text in a cell to uppercase and lowercase; display the number of characters in a text string; and combine text in different strings into one string.
Excel labels each cell with the column letter and then the row number, such as A5. This identification system makes it easy for you to refer to a cell when you enter a formula in another cell. For example, when you're in cell D9 and you want to multiply the number in cell D9 by 3, all you need to type is =(D9*3) in the Formula Bar.
You can create three different types of references:
$A$5
. When you add an absolute cell reference in a formula, the formula refers to a fixed point in the worksheet. For example, if you type the formula =($D$3*3) in cell D9 and then copy cell D9 to cell G9, cell G9 still calculates the formula using cell D3 (see the Formula Bar in Figure 4.2).$A5
. When you add a mixed cell reference in a formula, you specify that you want to refer to a value in a fixed column or row in a worksheet. For example, if you type the formula =($D3*3) in cell D9 and then copy cell D9 into cells D10 and D11, Excel multiplies the cells in cells D3, D4, and D5 and places those results in cells D9, D10, and D11, respectively (see Figure 4.3).After you insert a reference into a formula, you may need to change the reference type from one type to another. Excel saves you some time by allowing you to change the reference type quickly. Here's how to do this:
$A$2
, as shown in Figure 4.4.A$2
.$A2
.As you change each formula reference type in the Formula Bar, the new formula also appears in the cell.
You don't need to use column names and numbers when you refer to cells in a formula. You can also refer to a named cell range or a named table within a formula. Here is an example that you can follow:
The total of all the numbers within the table appears in the cell, and Excel selects the cell directly below it. When you click the cell in the table, as shown in Figure 4.5, you see the formula in the Formula Bar.
Excel includes a variety of built‐in functions for calculating numbers in a spreadsheet to make your life easier. For example, having to average numbers in a column by typing all the numbers within a formula is inefficient at best and tedious at worst.
Let Excel do the work for you when you use one or more of the following calculations in a formula:
You may also have times when you need to count instances in a worksheet. For example, you may want to find out how many blank cells are in a worksheet to confirm that you haven't missed adding any important data. Excel includes three counting functions.
If you need to go further and find out how many numeric values reach a certain threshold to meet a condition, such as where numbers are too hot or too cold, Excel includes the IF()
function.
Excel has four standard calculations built in: AVERAGE()
, MAX()
, MIN()
, and SUM()
. As with all other calculations you add to a formula, you need to precede any one of these arguments with the equal sign (=
) in the Formula Bar.
The average is also known as the arithmetic mean, if you remember your middle school math. You can take the average of a group of cells in a worksheet, within a range, or within a table. You can also take an average of two numbers.
In an empty cell, type =AVERAGE and then the cell range within the worksheet or table in parentheses. For example, if you type =AVERAGE (D3:D7) in the Formula Bar, as shown in the example in Figure 4.6, and then press Enter, the average of all five numbers in the column appears in the cell.
After you press Enter, Excel selects the cell directly below the cell with the average number. Click the cell with the average number to view the formula in the Formula Bar.
You can average as few as two or as many as 255 numbers by typing =AVERAGE and then entering up to 255 numbers within the parentheses. For example, if you type =AVERAGE(1,10,40,100,400) in the Formula Bar (see Figure 4.7) and then press Enter, the average of all five numbers appears in the cell.
If you need to find the largest number in a range of cells or cells within a table, the MAX()
function is the tool you need. After you select a blank cell to add the formula, you can add the MAX()
function in the Formula Bar in one of two ways:
If you need to find the smallest number in a range of cells or cells within a table, use the MIN()
function. After you select a blank cell to add the formula, you can add the MIN()
function in the Formula Bar in one of two ways:
When you need to summarize numbers or, more often, numbers in a range of cells, Excel makes this task easy with the SUM()
function. After you select a blank cell to add the formula, you can add the SUM()
function in the Formula Bar in one of three ways:
When you need to know how many cells in a worksheet or table have numbers, cells that are not empty, or cells that are empty, you don't have to go through a worksheet or table and count them yourself. You can use the three built‐in counting functions.
If you need to count how many cells in a range or cells within a table have numbers, use the COUNT()
function. After you select a blank cell to add the formula, you can add the COUNT()
function in the Formula Bar in one of three ways:
You can use the COUNTA()
function to count the number of cells that are not empty within a range in a worksheet or in a table. After you select a range, add the COUNTA()
function in the Formula Bar using one of the following methods:
The COUNTBLANK()
function counts the number of empty cells within a selected range in a worksheet or table.
Once you select the range, add the COUNTBLANK()
function in the Formula Bar by typing =COUNTBLANK( and then type the cell range, or you can select a range of cells in the worksheet or the table. Excel automatically adds the cell range in your worksheet, so all you have to do is type ) to close the formula and then press Enter. You can see the result in the cell shown in Figure 4.13.
If you've ever taken a computer programming class or even used a spreadsheet program before, you know that the if‐then
operation is one of the basic operations that you can use to find out if text or a numerical value is true or false.
You can easily add an if‐then
condition to a cell in a worksheet or table by using the IF()
function. There are two ways to compare values using the IF()
function: by having Excel tell you if a cell contains text or a number or if a numeric value meets the condition.
Here's how to have Excel tell you if a cell contains the text you want to show:
Excel shows FALSE
within the cell, as shown in Figure 4.14.
To show that a numeric value meets a certain condition, such as the value in one cell being smaller than another, follow these steps:
In the table, Excel shows results not only in cell R3 but also within all cells within column R (see Figure 4.15).
Excel copied the formula into all cells, so now you can see if all of the totals in column O when compared with the forecast numbers in column P resulted in a loss or profit for the year.
Lists are an effective way of presenting information that readers can digest easily, as demonstrated in this book. Excel includes many powerful tools to create lists easily and then format them so that they look the way you want them to appear.
When you need to extract specific characters from text to place it in another cell, such as only to show a prefix for a part name, you can do so by using the built‐in RIGHT()
, LEFT()
, and MID()
functions that you can add within a formula.
The RIGHT()
function shows the last characters in a string of text within a cell in a worksheet or table. Here's how to use the RIGHT()
function in a cell:
The last two letters in cell A4 appear in cell A9, as shown in Figure 4.16.
The LEFT()
function shows the first characters in a string of text within a cell in a worksheet or table. Use the LEFT()
function as demonstrated in the following example:
The first two letters in cell A6 appear in cell A10 (see Figure 4.17).
The MID()
function shows a specific number of characters in a string of text within a cell in a worksheet or table. Follow these steps to use the MID()
function, as shown in the following example:
The three letters starting with the fourth character in cell A7, which is the number 0, appear in cell A11 (see Figure 4.18).
When you need to change the text in a cell to all uppercase or all lowercase letters, especially in multiple cells, then that task becomes tedious in no time. Excel has two built‐in features for converting all text in cells within a worksheet or table to uppercase or lowercase.
The UPPER()
function converts all text in one or more cells to uppercase. Follow these steps to use the UPPER()
function:
Excel copies the formula into all cells in column C, so now all text in column B is uppercase in column C (see Figure 4.19).
The LOWER()
function converts all text in one or more cells to lowercase. Here's how to use the LOWER()
function:
Excel copies the formula into all cells in column C, so now all text in column B is lowercase in column C (see Figure 4.20).
The LEN()
function, which is short for length, tells you how many characters are in a text string within a cell. For example, you may need to have exactly 14 characters in a product code, and you want to find out which product code has too many or too few characters.
Add the LEN()
function in a cell by following these steps:
Excel copies the formula into all cells in column C, so the number of characters in cells A3 through A7 appear in column C, and you can confirm that all the product codes have the same length (see Figure 4.21).
When you need to join text from two or more cells and place the joined text into a new cell, Excel gives you two functions to do just that:
CONCAT()
, which replaces the CONCATENATE()
function in earlier versionsTEXTJOIN()
, which is new in Excel 2019 and Excel for Microsoft Office 365Adding the CONCAT()
function in the Formula Bar doesn't have as many arguments you need to add compared to TEXTJOIN()
, but you don't get any options. Follow the steps in this example to see what I mean:
You see the combined text from cells B3 and B4 in cell B9, as shown in Figure 4.22.
When you need to add spaces or another delimiter, such as a comma, between all the words in your combined text, the new TEXTJOIN()
function is what you need. Here's how to use TEXTJOIN()
:
TRUE
argument tells Excel to ignore any empty cells in the range.The combined text in the cell range appears in cell B10 (see Figure 4.23) with a space between each word.
This chapter started by showing you how to insert references in a worksheet or table, including relative, absolute, and mixed references. You also learned how to refer to named ranges and tables within a formula.
After you learned about references, you saw how to perform various calculations using built‐in Excel functions, including the average, maximum, minimum, and sum functions. Then you learned how to count within cells using the three different counting functions in a formula. You also saw how to add the IF()
function to perform conditional operations.
Next, I discussed how to format and modify text by using built‐in functions to extract text from the right, left, and middle portions of a text string. You saw how to use functions to change text to all uppercase or all lowercase, as well as get the length of characters in a cell. Finally, you learned how to combine text in two or more cells together using the CONCAT()
and TEXTJOIN()
functions.
absolute | mixed | |
average | references | |
Formula Bar | relative | |
maximum | sum | |
minimum |
CONCAT()
and TEXTJOIN()
functions to join text in two or more cells and know the difference between each function.COUNT()
COUNTA()
COUNTALL()
COUNTBLANK()
=RIGHT(A3,5)
do?
CONCAT()
TEXTJOIN()
SUM()
COUNT()
SUM()
formula for each range separated by a comma.SUM()
formula.SUM()
formula for each range separated by a plus (+
) sign.18.220.11.34