Excel for Data Analytics • Lesson 4
Excel Functions for Data Analytics
Learn how to use SUM, AVERAGE, MIN, MAX and COUNT to summarize business data and answer common data analytics questions in Excel.
Why Excel Functions Matter in Data Analytics
In the previous lessons, you learned how to work with cells, references and basic formulas. Now you will learn functions that help you summarize many values quickly.
Instead of manually adding hundreds of sales values, an analyst can use Excel functions to calculate totals, averages, minimums, maximums and counts.
DATASET
Your Sales Dataset
Imagine you are a junior data analyst working for an electronics retailer. Your manager gives you the following weekly sales data.
| Day | Region | Product | Units Sold | Revenue |
|---|---|---|---|---|
| Monday | North | Laptop | 4 | ₹200,000 |
| Tuesday | South | Mouse | 20 | ₹20,000 |
| Wednesday | East | Keyboard | 15 | ₹30,000 |
| Thursday | West | Monitor | 8 | ₹120,000 |
| Friday | North | Laptop | 3 | ₹150,000 |
| Saturday | South | Monitor | 5 | ₹75,000 |
STEP 1
SUM Function: Calculate a Total
The SUM function adds numbers together. It is one of the most useful functions for basic business analysis.
Syntax
This adds all numeric values from B2 through B7.
CHECK YOUR UNDERSTANDING
Which Excel function should you use to calculate the total Units Sold for the entire dataset?
FORMULA CHALLENGE
Units Sold are stored in D2:D7. Write the Excel formula to calculate total units sold.
DATA ANALYTICS PRACTICE
Calculate Total Weekly Revenue
The manager wants to know how much revenue the store generated during the week.
Revenue values:
Total Revenue = ₹595,000
CHECK YOUR UNDERSTANDING
What is the total revenue for the six sales records?
STEP 2
AVERAGE Function
AVERAGE calculates the arithmetic mean of a group of numbers.
If the Units Sold values are 4, 20, 15, 8, 3 and 5, Excel calculates their average.
CHECK YOUR UNDERSTANDING
Why might an analyst calculate the average number of units sold per transaction?
FORMULA CHALLENGE
Revenue values are stored in E2:E7. Write the formula to calculate average revenue per record.
STEP 3
MIN and MAX Functions
Analysts often need to identify the smallest and largest values in a dataset.
MIN
Finds the smallest numeric value.
MAX
Finds the largest numeric value.
CHECK YOUR UNDERSTANDING
Looking at the Revenue column, what is the highest single revenue value?
FORMULA CHALLENGE
Write an Excel formula to find the lowest revenue in E2:E7.
STEP 4
COUNT Function
COUNT tells us how many cells in a range contain numbers.
Because there are six numeric Units Sold values, the result is:
CHECK YOUR UNDERSTANDING
What does =COUNT(D2:D7) tell an analyst in this dataset?
Data Analytics Practice
Turn Functions Into Business Questions
Excel functions become useful when they help answer a real business question.
Business Question
What were our total sales?
SUM
Business Question
What was the typical revenue per transaction?
AVERAGE
Business Question
What was our largest individual sale?
MAX
Business Question
How many sales records do we have?
COUNT
CHECK YOUR UNDERSTANDING
A manager asks, 'What was the largest individual revenue generated during the week?' Which function should you use?
STEP 5
Compare Total and Average Performance
Analysts often use more than one metric to understand a dataset. Total revenue tells us the overall amount, while average revenue tells us the typical value per record.
Total Revenue
₹595,000
Calculated with SUM.
Average Revenue
₹99,166.67
Calculated with AVERAGE.
CHECK YOUR UNDERSTANDING
Why might a manager want to see both total revenue and average revenue?
STEP 6 • DEBUGGING
Find the Function Mistake
An analyst wants to calculate total revenue but writes:
The manager specifically asked for the total revenue.
CHECK YOUR UNDERSTANDING
What should the analyst use instead?
Final Challenge
Your First Excel Analytics Summary
Your manager asks you to prepare a quick summary of the six sales records.
The manager wants to know:
- • Total revenue
- • Average revenue per record
- • Highest individual revenue
- • Lowest individual revenue
- • Number of sales records
CHECK YOUR UNDERSTANDING
Which combination of functions answers all five questions?
FORMULA CHALLENGE
Write the Excel formula that calculates total revenue when revenue values are stored in E2:E7.
Lesson 4 Complete
You can now use essential Excel functions to summarize a dataset and answer basic business questions.