• Formula : a set of instructions that perform a specific calculation
• Operator : a symbol that names that type of operation or calculation to be performed
• Cell reference : a single cell or range of cells in a worksheet that can be used in a formula
• Range : a collection of two or more cells

Quick reference: Formulas in spreadsheets

You have been learning a lot about spreadsheets and all kinds of time-saving calculations and organizational features they offer. One of the most valuable spreadsheet features is a formula. As a quick reminder, a formula is a set of instructions that does a specific calculation using the data in a spreadsheet. Formulas make it easy for data analysts to do powerful calculations automatically, which helps them analyze data more effectively. Below is a quick-reference guide to help you get the most out of formulas.

Formulas

The basics

• When you write a formula in math, it generally ends with an equal sign (2 + 3 = ?). But with formulas, they always start with one instead (=A2+A3). The equal sign tells the spreadsheet that what follows is part of a formula, not just a word or number in a cell.
• After you type the equal sign, most spreadsheet applications will display an autocomplete menu that lists valid formulas, names, and text strings. This is a great way to create and edit formulas while avoiding typing and syntax errors.
• A fun way to learn new formulas is just by typing an equal sign and a single letter of the alphabet. Choose one of the options that pops up and you will learn what that formula does.

Mathematical operators

• The mathematical operators used in spreadsheet formulas include:
• Subtraction – minus sign ( - )
• Addition – plus sign ( + )
• Division – forward-slash ( / )
• Multiplication – asterisk ( * )

Auto-filling

The lower-right corner of each cell has a fill handle. It is a small green square in Microsoft Excel and a small blue square in Google Sheets.

• Click the fill handle for a cell and drag it down a column to auto-fill other cells in the column with the same value or formula in that cell.
• Click the fill handle for a cell and drag it across a row to auto-fill other cells in the row with the same value or formula in that cell.
• If you want to create a numbered sequence in a column or row, do the following: 1) Fill in the first two numbers of the sequence in two adjacent cells, 2) Select to highlight the cells, and 3) Drag the fill handle to the last cell to complete the sequence of numbers. For example, to insert 1 through 100 in each row of column A, enter 1 in cell A1 and 2 in cell A2. Then, select to highlight both cells, click the fill handle in cell A2, and drag it down to cell A100. This auto-fills the numbers sequentially so you don't have to type them in each cell.

Absolute referencing

• Absolute referencing is marked by a dollar sign ($). For example, =$A$10 has absolute referencing for both the column and the row value
• Relative references (which is what you normally do e.g. “=A10”) will change anytime the formula is copied and pasted. They are in relation to where the referenced cell is located. For example if you copied “=A10” to the cell to the right it would become “=B10”. With absolute referencing “=$A$10” copied to the cell to the right would remain “=$A$10”. But if you copied $A10 to the cell below, it would change to $A11 because the row value isn't an absolute reference.
• Absolute references will not change when you copy and paste the formula in a different cell. The cell being referenced is always the same.
• To easily switch between absolute and relative referencing in the formula bar, highlight the reference you want to change and press the F4 key; for example, if you want to change the absolute reference, $A$10, in your formula to a relative reference, A10, highlight $A$10 in the formula bar and then press the F4 key to make the change.

Data range

• When you click into your formula, the colored ranges let you see which cells are being used in your spreadsheet. There are different colors for each unique range in your formula.
• In a lot of spreadsheet applications, you can press the F2 (or Enter) key to highlight the range of data in the spreadsheet that is referenced in a formula. Click the cell with the formula, and then press the F2 (or Enter) key to highlight the data in your spreadsheet.

Combining with functions

• COUNTIF() is a formula and a function. This means the function runs based on criteria set by the formula. In this case, COUNT is the formula; it will be executed IF the conditions you create are true. For example, you could use =COUNTIF(A1:A16, “7”) to count only the cells that contained the number 7. Combining formulas and functions allows you to do more work with a single command.

Spreadsheet errors and fixes

When you are new to data analytics—and sometimes even when you aren't—spreadsheet struggles are real. It never feels good when you type in what you are sure is a perfect formula or function, only to get an error message. Understanding errors and how to fix them is a big part of keeping your data clean, so it’s important to know how to deal with issues as they come up, and more importantly, not to get discouraged.

Remember, even the most advanced spreadsheet users come across problems from time to time. In this reading, you will learn about common errors and how to fix them.

But before we do that, let’s go over a few best practices and helpful tips. These strategies will help you avoid spreadsheet errors in the first place, making your life in analytics a whole lot less stressful:

1. Filter data to make your spreadsheet less complex and busy.
2. Use and freeze headers so you know what is in each column, even when scrolling.
3. When multiplying numbers, use an asterisk (*) not an X.
4. Start every formula and function with an equal sign (=).
5. Whenever you use an open parenthesis, make sure there is a closed parenthesis on the other end to match.
6. Change the font to something easy to read.
7. Set the border colors to white so that you are working in a blank sheet.
8. Create a tab with just the raw data, and a separate tab with just the data you need.

