Excel is not going to die in 2026. Data analysts still dwell in it. Stakeholders email us Excel spreadsheets. In Excel, quick analysis happens before it touches a specific BI tool. Dashboards are prototyped in Excel far before they are re-built in Power BI. And although AI-powered features such as Copilot may now suggest formulas, explain faults and produce expressions from natural-language prompts, you still need to know the key formulas to ensure accuracy and to have true control over your own analysis.
Knowing there’s a formula and having actually used it ten times are two very different things. An analyst who’s used a formula time and time again works faster, makes less mistakes and doesn’t have to stop and look it up in the middle of doing it. In this book, we’ll cover the Excel formulae that every analyst should know and give you practical context for why and when to use each one.
Search formulas: Find and combine data
XLOOKUP Function
XLOOKUP is the current lookup standard that replaces VLOOKUP. It searches a range and returns a matching value, works both left-to-right and right-to-left (not like VLOOKUP which only searches left-to-right), handles missing values gracefully, and – crucially – doesn’t break when inserting or deleting columns in your source data, a common way VLOOKUP formulas silently break.
Find the price of a product from a different pricing database based on the SKU, and have the formula still operate correctly even if someone rearranges the columns.
VLOOKUP LOOKUP
VLOOKUP is still useful knowing as you will see it a lot in real-world spreadsheets and in files that other people have developed, although XLOOKUP has advantages. Looks up a value in a table and returns a value in the same row from a specified column. VLOOKUP is handy for looking up and combining data from two linked tables.
INDEX + MATCH
INDEX and MATCH combined are a very strong combo of two functions that are an advanced alternative to VLOOKUP and HLOOKUP with more flexibility. The major restriction of VLOOKUP and HLOOKUP is that VLOOKUP can only look to the right of your search column. INDEX returns the value of a cell in a table based on its row and column number. MATCH returns the position of a specified value within a row or column. Together they allow you to look up values in any direction , not simply from left to right .
Formula Example =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))
Conditional aggregation: sum and count with criteria
SUMIFS
SUMIFS sums for many criteria at the same time , not one criterion at a time . This is one of the most used formulas in actual analytical work, because business problems are rarely single-condition – you’re nearly always filtering by more than one thing at the same time.
For example, total sales for the North region in January 2026 alone, by filtering on both region and date range in one formula.
COUNTIFS
COUNTIFS counts cells that fulfill many specific criteria, using the same logic as SUMIFS but returning a count instead of a sum.
Count the number of orders that exceeded a dollar threshold and fell under a particular product category.
AVERAGEIFS()
AVERAGEIFS finds the average of a group of values that meet multiple criteria. The AVERAGEIFS function uses a multi-criteria pattern similar to SUMIFS and COUNTIFS. If you get one of the three, the other two are the same rationale.
Core Functions Every Analyst Uses All the Time
- SUM – adds a range of values; the most fundamental and most utilized calculation in Excel.
- AVERAGE – Finds the average of a group of numbers.
- COUNT – counts cells that contain numbers inside a range.
- IF – tests the condition and returns one value if the condition is true, and another if the condition is false. It is the logical backbone of countless more complex formulas built on top of it.
- IFERROR – swaps error messages (such #N/A or #DIV/0!) for a custom, more usable value, which makes a huge difference when formulas are shared with interested parties who shouldn’t be exposed to raw error codes in a finished report.
- ROUND – rounds a value to a number of decimal places. Great for reporting cleanly and consistently.
Functions for Text and Data Cleaning
- CONCATENATE / CONCAT – Joins text strings from multiple cells into one cell. Useful for concatenating entire names, formatted labels or unique identifiers from individual data fields.
- LEFT / RIGHT – takes a certain number of characters from the beginning (LEFT) or end (RIGHT) of a text string. This function is often used to grab codes, prefixes or suffixes from more messy source data.
- LEN – yields the number of characters in a text string; typically used to check the format of data, or to identify items that are not the correct length.
- UNIQUE – creates a list of all unique values in a data collection, so you can quickly see what types of categories or values are truly present in a column without having to manually read through it.
Next Advanced Formulas You Should Learn
OFFSET
OFFSET lets you build dynamic ranges that dynamically update to reflect changes in the underlying data and make calculations much more flexible than a hard-coded, fixed range reference.
PMT
PMT function returns the periodic payment for a loan with constant interest rate and payment terms . This function is useful for financial analysts that handle loan amortization , debt schedules or financing scenarios .
Pivot Tables
Pivot tables are not strictly one formula but are a must-have for any analyst. You can summarize, analyze and display enormous data sets by simply dragging and dropping variables into rows, columns and values, creating significant insights and surfacing patterns and trends without having to write a single difficult formula. A pivot table is frequently faster and more versatile for exploratory research, notably, than developing a formula-based summary from start.
Beginners vs. Advanced: Where Do You Start?
If you are new to Excel, start with the basics formulas – SUM, AVERAGE, COUNT and IF – and then go on to the lookup and conditional formulas such as VLOOKUP, XLOOKUP, SUMIFS and COUNTIFS. Jumping right into complex array formulas or OFFSET-based dynamic ranges without first making these basics second nature tends to slow learning down rather than speed it up.
How AI Is Transforming (But Not Replacing) Formula Knowledge
In 2026, AI-powered features like Copilot can suggest formulae, explain what an existing formula is doing, and produce new expressions directly from a natural-language description of what you are attempting to do. This really boosts efficiency as it pertains to ordinary duties. But that doesn’t diminish the value of actually understanding core formulas yourself – accuracy, the ability to catch a subtly-wrong AI-suggested formula, and real control over complex, multi-step analysis still depend on knowing what these functions are actually doing under the hood, not just accepting whatever an AI assistant suggests.
Final Thoughts
Mastering this particular set of Excel formulas – XLOOKUP and INDEX/MATCH for lookups, SUMIFS/COUNTIFS/AVERAGEIFS for conditional aggregation, core functions like IF and IFERROR, text-cleaning tools, and pivot tables for quick exploratory analysis – will prepare you for the vast majority of what you’ll encounter in real day-to-day analytical work. they are not textbook exercises, they are the formulas that skilled analysts grab for all the time, without often having to think about the syntax anymore. AI technologies can speed up the process of writing and fixing formulas, but knowing these functions yourself is still what distinguishes the analyst who can work quickly and properly from the one who is always second-guessing their own spreadsheet.
Common Questions (FAQs)
1. What are the important Excel formulas a data analyst should learn?
Then start with the basics – SUM, AVERAGE, COUNT and IF. Once you’ve got those down, get comfortable with lookup and conditional formulas like XLOOKUP, VLOOKUP, SUMIFS and COUNTIFS. These cover a lot of the day-to-day analytical work including combining data and summarizing data based on certain criteria.
2. Should I use VLOOKUP or XLOOKUP?
XLOOKUP is the new standard and has mostly replaced VLOOKUP for new work, as it searches both ways, handles missing values better and doesn’t break when columns are inserted or deleted. That said, VLOOKUP is still very frequent in current spreadsheets produced by other people, so it’s still worth knowing, even if you make XLOOKUP your default going forward.
3. What’s the difference between VLOOKUP and INDEX/MATCH?
VLOOKUP can only look to the right of your lookup column, and it will break if you add or rearrange columns. It is often considered to be a better replacement for VLOOKUP particularly for complicated finance modeling and analytical tasks. INDEX/MATCH is a more versatile combination of two functions that can lookup values in any direction.
4. With AI tools like copilot, I can have it construct Excel formulae for me, so do I really need to understand them?
Yes. AI technologies can suggest and construct formulas from natural-language cues, and that does really speed up mundane chores, but it doesn’t replace the importance of knowing what a formula truly does. You know the functions behind, so you can identify mistakes in the AI provided formulas and actually have meaningful control over more complex, multi-step analysis.
5. Is pivot tables a formula?
Not really – pivot tables are yet another feature of Excel, not a particular function or formula. That said, they are regarded fundamental, along with understanding of formulas, since they allow an analyst to swiftly summarize and analyze enormous data sets by dragging and dropping fields, frequently quicker than it would take to generate an identical output using formulas alone.
