# excel formulas

Excel's power is all in the formulas.

Without formulas, Excel is a calculator with a keyboard. With formulas, it becomes a data processing powerhouse that can load, parse, transform, and calculate almost anything.

This guide covers the essential formulas you need to solve 90% of your Excel problems.

# Quick Reference

## Function Keys

  • →Toggle Enter/Edit Mode: F2
  • →Toggle Reference Modes: F4

## Basic Operators

  • →Formula identifier: =
  • →Define ranges: :
  • →Separate terms: ,
  • →Group terms: ()
  • →Equality/inequality: =, <>
  • →Comparisons: >, >=, <, <=
  • →Math: +, -, *, /
  • →Text quotes: ""
  • →Text joining: &

## Essential Functions

### Aggregation

  • →MIN() - Find lowest value
  • →MAX() - Find highest value
  • →COUNT() - Count numbers
  • →COUNTA() - Count all items
  • →SUM() - Add values
  • →AVERAGE() - Calculate mean

### Conditionals

  • →IF() - If/then logic
  • →COUNTIFS() - Count with conditions
  • →SUMIFS() - Sum with conditions
  • →AVERAGEIFS() - Average with conditions

### Lookup

  • →INDEX() - Find value at position
  • →MATCH() - Search for position

# Key Concepts

## F2: Your Best Friend

F2 toggles between Enter Mode and Edit Mode. In Enter Mode, arrow keys move between cells. In Edit Mode, arrow keys move within the formula.

Use F2 to edit formulas without touching your mouse. While editing, place your cursor near an operator (+, -, &) and press F2 again to switch back to Enter Mode and add cell references with arrow keys.

## F4: Reference Locking

F4 cycles through cell reference modes when your cursor touches a cell reference:

  1. →$A$1 - Lock both row and column
  2. →A$1 - Lock row only
  3. →$A1 - Lock column only
  4. →A1 - No locks

Locked references don't change when you copy formulas to other cells.

## Formula Construction

### Ranges

A1:B2 includes cells A1, A2, B1, B2. First cell must be upper-left, last cell lower-right.

  • →Whole column: A:A
  • →Whole row: 1:1

### Text Handling

Wrap text in quotes: "Hello World" Join text with &: "Hello " & "World" becomes "Hello World"

### IF Statements

=IF(condition, value_if_true, value_if_false)

Example: =IF(A1>10, "High", "Low")

### VLOOKUP Alternative

Instead of VLOOKUP, use INDEX + MATCH: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

This is more flexible and performs better than VLOOKUP.

## Pro Tips

  1. →Use absolute references ($A$1) for lookup tables that shouldn't change when copying formulas
  2. →Keep ranges continuous - gaps in data cause unexpected results
  3. →Text vs Numbers - "2" + "3" = 5, but "2" & "3" = "23"
  4. →Error checking - Use IFERROR() to handle lookup failures gracefully

Master these fundamentals and you'll handle most Excel challenges with confidence.