The Handful of Spreadsheet Formulas That Cover Most Everyday Work

Spreadsheet programs ship with hundreds of functions, and almost nobody needs most of them. What actually comes up in everyday, non-analyst work is a short list, maybe two dozen formulas, that keep reappearing in slightly different combinations. Below is that list, grouped by the kind of question each one answers rather than alphabetically, since "what do I need this for" is a more useful way to find a formula than "what is it called."

Totals and counts

• SUMIF and SUMIFS, for adding up numbers that match one or more conditions (mysheetai.com/answers/sumif-sumifs).
• COUNTIF, for counting rows that match one condition (mysheetai.com/answers/countif), and COUNTIFS for counting rows that match several at once (mysheetai.com/answers/countifs).
• AVERAGEIF and AVERAGEIFS, for averaging only the rows that match a condition instead of every row (mysheetai.com/answers/averageif-averageifs).
• SUMPRODUCT, for weighted totals like quantity times price, or for OR-style conditions COUNTIFS cannot express (mysheetai.com/answers/sumproduct).
• A running total, built with an anchored range so it accumulates as you copy it down a column (mysheetai.com/answers/running-total).
• Pivot tables, for grouping and totaling data by drag-and-drop instead of writing a formula at all (mysheetai.com/answers/pivot-table-basics).
• Percentage change, for comparing an old value to a new one (mysheetai.com/answers/percentage-change).

Lookups: finding one value using another

• VLOOKUP, the classic left-to-right lookup (mysheetai.com/answers/vlookup).
• INDEX and MATCH together, for a lookup that can go in any direction (mysheetai.com/answers/index-match).
• XLOOKUP, the modern replacement for both, where your spreadsheet version supports it (mysheetai.com/answers/xlookup).

Logic and error handling

• A basic or nested IF, for a result that depends on a condition (mysheetai.com/answers/nested-if).
• IFERROR, for showing a clean message instead of a raw error like #N/A (mysheetai.com/answers/iferror).
• Conditional formatting, for highlighting cells automatically based on a rule instead of a formula result you have to read (mysheetai.com/answers/conditional-formatting).

Text and dates

• TEXT, for formatting a number or date as exactly the text you want (mysheetai.com/answers/text-date-formatting).
• TRIM and CLEAN, for stripping extra spaces and hidden characters that break lookups and totals (mysheetai.com/answers/trim-clean).
• CONCATENATE or TEXTJOIN, for combining text from several cells into one (mysheetai.com/answers/concatenate-textjoin).
• Splitting one column of text into several, with TEXTSPLIT or SPLIT depending on your spreadsheet program (mysheetai.com/answers/split-text).
• DATEDIF, for the difference between two dates in days, months, or years (mysheetai.com/answers/datedif).
• TODAY and NOW, for a live current date or date and time, and for calculating age or days elapsed (mysheetai.com/answers/today-now).

Structure and cleanup

• Absolute references (the $ symbol), so a formula copies down or across correctly instead of shifting (mysheetai.com/answers/absolute-references).
• Removing duplicate rows, either with a built-in tool or a flag formula (mysheetai.com/answers/remove-duplicates).

Newer functions that return a whole list at once

In modern Excel (365 and 2021 or newer) and Google Sheets, a small set of functions can return several results from a single formula instead of one value per cell. FILTER returns only the rows matching a condition (mysheetai.com/answers/filter-function), and UNIQUE returns a list of distinct values with duplicates removed (mysheetai.com/answers/unique-function). Both are worth learning once you have the basics above, since they replace what used to take a helper column or a manual filter.

Describe it instead of memorizing all of this

You do not need to keep this whole list memorized. As covered in the previous post in this series, the useful habit is describing the result you want in plain English; the right formula from the list above tends to follow naturally once the description is clear. MySheetAI's free formula generator (mysheetai.com/formula-generator) takes exactly that plain-English description and returns a working formula for Excel and Google Sheets, explained, with no account required. If you would rather have a whole spreadsheet checked and summarized for you, a free account includes one detailed analysis a week (up to 3MB), and Pro removes that limit for $12 a month or $100 a year.