The 10 Excel Formulas That Replace 90% of Manual Work

The Pareto Principle Applied to Excel

Excel has over 400 functions. Most Excel users interact with fewer than 20 on a regular basis. And most of the repetitive manual work — the copy-pasting, the manual lookups, the filter-and-count routines — is eliminated by just 10 formulas.

These are not the most obscure or technically impressive formulas. They are the ones with the highest ratio of effort saved to complexity required. Master these 10 and you will eliminate the majority of repetitive spreadsheet work.

1. XLOOKUP — Kills Manual "Find and Copy"

Before: You have a product ID in column A. The price is in a different sheet or table. You manually find the row, read the price, type it in. Repeat 500 times.

After: One formula that updates automatically when prices change.

=XLOOKUP(A2, ProductTable[ID], ProductTable[Price], "Not Found")

XLOOKUP replaced VLOOKUP as the standard lookup function in Microsoft 365. It looks in any direction, has built-in error handling (the fourth argument), and does not break when columns are inserted or deleted.

When NOT to use it: When you need to return multiple matching rows (use FILTER instead), or when you are working in Excel 2016 or earlier (use INDEX/MATCH).

See the full lookup guide: Lookup & Reference Mastery

2. SUMIFS — Kills Manual Filtering and Adding

Before: Filter the table by region, scroll to the total, note the number, remove the filter, filter by the next region, repeat.

After: One formula that calculates any conditional total instantly.

=SUMIFS(SalesTable[Amount], SalesTable[Region], "West", SalesTable[Quarter], "Q4")

SUMIFS handles multiple criteria — sum where Region equals "West" AND Quarter equals "Q4." The plural SUMIFS is always preferred over singular SUMIF because it handles multiple conditions and has a more consistent argument order (sum range comes first).

When NOT to use it: When you need to sum across multiple worksheets (use 3D references), or when your criteria are highly complex (consider a pivot table instead).

3. FILTER — Kills "Copy Matching Rows to Another Sheet"

Before: Filter the table, copy the visible rows, paste to another location. Repeat monthly. Maintain two copies of data that drift out of sync.

After: A dynamic formula that extracts matching rows automatically.

=FILTER(SalesTable, SalesTable[Region]="West", "No data")

FILTER is a dynamic array function — it spills results into as many cells as needed. The extracted subset updates automatically when source data changes. No more manually maintaining filtered copies.

Combine with multiple criteria using multiplication:

=FILTER(SalesTable, (SalesTable[Region]="West")*(SalesTable[Status]="Active"))

When NOT to use it: In Excel 2019 or earlier (dynamic arrays are not available). Use pivot tables as an alternative.

4. UNIQUE + SORT — Kills Manual Deduplication and Sorting

Before: Copy a column, paste elsewhere, Data → Remove Duplicates, manually sort. Repeat every time the source data updates.

After: A formula chain that stays current automatically.

=SORT(UNIQUE(CustomerTable[Company]))

This returns a sorted list of unique company names. Add a new customer and the list updates on recalculation. Add a new customer with a company that already exists and the deduplication handles it automatically.

Chain with FILTER for more specific extraction:

=SORT(UNIQUE(FILTER(CustomerTable[Company], CustomerTable[Status]="Active")))

When NOT to use it: When you need the unique values as static data rather than a dynamic formula (paste as values after generating).

5. TEXTJOIN — Kills Manual Concatenation

Before: =A2&", "&B2&", "&C2&", "&D2 — works until some cells are blank, then you get double commas. Or you write a complex IF chain to skip blanks.

After: One readable formula that handles blanks automatically.

=TEXTJOIN(", ", TRUE, A2:A10)

The TRUE argument skips empty cells. The delimiter is flexible — use ", " for comma-separated, " | " for pipes, or any other separator. Works across ranges, not just individual cells.

=TEXTJOIN("; ", TRUE, FILTER(Tags, Category="Priority"))

Combine with FILTER to join only the values that meet a condition.

When NOT to use it: When you need more control over which cells to join based on complex logic (build the logic with IF before passing to TEXTJOIN).

6. IF + AND/OR — Kills Simple Decision-Making

Before: Manually reviewing rows, applying flags, typing status values based on conditions.

After: Formulas that apply decisions consistently and instantly.

