Excel Cheat Sheet: 10 Formulas You’ll Actually Use Every Day (for business and beyond)
- Ethan Cole
- Oct 12
- 8 min read
1) SUM: add numbers fast without manual math
Goal: add a column or several ranges in one cell.
Syntax:=SUM(number1, [number2], …) or =SUM(B2:B12) or =SUM(B2:B12, D2:D12)
Example scenario: add monthly expenses in C2:C13.
Steps
Click the result cell, for example C14.
Type =SUM( then drag to select C2:C13.
Close the bracket ) and press Enter.
To add multiple blocks hold Ctrl while selecting and separate with commas: =SUM(C2:C13, E2:E13).
Avoid
Hidden text values like $120 typed with a dollar sign as text. Use numbers with cell formatting instead.
Blank rows inside the range if you rely on AutoSum guesswork. Confirm the marquee range.
Pro tip (business)
Combine with filters or a helper column to sum only visible rows using SUBTOTAL(9, range) when working with filtered reports.
Image: SUM quick card
2) AVERAGE: the everyday mean
Goal: calculate the average value in a range.
Syntax:=AVERAGE(B2:B13)
Example scenario: average order value from B2:B500.
Steps
Click a result cell.
Enter =AVERAGE( and select B2:B500.
Press Enter.
Avoid
Using AVERAGE when many zeros should be excluded. Consider AVERAGEIF(range, "<>0").
Pro tip (business)
Build KPI cards with AVERAGE for lead response times, ticket resolution, and delivery times.
Image: AVERAGE quick card
3) COUNT and COUNTA: how many entries you have
Goal: count numbers only, or count all non-blank cells.
Syntax:=COUNT(range) counts numeric cells=COUNTA(range) counts non-empty cells
Example scenario: count how many paid invoices in E2:E200 where amounts are numeric.
Steps
Enter =COUNT(E2:E200) to count numbers.
Use =COUNTA(A2:A200) to count filled names or IDs.
Avoid
Assuming COUNTA ignores spaces. A cell with a space is not blank.
Pro tip (business)
Pair COUNTIF(range, ">0") to count positive amounts or delivered quantities.
Image: COUNT quick card
4) MIN and MAX: find the smallest and largest
Goal: pull the lowest or highest value quickly.
Syntax:=MIN(C2:C100)=MAX(C2:C100)
Example scenario: lowest and highest quote in a price list.
Steps
In the “Lowest” cell enter =MIN(C2:C100).
In the “Highest” cell enter =MAX(C2:C100).
Avoid
Mixing text and numbers in the same column. Apply a consistent number format.
Pro tip (business)
Wrap with IFERROR when the range might be empty: =IFERROR(MIN(C2:C100), "").
Image: MIN/MAX quick card
5) IF: make decisions directly in cells
Goal: return one value when a condition is true and another when it is false.
Syntax:=IF(logical_test, value_if_true, value_if_false)
Example scenario: mark orders as Pass if margin in D2 is at least 20%.
Steps
In E2 type =IF(D2>=0.2, "Pass", "Review").
Fill down the column for all rows.
To check two conditions use AND: =IF(AND(D2>=0.2, F2="Paid"), "Ready", "Hold").
For either-or logic use OR: =IF(OR(G2="VIP", H2="Rush"), "Priority", "Normal").
Avoid
Quoting numeric thresholds. Use numbers for math, text in quotes for labels.
Nested IFs that get hard to read. Prefer IFS or use helper columns.
Pro tip (business)
Convert the results into a slicer-friendly Yes/No field for dashboards.
Image: IF quick card
6) SUMIF and SUMIFS: conditional totals that answer real questions
Goal: sum values that meet one or more conditions such as category and date range.
Syntax:=SUMIF(range, criteria, sum_range)=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example scenarios
Total spend for Groceries: =SUMIF(A:A, "Groceries", C:C)
Total sales for Groceries in 2025:=SUMIFS(C:C, A:A, "Groceries", B:B, ">=1/1/2025", B:B, "<=12/31/2025")
Steps
Identify the column with criteria and the column to sum.
Use plain text criteria like "Groceries" or cell references such as =SUMIF(A:A, F2, C:C).
For dates use two criteria on the date column to set a start and end.
Avoid
Mismatched range sizes. Every referenced range must have the same height and width.
Storing dates as text. Ensure the date column is a true date type.
Pro tip (business)
Combine with a table and slicers for instant segmented revenue views.
Image: SUMIF/SUMIFS quick card
7) XLOOKUP: modern lookups that work left or right (A new, more powerful VLOOKUP option)
Goal: return a value from another column based on a key. Works left-to-right or right-to-left and supports exact match by default.
Syntax:=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Example scenario: find a product price in column C by SKU typed in E2 that is listed in A:A.
Steps
In the price result cell enter:=XLOOKUP(E2, A:A, C:C, "Not found")
Press Enter.
Copy the formula down for more rows.
Avoid
Hidden leading or trailing spaces in keys. Use TRIM on imported data.
Ambiguous duplicates. Decide whether to deduplicate or define a rule for the first match.
Pro tip (business)
Retrieve entire rows by using structured references in tables and spill into adjacent columns.
Image: XLOOKUP quick card
8) INDEX + MATCH: flexible and robust when XLOOKUP is not available
Goal: combine the row position found by MATCH with the value returned by INDEX.
Syntax:=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example scenario: fetch a client name from C:C where ID in A:A equals the ID in E2.
Steps
Type =INDEX(C:C,
Add MATCH(E2, A:A, 0) then close the parenthesis.
Press Enter.
To search by both SKU and Region use MATCH(1, (A:A=E2)*(B:B=F2), 0) entered as a dynamic array or confirmed with Ctrl+Shift+Enter in legacy Excel.
Avoid
Using relative ranges of different sizes. Keep return and lookup ranges aligned.
Non-exact match by mistake. Use 0 as the third argument in MATCH for exact keys.
Pro tip (business)
Use MATCH to construct dynamic column numbers in INDEX(range, row, column) when building reporting grids.
Image: INDEX+MATCH quick card
9) TEXT, CONCAT, and TEXTJOIN: clean labels and formatted values
Goals:
Format numbers and dates for display with TEXT
Combine values across columns with CONCAT or TEXTJOIN
Syntax:=TEXT(value, "format_code")=CONCAT(text1, [text2], …)=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Examples
Currency label: =TEXT(D2, "$#,##0.00")
Date label: =TEXT(B2, "mmm d, yyyy")
Full name: =CONCAT(A2, " ", B2)
Join many columns with commas: =TEXTJOIN(", ", TRUE, A2:C2)
Steps
Decide on the output cell where a display label is needed.
Build a TEXT wrapper around raw values to control the display.
Join pieces with CONCAT or use TEXTJOIN when you need a delimiter and want to ignore blanks.
Avoid
Feeding the formatted text back into math. Keep raw numbers in separate cells for calculations.
Pro tip (business)
Build clean SKU descriptions for invoices and packing slips that merge size, color, and model codes.
Image: TEXT and CONCAT quick card
10) Dates, working days, and PMT: schedules and finance
Goals:
Today’s date
Month ends
Working days between two dates
Payment for a loan
Syntax:=TODAY() returns the current date=EOMONTH(start_date, months) moves to month end=NETWORKDAYS(start_date, end_date, [holidays]) counts business days=PMT(rate, nper, pv) returns payment for a loan
Examples
Last day of this month: =EOMONTH(TODAY(), 0)
Business days this quarter: =NETWORKDAYS(B2, C2, Holidays!A2:A20)
Car payment on a five-year loan of 15k at six percent: =PMT(0.06/12, 60, -15000)
Steps
For rolling reports place =TODAY() in a helper cell to anchor all date logic.
Use EOMONTH to build fiscal periods and EDATE for month offsets.
In finance models use positive cash for inflows and negative for outflows, so principal in PMT is negative to return a positive payment.
Avoid
Treating text like dates. If a date fails to increment when you add days, convert it with DATEVALUE.
Forgetting holiday ranges with NETWORKDAYS if you track SLAs or payroll.
Pro tip (business)
Create a small holiday table and reference it in all NETWORKDAYS calculations across workbooks.
Image: Dates and PMT quick card
Frequently Asked Questions:
What are the best Excel formulas to start with?
If you’re new to Excel, begin with SUM, AVERAGE, and IF. These three cover almost everything you’ll do in a normal day — adding totals, finding averages, and sorting results into categories. Once you get comfortable, move to XLOOKUP or INDEX + MATCH for searching and connecting data across sheets.
Which Excel formulas are most helpful for business tasks?
For business or office work, focus on SUMIFS, XLOOKUP, and PMT.
SUMIFS helps track totals by category, region, or time period.
XLOOKUP lets you find details instantly, such as pulling a client name from an ID list.
PMT calculates monthly loan or lease payments in seconds.These are the kind of formulas that save real time and reduce manual work.
How can Excel formulas make daily work faster?
Formulas handle the busywork so you don’t have to. Use IF to mark items as “Paid” or “Due,” NETWORKDAYS to count business days between deadlines, or TEXTJOIN to merge text into clean labels. Once set up, these update automatically whenever your data changes.
What’s the difference between VLOOKUP and XLOOKUP?
XLOOKUP is the newer and smarter version of VLOOKUP. It can search left or right, find exact matches by default, and adjust automatically if you add new columns. VLOOKUP only searches to the right and breaks if you move columns. If you have Excel 2021 or Microsoft 365, use XLOOKUP whenever possible.
Use IFERROR to clean up your results. Example:=IFERROR(XLOOKUP(A2, A:A, B:B), "")This replaces any error with a blank cell or message you choose. It keeps reports and dashboards neat and easier to read.
What are some underrated Excel formulas worth learning?
A few powerful ones often get overlooked:
TEXT for formatting numbers or dates into readable labels.
INDEX + MATCH for advanced searches when XLOOKUP isn’t available.
EOMONTH and NETWORKDAYS for project timelines, payroll, and schedule tracking.Once you start using these, you’ll notice how much smoother your spreadsheets become.
How can I learn Excel formulas faster?
Start small and build gradually. Try one new formula each week, and practice it on a real project — like a household budget or sales report. Keep a separate “practice” workbook to test examples without breaking your real files. Learning by doing is the fastest way to remember what each formula does.
Which formulas are most useful for personal finance?
For personal budgets, SUM, AVERAGE, IF, and PMT are all you need. Use SUM to track monthly spending, AVERAGE to find your average bills, and PMT to figure out loan payments. Combine these with a free budget template, and Excel becomes your own financial assistant.
Can these formulas be used in Google Sheets?
Yes, almost all of them work exactly the same in Google Sheets. Only a few advanced ones like XLOOKUP are newer, but Google Sheets now supports it too. You can copy these formulas directly without changing anything.
What’s the best way to fix a formula that isn’t working?
Click the cell and press F2 to check your references.
Make sure you didn’t mix text with numbers (for example, "$500" typed as text).
Confirm your parentheses match correctly.
Test each part separately to see where it breaks.
Add IFERROR once it’s working to hide mistakes neatly.
Quick Copy Area: Paste These Examples Into Your Sheet
Below are ready-to-use Excel formulas you can copy directly into your own workbook. Each one uses real-world logic for budgets, projects, invoices, and reports. Replace the sample ranges with your own data columns to make them work for your situation.
Everyday Calculations
Total a range: =SUM(C2:C13)
Average value: =AVERAGE(B2:B500)
Count numeric cells: =COUNT(E2:E200)
Count all filled cells: =COUNTA(A2:A200)
Find Highs and Lows
Lowest value: =MIN(C2:C100)
Highest value: =MAX(C2:C100)
Decisions and Labels
Pass/Review check: =IF(D2>=0.2, "Pass", "Review")
Conditional totals by category: =SUMIF(A:A, "Groceries", C:C)
Totals filtered by category and date:=SUMIFS(C:C, A:A, "Groceries", B:B, ">=1/1/2025", B:B, "<=12/31/2025")
Lookups and Searches
Modern lookup: =XLOOKUP(E2, A:A, C:C, "Not found")
Classic two-step lookup: =INDEX(C:C, MATCH(E2, A:A, 0))
Formatting and Text
Currency label: =TEXT(D2, "$#,##0.00")
Join multiple cells with commas: =TEXTJOIN(", ", TRUE, A2:C2)
Dates and Finance
Today’s date: =TODAY()
End of current month: =EOMONTH(TODAY(), 0)
Working days between two dates: =NETWORKDAYS(B2, C2, Holidays!A2:A20)
Monthly loan payment: =PMT(0.06/12, 60, -15000)
Tips for Copying
Paste the formulas into your own Excel or Google Sheets file.
Adjust column letters and row numbers to match your data.
Check that your numbers are stored as true numeric values, not text.
If an error appears, wrap it in IFERROR — for example:=IFERROR(XLOOKUP(E2, A:A, C:C), "")
Save these examples in a “Formula Reference” tab so you can reuse them across projects.
These examples cover the functions most people use daily for personal budgets, business reports, and classroom work. Copy them as-is, test them on your data, and tweak the ranges to fit your workbook.
Comments