Excel Formulas Cheat Sheet 2026: 42 Formulas, Worked Examples and the Lookups That Replaced VLOOKUP

It is 9pm on the second of the month. The ERP export has landed as a 40,000 row CSV, the city column has trailing spaces in about a third of the rows, and the SUMIFS you dragged down column H now makes the workbook pause for three or four seconds every time you click a cell. That evening is where most analysts actually learn Excel, and it is the evening this cheat sheet is written for. Everything below is organised the way the problem arrives: look things up, add things up conditionally, clean the mess, then explain the error code.

The Three Excel Limits That Shape Every Formula You Write

Three hard numbers govern what you can and cannot do in a worksheet, and knowing them saves you from a whole class of pointless debugging. Microsoft's official worksheet and workbook specifications page states that a sheet holds 1,048,576 rows by 16,384 columns, that the contents of a single formula cannot exceed 8,192 characters, and that a function accepts at most 255 arguments.

The row ceiling is the one people hit first, usually when a CSV opens and the last rows are quietly missing. The 8,192 character limit is the one that kills a nested IF chain built by a colleague who never learned SWITCH. Neither is negotiable, so the moment a dataset approaches a million rows you are no longer in Excel's territory, and a load into Power Query or a database is the honest answer.

Excel Formulas Cheat Sheet: Lookups, Filters and Spill Control

A lookup answers the question "what is the value for this key". A filter answers "which rows match this condition". Analysts reach for the first when they mean the second, end up with an array of INDEX formulas, and wonder why the file is slow. FILTER is the right function more often than people think.

How to Use XLOOKUP in Excel Without the Four Common Traps

Microsoft's XLOOKUP documentation sets the defaults that matter: match_mode defaults to 0, which is an exact match returning #N/A when nothing is found, and search_mode defaults to 1, a first-to-last scan, with -1 running the scan backwards. The same page states plainly that XLOOKUP is not available in Excel 2016 or Excel 2019, so a workbook you build on Microsoft 365 will break on a colleague's older install.

# Exact match with a readable fallback instead of #N/A
=XLOOKUP(D2, Orders[SKU], Orders[Margin], "SKU missing")

# Last matching row, which is how you get the most recent price
=XLOOKUP(D2, Prices[SKU], Prices[Rate], "no rate", 0, -1)

# Return three columns at once by passing a multi-column return array
=XLOOKUP(D2, Orders[SKU], Orders[[City]:[Margin]], "missing")

The traps, in the order you will meet them: passing a lookup array and a return array of different heights, which gives #VALUE!; forgetting that a text key with a trailing space will not match a clean one; wrapping the whole thing in IFERROR so a genuine #REF! disappears along with the #N/A; and using full-column references inside a formula you then copy down 40,000 rows.

Dynamic Array Formulas and the Spill Operator

Dynamic array formulas changed the habit of dragging a formula down a column. One FILTER in H2 produces as many rows as there are matches, and H2# then refers to that whole block wherever it lands. That is what lets you write =ROWS(H2#) for a live match count, or feed the spilled range straight into SUM without guessing how far it will reach.

Also read: SQL Commands Cheat Sheet 2026, which is the same reference treatment for the language you will move to once the data outgrows a sheet.

Advanced Excel Formulas for Data Analysis: Conditional Aggregation

This is where an analyst earns their keep. Take that two-person analytics team at a mid-size auto-parts distributor in Pune: every month they need revenue by city, margin by product family, and a count of orders above a threshold, from one flat ERP export. The whole job is conditional aggregation.

GROUPBY and PIVOTBY: a Pivot Table You Can Audit

GROUPBY and PIVOTBY are the two additions that genuinely changed monthly reporting, because the output refreshes with the data instead of needing a right-click and Refresh. Microsoft's PIVOTBY function page lists it for Excel for Microsoft 365, Excel 2024 and Excel 2021, including the Mac builds, and notes that it is not directly related to the PivotTable feature. That last detail is the point: the result is a formula someone else can read.

# Revenue by city, totals row suppressed, sorted descending by value
=GROUPBY(Orders[City], Orders[Revenue], SUM, , 0, -2)

# City down the side, product family across the top
=PIVOTBY(Orders[City], Orders[Family], Orders[Revenue], SUM, , 0, , 0)

# Two aggregations side by side using HSTACK
=HSTACK(GROUPBY(Orders[City],Orders[Revenue],SUM,,0),
        GROUPBY(Orders[City],Orders[Revenue],COUNT,,0))

The reporting layer that sits on top of this is Power BI, and the DAX measures you write there are conditional aggregation under a different name. 360DT's Data Analyst course runs Excel, SQL, Python and Power BI in one sequence for exactly that reason, with PL-300 preparation attached to the Power BI half.

Text, Date and Logic Formulas That Clean Messy Exports

Nobody warns you that most of the job is this. The Pune team's city column arrives as "Pune ", "pune" and "PUNE, MH" in the same file, and no amount of clever SUMIFS fixes a key that does not match. Clean first, aggregate second.

One version warning that costs people an afternoon: Microsoft's TEXTSPLIT page lists the function for Excel for Microsoft 365 and Excel 2024 only, with Excel 2021 absent from the list. If you are on a 2021 install, the split has to be done with the older LEFT, MID and FIND combination, or in Power Query.