=IF(AND(Score>=80, Attendance>="90%"), "Eligible", "Not Eligible")

For multiple conditions, use IFS instead of nested IF:

=IFS(Score>=90, "A", Score>=80, "B", Score>=70, "C", Score<70, "F")

IFS evaluates conditions in order and returns the first true result. It is far more readable than nested IF statements.

When NOT to use it: When conditions are highly complex (consider SWITCH for value-matching scenarios) or when you need to handle many exceptions (a lookup table is often cleaner).

7. INDEX/MATCH — Kills VLOOKUP Limitations

Before: VLOOKUP that breaks every time someone inserts a column, or that cannot look left.

After: A lookup combination that works in any direction and handles column changes gracefully.

=INDEX(ProductTable[Name], MATCH(A2, ProductTable[ID], 0))

For two-way lookups (intersection of row and column):

=INDEX(RateMatrix, MATCH(Region, RowHeaders, 0), MATCH(Product, ColHeaders, 0))

While XLOOKUP handles most modern use cases, INDEX/MATCH remains valuable for complex multi-dimensional lookups and in workbooks that must be compatible with older Excel versions.

When NOT to use it: In Microsoft 365, prefer XLOOKUP for simpler lookups — it is more readable and has built-in error handling.

8. LET — Kills Repeated Calculations in Complex Formulas

Before: A long formula that references the same complex expression three times, making it slow to calculate and impossible to read.

After: Named intermediate values that calculate once and make the formula self-documenting.

=LET(
  base_price, XLOOKUP(A2, Products[ID], Products[BasePrice]),
  discount, IF(Quantity>=100, 0.15, IF(Quantity>=50, 0.10, 0)),
  base_price * (1 - discount)
)

LET is available in Microsoft 365. It is the spreadsheet equivalent of declaring variables in code — it dramatically improves readability for any formula longer than a single function call.

When NOT to use it: For simple one-line formulas where the variable naming adds complexity without clarity benefit.

9. IFERROR — Kills Ugly #N/A Displays

Before: A spreadsheet full of #N/A, #REF!, and #VALUE! errors that distract from the actual data and cause formulas that reference those cells to fail.

After: Clean, meaningful text when lookups find no match.

=IFERROR(VLOOKUP(A2, LookupTable, 2, FALSE), "Not Found")

Use IFNA instead when you only want to catch #N/A errors and want other error types to remain visible (they indicate real problems):

=IFNA(XLOOKUP(A2, Codes, Labels), "Unknown Code")

When NOT to use it: Do not use IFERROR to silence errors you have not diagnosed. Understand what is causing the error first. Silent errors create data quality problems that surface later in unexpected ways.

10. TEXT — Kills Formatting Complaints

Before: "Why does my date show as 45678?" "Why does the number lose its formatting when I combine it with text?"

After: Complete control over how numbers and dates appear when used in text or exported.

="Report for "&TEXT(ReportDate, "MMMM D, YYYY")

="Total: "&TEXT(SUM(Revenue), "$#,##0.00")

TEXT converts any number or date to a formatted string. The format codes match Excel's number formatting system — any format you can apply with Ctrl+1 can be expressed as a TEXT format string.

When NOT to use it: When the result will be used in further calculations — TEXT converts values to text, which cannot be summed or compared numerically. Use TEXT only at the display/output stage.

The Complete Toolkit

These 10 formulas cover the core operations of business analysis:

| Category | Formulas |

|----------|----------|

| Lookup | XLOOKUP, INDEX/MATCH |

| Aggregation | SUMIFS |

| Filtering | FILTER |

| Deduplication | UNIQUE + SORT |

| Text | TEXTJOIN, TEXT |

| Logic | IF + AND/OR (IFS) |

| Complexity management | LET |

| Error handling | IFERROR / IFNA |

Master these and you have eliminated the vast majority of repetitive spreadsheet work. The remaining 390+ functions in Excel address specialized use cases — statistics, financial modeling, engineering calculations — that you will learn as you need them.

For the complete syntax reference, see the Excel Formula & Function Reference.

For the context that makes these formulas maintainable, see Data Modeling & Structured Tables — clean data structure is what makes lookup and aggregation formulas reliable at scale.

And for users who have outgrown Excel for project management and work tracking, see the Smartsheet Mastery pillar — the relational data thinking you build in Excel transfers directly.