Why a small set of formulas does most of the work
Most analyst work in Excel follows the same pattern: bring data from two sheets together, clean it, then total or count it by category. A dozen formulas cover those steps. Knowing them well, including their mistakes and edge cases, matters more than knowing hundreds by name.
These are also the formulas interviewers ask about in Excel tests, often with a small dataset and a question such as "total sales by region for last quarter".
Lookup and conditional total formulas
Examples assume a sales table with columns for region, product, date and amount.
XLOOKUP
=XLOOKUP(A2, Products[ID], Products[Price], "Not found") finds a product's price by its ID and shows a message when it is missing. Use INDEX with MATCH in older versions of Excel.
SUMIFS
=SUMIFS(Sales[Amount], Sales[Region], "South", Sales[Date], ">="&DATE(2026,7,1)) totals South sales from July 2026 onwards.
COUNTIFS
=COUNTIFS(Sales[Region], "South", Sales[Amount], ">10000") counts large orders in one region.
AVERAGEIFS
=AVERAGEIFS(Sales[Amount], Sales[Product], "Laptop") gives the average order value for one product.
Cleaning, logic and date formulas
Use these to prepare messy data before you total it:
- TRIM and CLEAN remove extra spaces and hidden characters that stop lookups from matching.
- UPPER, LOWER and PROPER standardise text such as city names.
- LEFT, RIGHT, MID and TEXTSPLIT pull codes or names out of longer text.
- IF and IFS assign categories, such as =IF(D2>10000,"Large","Standard").
- IFERROR replaces error values with something readable, but use it after you understand why the error happened.
- TEXT, DATE, EOMONTH and DATEDIF format dates and work out month ends and ages.
Dynamic array formulas that save time
In Microsoft 365 and Excel 2021 or later, one formula can return a whole list.
UNIQUE
=UNIQUE(Sales[Region]) lists each region once, a quick way to build a category list.
FILTER
=FILTER(Sales, Sales[Region]="South") returns only the South rows and updates when data changes.
SORT and SORTBY
=SORTBY(Sales, Sales[Amount], -1) sorts the table from the largest order down.
LET
Names a part of a long formula so you calculate it once and the formula stays readable.
Formulas or a pivot table: how to decide
Both summarise data. Pick the one that fits the job.
Exploring a new dataset
Use a pivot table. You can drag fields around and see totals by any category in seconds.
Building a fixed report
Use SUMIFS or COUNTIFS in a layout you design, so the numbers sit exactly where the report needs them.
Data that grows every week
Turn the data into an Excel Table first, so formulas and pivot tables pick up new rows automatically.
Repeating the same cleaning
When you clean the same export every week, move the steps into Power Query instead of copying formulas.
Quick answers about Excel for data analysts
Short answers to what learners search for most.
Is XLOOKUP better than VLOOKUP?
Usually yes. XLOOKUP can look left, does not break when columns are inserted, defaults to an exact match and has a built-in value for missing results. VLOOKUP is still worth recognising in older files.
Which Excel version do analysts need?
Microsoft 365 or Excel 2021 and later include XLOOKUP and dynamic arrays such as UNIQUE and FILTER. Older versions can do the same work with INDEX, MATCH and pivot tables.
Are pivot tables enough for data analysis in Excel?
They are excellent for quick summaries, but you still need formulas to clean data, join tables and build fixed report layouts.
What Excel questions come up in analyst interviews?
Common tasks are a lookup between two sheets, a conditional total with SUMIFS, cleaning text with TRIM, a pivot table by category and a simple chart.
Should I learn Excel or SQL first?
Excel is usually the easier start and is used in almost every team. SQL becomes essential once data lives in databases, so plan to learn both.
Grow from Excel to full analytics
See where Excel sits in the Data Analytics syllabus, or read the related guides.
Practise on one real dataset
Download a public sales or survey dataset and answer five business questions with lookups, conditional totals and a pivot table. Then rebuild the same answers with dynamic arrays. That single file teaches more than a list of formulas.