Excel Formula Exercises
Practise Excel formulas through practical business problems. Learn arithmetic calculations, logical functions, conditional totals, counting, text manipulation, date calculations, and lookup functions with examples and assignments.
1. Introduction to Excel Formula Exercises
Excel formulas help users perform calculations, analyse information, compare values, and automate repetitive calculations. A formula begins with an equals sign (=) and can contain cell references, operators, constants, functions, and ranges.
For example, a business may need to calculate total sales, determine employee bonuses, count completed orders, identify products that have reached a sales target, or retrieve product prices from a master list. Excel formulas can perform these tasks efficiently.
In this module, you will practise commonly used formulas with sample datasets. Each exercise includes a requirement, a formula, an explanation, and an expected result.
How an Excel Formula Works
Conceptual illustration of formula evaluation.
2. Prepare the Practice Dataset
Create an Excel worksheet named SalesData and
enter the following sample records. Use the column headings
shown below so that the formulas in this tutorial match
the dataset.
| Order ID | Product | Region | Quantity | Unit Price (₹) | Sales (₹) |
|---|---|---|---|---|---|
| ORD001 | Laptop | West | 2 | 50,000 | 1,00,000 |
| ORD002 | Mobile | South | 3 | 20,000 | 60,000 |
| ORD003 | Tablet | North | 4 | 12,000 | 48,000 |
| ORD004 | Laptop | West | 1 | 50,000 | 50,000 |
| ORD005 | Mobile | North | 2 | 20,000 | 40,000 |
3. Basic Arithmetic Operators
Arithmetic operators perform calculations using numeric values or cell references. They are useful for sales calculations, discounts, commissions, expenses, and profits.
| Operator | Operation | Example | Result |
|---|---|---|---|
+ |
Addition | =100+50 |
150 |
- |
Subtraction | =100-50 |
50 |
* |
Multiplication | =100*5 |
500 |
/ |
Division | =100/4 |
25 |
^ |
Exponentiation | =5^2 |
25 |
Exercise 1: Calculate Total Sales
If Quantity is in D2 and Unit Price is in E2, multiply the two values to calculate sales for the order.
=D2*E2
For the first record, 2 units multiplied by ₹50,000 gives ₹1,00,000. Copy the formula down to calculate sales for the remaining records.
Exercise 2: Calculate Discounted Sales
Suppose the sales amount is in F2 and the discount rate is 10%. The discounted amount can be calculated as follows.
=F2*(1-10%)
If F2 contains ₹1,00,000, the amount after a 10% discount is ₹90,000. This formula assumes the discount is applied directly to the sales amount.
4. SUM, AVERAGE, MIN, MAX and COUNT
Excel summary functions calculate totals, averages, minimums, maximums, and counts. They are widely used in reports and dashboards to summarize business data.
| Function | Formula | Purpose |
|---|---|---|
| SUM | =SUM(F2:F6) |
Adds all sales values. |
| AVERAGE | =AVERAGE(F2:F6) |
Calculates average sales per order. |
| MIN | =MIN(F2:F6) |
Returns the smallest sales value. |
| MAX | =MAX(F2:F6) |
Returns the largest sales value. |
| COUNT | =COUNT(F2:F6) |
Counts numeric cells. |
| COUNTA | =COUNTA(A2:A6) |
Counts non-empty cells. |
Example: Total Sales
=SUM(F2:F6)
Based on the sample dataset, total sales are ₹2,98,000. The formula adds the five sales values in cells F2 through F6.
Example: Average Sales
=AVERAGE(F2:F6)
The average sales value is ₹59,600 per order. AVERAGE calculates the mean of numeric cells and ignores blank cells.
5. IF, AND and OR Functions
Logical functions evaluate conditions and return results based on whether those conditions are true or false. They are useful for eligibility checks, target achievement, performance categories, and business rules.
5.1 IF Function
The IF function checks a condition and returns one value when the condition is true and another when it is false.
=IF(logical_test, value_if_true, value_if_false)
Example: classify orders with sales of at least ₹50,000.
=IF(F2>=50000,"Target Achieved","Below Target")
5.2 AND Function
AND returns TRUE only when all specified conditions are true. For example, an order may need sales of at least ₹50,000 and a quantity of at least 2.
=IF(AND(F2>=50000,D2>=2),"Qualified","Not Qualified")
5.3 OR Function
OR returns TRUE when at least one specified condition is true. In this example, an order qualifies if its sales reach ₹80,000 or its quantity is at least 4.
=IF(OR(F2>=80000,D2>=4),"Priority","Normal")
Understanding Logical Conditions
6. SUMIF, SUMIFS, COUNTIF and COUNTIFS
Conditional functions calculate totals or counts based on specified criteria. They are useful when you need to analyse selected products, regions, departments, or order categories.
6.1 SUMIF
Calculate total sales for the West region.
=SUMIF(C2:C6,"West",F2:F6)
The formula checks the region in C2:C6 and adds the matching sales values from F2:F6. Based on the sample dataset, West region sales total ₹1,50,000.
6.2 SUMIFS
Calculate sales for Laptop orders in the West region.
=SUMIFS(F2:F6,B2:B6,"Laptop",C2:C6,"West")
The formula adds sales only when the product is Laptop and the region is West. The result for the sample dataset is ₹1,50,000.
6.3 COUNTIF
Count the number of Laptop orders.
=COUNTIF(B2:B6,"Laptop")
The result is 2 because two records contain Laptop.
6.4 COUNTIFS
Count orders from the West region with sales of at least ₹50,000.
=COUNTIFS(C2:C6,"West",F2:F6,">=50000")
The result is 2. Both West region records meet the sales threshold in this dataset.
7. Text Functions
Text functions help clean, combine, extract, and standardize text values. They are particularly useful when importing names, product codes, addresses, and other business records.
| Function | Example | Purpose |
|---|---|---|
| LEFT | =LEFT(A2,3) |
Returns the first three characters. |
| RIGHT | =RIGHT(A2,3) |
Returns the last three characters. |
| MID | =MID(A2,4,2) |
Returns characters from a specified position. |
| LEN | =LEN(A2) |
Counts characters in a text value. |
| TRIM | =TRIM(A2) |
Removes leading, trailing, and repeated ordinary spaces. |
| UPPER | =UPPER(B2) |
Converts text to uppercase. |
| CONCAT | =CONCAT(A2,"-",B2) |
Combines text values. |
Example: Clean a Product Name
If a cell contains extra spaces around a product name, TRIM can remove unnecessary ordinary spaces.
=TRIM(B2)
Example: Create an Order Label
=CONCAT(A2," - ",B2)
If A2 contains ORD001 and B2 contains Laptop, the formula
returns ORD001 - Laptop.
8. Date Functions
Date functions help calculate dates, extract date components, and determine time intervals. These functions are useful for monthly reporting, order tracking, and attendance analysis.
| Function | Example | Purpose |
|---|---|---|
| TODAY | =TODAY() |
Returns the current date. |
| DAY | =DAY(B2) |
Extracts the day of the month. |
| MONTH | =MONTH(B2) |
Extracts the month number. |
| YEAR | =YEAR(B2) |
Extracts the year. |
| DATE | =DATE(2026,10,17) |
Creates a date from year, month, and day values. |
| DATEDIF | =DATEDIF(B2,C2,"d") |
Calculates the difference between dates in days. |
Example: Extract the Month
=MONTH(B2)
If B2 contains a valid Excel date in October, the formula returns 10. Make sure the cell contains a real Excel date, not text that only looks like a date.
9. Lookup Functions
Lookup functions retrieve information from another range or table. For example, an order sheet may contain a product name but need to retrieve its standard unit price from a product master table.
Sample Product Master
| Product | Unit Price (₹) |
|---|---|
| Laptop | 50,000 |
| Mobile | 20,000 |
| Tablet | 12,000 |
9.1 VLOOKUP
If the product name is in H2 and the product master is in K2:L4, the following formula retrieves the matching price.
=VLOOKUP(H2,$K$2:$L$4,2,FALSE)
The formula searches for the product name in the first column of the lookup range and returns the value from the second column. FALSE requests an exact match.
9.2 XLOOKUP
In Excel versions that support XLOOKUP, the equivalent lookup can be written as follows.
=XLOOKUP(H2,K2:K4,L2:L4,"Not Found")
XLOOKUP searches the product range and returns the matching price. If no matching product exists, it displays "Not Found".
How a Lookup Formula Retrieves Data
10. Common Excel Formula Errors
Understanding common formula errors helps you identify problems quickly and improve the reliability of your calculations.
| Error | Possible Cause | What to Check |
|---|---|---|
#DIV/0! |
A formula divides by zero or a blank denominator. | Check the denominator before dividing. |
#N/A |
A lookup value cannot be found. | Check the lookup value and lookup range. |
#VALUE! |
An operation uses an unsuitable value type. | Check cell contents and formula arguments. |
#REF! |
A formula refers to an invalid cell reference. | Check deleted or moved cells and ranges. |
#NAME? |
A function name or text reference is invalid. | Check spelling and quotation marks. |
| Incorrect result | Wrong cell references or criteria. | Check ranges, operators, and assumptions. |
Example: Handle Division by Zero
If profit is in G2 and sales are in F2, a profit margin calculation should account for the possibility of zero sales.
=IF(F2=0,0,G2/F2)
This formula returns 0 when sales are zero; otherwise, it divides profit by sales. Format the result as a percentage. Choose an alternative such as "N/A" if zero sales should be reported as unavailable rather than as a 0% margin.
11. Practice Assignments
Complete the following exercises using the sample sales dataset. Try solving each task yourself before checking the suggested formula.
| No. | Exercise | Suggested Formula or Function |
|---|---|---|
| 1 | Calculate total sales for all orders. | SUM |
| 2 | Calculate average sales per order. | AVERAGE |
| 3 | Find the highest sales value. | MAX |
| 4 | Count Laptop orders. | COUNTIF |
| 5 | Calculate total sales for the North region. | SUMIF |
| 6 | Count West region orders with sales above ₹50,000. | COUNTIFS |
| 7 | Classify orders that reach the ₹50,000 target. | IF |
| 8 | Retrieve a product's unit price from the master list. | XLOOKUP or VLOOKUP |
| 9 | Calculate sales after a 5% discount. | Arithmetic formula |
| 10 | Create a combined label using Order ID and Product. | CONCAT |
12. Important Points
- Every Excel formula begins with an equals sign (=).
- Use cell references and ranges that match your dataset.
-
Use absolute references such as
$K$2:$L$4when a lookup range must remain fixed while copying formulas. - SUMIF and COUNTIF apply one criterion; SUMIFS and COUNTIFS support multiple criteria.
- Check date values, text values, and numeric values before processing.
- Handle division by zero and lookup failures appropriately.
- Format currency, dates, and percentages for readability.
- Verify important results against the original records.
13. Summary
In this module, you learned how Excel formulas perform arithmetic calculations, summarize datasets, evaluate logical conditions, calculate conditional totals, process text and dates, and retrieve information using lookup functions.
You also explored common formula errors, result validation, and practical exercises based on sales data. These skills form the foundation for more advanced Excel analysis, PivotTables, dashboards, and VBA automation.
Practise the assignments using your own workbook, verify the output, and then continue to PivotTable Exercises.