Ankashram Logo
AnkashramData Culture Studio
AnkashramData Culture Studio
ProgramsConsultingResources
About Us
Data Analytics

15 Excel Formula’s Business Analysts Need To Know

Admin-Ankashram
Admin-Ankashram
November 12, 2026·5 min read
think with data

Most business analysts will know that a large amount of their time is spent in spreadsheets. Often you will be given some data, tidying it up and trying to find any patterns to help answer any questions you have prior to meetings.

Often knowing a handful of formulas can help you flow through the process with ease.

Below are 15 formulas that are always super useful for Business Analysts:

Register Banner

1. XLOOKUP/VLOOKUP

This is the most common lookup formula. If you need to bring across a value from a different table using an ID or Name, XLOOKUP is a much cleaner, flexible option. You can look left or right and deal with blanks better.

2. INDEX & MATCH

INDEX will bring back a value from a range of cells, whilst MATCH will look up the position of something. This is a go-to for anyone who can’t use XLOOKUP.

Register Banner

3. SUMIFS

This formula allows you to sum values based on multiple conditions. For example if you wanted to know the total revenue for a certain region in a certain month. This is one of the most used functions.

4. COUNTIFS

This will count how many rows meet your multiple conditions. This can be useful if you want to know how many orders you have, how many customers in a certain segment or how many deals you closed above a certain value.

5. AVERAGEIFS

This does the same thing as SUMIFS & COUNTIFS but averages values. If you want to know the average deal size for a certain product line or region this can be done quickly.

6. IF/IFS

This formula is great for using conditions. You can highlight rows that meet a condition, categorise them or create simple status fields. IFS allows you to have multiple conditions.

7. IFERROR

This will stop any nasty error messages popping up. If you wrap any formula in IFERROR you can return a blank, 0 or message of choice. This means your reports will look much cleaner.

8.TEXT

This will format numbers and dates. If you need to format a date to show as Jan-2026 or ensure numbers are displayed with leading zeros this can be really useful. Especially if you need data for a presentation or export.

Read More: How to Think Like a Data Analyst (Even If You’ve Never Touched a Spreadsheet

9.TEXTJOIN/CONCAT

These will join text together. TEXTJOIN is very useful as you can insert a delimiter such as a comma. Any blank cells will be ignored. This can be useful for creating full names, addresses etc.

10.LEFT, RIGHT, MID & TRIM

These will extract parts of text. LEFT & RIGHT will bring back characters from the beginning/end of text. MID will bring back characters from the middle. TRIM will remove any extra spaces. This can be really useful if you have messy data that has been imported.

11.EOMONTH/NETWORKDAYS

EOMONTH will return the last day of the month. This can be useful for end of period reporting. NETWORKDAYS will return the number of working days between two dates, excluding weekends and optional public holidays. These are often used in project or operations analysis.

Read More: Why Data Analytics Projects Matter So Much for Beginners

12.UNIQUE

This will return a list of unique values. This can be really useful to see how many unique customers/products/regions you have.

13.FILTER

This will filter your data to return only the rows that meet your conditions. If you want to see all of the orders above a certain value for one region this can be done instantly.

14.SORT

This will sort your range/array. This can be useful for having a clean list sorted.

15.SUMPRODUCT

This is a very flexible function. It allows you to multiply and sum arrays. Often it is used to replace complex SUMIFS or array formulas. It is commonly used by Business Analysts for weighted averages, conditional calculations and checking multiple conditions.

Why these are important to know

You won’t need to know all of these formulas. However, knowing the ones that will help you to look up related information, sum/count with conditions, clean text, date functions and provide clean summaries.

By knowing these 15 formulas you will be able to do the majority of analysis you need to do on a daily basis. By becoming familiar with these you will no longer be hindered by the spreadsheet.

At Ankashram we have courses like Think with Data that focus on these practical skills.

Frequently Asked Questions

Q1.Do I need to know all 15?

No. Start off with the ones you use the most. This will usually be XLOOKUP or INDEX+MATCH, SUMIFS, COUNTIFS and IF. Then as you need to use the others for actual tasks you can learn them.

Q2.Is XLOOKUP available in all versions of Excel?

It is available in Microsoft 365 and newer versions. If you have an older version INDEX + MATCH is a good alternative.

Q3.What is the difference between Pivot Tables?

Pivot Tables are brilliant for summarising data. However these formulas can give you more control if you need to do any custom calculations, clean your data or to have the results within a bigger model/dashboard.

Q4.Will they help if my data is messy?

Yes! TRIM, LEFT, RIGHT, MID, IFERROR and TEXT can all be really useful for cleaning up your data.

Q5.Do Ankashram teach these formulas?

Our practical courses like Think with Data focus on using tools like Excel. We focus on applying formulas to real situations.

Q6.Should I still learn Power Query/Power BI as a Business Analyst?

Yes, however knowing your formulas well will give you a strong base. Most analysts still spend a lot of time in spreadsheets and these formulas will make this much more productive.

Q7.What is the quickest way to become familiar?

Use them in real files. Try taking a report you use and using one of these formulas to replace any manual processes.

Learn
Hire Us
Resources
About
15 Excel Formula’s Business Analysts Need To Know — Ankashram | Ankashram