Now that you have learned some basic ways to avoid errors, let’s review what to do when that dreaded pop-up does appear. The following table lists common spreadsheet errors and examples of each. Knowing what the errors mean takes some of the fear out of getting them.

Error	Description	Example
#DIV/0!	A formula is trying to divide a value in a cell by 0 (or an empty cell with no value)	=B2/B3, when the cell B3 contains the value 0
#ERROR!	(Google Sheets only) A space is used instead of a colon or comma to separate cell references or ranges	=COUNT(B1:D1 C1:C10) is invalid because the cell ranges aren't separated by a comma
#N/A	A formula can't find the data	The cell being referenced can't be found
#NAME?	The name of a formula or function used isn't recognized	The name of a function is misspelled
#NULL!	(Microsoft Excel only) A space is used instead of a colon or comma to separate cell references or ranges	=SUM(B2:B6 C2:C6) is invalid because the cell ranges aren't separated by a comma
#NUM!	The spreadsheet can't perform a formula calculation because a cell has an invalid numeric value	=DATEDIF(A4, B4, "M") is unable to calculate the number of months between two dates because the date in cell A4 falls after the date in cell B4
#REF!	A formula is referencing a cell that isn't valid	A cell used in a formula was in a column that was deleted
#VALUE!	A general error indicating a problem with a formula or with referenced cells	There could be problems with spaces or text, or with referenced cells in a formula; you may have additional work to find the source of the problem.

The next sections provide examples of these errors and possible solutions. We also provide a pro tip at the end of this reading about how you can spot errors quickly in your spreadsheet by using conditional formatting.

Tip: If you are new to spreadsheets, focus on the descriptions of the errors in the previous table. Spend some time working with spreadsheets and then come back to read the examples below.

#DIV/0!

A #DIV/0! error means that your formula is trying to divide a value in a cell by 0, or by an empty cell (with no value).

Let's say you are trying to calculate the percentage of required tasks that have been completed in a project. Column B has the number of required tasks and Column C has the number of completed tasks. You enter the formula =C2/B2*100 in cell D2 to calculate the percentage of completion. You copy the formula to the rest of the cells in Column D, but there is a #DIV/0! error in cell D4. No tasks are required for that particular line item so the formula is trying to divide by 0 in cell B4.

You could delete row 4, but if things change and tasks are required for that line item in the future, you would have to insert that row back into the spreadsheet.

A better solution is to have the spreadsheet enter "N/A" (not applicable) whenever a cell in the B column contains a 0.

Fixing the error

Change the formula in the D column cells so the formula in cell D4 changes from =C4/B4100 to **=IF(B4, C4/B4100, "N/A")**. The results are still the same for all other cells in the D column, but the #DIV/0! error no longer appears in cell D4. The new formula automatically catches the 0 in cell B4 and inserts N/A into cell D4.

#ERROR! or #NULL!

A #ERROR or #NULL! error means that you have specified two or more cell ranges without a comma as the delimiter to separate them. The spreadsheet can't figure out what the true cell ranges are.

The following formulas written without a comma cause these errors to appear:

=COUNT(B1:D1 C1:C10)

=SUM(B2:B6 C2:C6)

Fixing the error

You can fix these errors by replacing the space between the cell ranges with a comma.

=COUNT(B1:D1,C1:C10) instructs the spreadsheet to count the number of values from cell B1 through cell D1 and from C1 through C10.

=SUM(B2:B6,C2:C6) instructs the spreadsheet to add the values in cell B2 through cell B6 and from cell C2 through cell C6.

#N/A

A #N/A error tells you that the data in your formula or function can’t be found by the spreadsheet. Generally, this means the data doesn’t exist. This error most often occurs when you are using functions like VLOOKUP to look up a value in a spreadsheet based on matching criteria. For example, if the following lookup table is at the top of a large spreadsheet, the spreadsheet could automatically look up and fill in the price of almonds anywhere in the spreadsheet that you have inserted a VLOOKUP function.

Let's say cell C100 in the spreadsheet contains the formula: =VLOOKUP(B100, $B$4:$C$6, 2, 0). This formula should compare the text in cell B100 with Almonds, Cashews, or Walnuts in the lookup table and insert the price for the matching nut in cell C100. But there is a #N/A error in cell C100 because the price for Almond doesn't exist in the lookup table. The plural, Almonds, is in the lookup table.

Fixing the error

To fix the error, change Almond in cell B100 to Almonds. This will enable the spreadsheet to automatically find and insert the price for almonds from the lookup table, $2.00 in cell C4, into cell C100.

#NAME?

A #NAME? error means that your spreadsheet needs help understanding your formula or function. To solve #NAME? errors, the first step is to check your spelling. Then, be sure to use the full name for any formulas or functions. Spreadsheet applications will suggest formulas and functions for you so it is a good idea to make use of this feature.

