10 Most Useful Excel Formulas for Data Analytics [With Example] MS Excel Guide
10 Most Useful Excel Formulas for Data Analytics – Complete Guide with Examples
Learning Excel becomes much easier when you know which formulas to learn first.
In this guide. you will learn 10 most useful Excel formulas for data analytics with real examples and free practice file.
A few useful formulas can handle most everyday spreadsheet tasks. They can help with calculations, reports, attendance sheets, sales data, and Excel data analytics.
In this guide, we will look at 10 important MS Excel formulas with simple examples.
Excel Formulas List
| # | Formula | What It Does |
|---|---|---|
| 1 | SUM() | Adds numbers |
| 2 | AVERAGE() | Finds the average |
| 3 | COUNT() | Counts numbers |
| 4 | COUNTA() | Counts filled cells |
| 5 | IF() | Checks a condition |
| 6 | SUMIF() | Adds values based on a condition |
| 7 | COUNTIF() | Counts values based on a condition |
| 8 | XLOOKUP() | Finds matching data |
| 9 | INDEX() + MATCH() | Performs advanced lookups |
| 10 | IFERROR() | Handles formula errors |
These Excel spreadsheet formulas are a good starting point for beginners and anyone working with data analysis from Excel.
1. SUM Formula in Excel
SUM() is probably the first formula most people learn.
It adds numbers from a selected range. This makes it useful for sales, expenses, invoices, budgets, and reports.
Excel SUM Formula
=SUM(B2:B10)This adds all numbers from B2 to B10.
You can also add individual cells:
=B2+B3+B4Butt the Excel sum formula is much easier when your data has many rows.
Common Uses of SUM
- Excel sum formula
- Excel summation formula
- Excel total formula
- Adding sales
- Calculating expenses
- Creating invoice totals
2. AVERAGE Formula in Excel
Want to know the average sales or average marks? The AVERAGE() function does it quickly.
Average Excel Formula
=AVERAGE(B2:B10)Excel adds the values and divides them by the numbers of value.
This formula is usefull for marks, salaries, sales, ratings, and performance reports.
For Excel data analytics, averages can give you a quick idea of how a dataset is performing.
Weighted Average in Excel
Sometimes every value does not have the same importance.
In that case, you can calculate a weighted average in Excel:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)Here, column B contains the value and column C contains their weights.
The AVERAGE Excel formula helps find the average value in a dataset.
3. COUNT Formula in Excel
COUNT() is used when you need to know how many cells contain numbers.
Count Excel Formula
=COUNT(B2:B100)For example, if 70 cells contain numbers, Excel returns 70.
This is handy when checking large datasets.
COUNT vs COUNTA
| Function | Counts |
|---|---|
COUNT() | Numbers |
COUNTA() | Non-empty cells |
Use COUNT() when you only need numerical entries.
4. COUNTA Formula in Excel
COUNTA() counts cells that are not empty.
It can count names, numbers, dates, and other entered values.
COUNTA Formula
=COUNTA(A2:A100)If 80 cells contain employee names, the result will be 80.
This is useful for Excel data entry work. You can quickly check how many records have been entered.
It can also be useful in an attendance sheet in Excel with formula.
5. IF Formula in Excel
The IF() function lets Excel make a decision.
You give it a condition. Excel then returns one result if the condition is true and another if it is false.
IF Function Excel Formula
=IF(B2>=50,"Pass","Fail")If B2 is 50 or more, Excel shows "Pass".
If it is below 50, Excel shows "Fail".
Where IF Is Useful
- Pass or fail results
- Attendence status
- Sales targets
- Payments status
- Employee performance
- Stoke alerts
IF AND Formula in Excel
You can combine IF() with AND() when you have more than one condition.
=IF(AND(B2>=50,C2="Yes"),"Eligible","Not Eligible")Both conditions must be true for the result to be "Eligible".
IFS Excel Formula
For several conditions, IFS() can make the formula easier to read.
=IFS(B2>=90,"Excellent",B2>=70,"Good",B2>=50,"Average",B2<50,"Poor")
Excel Percentage Formula
Percentage calculations are common in almost every Excel workbook.
=B2/C2After applying percentage formatting, Excel displays the result as a percentage.
You can use this for sales growth, marks, discounts, profit margins, and attendance.
Attendance Percentage Formula
For an attendance sheet, you could use:
=COUNTIF(B2:AF2,"P")/COUNTA(B2:AF2)This calculates the percentage of days marked as present.
It is useful when creating a monthly attendance sheet in Excel with formula.
6. SUMIF Formula in Excel
SUMIF() is useful when you want to add values that match a particular condition.
Suppose column A contains product names and column B contains sales.
=SUMIF(A2:A100,"Laptop",B2:B100)This returns the total sales for Laptop.
The SUMIF function in Excel is very useful for reports.
SUMIF Can Be Used For
- Product sales
- Department expenses
- Regional revenue
- Inventory
- Attendance data
When you have multiple conditions, SUMIFS() is usually a better option.
7. COUNTIF Formula in Excel
COUNTIF() counts cells that meet a condition.
=COUNTIF(A2:A100,"Laptop")This tells you how many times Laptop appears in the list.
You can also count numbers above a certain value:
=COUNTIF(B2:B100,">50000")This counts values greater than 50,000.
COUNTIF for Attendance
COUNTIF is particularly useful for attendance records.
=COUNTIF(B2:AF2,"P")This counts the days marked as Present.
That makes COUNTIF a useful formula for an attendance sheet in Excel with formula.
8. XLOOKUP Formula in Excel
Finding information in a large spreadsheet can take time if you do it manually.
XLOOKUP() makes this much easier.
XLOOKUP Formula in Excel
=XLOOKUP(E2,A2:A100,B2:B100)Excel searches for the value in E2.
It then returns the matching value from column B.
For example, you can enter an employee ID and return the employee's name, department, or salary.
VLOOKUP Formula in Excel
Before XLOOKUP, VLOOKUP formula in Excel was one of the most popular lookup functions.
=VLOOKUP(E2,A2:C100,3,FALSE)This searches for E2 in the first column and returns the matching value from the third column.
If you are learning Excel, it is still worth knowing vlookup, vlookup formula, vlookup excel, and vlookup formula in excel with example.
You may also see the term v lookup formula when people search for the same function.
9. INDEX MATCH in Excel
INDEX() and MATCH() can work together as a powerful lookup method.
INDEX MATCH Excel Formula
=INDEX(B2:B100,MATCH(E2,A2:A100,0))The MATCH() function finds the position of the value.
Then INDEX() returns the corresponding result.
This method is still common in many professional spreadsheets.
It is also an important topic in an advanced Excel formulas list.
Excel INDEX Function
The Excel INDEX function can return a value from a specific position in a range.
When combined with MATCH, it becomes much more flexible for lookups.
10. IFERROR Formula in Excel
Sometimes an Excel formula returns an error.
Instead of showing #N/A or #DIV/0!, you can use IFERROR().
IFERROR Formula
=IFERROR(A2/B2,0)If the calculation fails, Excel returns 0.
You can also display your own message:
=IFERROR(XLOOKUP(E2,A2:A100,B2:B100),"Not Found")This makes reports look much cleaner.
It is especially helpful when working with lookup formulas and large datasets.
VLOOKUP Formula vs XLOOKUP
Both functions can search for information in a table.
However, XLOOKUP is more flexible in a newer Excel versions.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Lookup data | Yes | Yes |
| Exact match | Yes | Yes |
| Return values | Yes | Yes |
| Easy to learn | Yes | Yes |
| Modern Excel | Supported | Recommended |
If you work with older files, knowing VLOOKUP formula in Excel is still useful.
For newer workbooks, learning XLOOKUP is a good idea.
Excel Addition Formula
Excel can also be used for simple calculations.
The Excel addition formula is:
=A2+B2You can add several cells with:
=SUM(A2:A10)For regular spreadsheet work, SUM is usually the better choice.
Excel Formula for Subtracting
To subtract one value from another:
=A2-B2This is the basic Excel formula for subtracting.
It can be used for expenses, profit calculations, stock balances, and invoice calculations.
Excel Formula for Multiplication
Use the * symbol for multiplication.
=A2*B2For example, if A2 contains quantity and B2 contains price, the formula calculates the total amount.
The * is the standard Excel * in formula operator for multiplication.
CONCAT Formula in Excel
Sometimes you need to combine text from two or more cells.
CONCAT() can do this.
=CONCAT(A2," ",B2)For example, it can combine first and last names.
You may also come across the older CONCATENATE Excel function:
=CONCATENATE(A2," ",B2)These CONCAT formula in Excel methods are useful when cleaning and preparing data.
Current Date Formula in Excel
Need today's date in a spreadsheet?
Use:
=TODAY()Excel automatically updates the date.
This can be useful in invoices, reports, attendance sheets, and Excel data entry templates.
Calculate Days in Excel
To find the number of days between two dates:
=B2-A2This is a simple way to calculate days in Excel.
Age Calculation Formula in Excel
Excel can also calculate someone's age from their date of birth.
A simple formula is:
=DATEDIF(A2,TODAY(),"Y")This returns the completed number of years.
It is commonly used in employee records and student databases.
You may see this searched as age calculation formula in Excel, age calculation in Excel, age Excel formula, ageing formula in Excel, and aging formula in Excel.
XIRR Formula in Excel
XIRR() is a financial function.
It calculates the internal rate of return when cash flows happen on different dates.
=XIRR(B2:B10,A2:A10)This is useful for investment and financial analysis.
Other related topics include XIRR formula, IRR formula Excel, and CAGR formula Excel.
CAGR Formula in Excel
CAGR means Compound Annual Growth Rate.
A basic CAGR calculation can be written as:
=(Ending_Value/Beginning_Value)^(1/Years)-1For example, it can help measure how an investment or business has grown over several years.
This is one of the more useful advanced Excel formulas for financial analysis.
Conditional Formatting in Excel
Formulas are not the only useful Excel feature for data analysis.
Conditional formatting Excel tools can automatically highlight important values.
Examples
- Low sales
- High sales
- Late payments
- Low attendance
- Duplicate values
- Expired dates
This makes a large spreadsheet much easier to read.
Excel Formulas for Data Analytics
If your goal is Excel data analytics, formulas are only one part of the learning process.
You should also learn how to organize and analyze data.
- Sorting and filtering
- Excel Tables
- PivotTables
- Charts
- Conditional formatting
- Data cleaning
- Data validation
- Power Query
- Lookup functions
A combination of these tools can turn raw spreadsheet data into a useful report.
Pivot Table in Excel
PivotTables are extremely useful when working with large datasets.
You can summarize sales by product, region, employee, or month without creating complicated formulas.
For example, thousands of sales records can be turned into a small summary report.
You may also come across searches such as pivot table formula and pivot table in Excel formula.
Technically, a PivotTable itself is not a formula. It is an Excel data-analysis feature that summarizes information from a dataset.
Image Prompt: Create a realistic Microsoft Excel PivotTable report on a laptop showing thousands of sales records summarized by product and region, with grand totals and a related chart, professional data analyst office environment, 16:9.
Alt Text: Pivot Table in Excel for data analysis
Attendance Sheet in Excel with Formula
Excel is often used for employee and student attendance.
A simple attendance sheet can use P for Present and A for Absent.
Count Present Days
=COUNTIF(B2:AF2,"P")To calculate the attendance percentage:
=COUNTIF(B2:AF2,"P")/COUNTA(B2:AF2)You can then format the result as a percentage.
This makes it easy to create a monthly attendance sheet in Excel with formula.
Image Prompt: Create a realistic Microsoft Excel monthly attendance register with employee names, dates from 1 to 31, P and A attendance marks, present count and attendance percentage formulas, clean professional spreadsheet design, 16:9.
Alt Text: Monthly attendance sheet in Excel with formula
Basic Excel Formulas to Learn First
If you are completely new to Excel, do not jump straight into complicated functions.
Start with these simple Excel formulas:
=SUM(A2:A10)=AVERAGE(A2:A10)=A2-B2=A2*B2=A2/B2=IF(A2>=50,"Pass","Fail")Once these feel comfortable, move on to SUMIF, COUNTIF, XLOOKUP, VLOOKUP, and INDEX MATCH.
Advanced Excel Formulas List
After learning the basics, you can explore these functions:
SUMIFS()COUNTIFS()XLOOKUP()VLOOKUP()INDEX()MATCH()IFERROR()SUMPRODUCT()XIRR()IFS()CONCAT()
These are useful additions to your advanced Excel formulas list.
You do not need to learn all of them in one day.
Practice one function at a time with real spreadsheet data.
Excel Formulas and Functions for Beginners
The main difference is simple.
A formula is something you create using operators, cell references, and functions.
For example:
=A2+B2A function is a built-in Excel calculation such as:
=SUM(A2:A10)Both are important when learning Excel formulas and functions.
Google Spreadsheet Formulas
Many Excel formulas also work in Google Sheets.
Functions such as SUM(), AVERAGE(), COUNTIF(), SUMIF(), IF(), and VLOOKUP() are commonly available in both platforms.
So the skills you learn from Excel can also help when working with Google spreadsheet formulas.
Excel Calculator for Everyday Work
Excel can handle many small calculations without needing a separate calculator.
- Addition
- Subtraction
- Multiplication
- Division
- Percentage
- Discounts
- Profit
- Average
- Growth rate
This is one reason Excel remains useful for everyday office work.
Final Thoughts
You do not need to learn all Excel formulas to become good at Excel.
Start with the basics.
Learn SUM(), AVERAGE(), COUNT(), COUNTA(), and IF() first.
Then move to SUMIF(), COUNTIF(), XLOOKUP, VLOOKUP, INDEX MATCH, and IFERROR.
Once these functions become familiar, start practicing with real datasets.
That is where data analysis and Excel become much more interesting. You can build reports, attendance sheets, sales dashboards, financial calculations, and useful business reports without doing every calculation manually.
For anyone learning MS Excel data analysis, these formulas are a solid starting point.
Download the free Excel practice file and start practicing Excel formulas and data analysis.
⬇️ Download
Comments
Post a Comment