10 Spreadsheet Formulas Every User Should Know
Spreadsheets are arguably the most powerful everyday software most people have access to, yet the average user knows fewer than five formulas. That means they spend hours doing manually what a single formula could accomplish in seconds. This is not about becoming a spreadsheet expert — it is about knowing the ten formulas that cover 90% of real-world needs. Whether you track expenses, manage inventory, analyze survey responses, or create simple reports, these formulas will immediately make you faster and more accurate. Each formula below includes a practical example you can adapt to your own data. We cover counting, looking up data, conditional logic, text manipulation, and date calculations — the building blocks that let you stop copying numbers by hand and start letting the spreadsheet do the work.
SUM, AVERAGE, COUNT — The Foundation
SUM adds numbers: =SUM(B2:B100) totals all values in column B. Use it for expenses, revenue, hours worked, or any numeric total. AVERAGE calculates the mean: =AVERAGE(C2:C50) tells you the average sale price in column C. COUNT tells you how many cells contain numbers: =COUNT(A2:A200) shows how many entries exist. Its sibling COUNTA counts non-empty cells including text, which is useful for tracking responses in a survey. COUNTIF adds a condition: =COUNTIF(D2:D100, "Completed") counts how many tasks are marked as completed. SUMIF works similarly: =SUMIF(A2:A100, "Marketing", B2:B100) sums only the budget column where the department column says "Marketing." These six variations of three core functions handle most basic reporting needs without any complexity.
VLOOKUP and INDEX-MATCH — Finding Data
VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. Example: =VLOOKUP("SKU-1234", A2:D500, 3, FALSE) finds "SKU-1234" in column A and returns the value from the third column (perhaps the price). The FALSE parameter requires an exact match. VLOOKUP's limitation is that it only searches rightward. INDEX-MATCH is more flexible: =INDEX(C2:C500, MATCH("SKU-1234", A2:A500, 0)) achieves the same result but can look in any direction. For most users, VLOOKUP is simpler to learn and covers 80% of lookup needs. Use INDEX-MATCH when your lookup column is to the right of your result column, or when you need better performance with very large datasets exceeding 50,000 rows.
IF, IFS, and Nested Logic
The IF function makes decisions: =IF(B2>1000, "High", "Low") labels sales over 1000 as "High" and everything else as "Low." You can nest IF statements for multiple conditions: =IF(B2>1000, "High", IF(B2>500, "Medium", "Low")) creates three categories. For cleaner multi-condition logic, use IFS (available in modern Excel and Google Sheets): =IFS(B2>1000, "High", B2>500, "Medium", TRUE, "Low") — the TRUE at the end acts as a default. Combine IF with AND or OR for complex conditions: =IF(AND(B2>500, C2="Active"), "Priority", "Normal") marks rows as Priority only when the amount exceeds 500 AND the status is Active. These conditional formulas are the foundation of automated reporting — they turn raw data into categorized, actionable information without manual review.
TEXT, CONCATENATE, and LEFT/RIGHT/MID
TEXT manipulates how data displays: =TEXT(A2, "DD/MM/YYYY") formats a date, =TEXT(B2, "$#,##0.00") formats a number as currency. CONCATENATE (or the & operator) joins values: =A2&" "&B2 combines first and last name with a space. In newer versions, TEXTJOIN is even better: =TEXTJOIN(", ", TRUE, A2:A10) joins all values with commas, skipping blanks. LEFT, RIGHT, and MID extract parts of text: =LEFT(A2, 3) gets the first three characters (useful for extracting area codes), =RIGHT(A2, 4) gets the last four (last digits of an ID), =MID(A2, 5, 3) extracts three characters starting at position 5. These text functions are essential when cleaning imported data — splitting full names into first and last, extracting dates from mixed text fields, or standardizing inconsistent formatting across hundreds of rows.
Date Functions and Practical Tips
TODAY() returns the current date, useful in formulas like =TODAY()-A2 to calculate how many days since a date in A2. DATEDIF calculates differences in specific units: =DATEDIF(A2, B2, "M") returns the number of complete months between two dates. EOMONTH finds month ends: =EOMONTH(A2, 0) returns the last day of A2's month, =EOMONTH(A2, 1) returns the last day of the following month. NETWORKDAYS counts business days between two dates, excluding weekends: =NETWORKDAYS(A2, B2) — pass a range of holiday dates as the third parameter to exclude those too. These functions are invaluable for project timelines, invoice due dates, and age calculations. A final tip: always use absolute references ($A$1) when a formula references a fixed cell like a tax rate, so copying the formula to other rows does not shift the reference incorrectly.