Here is an example of a #NAME? error resulting from an extra O in the VLOOKUP function. The spreadsheet is trying to use VLOOOKUP which doesn't exist.

Fixing the error

Change the date in cell A4 from 9/14/2020 to 10/1/2019 and the date in cell B4 from 10/1/2019 to 9/14/2020. The dates will be in the correct order for the formula to work, and the error will no longer appear.

#REF!

A #REF! error tells you that your formula or function is referencing a cell that is no longer valid. A cell (or range of cells) may be missing because it was deleted.

In the example below, a simple formula is adding the values in cells A2, A3, and A4.

If you delete row 4 (and the value 26 in cell A4), the #REF! error appears and the spreadsheet can no longer calculate the total.

Fixing the error

You can fix the error by updating the formula in cell A5 to add the values from cell A2 and cell A3 only. =A2+A3

#VALUE!

A #VALUE! error is a general error that could indicate a problem with a function or referenced cells. It might not be clear right away what the problem is, so this error could require a little more effort to fix.

If you are working with Microsoft Excel, there is an interactive page, How to correct a #VALUE! error, that can help you narrow down the cause of this error. You can select a specific function from a drop-down list to display a link to tips to fix the error when using that function.

In the example below, a text string "James" is in End Date column instead of a date. The spreadsheet can’t perform the =DATEDIF(A2,B2, "M") calculation.

Fixing the error

Replace "James" in cell B2 with an end date in the right format, and the error will no longer appear.

Pro tip: spotting errors in spreadsheets with conditional formatting

Conditional formatting can be used to highlight cells a different color based on their contents. This feature can be extremely helpful when you want to locate all errors in a large spreadsheet. For example, using conditional formatting, you can highlight in yellow all cells that contain an error, and then work to fix them.

Conditional formatting in Microsoft Excel

To set up conditional formatting in Microsoft Excel to highlight all cells in a spreadsheet that contain errors, do the following:

1. Click the green triangle above row number 1 and to the left of Column A to select all cells in the spreadsheet.
2. From the main menu, click Home, and then click Conditional Formatting to select Highlight Cell Rules > More Rules.
3. For Select a Rule Type, choose Use a formula to determine which cells to format.
4. For Format values where this formula is true, enter =ISERROR(A1).
5. Click the Format button, select the Fill tab, select yellow (or any other color), and then click OK.
6. Click OK to close the format rule window.

To remove conditional formatting, click Home and select Conditional Formatting, and then click Manage Rules. Locate the format rule in the list, click Delete Rule, and then click OK.

Conditional formatting in Google Sheets

To set up conditional formatting in Google Sheets to highlight all cells in a spreadsheet that contain errors, do the following:

1. Click the empty rectangle above row number 1 and to the left of Column A to select all cells in the spreadsheet. In the Step-by-step in spreadsheets video, this was called the Select All button.
2. From the main menu, click Format and select Conditional Formatting to open the Conditional format rules pane on the right.
3. While in the Single Color tab, under Format rules, use the drop-down to select Custom formula is, enter =ISERROR(A1), select yellow (or any other color) for the formatting style, and then click Done.

To remove conditional formatting, click Format and select Conditional Formatting, and then click the Trash icon for the format rule.

Spreadsheet error resources

To learn more and read about additional examples of errors and solutions, explore these resources:

• Microsoft Formulas and Functions: This resource describes how to avoid broken formulas and how to correct errors in Microsoft Excel. This is a useful reference to have saved in case you run into a specific error and need to find solutions quickly while working in Excel.
• When Your Formula Doesn’t Work: Formula Parse Errors in Google Sheets: This resource is a guide to finding and fixing some common errors in Google Sheets. If you are working with Google Sheets, you can use this as a quick reference for solving problems you might encounter working on your own.

With some practice and investigative determination, you will become much more comfortable handling errors in spreadsheets. Each error you catch and fix will make your data clearer, cleaner, and more useful.

---

Test your knowledge on using formulas in spreadsheets

TOTAL POINTS 4

Question 1

Which of the following are examples of operators used in formulas? Select all that apply.

• Forward slash (/)
• Hyphen (-)
• Asterisk ()*

Correct. The asterisk, hyphen, and forward slash are examples of operators used in formulas.

• Hash (#)

Question 2

In a spreadsheet, a formula should always start with which of the following operators?

• Plus-minus (±)
• Dash (—)
• Equal sign (=)
• Colon (:)

Correct. In a spreadsheet, a function should always start with an equal sign.

Question 3

What is the term for the set of cells that a data analyst selects to include in a formula?

• Cell set
• Cell domain
• Data range
• Data boundary

Correct. The set of cells a data analyst selects to include in a formula is called the data range.

Question 4

In a formula, the plus sign (+) is the operator for addition, and the hyphen (-) is the operator for subtraction.

• True
• False

Correct. In a formula, the plus sign (+) is the operator for addition, and the hyphen (-) is the operator for subtraction.