# A cleaning chain that is readable six months later
=LET(
  raw,   A2,
  flat,  SUBSTITUTE(raw, CHAR(160), " "),
  clean, TRIM(CLEAN(flat)),
  city,  TEXTBEFORE(clean & ",", ","),
  IF(city = "", "unknown", PROPER(city))
)

That formula is longer than the nested version it replaces, and that is the trade-off worth accepting. LET names each step, so when the output is wrong you can comment out one line of thinking instead of counting brackets.

A Worked Example: 40,000 ERP Rows to a Monthly Margin Report

Here is the whole month-end job for the Pune distributor, start to finish, in five moves. Reproduce it on any flat sales export with city, date, product family, revenue and cost columns.

Step 1. Select the raw range and press Ctrl+T to make it a table named Orders. Structured references like Orders[Revenue] grow with the data, which is what stops next month's rows from being silently excluded.

Step 2. Add one calculated column for the clean key, using the LET chain above. Do not clean inside the aggregation formula; a separate column is debuggable.

Step 3. Add a margin column: =([@Revenue]-[@Cost])/[@Revenue], wrapped in IFERROR so a zero-revenue credit note returns 0 rather than #DIV/0!.

Step 4. On a reporting sheet, write one PIVOTBY for the crosstab and one GROUPBY for the ranked city list. Both spill, so no dragging.

Step 5. Add the sanity check that catches 90 percent of real mistakes: =SUM(Orders[Revenue])-SUM(H2#). If that is not zero, a row is being excluded by a filter condition you forgot about.

Total elapsed time once you have done it twice: about twenty minutes, and it refreshes next month by pasting new rows into the table. That is the actual deliverable an interviewer wants to hear about, and it is the shape of project that a data analyst portfolio is built from.

Excel Formula Errors: What Each Code Means and the Fix

Excel formula errors are diagnostic, not decorative. Each code narrows the problem to one cause, and the fix is usually mechanical once you read the code properly rather than reaching for IFERROR.

Six Habits That Make an Excel Workbook Slow and Fragile

The honest caveat: do not over-invest in formulas

Advanced Excel has a ceiling, and the marketing around it rarely says so. A workbook cannot be code reviewed, cannot be scheduled, and cannot be diffed, which means two people editing the same monthly report will eventually produce two different numbers with no way to find out who changed what. If your job already involves a database, learn SQL before you learn LAMBDA. And if your ambition is data engineering rather than analysis, the hours are better spent on Microsoft Fabric and the DP-700 track than on perfecting array formulas.

One more thing worth knowing before you build anything on AI-written formulas. Microsoft's Excel Tech Community post announcing the =COPILOT worksheet function carries an update stating that from 14 September 2026 the function is no longer available, pointing users to the Copilot side pane instead. When it did work, the documented cap was 100 Copilot function calculations in any 10 minutes. That is the pattern with AI features inside Excel: useful, and not yet something to build a month-end process on. Python in Excel is steadier but still gated, since Microsoft's availability page states it requires a paid Microsoft 365 licence that includes the desktop apps, with premium compute allowance resetting at the start of each month. For non-engineers who want to use these assistants well rather than fight them, the CCAO-F foundations prep course is the lighter entry point, and the AB-900 Copilot administrator course is the one for whoever has to govern the rollout.

How to Learn This Excel Formulas Cheat Sheet in Six Weeks

Do not try to memorise 42 formulas. Learn them in the order that problems arrive, with one real dataset you care about, and the recall takes care of itself.

Week 6 is where self-teaching usually stalls, because Power Query and the PL-300 model layer reward being walked through once. That is the part a live weekend cohort is genuinely useful for, and it is covered in the Data Analyst course alongside the SQL and Python that follow. If you are not ready to commit, sit in on a free webinar or a demo class first, and look at the full certifications overview to see where PL-300 sits relative to the Fabric and AI tracks. Analysts who later move toward retrieval pipelines and agents tend to land in the AI Engineer course, but that is a decision for after the formulas are automatic.

Also read: Power BI Tutorial for Beginners 2026, which picks up exactly where week 6 of this plan ends.

If I were starting over with thirty spare hours a month, I would spend the first month on this cheat sheet and never touch it again as a memorisation exercise, because the formulas that stick are the ones you needed at 9pm on the second of the month. Then I would move straight to SQL, since every ceiling described above is a reason to leave the sheet rather than a reason to get better at it. Build the five-stage report on your own data this week; it is the single most convincing thing you can put in front of a hiring manager who has seen a hundred certificates.

Related guides

• PL-300 Exam Prep 2026 the six-week study plan for the certification that sits on top of these Excel and Power BI skills.
• How to Become a Data Analyst Without Experience in India the ten-month switch plan if these formulas are your first technical skill.
• Power BI vs Tableau in 2026 which reporting tool to learn after Excel, judged on Indian job demand.
• SQL Interview Questions for Data Analyst Roles the twenty questions you will face once the sheet work is behind you.
• Data Analyst Jobs in Pune 2026 pay bands and hiring clusters in the city this article's worked example is set in.

Frequently asked questions

About this guide. 360 Digital Transformation is an Anthropic Partner and a Microsoft Authorized Training Partner. Other certification bodies, vendors and employers named here are not affiliated with us. Tools and versions change quickly; commands and figures cited were checked on 1 November 2026.