David ParkMaster the most useful Excel formulas for work and personal use. From basic calculations to powerful data analysis functions.
30 min
Time Required
Free
Estimated Cost
8
Steps
low
Safety
What You'll Need
Tools
Step 1 of 8
Understanding Formulas
Every formula starts with = (equals sign). Formulas can contain numbers, cell references (A1, B2), operators (+, -, *, /), and functions. Press Enter to calculate.
Step 2 of 8
SUM — Add Numbers
The most common function. Use =SUM(A1:A10) to add all numbers in a range. You can also use =A1+A2+A3 for specific cells.
Tip
Click and drag to select a range instead of typing it manually.
Step 3 of 8
AVERAGE — Find the Mean
Use =AVERAGE(A1:A10) to calculate the arithmetic mean of a range. This automatically ignores empty cells and text.
Step 4 of 8
IF — Conditional Logic
Use =IF(A1>100, 'Over budget', 'Under budget') to test conditions. The first argument is the test, second is if true, third is if false.
Step 5 of 8
VLOOKUP — Search Tables
Use =VLOOKUP(search_value, table_range, column_number, FALSE) to find data in a table. The FALSE parameter ensures exact matches.
Common Mistake
VLOOKUP only searches rightward. Use INDEX/MATCH for leftward lookups.
Step 6 of 8
COUNTIF — Count with Conditions
Use =COUNTIF(A1:A10, '>50') to count cells meeting criteria. Works with numbers, text, and wildcards (* and ?).
Step 7 of 8
CONCATENATE — Join Text
Use =CONCATENATE(A1, ' ', B1) or the simpler =A1&' '&B1 to join text from multiple cells. Great for creating full names or addresses.
Step 8 of 8
Absolute vs Relative References
Use $ to lock references: $A$1 stays fixed when copied. A1 changes relative to its new position. Mix them: $A1 locks column only, A$1 locks row only.
Pro Tip
Press F4 while editing a cell reference to cycle through absolute/relative options.