Excel for Data Analytics • Lesson 3
Excel Formulas for Data Analytics
Learn how to create basic Excel formulas and use them to solve simple data analytics and business problems.
What You Will Learn
Excel formulas allow analysts to turn raw data into useful information. In this lesson, you will build simple formulas using a small sales dataset.
STEP 1
What Is an Excel Formula?
An Excel formula is an instruction that tells Excel how to calculate a result.
Excel formulas normally begin with an equals sign:
Excel calculates the result:
CHECK YOUR UNDERSTANDING
Which symbol normally starts an Excel formula?
STEP 2
Basic Arithmetic in Excel
Excel can perform common mathematical operations using simple operators.
+ Addition
=10+5 → 15
- Subtraction
=10-5 → 5
* Multiplication
=10*5 → 50
/ Division
=10/5 → 2
CHECK YOUR UNDERSTANDING
A company sold 20 units at ₹500 each. Which operator is needed to calculate total sales?
STEP 3
Build a Revenue Formula
Revenue is one of the first metrics a data analyst may calculate from a sales dataset.
Revenue Formula
Suppose:
- Units = 5
- Price = ₹2,000
Revenue = ₹10,000
FORMULA CHALLENGE
Units are in C2 and Price is in D2. Write the Excel formula for Revenue.
CHECK YOUR UNDERSTANDING
A store sells 8 monitors at ₹15,000 each. What is the revenue?
STEP 4
Calculate Total Cost
Revenue tells us how much money a company receives from sales. Cost tells us how much it spends to provide those products.
Total Cost
Example:
10 units × ₹600 cost per unit = ₹6,000 total cost.
FORMULA CHALLENGE
Units are in C2 and Unit Cost is in E2. Write the formula for Total Cost.
STEP 5
Calculate Profit
Once we know revenue and cost, we can calculate profit.
Revenue
₹100,000
Total Cost
₹70,000
Profit = Revenue − Total Cost
₹100,000 − ₹70,000 = ₹30,000
CHECK YOUR UNDERSTANDING
A business has revenue of ₹250,000 and total cost of ₹180,000. What is its profit?
FORMULA CHALLENGE
Revenue is in F2 and Total Cost is in G2. Write the formula for Profit.
STEP 6
Calculate Profit Margin
Profit margin helps us understand how much of the revenue remains as profit.
Example:
Profit = ₹30,000
Revenue = ₹100,000
Profit Margin = 30%
FORMULA CHALLENGE
Profit is in H2 and Revenue is in F2. Write the formula for Profit Margin.
CHECK YOUR UNDERSTANDING
A company earns ₹20,000 profit on ₹100,000 revenue. What is its profit margin?
STEP 7
Copy Formulas Across a Dataset
Data analysts usually work with many rows. Once a formula is correct, it can be copied down the dataset.
Suppose the Revenue formula in E2 is:
After copying it to E3:
Excel changes the row references automatically.
CHECK YOUR UNDERSTANDING
If =C2*D2 is copied from E2 to E7, what formula should appear in E7?
STEP 8
Debug a Formula
Analysts also need to check whether formulas are calculating the correct values.
A sales analyst writes:
The columns contain Units and Price. The analyst wants Revenue.
CHECK YOUR UNDERSTANDING
What is wrong with =C2+D2 if C2 contains Units and D2 contains Price?
Data Analytics Practice
Use Formulas to Understand Business Performance
Consider these two products:
| Product | Units | Price | Unit Cost |
|---|---|---|---|
| Laptop | 3 | ₹50,000 | ₹40,000 |
| Monitor | 10 | ₹15,000 | ₹11,000 |
CHECK YOUR UNDERSTANDING
Which product generates more revenue?
CHECK YOUR UNDERSTANDING
Based on the same data, which product generates more total profit?
Final Challenge
Your First Formula-Based Analytics Task
A company sells laptops using the following information:
Units
5
Selling Price
₹60,000
Unit Cost
₹45,000
Required
Profit
CHECK YOUR UNDERSTANDING
What is the total profit from these 5 laptops?
FORMULA CHALLENGE
If Units are in C2, Selling Price is in D2 and Unit Cost is in E2, write one Excel formula that directly calculates Total Profit.
Lesson 3 Complete
You have now created basic Excel formulas and used them to calculate revenue, cost, profit and profit margin. You also practiced copying and debugging formulas.