Excel Formula Help: 10 Functions Everyone Should Know
Quick reference for the most useful Excel functions: SUM, IF, VLOOKUP, INDEX-MATCH, COUNTIF, SUMIF, TEXT, CONCATENATE, LEFT/RIGHT/MID, and IFERROR.
The Foundation: SUM, AVERAGE, COUNT
Every Excel user starts here. =SUM(A1:A100) adds up a range. =AVERAGE(A1:A100) calculates the mean. =COUNT(A1:A100) counts cells with numbers, while =COUNTA counts non-empty cells. These functions accept multiple ranges: =SUM(A1:A100, C1:C100). For conditional versions, use SUMIF, AVERAGEIF, and COUNTIF — for example, =SUMIF(B:B, "East", C:C) sums column C where column B equals 'East'.
Logic and Lookups: IF, VLOOKUP, INDEX-MATCH
=IF(condition, value_if_true, value_if_false) is the building block for decision logic. Nest IFs for multiple conditions, or use IFS in Excel 365. VLOOKUP and INDEX-MATCH let you pull data from other tables. INDEX-MATCH is more flexible: =INDEX(C:C, MATCH(A2, A:A, 0)) returns the value from column C where column A matches A2. It works in any direction and does not break when columns are inserted.
Text Functions: CONCATENATE, LEFT, RIGHT, MID, TEXT
=CONCATENATE(A1, " ", B1) joins text (or use the & operator). LEFT, RIGHT, and MID extract substrings: =LEFT(A1, 3) gets the first 3 characters. =TEXT(A1, "$#,##0.00") formats numbers as text with custom patterns. TRIM removes extra spaces, CLEAN removes non-printable characters, and UPPER/LOWER/PROPER change case. These are essential for cleaning imported data.
Stop Memorizing Formulas — Just Describe What You Need
There are over 500 functions in Excel, and remembering the right syntax for each is impractical. With ohsheet.ai, you describe the result you want — 'Add a column that calculates the running total of daily sales' or 'Split the full address into street, city, state, and ZIP columns' — and our AI writes the formulas for you. No syntax errors, no debugging, no Stack Overflow.