15 Excel formulas that will save you hours in 2026
Every Excel workflow has a slow way and a fast way. The slow way involves manual clicking, copy-pasting, and rebuilding the same thing every week. The fast way is one formula that does the work automatically and updates as data changes. This is a curated list of 15 formula patterns that eliminate hours of manual work — with real, copy-ready examples for each.
⚡ How this list is different
This isn't a "top functions" list — it's a top patterns list. Each entry solves a real workflow problem that Excel users spend hours on manually. Copy the formulas, replace the range references with your own data, and reclaim your afternoons.
Quick navigation — jump to any pattern
- Instant deduplication
- Auto-updating filtered lists
- One-formula leaderboards
- Dynamic comma-separated lists
- Bulletproof lookups
- Multi-criteria totals
- Clean messy text
- Extract text before/after delimiter
- Fiscal quarter from any date
- Working days between dates
- Age that stays accurate
- Running totals
- Auto-tier customers with IFS
- Count unique matching values
- Reusable LAMBDA functions
The rules for this list
Every formula on this list meets three criteria: it replaces a common manual task, it updates automatically when source data changes, and it's short enough to memorize. No 200-character monsters that require reference material to type. If a formula can't fit in one line of clear code, it doesn't belong here.
Extract unique values from any list
Before UNIQUE, deduplication meant Data → Remove Duplicates (destructive) or Advanced Filter dialogs (fiddly). Now it's one formula that spills unique values automatically and updates as source data changes.
Filtered data that updates automatically
FILTER extracts rows matching your criteria into a new location, leaving source data untouched. When source data changes, the filter output updates instantly. Perfect for dashboards where users pick a filter and see results update in real time.
Top 10 anything, sorted and filtered in one line
Combining SORT with FILTER produces ranked leaderboards that maintain themselves. Add data, remove data, edit values — the top 10 always shows the current top 10 in the right order.
The 3 means "sort by the 3rd column" and -1 means "descending order". Change the region filter or the sort column and the leaderboard rebuilds instantly.
Turn a column into a comma-separated string
Need to email a list of names? Build a comma-separated list for a form? TEXTJOIN handles it in one formula, skipping blank cells automatically.
The TRUE flag tells TEXTJOIN to skip blank cells. Combine with FILTER for dynamic lists that only include active/matching entries:
See the full text-joining showdown in CONCATENATE vs TEXTJOIN vs &.
XLOOKUP with graceful fallback
XLOOKUP's built-in 4th argument handles the "not found" case gracefully. No IFERROR wrapper needed. No cryptic #N/A cluttering your dashboards.
If the ID isn't found, you get "New customer" instead of #N/A. Cleaner than the old =IFERROR(VLOOKUP(...), "New customer") pattern. See XLOOKUP vs VLOOKUP for the full lookup story.
Sum values matching multiple conditions
SUMIFS totals values where every condition is met. This one formula replaces what would be filtering data three times, copying results, and manually adding them up.
Reference cells instead of hardcoded values for reusable dashboards:
Change D1, D2, or D3 and every total updates. See SUMIF vs SUMIFS for the argument-order gotchas.
TRIM + SUBSTITUTE to fix messy data
Imported data is usually messy — extra spaces, wrong casing, unwanted characters. TRIM removes extra spaces (leading, trailing, and duplicate internal). SUBSTITUTE replaces specific characters. Together, they handle 90% of text cleanup.
This cleans typical web-copied text: converts non-breaking spaces (CHAR(160)) to regular spaces, then trims excess whitespace. Chain more SUBSTITUTE calls to remove other characters.
For phone numbers or IDs that need only digits:
Split text at a character in one formula
Need to extract the domain from an email? Get the first name from "First Last"? TEXTBEFORE and TEXTAFTER (Excel 365) handle this cleanly without the old LEFT/RIGHT + FIND gymnastics.
Returns everything before the "@" — perfect for extracting usernames from emails. For everything after:
For older Excel without TEXTBEFORE/TEXTAFTER, the classic equivalent is =LEFT(A2, FIND("@", A2)-1) — much less readable.
Get the quarter from a date in one formula
Standard calendar quarters (Q1 = Jan-Mar) are trivial. Fiscal quarters offset by a month or more require a small adjustment. This formula returns Q1-Q4 based on the standard calendar:
For fiscal years starting in July (US federal, Indian FY), add 6 months worth of adjustment:
Adjust the -7 to match your fiscal year start month. This one formula in a Quarter column replaces manual quarter assignment across thousands of rows.
Count business days automatically
NETWORKDAYS counts weekdays between two dates, excluding weekends. Add an optional holiday list to exclude company holidays too.
Perfect for SLA tracking, project deadlines, or billable-day calculations. If your holiday list is in H1:H20:
For non-standard weekends (Middle East uses Fri-Sat), use NETWORKDAYS.INTL which lets you specify custom weekend days.
Calculate age correctly with DATEDIF
The naive =(TODAY()-BirthDate)/365.25 gives approximate ages that can be off by a day. DATEDIF handles leap years, month boundaries, and edge cases correctly — always returns the exact completed years.
DATEDIF is a "hidden" function — it exists but isn't in Excel's autocomplete. You have to type it yourself. Change the "Y" to "M" for months, "D" for days, or "YM" for months after complete years (for "3 years and 4 months" style output).
Running total in one formula, drag down
Manual running totals require you to type =B2 in the first row, =C2+B3 in the second, and so on. A single SUM with a mixed reference makes it a drag-down formula.
The $B$2 anchor stays fixed while B2 grows as you drag down. Row 2 sums B2. Row 3 sums B2:B3. Row 4 sums B2:B4. Instant running total that updates as you add data.
In modern Excel, SCAN with LAMBDA creates a spilled running total in one formula:
Assign tiers based on value with IFS
Bucket customers, prioritize tasks, assign grades — anywhere you need multi-branch logic without nested IF chaos.
The final TRUE acts as a catch-all "else" — every row that doesn't match earlier conditions gets "Bronze". Compare this to nested IF: =IF(Revenue>=100000,"Platinum",IF(Revenue>=50000,"Gold",IF(Revenue>=10000,"Silver","Bronze"))) — same logic, worse syntax.
See IFS vs Nested IF for the full comparison.
Count distinct values that meet criteria
The classic Excel puzzle: "how many unique customers ordered in Q3?" Combining FILTER, UNIQUE, and COUNTA solves it in one clean formula.
The old-Excel equivalent was =SUMPRODUCT((Quarter="Q3")/COUNTIF(Customers, Customers)) — cryptic, slow, and array-formula-based. The modern version is dramatically clearer.
See COUNTIF vs COUNTIFS vs COUNTA for the full counting family.
Build a LAMBDA once, reuse forever
LAMBDA lets you build custom functions without VBA. Define a complex calculation once, save it in the Name Manager, and use it across your entire workbook as if it were built-in.
Example: a NET_MARGIN function that takes revenue, COGS, and OpEx:
Save to Name Manager as NET_MARGIN, then use anywhere:
Change the LAMBDA formula in Name Manager and every usage across the workbook updates automatically. This is the closest Excel has come to real programming — and it saves hours across large workbooks by eliminating copy-paste-modify inconsistencies.
⏱ Total time saved across all 15 formulas
The numbers vary by workload, but the pattern is consistent: analysts who master these 15 formula patterns typically save 1-2 hours per day on tasks that used to be manual. That's a full workday every week, back in your pocket.
And that's just the time savings. The bigger benefit is error reduction — automated formulas don't forget rows, don't skip columns, and don't get tired at 4pm on Fridays.
How to actually start using these
You can't learn 15 formulas at once. Here's the practical adoption plan:
Today: Pick 3 formulas from this list that solve problems you've had this week. Copy them into your workbook. Replace the range references with your data. See them work.
This week: Add the other formulas relevant to your daily work. Skip the ones that don't apply — nobody needs all 15.
This month: Refactor one existing workbook to use these patterns instead of manual workflows. Note how much time it saves you the next time you run that workflow.
This quarter: Build a personal formula reference in your Notes app or a document. When you find a pattern that saves you time, add it. Your reference will become genuinely valuable within 6 months.
Roughly 80% of your Excel time savings come from mastering 20% of the possible formulas. This list of 15 is that 20%. There are hundreds of other formulas, but most either duplicate what these do or solve edge cases you'll hit once a year. Master these first — expand later when specific problems demand it.
The compatibility table
| Formula category | Minimum Excel version | Google Sheets |
|---|---|---|
| TRIM, SUBSTITUTE, DATEDIF, NETWORKDAYS, SUM anchor | Every version | Yes |
| SUMIFS, COUNTIFS, IFERROR | Excel 2007 | Yes |
| IFS, TEXTJOIN, IFNA | Excel 2019 | Yes |
| XLOOKUP, FILTER, UNIQUE, SORT | Excel 365 / 2021 | Yes |
| TEXTBEFORE, TEXTAFTER, LAMBDA, SCAN | Excel 365 (2022+) | Partial |
What if you're stuck on old Excel?
If you're on Excel 2016 or older, you can still use about half of this list. The classic equivalents for the modern functions:
Instead of XLOOKUP: INDEX/MATCH or VLOOKUP (see XLOOKUP vs VLOOKUP).
Instead of FILTER: Advanced Filter dialog, or SUMIFS/COUNTIFS with criteria arrays.
Instead of UNIQUE: Advanced Filter → Unique Records Only, or PivotTable field rows.
Instead of IFS: Nested IF (uglier but functional).
Instead of TEXTJOIN: Chain & operators manually.
Instead of LAMBDA: VBA custom functions (much heavier).
The classic approaches work but require significantly more code, more debugging, and more manual updates. Upgrading to Excel 365 or 2021 pays for itself within weeks of active use.
Related resources
Go deeper into any of these
Complete pillar guides with real-world patterns and companion workbooks for lookups, logic, dynamic arrays, and error handling.
VLOOKUP Guide → IF Guide → Dynamic Arrays Guide → Errors Guide →Frequently asked questions
What Excel formulas save the most time?
The formulas that save the most time are dynamic-array functions like FILTER, UNIQUE, and SORT which replace entire manual workflows in one line. TEXTJOIN eliminates hours of manual concatenation. XLOOKUP replaces multi-step VLOOKUP + IFERROR patterns. Combined, these modern Excel functions can turn 30-minute manual workflows into instant one-formula solutions.
Do these formulas work in Excel 2019?
Most work in Excel 2019 including IFS, TEXTJOIN, SUMIFS, COUNTIFS, IFNA, and TRIM. The dynamic array functions FILTER, UNIQUE, SORT, XLOOKUP, and LAMBDA require Excel 365 or Excel 2021. For Excel 2016 or older, use the classic equivalents — VLOOKUP for lookups, nested IF for logic, SUMPRODUCT for unique counts.
Which Excel formula is best for cleaning messy data?
The combination of TRIM and SUBSTITUTE handles most text cleanup tasks. TRIM removes leading, trailing, and duplicate internal spaces. SUBSTITUTE replaces specific characters or strings. For more complex cleaning, combine with UPPER, LOWER, PROPER, or LEFT/RIGHT/MID. TEXTSPLIT in Excel 365 handles delimiter-based splitting in one formula.
How do I count unique values matching criteria in Excel?
In Excel 365 or 2021, use =COUNTA(UNIQUE(FILTER(range, condition))). This combines FILTER to apply the criteria, UNIQUE to deduplicate the results, and COUNTA to count them. In older Excel, the classic pattern is =SUMPRODUCT((condition)/COUNTIF(range, range)) but this is significantly harder to read and debug.
What is the fastest way to remove duplicates in Excel?
For a one-time cleanup, use Data → Remove Duplicates from the ribbon — it is a single click. For a formula-based approach that updates automatically as source data changes, use =UNIQUE(range) which spills the deduplicated values into a new range. UNIQUE requires Excel 365 or Excel 2021.
How do I calculate age accurately in Excel?
Use =DATEDIF(BirthDate, TODAY(), "Y") to calculate age in complete years. DATEDIF handles leap years correctly and always returns an accurate age. Simpler formulas like =(TODAY()-BirthDate)/365.25 give approximate ages but can be off by a day. DATEDIF is the correct professional choice.
Do these formulas work in Google Sheets?
Most do. XLOOKUP, FILTER, UNIQUE, SORT, IFS, TEXTJOIN, IFNA, SUMIFS, COUNTIFS, TRIM, SUBSTITUTE, and DATEDIF all work in Google Sheets with identical syntax. LAMBDA works in both platforms with slightly different capabilities. Google Sheets adds some exclusive functions like QUERY and IMPORTRANGE that are not available in Excel. See Excel vs Google Sheets for more platform comparisons.
How much time can I realistically save with these formulas?
Analysts who master these 15 patterns typically save 8-12 hours per week — roughly one full workday. The savings compound because automated formulas don't need to be rerun manually as data changes. Set up once, works forever. Additional benefit: dramatically fewer errors from manual copy-paste mistakes.
Which formula should I learn first?
Start with XLOOKUP if you do lookups (most analysts do). Add SUMIFS for aggregation and FILTER for extracting data subsets. These three cover 60% of typical analyst work. From there, add functions based on your specific workflow — TEXTJOIN if you build lists, DATEDIF if you work with ages/durations, IFS if you have tier-based logic.
Bottom line: Every formula on this list replaces a manual task that Excel users spend hours on weekly. Copy them, adapt them to your data, and reclaim your afternoons.