Future Savvy Logo
Excel Formulas Cheat Sheet
  • TIPS & TRICKS/
  • Excel Formulas Cheat Sheet: 34 Formulas With Examples/
Tips & Tricks

Excel Formulas Cheat Sheet: 34 Formulas With Examples

Written by FutureSavvy Team
Updated 9 Oct 2026 · 1 min read

Every formula below is written against the same shape of sheet: data starts in row 2, names in column A, dates in B, amounts in C. Change the ranges and the rest works as printed. Each one has a one-line note on what it does and the mistake people most often make with it.

Download the printable PDF (two pages)

Maths and aggregation

Excel formula
=SUM(B2:B13)
Adds a range. Blanks and text are ignored, so a stray note in the column will not break it.
Excel formula
=AVERAGE(B2:B13)
Mean of the numbers in the range. Empty cells are skipped; a zero is counted.
Excel formula
=MIN(B2:B13)
Smallest value. Pair with MAX for a quick range check on imported data.
Excel formula
=MAX(B2:B13)
Largest value.
Excel formula
=COUNT(B2:B13)
How many cells hold a number. Use COUNTA to count anything non-blank.
Excel formula
=ROUND(B2,2)
Round to two decimals. ROUNDUP and ROUNDDOWN force the direction.
Excel formula
=SUBTOTAL(109,B2:B13)
Sum that ignores rows hidden by a filter. 109 is sum, 101 is average, 103 is count.

Logic

Excel formula
=IF(B2>=50,"Pass","Fail")
One test, two outcomes. Nest sparingly; IFS reads better past two levels.
Excel formula
=IFS(B2>=70,"Merit",B2>=50,"Pass",TRUE,"Fail")
Several tests in order; the first true one wins. The final TRUE is the catch-all.
Excel formula
=IFERROR(B2/C2,0)
Return 0 instead of #DIV/0! or any other error. Use IFNA when you only want to catch a failed lookup.
Excel formula
=AND(B2>0,C2<100)
TRUE only if every test passes. OR needs just one.

Lookups

Excel formula
=XLOOKUP(E2,A2:A200,C2:C200,"Not found")
Find E2 in column A, return the matching value from column C. Exact match by default, searches any direction, has a built-in not-found value.
Excel formula
=VLOOKUP(E2,A2:C200,3,FALSE)
The older version: looks in the first column of the table, returns column 3. FALSE forces an exact match. Breaks if you insert a column.
Excel formula
=INDEX(C2:C200,MATCH(E2,A2:A200,0))
Two-step lookup that works in every Excel version. MATCH finds the row, INDEX returns it.

Conditional sums and counts

Excel formula
=SUMIF(A2:A200,"North",C2:C200)
Sum column C where column A says North.
Excel formula
=SUMIFS(C2:C200,A2:A200,"North",B2:B200,">="&DATE(2026,1,1))
Sum with several conditions. Ranges first, then each criteria pair.
Excel formula
=COUNTIF(A2:A200,"North")
How many rows say North. Wildcards work: "North*".
Excel formula
=COUNTIFS(A2:A200,"North",C2:C200,">1000")
Count with several conditions.
Excel formula
=AVERAGEIFS(C2:C200,A2:A200,"North")
Average of C where A is North.

Text

Excel formula
=TRIM(A2)
Strip leading, trailing and doubled spaces. Run this on anything pasted from another system.
Excel formula
=LEFT(A2,3)
First three characters. RIGHT takes from the end, MID from the middle.
Excel formula
=TEXTSPLIT(A2,",")
Split on a delimiter into separate cells. Excel 365 only; Text to Columns does it in older versions.
Excel formula
=TEXTJOIN(", ",TRUE,A2:A10)
Join a range with a separator. TRUE skips blanks.
Excel formula
=TEXT(B2,"dd mmm yyyy")
Format a number or date as text for a label or a concatenated sentence.
Excel formula
=LEN(A2)
Character count. Useful for spotting hidden spaces: LEN(A2)-LEN(TRIM(A2)).

Dates

Excel formula
=TODAY()
Today's date, recalculates every time the workbook opens. NOW() includes the time.
Excel formula
=DATEDIF(B2,TODAY(),"Y")
Complete years between two dates. "M" for months, "D" for days.
Excel formula
=EOMONTH(B2,0)
Last day of the month that B2 is in. EOMONTH(B2,-1) is the end of the previous month.
Excel formula
=NETWORKDAYS(B2,C2)
Working days between two dates, weekends excluded. Add a holidays range as the third argument.
Excel formula
=EDATE(B2,3)
Same day three months later.

Dynamic arrays (Excel 365)

Excel formula
=UNIQUE(A2:A200)
Distinct values, spilled down as many rows as needed.
Excel formula
=FILTER(A2:C200,B2:B200>1000)
Rows where column B is over 1000. Add a third argument for the no-results message.
Excel formula
=SORT(A2:C200,3,-1)
Sort by the third column, descending.
Excel formula
=SEQUENCE(12,1,1,1)
The numbers 1 to 12 in a column. Handy for building date lists with EDATE.

Which course covers these

SUM through COUNTIF are Excel Introduction. XLOOKUP, SUMIFS, the text and date functions and PivotTables are Excel Intermediate. Dynamic arrays, LET and LAMBDA, Power Query and modelling are Excel Advanced. Not sure which you are? The fifteen-question self-assessment places you in two minutes.

Take the Excel skills self-assessmentSee Excel Intermediate

Next available dates

Excel Introduction

Live, instructor-led training with a real trainer. Book a seat on a scheduled date below, or get a quote for your team to run it privately.

  • 5 Nov 2026

    Thursday · Virtual · 1 day

    £325per person + VAT

  • 9 Nov 2026

    Monday · Virtual · 1 day

    £325per person + VAT

  • 1 Dec 2026

    Tuesday · Virtual · 1 day

    £325per person + VAT

See 1 more date and the full outline

Other courses related to this article

Ready to train your team?

Tell us what you need and we'll come back to you with a detailed quote.

  • Tailored contentBuilt around your team's work and skill levels.
  • Flexible schedulingDates and times that suit your team, not ours.
  • Your place or onlineDelivered at your office or virtually.
  • Better value per headOne trainer, one day, your whole team.
  • A dedicated trainerYour team gets all of the trainer's attention.
  • Realistic examplesSample data built around the kind of work your team does.
Get a quote for your team

We reply with available dates and a price for your team. No obligation.

Related Articles

  • 25 Most Popular Excel Formulas & Functions
    Tips & Tricks

    25 Most Popular Excel Formulas & Functions

    Mastering Excel formulas and functions boosts efficiency, accuracy, and workflow, so the article offers a downloadable PDF of 25 essential formulas - ranging from SUM and IF to VLOOKUP and XLOOKUP - each with syntax and practical use cases. The article also spotlights five must-know Excel visualizations (heat maps, box plots, Pareto charts, histograms, and scatter plots) and compares Excel’s hands-on data control with Power BI’s cloud-hosted, interactive dashboards for large datasets.

    Read More

Insights for modern teams

Stay ahead with our latest learning trends, tools, and success stories. Enter your email address below to receive updates from us.

Future Savvy
Future Savvy

Registered England and Wales: 11477692 VAT Number: GB 3123317 52All trademarks are owned by their respective owners. Click here for details.

  • Mastercard
  • American Express
  • Visa
© 2026 Future Savvy. All Rights Reserved.

Future Savvy is a trading name of Systematix Limited. Registered in England and Wales, company number 11477692. Registered office: Blackwell House, Guildhall Yard, London EC2V 5AE.