Excel formulas are the backbone of data analytics. In Data Analytics FULL Course 2026 – Part 03, the focus shifts from understanding the Excel dashboard to mastering the most important Excel formulas used by data analysts in real projects.
In this lecture, you learn 20 essential Excel formulas, complete with examples, explanations, and a downloadable practice dataset. Whether you are using Microsoft Excel Online, Offline Excel, or Google Sheets, these formulas remain almost the same—making them universally valuable.
Why Excel Formulas Are Critical for Data Analysts
Every data analyst works with:
- Raw data
- Calculations
- Conditions
- Data cleaning
- Lookups
- Reporting logic
Excel formulas help automate these tasks efficiently. Instead of manual calculations, formulas allow you to scale your analysis to thousands or even millions of rows.
Getting the Practice Excel Sheet
The instructor provides a read-only Excel sheet that includes:
- Formula name
- Syntax & example
- Use case description
- Separate practice tabs for each formula
You can:
- Create an online copy (Excel Web)
- Download an offline copy (Windows/Mac)
All formulas work the same in both environments.
Basic Excel Formulas Every Analyst Must Know
1. SUM Formula
The SUM function adds numeric values in a range.
Use case:
Find total units sold or total revenue.
Example:
=SUM(E4:E10)
This formula adds all values between cells E4 and E10.
2. AVERAGE Formula
The AVERAGE function calculates the mean of numeric values.
Use case:
Find average unit price or average sales.
Example:
=AVERAGE(F3:F9)
Tip: Decimal places can be adjusted without changing the actual value.
3. COUNT vs COUNTA
Understanding the difference is very important:
- COUNT → Counts only numeric values
- COUNTA → Counts all non-empty cells
Example:
=COUNT(B3:B9)
=COUNTA(C3:C9)
4. Counting Unique Values
To count unique values, combine formulas:
=COUNTA(UNIQUE(D3:D9))
This is commonly used to find:
- Unique products
- Unique categories
- Unique customers
Minimum & Maximum Analysis
5. MIN Formula
Finds the smallest value in a range.
=MIN(F3:F9)
6. MAX Formula
Finds the largest value in a range.
=MAX(F3:F9)
These formulas are essential for pricing analysis and performance tracking.
IF Formula – The Most Important Logic Function
7. IF Formula
The IF function applies conditions.
Syntax:
=IF(condition, value_if_true, value_if_false)
Example:
=IF(Q6>100,"High","Low")
Real use cases:
- Bulk order detection
- Performance grading
- Eligibility checks
This formula is one of the most used and most asked interview questions.
Conditional Aggregation Formulas
8. SUMIF
Adds values based on one condition.
Example:
=SUMIF(D3:D9,"Stationery",E3:E9)
Use case:
Total units sold for a specific category.
9. COUNTIF
Counts records matching a condition.
Example:
=COUNTIF(C3:C9,"Pen")
Use case:
How many times a product was sold.
10. SUMIFS & COUNTIFS
Used when multiple conditions are required.
Example:
=SUMIFS(E3:E9,D3:D9,"Stationery",C3:C9,"Notebook")
This is extremely common in real business reports.
Text Handling & Data Cleaning Formulas
11. CONCAT
Joins text values.
=CONCAT(A3," - ",C3," - ",D3)
12. TEXTJOIN
Better than CONCAT when working with many cells.
=TEXTJOIN("-",TRUE,A3,C3,D3)
Automatically handles separators and empty cells.
13. LEFT & RIGHT
Extract characters from text.
LEFT example (country code):
=LEFT(A2,2)
RIGHT example (last 4 digits):
=RIGHT(A2,4)
Used heavily in:
- Phone number cleaning
- Masking sensitive data
14. LEN
Counts number of characters in a cell.
=LEN(A2)
15. TRIM
Removes extra spaces.
=TRIM(A2)
Very useful for cleaning imported data.
Lookup Formulas – Most Asked in Interviews
16. VLOOKUP
Finds values vertically.
=VLOOKUP(103,B3:H9,2,TRUE)
Limitation:
Does not work with horizontal data.
17. XLOOKUP (Recommended)
Works with both vertical and horizontal data.
=XLOOKUP("Mouse",C3:C9,E3:E9)
Why XLOOKUP is better:
- No column numbers
- Works both ways
- Easier to remember
Used heavily in joins-like logic, similar to SQL joins.
Date & Automation Functions
18. TODAY
Returns today’s date.
=TODAY()
19. NOW
Returns current date and time.
=NOW()
Useful for:
- Automation
- Dynamic dashboards
- Time-based tracking
20. Date Calculations
Subtracting dates gives the number of days between them.
=TODAY()-A2
Used for:
- Aging analysis
- SLA tracking
- Timeline analysis
What’s Next in the Course?
This lecture builds strong Excel formula fundamentals. In upcoming lessons, you’ll learn:
- Pivot Tables
- Advanced Lookups
- SQL-style analysis
- Real-world projects
- Dashboards with Excel & Power BI