Is it true that Excel formulas only work with numbers.?

Excel formulas can manipulate text, dates, and logical values, not just numbers.

Is it true that All cell references in a copied formula change automatically.?

Absolute references ($) prevent specific parts of a cell reference from changing when a formula is copied.

Is it true that IFERROR() is only for division by zero errors.?

IFERROR() can catch any type of error that occurs in a formula and provide a specified alternative value.

Excel Basics Cheat Sheet

Master fundamental Excel functions and operators for efficient data management and analysis. This cheat sheet covers definitions, operators, logical functions, data types, and essential math and text functions.

Core Principles

Understand cell references (e.g., A1) and cell ranges (e.g., A1:C3) for targeting data.
Utilize arithmetic operators (+, -, *, /, %, ^) for basic calculations.
Employ comparison operators (=, <>, >, <, >=, <=) to test conditions.
Leverage logical functions (AND, OR, NOT, XOR) to combine multiple conditions.
Master text functions (LEN, MID, UPPER, LOWER, PROPER, TEXTSPLIT) for string manipulation.
Apply math functions (SUM, AVERAGE, MAX, MIN, ROUND) for numerical analysis.

Formulas

Arithmetic: =A2+A3
Comparison: =A2=B2
Logical: =AND(A1>10, B1<20)
Conditional: =IF(ISBLANK(A5), "Blank", "Not Blank")
Conditional Multiple: =IFS(A1>B1, "1st", A2>B2, "2nd")
Error Handling: =IFERROR(A5/A5, 1)
Switch: =SWITCH(MID(D3, 1, 5), "World", "planet", "Solar", "planetary system")
Count: =COUNTIF(A1:A6, ">5")
Sum: =SUMIF(A1:A6, ">5", B1:B6)
Average: =AVERAGEIF(A1:A6, ">5", B1:B6)
Text Length: =LEN(D5)
Text Extract: =MID(D6, 4, 5)
Text Repeat: =REPT(D6, 3)
Text Split: =TEXTSPLIT(D4, "o")
Text Case: =UPPER(D3)
Data Filter: =FILTER(A1:B6, C1:C6>100)
Data Sort: =SORT(A1:E6, 4)
Data Unique: =UNIQUE(A1:A6)
Sequence: =SEQUENCE(5, 1, 3, 2)

Pitfalls to Avoid

Forgetting the equals sign (=) at the beginning of a formula.
Using relative references when absolute references ($) are needed after copying formulas.
Confusing COUNT (numbers only) with COUNTA (non-empty cells).
Incorrectly applying logical operators, leading to unexpected TRUE/FALSE results.
Not specifying the correct range or criteria in functions like COUNTIF or SUMIF.

Myth vs Reality

Excel formulas only work with numbers.: Excel formulas can manipulate text, dates, and logical values, not just numbers.
All cell references in a copied formula change automatically.: Absolute references ($) prevent specific parts of a cell reference from changing when a formula is copied.
IFERROR() is only for division by zero errors.: IFERROR() can catch any type of error that occurs in a formula and provide a specified alternative value.