Hello readers, this is Nakul. I’m a student at ADMEC Multimedia and pursuing a data analytics course here. Today, I am going to talk about important formulas of Excel that every data student should know. I’m covering the top 10 formulas only, as I believe that we should focus on mastering the important ones only. There are hundreds of formulas in Excel, but not all are necessary to memorize.
These formulas help in cleaning data, analyzing trends, and making simple reports. And this is what you will be doing under entry-level data analytics roles in the future.
So, whether you are a student of any data analytics diploma or have completed your course, you must become an expert in the right set of Excel formulas. Then only you can prepare fully for your first job.
In this blog, we’ll walk through the 10 Excel formulas you’ll actually use in your first data analytics job. These formulas save a lot of your time, help you in reducing errors, and make data meaningful.
But before we jump into the formulas, let’s first talk about why Excel still matters in data analytics. I believe this is something that many of you want to know.
Why Excel Is Still a Must-Have Skill in Data Analysis
Despite having advanced tools like Power BI and Python, Excel still continues to be the first choice for doing everyday tasks in companies.
I feel the main reason is its simplicity. With Excel, organizations can perform various tasks, like:
- Cleaning and organizing raw data.
- It helps in preparing dashboards and MIS reports
- You can use it for making regular business reports.
- You don’t have to depend on advanced tools like Tableau and Power BI for performing quick analysis.
Let’s understand it in this way: In most of the entry-level data analytics jobs, we receive data of customers related to sales or leads in Excel format only. Thus, we first clean, filter, and structure it in Excel, then forward it to tools like Power BI and Python. These advanced tools will later work on the remaining visualization and analysis. And this is what I learned during my training in the data analytics courses at ADMEC.
Now we understand why Excel is so important, so I think we are all ready to begin with the Excel formulas that you, as a beginner data analyst, should know.
10 Excel Formulas for Data Analytics
Let’s start with the first and most essential Excel formula from the list.
1. SUM()
The SUM() formula is one of the simplest and commonly used formulas in Microsoft Excel. We use it to add the total sum value of a selected range.
You’ll commonly use the SUM formula in:
- Performance summaries
- Expense tracking
- Sales reports, etc.
In your first job, you’ll often work with large datasets and take quick totals out of them. With the help of this formula, “SUM”, you can calculate the “total monthly leads, monthly expenses, or total monthly sales, very quickly..
Example:
=SUM(A1:A50) means sum up all data in cells from 1 to 50.Watch this video from the Microsoft team and try it practically:
2. AVERAGE()
After SUM, another important formula is AVERAGE(). It is used to calculate the average value of selected data lines in the file.
We use this formula for a variety of things, like:
- Compare results
- Analyze performance
- Identify trends, etc.
In your job, you will be using AVERAGE to take out average sales, average response time, or average customer ratings from given data.
Example:
=AVERAGE(C2:C40) means get the average of data in the selected cells.Understand more with an article on the AVERAGE Function in Microsoft Excel.
3. IF()
The IF() formula is another helpful formula that helps in applying conditions or logic to data. It is helpful in making suitable decisions.
Some situations where you can use IF function are:
- To check bonus eligibility
- To evaluate pass/fail results
- For checking the status of goal accomplishment, etc.
You can use this IF formula to convert raw data into meaningful insights while working on real analytics work. My trainer, Ravi Sir, also emphasized it much and taught me to use it for decision-making.
Example:
=IF(B2>=50000,"Target Achieved","Target Not Achieved")Watch the video given below to under in a better way:
4. VLOOKUP / XLOOKUP
Data is rarely stored in a single sheet in the real world. This is the reason we have Lookup formulas. They help you match data and return values from different tables or sheets of the file.
We can do a lot of tasks using VLOOKUP and XLOOKUP, some of them are:
- We can combine customer data with sales data.
- We can match employee IDs with employee details.
- We can also fetch product prices from the list, etc.
VLOOKUP is available in older versions of Excel. It works from left to right only. On the other hand, XLOOKUP is available in modern versions of Excel. It is preferred over VLOOKUP because of its flexibility, ease of use.
Tip: Please don’t memorize the syntax for these lookup formulas. Rather, focus on understanding how these lookup logics work. You must be good at them as lookup formulas are very widely used in data analytics roles.
Example (XLOOKUP):
=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)Check the videos given below for the practical presentation:
For XLOOKUP:
5. COUNT, COUNTA, COUNTIF
We use COUNT formulas to count cells. All three have different functions.
- COUNT counts cells with numbers only.
Eg, =COUNT(A1:A10)means count all the numeric values in cells from A1:A10
- COUNTA counts non-blank cells. It counts text and errors along with numbers.
Eg, =COUNTA(B1:B10)means count all the values in cells from B1:B10
- COUNTIF counts cells that meet a specific condition.
Eg, =COUNTIF(C1:C10, “March”) means count cells that has “March” in C1:C10
Read more on Count, CountA, CountIf, and CountRows functions in Microsoft Excel.
6. SUMIF
The SUMIF() formula is used to get a value based on a specific condition. The best thing about this SUM() formula is that it analyzes the specific part of the data and skips the unwanted dataset.
Watch the video from Microsoft on the SUMIF formula.
You can break down the raw data into meaningful insights easily with SUMIF. Also note that it is a frequently asked formula in interviews, especially in entry-level data analytics positions. So, don’t skip it.
7. TEXT & CONCAT
We all know that raw data is always messy, and we can’t use it for analysis and reporting. Thus, you can use formulas like TEXT() and CONCAT(). They help in cleaning, formatting, as well as presenting the available raw data in a good state.
We can use these formulas for different purposes, like:
- To format dates and numbers
- To prepare data for reports
- And to combine first and last names into a single column.
It is our very first job to make data look clean and well-formatted so that decision makers can read it properly. Using these formulas, we can achieve this goal. So, everyone, focus on mastering the data formatting skill as it will make your job easier in the industy.
Examples:
=TEXT(A2,"dd-mm-yyyy")=CONCAT(A2," ",B2)Check the video for a better understanding below:
8. LEFT, RIGHT, MID
What we normally see in data is not actually its true information, as the important information is sometimes not visible to us. It happens when we collect it from systems or external sources. Thus, we use LEFT(), RIGHT(), and MID() formulas to extract the hidden meaning from specific parts of text so that the data becomes useful for analysis.
You can extract area codes from phone numbers, pull codes from IDs, or even identify categories from text strings using these formulas in MS Excel.
These formulas are especially useful when you work with some imported or system-generated data where clear meaning is not visible.
Examples:
=LEFT(A2,3)=RIGHT(A2,4)=MID(A2,2,5)Read more on Microsoft: Functions Left, Mid, and Right in Excel
9. IFERROR
In Data Analytics, errors are very common, but we should know the best ways to solve them simply.
So we can use this formula IFERROR() to handle these errors by replacing the errors with meaningful messages or values. We can even set to leave the cell blank.
It saves our report from looking unclear and messy.
Showing the errors as #N/A, or #DIV/0! is not good to show in the report, and using the error formula is an ideal option to go for.
Example:
Show nothing if any error is there.
=IFERROR(Formula, “”) means leave the cell blank if there’s an error.
For better understanding, read IFERROR in Excel on Microsoft
10. Pivot Tables (Not a Formula, But a Must-Know Tool)
Pivot Tables are not Excel formulas, but they are one of the most powerful options that I would recommend you learn. They are useful in data analytics and help in quickly analyzing and summarizing large datasets without writing any complex formulas.
You can use Pivot Tables to analyze trends, sum up large datasets, and even create quick and interactive reports.
When you join the industry, then I’m sure, you’ll see that mostly data analysts use Pivot Tables reports to understand the data before moving it to visualization tools like Power BI or Tableau.
So, don’t forget to include Pivot Tables in your Excel course in data analytics, as it is a must-know skill for your career in data analytics.
How Excel Connects with Advanced Analytical Tools
Let me break your bubble that Excel works alone. Instead, it works in coordination with other advanced tools, like:
- Power BI for dashboard creation,
- SQL for database-related work, and
- Python for automation, etc.
You can also read about Visuals and Their Uses in Power BI and Excel
So, this means, once you learn Excel skills then you will easily learn the other advanced tools as they are connected with each other in some ways.
Even learning Excel is good for learners who are attending Machine Learning Classes in Delhi as even in AI and ML we need structured data.
Join Advanced Excel Training at ADMEC Multimedia Institute, Delhi
At ADMEC Multimedia Institute, students get the proper training in Excel with real datasets and job-focused exercises. Our practical courses also focus on giving a sense to use data. That is why we cover classes on topics like why you should learn formulas, where, and why they are used, how they support decision-making, where you can apply them, etc.
All these skills and advanced Excel training will help students get jobs.
Closing words on 10 Excel Formulas for Data Analytics
So, we saw that Excel will never be outdated. It is always a core skill for every data analyst. Just focus on mastering all 10 formulas we discussed in this blog. They will help you to improve your accuracy, speed and build confidence.
Excel is your first step towards a career in data analytics that goes to advanced concepts like Power BI, Python, Machine Learning, etc. It is your right tool and its training matters a lot for you.
