Excel formulas for beginners: SUM, IF and lookups

In short: Formulas begin in Excel and LibreOffice Calc with an equals sign; with a few functions such as SUM, IF and VLOOKUP a large part of everyday tasks can be solved.

In more detail: A formula calculates with numbers or cells: =A1+B1. Functions are built-in calculation paths, such as =SUM(A1:A10). If a value changes, the sheet recalculates automatically. The same functions exist in LibreOffice Calc and Google Sheets.

In Depth

Basics

• Cells are addressed by column and row: B3. A range is B3:B10.
• Operators: + - * / ^, brackets determine the order.
• Relative and absolute: A1 shifts when copying, $A$1 stays fixed, $A1 fixes only the column. The F4 key toggles.
• The fill handle at the bottom right of the cell copies formulas downwards.

Important functions

• =SUM(A1:A10), =AVERAGE(...), =MIN(...), =MAX(...), =COUNT(...)
• =ROUND(number, 2)
• =IF(B2>=50, "passed", "failed")
• =SUMIF(A:A, "Rent", B:B): sums B where A is “Rent”.
• =COUNTIF(A:A, "Yes")
• =TODAY(), =NOW()
• Text: =CONCAT(A1, " ", B1) or =A1&" "&B1, =LEFT(A1,3)

Searching and lookups

=VLOOKUP(search value, matrix, column, FALSE) searches in the first column of the matrix and returns the value of the desired column (FALSE = exact match). More modern and flexible: XLOOKUP(search value, search range, return range). Both replace manual searching in long lists. Error #N/A means “not found”, #REF! “cell missing”, #DIV/0! “division by zero”; with =IFERROR(..., "") you catch them.

Tips for clean sheets

One header row, no merged cells, one data type per column, formulas instead of typed results, format as table (Ctrl+T) for automatically growing ranges and pivot tables for analysis. Check formulas with Formulas → Evaluate Formula. Back up important files regularly.

See also: Spreadsheets for beginners, Excel, File formats (CSV etc.)