46  Quick Reference: Functions in Spreadsheets

Functions are preset commands that automatically perform specific tasks using the data in a spreadsheet. They are essential tools for data analysts, enabling calculations ranging from basic arithmetic to complex operations. This reference summarizes important concepts and examples to help you use spreadsheet functions effectively.

46.1 The Basics

• Begin every function with an equal sign (=), such as =SUM.
  The equal sign signals that what follows is a function, not plain text or a number.
  
• Most spreadsheet applications display an autocomplete menu after typing the equal sign. This feature helps you avoid syntax and typing errors.
  
• You can explore functions by typing an equal sign followed by a single letter to view a list of available functions starting with that letter.

46.2 Difference Between Formulas and Functions

• Formula: A set of instructions used to perform a calculation with data in a spreadsheet.
  
• Function: A preset command that automatically performs a specific task or calculation using spreadsheet data.

Example:
- Formula: =A1+B1
- Function: =SUM(A1:B1)

46.3 Popular Functions

Spreadsheet programs come with many built-in functions that make repetitive tasks simple. In addition to data functions, common keyboard shortcuts—such as cut, copy, save, and find—are also built-in functions designed to save time and increase productivity.

To explore keyboard shortcuts:
- Chromebook: Keyboard shortcuts for Google Sheets
- Windows (PC): Excel keyboard shortcuts
- Mac: Mac Excel shortcuts

46.4 Auto-Filling

Auto-filling allows you to quickly copy a function or formula across cells.

• In Excel, the fill handle is a small green square in the bottom-right corner of a cell.
  
• In Google Sheets, it appears as a small blue circle.

How to use Auto-Fill: - Drag the fill handle down a column to apply the same function to multiple cells vertically.
- Drag the fill handle across a row to copy the same function horizontally.

46.5 Relative, Absolute, and Mixed References

Understanding how cell references work is key to accurate formulas.

• Relative reference: Adjusts when copied or moved.
  Example: A2
  
• Absolute reference: Remains constant when copied or moved.
  Example: $A$2
  
• Mixed reference: Partially adjusts—either the row or column remains fixed.
  Examples: $A2 or A$2

Shortcut Tip:
Press F4 while editing a formula to toggle between relative, absolute, and mixed references.

46.6 Data Ranges

When you click a cell containing a function, the formula bar highlights the data ranges in different colors.
Each color corresponds to a unique data range, making it easier to trace which cells are being used.

Shortcut Tip:
Press F2 to view and highlight the data range used by a function.

46.7 Conditional Evaluation of Data Ranges

Some functions evaluate a data range based on specific conditions.
A common example is the COUNTIF function, which counts the number of cells meeting a defined criterion.

Example:
=COUNTIF(B2:B10, "airfare")
This function counts how many cells in the range B2:B10 contain the word “airfare.”

To explore further:
- COUNTIF in Microsoft Excel
- COUNTIF in Google Sheets (with template examples)

46.8 Key Takeaways

• Start all functions with = to signal a command.
  
• Use auto-fill and cell referencing to efficiently manage formulas across multiple cells.
  
• Press F4 to switch between reference types and F2 to view data ranges.
  
• Explore conditional functions like COUNTIF for criteria-based analysis.
  
• Continue learning—functions are one of the most powerful tools for solving complex analytical problems efficiently and accurately.