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.

Fomula

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.

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.

Hands-on Guided Project: Create a Personal Spending Budget Using Google Sheets

About Guided Projects

In this Guided Project, you will be assigned a cloud desktop that has the required software pre-installed. This will allow you to follow along with the instructor to complete the project's tasks.

You will work side-by-side with an industry expert to help a fictional client create a personal spending budget in order to make data-informed decisions about how to spend money. To do this, you will apply your knowledge of spreadsheet formulas to add values, calculate averages, and assess spending patterns across different expense categories.

After completing this project, you can reference a summary of the key takeaways from this document.

Please refer to the Coursera Help Center for more information.

Ready to get started?

Check the I agree to use this tool responsibly box and click the Open Tool button to access your cloud workspace.

This course uses a third-party app, Hands-on Guided Project: Create a Personal Spending Budget Using Google Sheets, to enhance your learning experience. The app will reference basic information like your name, email, and Coursera ID.

More about spreadsheet errors and fixes

_The PDF file in the attachment below includes topics and content from both the companion video and this reading. You can save this file for future reference here

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.

As a follow-up to what you learned in the previous video, here are a few best practices and helpful tips. These strategies will help you avoid spreadsheet errors to begin with, 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, you can focus on what to do when that dreaded pop-up does appear. The following table is a reference you can use to look up 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) Something can't be interpreted as it has been input. This is also known as a parsing error.	=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
#NUM!	The spreadsheet can't perform a formula calculation because a cell has an invalid numeric value	=DATEDIF(A4, B4, "M") is unable to caluclate 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.

If you are working with Microsoft Excel, an interactive page, How to correct a #VALUE! error, 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.

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 gray 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

Question 1

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

• Asterisk (*)

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

• Forward slash (/)

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

• Hyphen (-)

Explain: 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?

A. Equal sign (=)

B. Dash (—)

C. Plus-minus (±)

D. Colon (:)

The correct answer is A. Equal sign (=). Explain: In a spreadsheet, a formula 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?

A. Cell domain

B. Data range

C. Data boundary

D. Cell set

The correct answer is B. Data range. Explain: 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 or False?

A. True

B. False

It is true statement. Explain: In a formula, the plus sign (+) is the operator for addition, and the hyphen (-) is the operator for subtraction.