Top Must To Know Excel Formulas for Data Analysts in 2026
Knowing the Excel Formulas is a must if you want to become a Data analyst. But is entering a number in a spreadsheet sufficient?
The answer is “No.”
If you want to be a successful Data Analyst, then learning to use Excel formulas is necessary. Knowing these Excel formulas helps you quickly add data and make decisions. Moreover, you can use these formulas to spot trends and build mini dashboards to analyze them.
But do you have to learn the complete Excel formula to become a successful Data Analyst!?
The answer is no. You don’t need to learn every formula Excel offers. You can start with the basics for your daily tasks.
In this article, I will share the top Excel formulas every data analyst must know. To make it more relatable, I will also explain the uses of the formulas. Moreover, I will also share how this formula works and give you an easy example.
So without any further delay, let’s start…
Lists of Excel Formulas for Data Analysts
- SUM(): The Fastest Way to Add Numbers
- AVERAGE(): Find the Middle Value with Ease
- COUNT(): Quickly See How Many Numbers You Have
- IF(): Make Smart Choices in Your Data
- VLOOKUP(): Find Information Fast in a Table
- HLOOKUP(): Find Data Across Rows
- INDEX(): Get Any Value by Row and Column
- MATCH(): Find Where Something Is
- CONCATENATE() or CONCAT(): Join Text in Seconds
- TEXT(): Make Numbers and Dates Look Just Right
SUM(): The Fastest Way to Add Numbers
The SUM() formula is one of the first tools every data analyst should learn. It helps you add numbers from many cells, saving time and effort. Instead of typing out each number or adding them individually, SUM lets you get the total with a single formula.
Why to Use It: Whenever you need a total, like total sales for the month, total hours worked, or the sum of values in a data column, SUM is your best friend. It works with both small and large data sets.
How to Use: You have numbers in cells A1 to A10 and want to know the total. All you need to do is type:
=SUM(A1:A10)
Excel will instantly add everything in those cells and show you the answer. No more manual adding or mistakes!
Example: Imagine you have a list of daily expenses. With SUM, you can see how much you’ve spent in seconds. This formula is also great for tracking team sales, project budgets, or anything that needs a total.
Tip: You can use SUM for rows, columns, or scattered cells (like =SUM(A1, B3, C5)). The more you use it, the faster your calculations become.
AVERAGE(): Find the Middle Value with Ease
The AVERAGE() formula is another must-have tool for data analysts. It helps you find the mean (or average) of a list of numbers, which is helpful in many situations. Instead of adding up numbers and dividing by the count yourself, AVERAGE does it all at once.
Why You’ll Use It: This formula is your go-to whenever you want to know a group’s “typical” value. You can use it to get values such as students’ average marks, monthly sales, or website visitors.
How to Use: Suppose you have test scores in cells B1 to B10. To find the average score, type:
=AVERAGE(B1:B10)
Excel will add all the numbers and divide by how many there are, giving you the average.
Example: Imagine you track the temperature each day for a week. With AVERAGE, you can see the typical temperature at a glance. It’s also helpful to see trends, like how your sales or expenses change over time.
Tip: AVERAGE ignores empty cells and text, so you don’t need to clean your data before using it. It saves time and helps you get results faster.
COUNT(): Quickly See How Many Numbers You Have
The COUNT() formula is a simple but powerful way to determine how many cells in a range contain numbers. It is perfect for determining the size of your data or checking the number of entries or responses.
Why You’ll Use It: Use COUNT any time you need to see how many people answered a survey, how many sales you made, or how many days have recorded data. It ignores text and empty cells, so you only count the numbers that matter.
How to Use: Let’s assume you have numbers in cells C1 to C10, but not every cell has a value. To count only the filled cells with numbers, type:
makefile
CopyEdit
=COUNT(C1:C10)
Excel will show you how many cells in that range have numbers in them.
Example: If you manage attendance, you might use COUNT to see how many team members showed up on a given day. Moreover, for example, if you are running a poll, you can use it to count how many people gave numeric feedback.
Tip: If you want to count all filled cells, including those with text, use COUNTA() instead. But for numbers only, COUNT is the best choice.
IF(): Make Smart Choices in Your Data
The IF() formula is one of Excel’s most flexible tools. It allows you to set up a simple rule. So, if something is true, do one thing; if not, do something else. This helps you make quick decisions with your data without any manual checking.
Why You’ll Use It: Whenever you want to check if a condition is met and take action, IF is your answer. It’s perfect for marking sales as “Achieved” or “Missed,” tracking if students passed an exam, or flagging items above a certain price.
How to Use: Suppose you want to check if the number in cell A1 is greater than 10. You can type:
=IF(A1 > 10, “Yes”, “No”)
If A1 is more than 10, Excel will display “Yes.” If not, it will show “No.”
Example: You’re tracking project deadlines. Use IF to mark tasks as “On Time” or “Late” based on the due date. This makes reports much easier to understand and act on.
Tip: Use IF with numbers, text, or other formulas. Later, you can combine IF with different functions for more advanced checks.
VLOOKUP(): Find Information Fast in a Table
The VLOOKUP() formula is a favorite for anyone who works with tables of data. It helps you search for a value in the first column and bring back related information from another column in the same row.
Why You’ll Use It: We generally use this option to find information. VLOOKUP makes searching for a product’s price, a student’s grade, or a customer’s phone number easy. It saves you from scrolling through long lists.
How to Use: Imagine you have a table with product names in column A and their prices in column B. To find the price for the product in cell D1, use:
=VLOOKUP(D1, A1:B10, 2, FALSE)
Excel will look for the value in D1 in column A, then return the price from column B.
Example: Suppose you have hundreds of products and want to know the price of “Product X.” Just enter its name in D1 and let VLOOKUP search for you. It pulls the correct price instantly, even in large lists.
Tip: VLOOKUP constantly searches the first column of the range you choose. Ensure your lookup values are in that column to ensure the formula works correctly.
HLOOKUP(): Find Data Across Rows
The HLOOKUP() formula works much like VLOOKUP, but instead of searching down columns. It searches across the first row and returns data from another row. Think of it as a quick way to find information in wide tables where your headers run from left to right.
Why You’ll Use It: HLOOKUP is handy when your data is organized in rows instead of columns. If each row contains a different data type for the same category, you can quickly find values based on a header.
How to Use: Suppose your headers (like “Sales,” “Profit,” “Cost”) are in cells A1 to F1, and you want to find the sales value in the third row:
=HLOOKUP(“Sales”, A1:F5, 3, FALSE)
This tells Excel to look for “Sales” in the first row, then return the value from the third row of the same column.
Example: If you have monthly sales figures listed across the top row and want to quickly find out the sales in March (the third row), HLOOKUP will find it for you in seconds.
Tip: Like VLOOKUP, HLOOKUP usually needs an exact match. Using “FALSE” at the end ensures you get only precise results.
INDEX(): Get Any Value by Row and Column
The INDEX() formula gives you the value from any cell in a range, just by telling it the row and column numbers. It is one of the most valuable tools in Excel. Moreover, most of the time, it is used with other formulas for advanced tasks.
Why You’ll Use It: INDEX is excellent when you want a specific piece of data from a large table. You just tell it where to look, and it brings back the value. This is useful for custom reports or dashboards that pull data from different places.
How to Use: If your data is in cells A1 to C10, and you want the value in the second row, third column, you’d use:
=INDEX(A1:C10, 2, 3)
In your selected range, Excel will return the value from row 2, column 3.
Example: Suppose you’re building a report and must display sales figures for a specific day and product. INDEX helps you pick that exact number from a big table without scrolling or searching.
Tip: INDEX is even more powerful when combined with MATCH, which I will share next. Together, they create one of Excel’s most accurate lookup solutions.
MATCH(): Find Where Something Is
The MATCH() formula helps you find the position of a value within a range. Instead of returning the value itself, MATCH tells you the location (row or column number) where your item appears. It’s beneficial for locating items in big lists or tables.
Why You’ll Use It: MATCH is handy when you want to know where something is, not just what it is. It’s often used with INDEX for precise lookups, but finding an item’s position in a list is also helpful.
How to Use: Suppose you have a list of product names in cells A1 to A10, and you want to find the position of “Product B”:
=MATCH(“Product B”, A1:A10, 0)
Excel will return the number of the row where “Product B” is found in your list. If it’s the fourth item, you’ll get “4.”
Example: You have a long list of employee names and want to see where a specific name appears. MATCH can tell you in seconds, so you don’t have to scan the list yourself.
Tip: The “0” at the end means Excel looks for an exact match. If you want the closest value instead, you can use “1” or “-1,” but for most data analysis, “0” is safest.
CONCATENATE() or CONCAT(): Join Text in Seconds
The CONCATENATE() and CONCAT() formulas let you join text from different cells into one cell. This is useful when combining names, codes, or other information into a single line.
Why You’ll Use It: Whenever you have separate pieces of information, like a first name and last name, or want to create custom IDs by joining text and numbers, these formulas make the task quick and easy.
How to Use: Suppose you have a first name in cell A1 and a last name in B1. You want to join them together, separated by a space:
=CONCATENATE(A1, ” “, B1)
or, using the newer formula:
=CONCAT(A1, ” “, B1)
Excel will put the first and last names together in one cell, with a space between them.
Example: You’re preparing a list of customer names for a mailing label. Instead of typing each full name by hand, CONCATENATE or CONCAT can instantly join the first and last names, saving you much time.
Tip: If you want to add commas, dashes, or other text, just include them in the formula. For example, =CONCAT(A1, “-“, B1) would join the values with a dash.
TEXT(): Make Numbers and Dates Look Just Right
The TEXT() formula helps you format numbers or dates so they appear exactly how you want. This is especially useful when you need your data to look neat and precise in reports or dashboards.
Why You’ll Use It: Sometimes, the default way Excel shows dates or numbers isn’t what you need. With TEXT, you can control how things look, like turning a date into “16/05/2025” instead of “5/16/25” or showing numbers as currency.
How to Use: Suppose you have a date in cell A1 and want to display it as “day/month/year.” Use this formula:
=TEXT(A1, “dd/mm/yyyy”)
Excel will change the appearance, so the date looks just right for your report or audience.
Example: Let’s say you’re sharing monthly sales figures with your team, and you want all numbers to show as currency (like ₹1,000 instead of 1000). TEXT can format numbers as currency using:
=TEXT(A1, “₹#,##0”)
Tip: You can use TEXT to add custom words, too. For example, =TEXT(A1, “0”) & ” units sold” will show the number along with your custom text.
Why Every Data Analyst Should Know These Excel Formulas
These Excel formulas will help you build a strong foundation as a Data Analyst. Moreover, it saves much of your time, cuts out manual work, and enables you to find answers faster.
These formulas will help you check totals, find averages, or look up values. Moreover, these Excel formulas will help you organize your messy sheet into a clear story.
Applying These Formulas to Everyday Data Challenges
Save Time: You don’t have to add, search, or join data manually. Formulas do it in seconds.
Spot Trends: AVERAGE or COUNT can show you patterns, like rising sales or missed deadlines.
Make Decisions: IF lets you label results as “Passed” or “Failed,” “High” or “Low,” with just one click.
Find Data Fast: VLOOKUP, HLOOKUP, INDEX, and MATCH help you quickly locate what you need, even in big tables.
Create Clear Reports: TEXT and CONCAT make your results easy to read and ready to share.
How to Integrate Excel Functions for Advanced Analysis
When you combine formulas, Excel gets even smarter. Here’s a simple example:
Suppose you want to label all sales above ₹10,000 as “Top Seller.” First, use VLOOKUP to pull the sales number, then IF to check the value:
=IF(VLOOKUP(A2, D2:E10, 2, FALSE) > 10000, “Top Seller”, “Regular”)
This checks if the sales number is above ₹10,000 and instantly gives you a label. Mixing and matching formulas helps you analyze data much more effectively. Moreover, it gives you more desired results.
Conclusion
These Excel formulas will help you as a Data Analyst, give you confidence, and speed up your work. Learning SUM, AVERAGE, COUNT, IF, and the others enables you to turn raw data into trustworthy insights.
The more you practice these formulas, the more they’ll become second nature. After learning these formulas, you can create clear reports and handle data smoothly.
I hope this article will surely help you in the long run. If you have any additional questions, you can ask me at contact@codezeneduversity.com.