Skip to content
Excel formulas for data analysts: XLOOKUP, SUMIFS, IFERROR, text, dates and FILTER

Quick answer: The Excel formulas every data analyst should know are XLOOKUP (or INDEX with MATCH), SUMIFS, COUNTIFS, AVERAGEIFS, IF with IFERROR, TRIM and the text functions, TEXTJOIN, date functions such as EOMONTH, and the dynamic array functions UNIQUE, FILTER and SORT. Add pivot tables and you can answer most everyday business questions in a spreadsheet.

Not sure where to start? Try a free demo class, online or at our J. P. Nagar centre. Book on WhatsApp

11formulas and tools covered
Freepractice with any workbook
4 monthsData Analytics course
2 monthsinternship included

Excel is still the first tool most analysts open, because business data often arrives as a spreadsheet and a manager wants an answer today. You do not need hundreds of functions. A short list, used well, covers most of the work. This guide explains eleven formulas and tools, shows what each one is for, and tells you the mistakes that quietly give wrong numbers.

The 11 formulas and tools, grouped by the job they do

Learn them in this order: look things up, add things up, clean the text, handle errors, then reshape the data. Type each formula into a small practice sheet instead of only reading it.

Eleven Excel formulas for data analysts: lookups, conditional sums and counts, IF and IFERROR, text cleaning, dates and dynamic arrays
Eleven formulas and tools, grouped by the job each one does.
Excel formulas for data analysts, what each does and when to use it
FormulaWhat it doesUse it when
XLOOKUPFinds a value in one column and returns a match from anotherYou need a price, name or category from a lookup table
INDEX with MATCHLooks up in any direction and works in older Excel versionsThe file must open in Excel 2016 or 2019
SUMIFSAdds numbers that meet several conditionsSales for one region in one month
COUNTIFSCounts rows that meet several conditionsOrders above a value from one channel
AVERAGEIFSAverages numbers that meet conditionsAverage order value for new customers
IF, IFSReturns one result or another based on a testLabelling rows such as Low, Medium, High
IFERRORReplaces an error with a value you chooseA lookup may not find a match
TRIM, LEFT, RIGHT, MID, TEXTJOINCleans, slices and joins textImported data has extra spaces or joined codes
EOMONTH, DATEDIFMoves to month end, counts days, months or yearsMonth-end reports and age or tenure
UNIQUE, FILTER, SORTReturn a live list of unique, filtered or sorted rowsA report that must update when data changes
Pivot tableSummarises thousands of rows by dragging fieldsA first look at any new dataset
  1. XLOOKUPWrite =XLOOKUP(A2, Products[ID], Products[Price], "Not found"). It looks for A2 in the ID column and returns the matching price. Unlike VLOOKUP it can look left and does not break when you insert a column.
  2. INDEX with MATCHWrite =INDEX(Price, MATCH(A2, ID, 0)). MATCH finds the row number, INDEX returns the value at that row. The final 0 means exact match, and forgetting it is a classic error.
  3. SUMIFSWrite =SUMIFS(Sales, Region, "South", Month, "Oct"). The sum range comes first, then each range and its condition. Every range must be the same size.
  4. COUNTIFS and AVERAGEIFSThese use the same pattern as SUMIFS. =COUNTIFS(Channel, "Ads", Value, ">5000") counts matching rows.
  5. IF and IFSWrite =IF(B2>=100, "High", "Low"). IFS tests several conditions in order and returns the first that is true, so it replaces long nested IF formulas.
  6. IFERRORWrap a formula that may fail: =IFERROR(XLOOKUP(...), 0). Use it carefully, because it can also hide a real mistake.
  7. TRIM and text functionsTRIM removes extra spaces, LEFT, RIGHT and MID cut text by position, and TEXTJOIN joins cells with a separator. Clean a column before you look anything up in it.
  8. Date functionsEOMONTH(A2, 0) gives the last day of the month. DATEDIF(start, end, "M") returns whole months between two dates.
  9. UNIQUE, FILTER, SORT=SORT(UNIQUE(Region)) returns a sorted list of regions. =FILTER(Table, Table[Status]="Open") returns only open rows. These need a version of Excel with dynamic arrays.
  10. Pivot tablesSelect the data, insert a PivotTable, then drag Region to rows and Sales to values. Refresh it when the data changes.

Mistakes that give wrong numbers without an error

  • Numbers stored as text. A column that looks numeric but is left-aligned will not add up. Convert it first.
  • Hidden spaces. A value with a trailing space will not match its twin. Use TRIM before a lookup.
  • Using approximate match by accident. Lookups should use exact match unless you are sorting values into bands.
A habit worth building

After every formula, check one result by hand. If you cannot explain why a number is right, you do not yet know that it is.

A 7-day practice plan

  • Days 1 and 2: SUMIFS, COUNTIFS and AVERAGEIFS on a sales sheet.
  • Day 3: XLOOKUP and IFERROR against a product list.
  • Day 4: clean a messy file with TRIM, LEFT, RIGHT and TEXTJOIN.
  • Day 6: UNIQUE, FILTER and SORT for a live summary.
  • Day 7: build one pivot table and write three sentences about what it shows.

Microsoft keeps an official list of every function with examples in its Excel functions reference. Use it whenever a formula behaves in a way you do not expect.

Where Excel fits in a data analyst career

Excel is the starting point, not the end. Once a dataset is too big or too shared for a spreadsheet, analysts move to SQL for querying, Power BI for dashboards and Python for heavier cleaning. The Data Analytics with AI course at our J. P. Nagar centre runs for 4 months: 3 hours a day, Monday to Friday, plus a Saturday case clinic, with a weekend batch also available. A 2-month internship is included for every enrolled student.

Next, read the data analyst roadmap and practise with our SQL interview questions.

Ready to try a class before you decide?Book a free demo class to see the syllabus, ask about fees and batches, and meet the team. Every course includes a 2-month internship, and classes run online and offline.Book on WhatsAppCall +91 99020 58603

Frequently asked questions

Which Excel formula should a beginner learn first?

Start with SUMIFS and XLOOKUP. Between them they answer many everyday questions about totals and lookups.

Is XLOOKUP better than VLOOKUP?

For most work, yes. XLOOKUP can return values from columns to the left, uses exact match by default and does not break when columns are inserted. It needs Microsoft 365 or Excel 2021 and later.

Do data analysts still use Excel?

Yes. Excel is widely used for quick analysis and sharing, alongside SQL, Power BI and Python for larger or repeated work.

How long does it take to learn these formulas?

With a few hours of practice a day, many learners can use the core formulas in a couple of weeks. Fluency comes from using them on real data.

Can a non-IT graduate learn Excel for data analysis?

Yes. Our Data Analytics with AI course is open to any graduate, including B.Com, BBA and BSc graduates, and starts from the basics.

Not sure yet? Talk to us

Book a free demo class or ask for 1:1 guidance on WhatsApp at +91 99020 58603, or use the form on our contact page. You can also compare all our courses. Scholar’sEdge Academy is at Building No. 19, 25/1, 9th Cross Road, 2nd Phase, J. P. Nagar, Bengaluru, Karnataka 560078. Read how we write and check our articles in our editorial policy.