• Function : a preset command that automatically performs a specific process or task using the data

Quick reference: Functions in spreadsheets

As a quick refresher, a function is a preset command that automatically performs a specific process or task using the data in a spreadsheet. Functions give data analysts the ability to do calculations, which can be anything from simple arithmetic to complex equations. Use this reading to help you keep track of some of the most useful options.

Functions

The basics

• Just like formulas, start all of your functions with an equal sign (=SUM). The equal sign tells the spreadsheet that what follows is part of a function, 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 functions, names, and text strings. This is a great way to create and edit functions while avoiding typing and syntax errors.
• A fun way to learn new functions is by simply typing an equal sign and a single letter of the alphabet. Choose one of the options that pops up and learn what that function does.

Difference between formulas and functions

• A formula is a statement written by the user within a spreadsheet application.
• A function is a predefined operation built into the spreadsheet application.

Popular functions

A lot of people don’t realize that keyboard shortcuts like cut, save, and find are actually functions. These functions are built into an application and are amazing time-savers. Using shortcuts lets you do more with less effort. They can make you more efficient and productive because you are not constantly reaching for the mouse and navigating menus. The following table shows some of the most popular shortcuts, for Chromebook, PC, and Mac:

Command	Chromebook	PC	Mac
Create new workbook	Control+N	Control+N	Command+N
Open workbook	Control+O	Control+O	Command+O
Save workbook	Control+S	Control+S	Command+S
Close workbook	Control+W	Control+W	Command+W
Undo	Control+Z	Control+Z	Command+Z
Redo	Control+Y	Control+Y	Command+Y
Copy	Control+C	Control+C	Command+C
Cut	Control+X	Control+X	Command+X
Paste	Control+V	Control+V	Command+V
Paste values only	Control+Shift+V	Control+Shift+V	Command+Shift+V
Find	Control+Shift+F	Control+F	Command+F
Find and replace	Control+H	Control+H	Command+Shift+F
Insert link	Control+K	Control+K	Command+K
Bold	Control+B	Control+B	Command+B
Italicize	Control+I	Control+I	Command+I
Underline	Control+U	Control+U	Command+U
Zoom in	Control+Plus (+)	Control+Plus (+)	Option+Command+Plus (+)
Zoom out	Control+Minus (-)	Control+Minus (-)	Option+Command+Minus (-)
Select column	Control+Spacebar	Control+Spacebar	Command+Spacebar
Select row	Shift+Spacebar	Shift+Spacebar	Up Arrow+Spacebar
Select all cells	Control+A	Control+A	Command+A
Edit the current cell	Enter	F2	F2
Comment on a cell	Ctrl + Alt + M	Alt+I+M	Option+Command+M
Insert column to the left	Ctrl + Alt + = (with existing column selected)	Alt+Shift+I, then C	⌘ + Option + = (with existing column selected)
Insert column to the right	Alt + I, then O	Alt+Shift+I, then O	Ctrl + Option + I, then O
Insert row above	Ctrl + Alt + = (with existing row selected)	Alt+Shift+I, then R	⌘ + Option + = (with existing row selected)
Insert row below	Alt + I, then R, then B	Alt+Shift+I, then B	Ctrl + Option + I, then B

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 function used 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 function used in that cell.

Absolute referencing

• Relative references will change anytime the function is copied and pasted. They are in relation to where the referenced cell is located.
• Absolute references will not change when you copy and paste the function in a different cell. The cell being referenced is always the same.

Data range

• When you click into your function, the colored ranges indicate which cells are being used in your spreadsheet. There are different colors for each unique range in your function.
• Data ranges help keep you from getting lost in complex functions.
• In many spreadsheet applications, you can press the F2 key to highlight the range of data in a function. Click in to edit the cell, then click on the range of data in your function, then press F2. It will highlight the range of data for you.

Combining with formulas

• COUNTIF() is the combination of a formula and a function. This allows the function to run based on a criteria set by the formula.

There are a lot more functions that can help you make the most of your data. This is just the start. You can keep learning how to use functions to help you solve complex problems efficiently and accurately throughout your entire career.

---

Test your knowledge on using functions in spreadsheets

TOTAL POINTS 3

Question 1

Data analysts use which of the following functions to quickly perform calculations in a spreadsheet? Select all that apply.

• SUM
• MIN
• MASS
• AVERAGE

Correct. AVERAGE, MIN, and SUM are functions used to quickly perform calculations in a spreadsheet.

Question 2

What is the term for a preset command in a spreadsheet?

• Quotient
• Function
• Range
• Cell

Correct. A preset command in a spreadsheet is called a function.

Question 3

You are working with spreadsheet data about a cross-country relay race. Each runner’s times are located in cells H2 through H28. To find the runner with the slowest time, what is the correct function?

• =MAX(H2-H28)
• =MIN(H2:H28)
• =MAX(H2:H28)
• =MIN(H2-H28)

Correct. The function is =MAX(H2:H28). The largest numeric value corresponds to the slowest time in the race. MAX returns the largest numeric value from a range of cells. And H2:H28 is the specified range.