Excel Formulas Calculation and Cell References Practice Questions with Solutions

Introduction

Excel formulas are used to perform calculations and solve practical data problems. In this chapter, you will practice arithmetic calculations, formula operators, cell references, relative references, absolute references, mixed references, and copying formulas. The questions are intentionally different from each other so you can test whether you can apply Excel formulas correctly to different types of data. Excel Formulas Calculation and Cell References practice help you understand the concepts.

Question 1: Calculate Total Sales

Problem Statement

A shop has sold different products. Calculate the total sales amount for each product using an Excel formula.

Excel Data

Solution

In cell D2, enter:

=B2*C2

Press Enter and copy the formula down to D6.

Expected Output

Question 2: Calculate Student’s Total and Average Marks

Problem Statement

Calculate the total marks and average marks obtained by each student in five subjects.

Excel Data

Solution

In G2, enter:

=SUM(B2:F2)

In H2, enter:

=G2/5

Copy both formulas down.

Expected Output

Question 3: Calculate Profit or Loss

Problem Statement

A business purchases products at one price and sells them at another price. Calculate the profit or loss for each product.

Excel Data

Solution

In D2, enter:

=C2-B2

Copy the formula down to D6.

Expected Output

A positive result represents profit, while a negative result represents a loss.

Question 4: Calculate Employee Net Salary

Problem Statement

Calculate the net salary of employees after deducting the provided deductions.

Excel Data

Solution

Net Salary is:

Basic Salary + Allowance − Deduction

In E2, enter:

=B2+C2-D2

Copy the formula down.

Expected Output

Question 5: Calculate Discount Using a Percentage

Problem Statement

Calculate the discount amount and final price for each product.

Excel Data

Solution

In D2, calculate the discount amount:

=B2*C2

In E2, calculate the final price:

=B2-D2

Copy both formulas down.

Expected Output

Question 6: Use an Absolute Cell Reference for GST

Problem Statement

A company applies a fixed 18% GST rate to all products. Store the GST rate in one cell and calculate the GST amount for each product.

Excel Data

Place the GST rate in F1:

Product data:

Solution

First calculate the amount in D2:

=B2*C2

In E2, calculate GST:

=D2*$F$1

Copy the formulas down.

The $ signs make F1 an absolute reference, so the GST rate remains fixed when the formula is copied.

Expected Output

Question 7: Calculate Commission Using a Mixed Reference

Problem Statement

A sales employee receives commission based on the sales amount. The commission rate is stored separately for each employee. Calculate the commission amount.

Excel Data

Solution

In D2, enter:

=B2*C2

Copy the formula down.

Expected Output

This example demonstrates how Excel adjusts relative references when a formula is copied to another row.

Question 8: Calculate Monthly Expenses and Remaining Budget

Problem Statement

A person has a monthly budget of ₹50,000. Calculate total expenses and the amount remaining from the budget.

Excel Data

Enter the budget in B1:

Solution

Calculate total expenses in B8:

=SUM(B3:B7)

Calculate the remaining budget in B9:

=B1-B8

Expected Output

Question 9: Calculate Percentage Increase in Sales

Problem Statement

A company wants to compare sales from two years. Calculate the percentage increase or decrease in sales.

Excel Data

Solution

In D2, enter:

=(C2-B2)/B2

Format column D as Percentage and copy the formula down.

Expected Output

A positive percentage indicates an increase, while a negative percentage indicates a decrease.

Question 10: Create a Product Price Calculator Using Multiple Cell References

Problem Statement

Create a product calculator that calculates the subtotal, discount amount, GST amount and final payable amount.

Use:

• Discount Rate = 10%
• GST Rate = 18%

Excel Data

Place the rates separately:

Solution

In D2, calculate Subtotal:

=B2*C2

In E2, calculate Discount:

=D2*$J$1

In F2, calculate GST on the discounted amount:

=(D2-E2)*$J$2

In G2, calculate Final Amount:

=D2-E2+F2

Copy all formulas down.

Expected Output

This question combines multiplication, subtraction, percentages, absolute references and multiple formulas in one practical calculation.

Key Takeaways

• Excel formulas normally begin with the = sign.
• Arithmetic operators include +, -, *, / and ^.
• Cell references allow formulas to use values stored in other cells.
• A relative reference changes when a formula is copied.
• An absolute reference, such as $A$1, remains fixed when copied.
• Mixed references can lock either the row or the column.
• Percentages can be used directly in Excel calculations.
• Formulas can be combined to solve multi-step calculations.
• Copying formulas saves time when the same calculation is required for many rows.
• Always check cell references carefully when copying complex formulas.

FAQs

1. What is an Excel formula?

An Excel formula is an expression used to perform calculations or process data. Most Excel formulas begin with the = symbol.

2. What are the basic arithmetic operators in Excel?

The main arithmetic operators are + for addition, - for subtraction, * for multiplication, / for division and ^ for powers.

3. What is a cell reference in Excel?

A cell reference identifies a cell by its column and row, such as A1, B5 or D10.

4. What is a relative cell reference?

A relative reference changes automatically when a formula is copied to another cell. For example, A1 can become A2 when copied down one row.

5. What is an absolute cell reference?

An absolute reference remains fixed when a formula is copied. For example, $A$1 always refers to cell A1.

6. What is a mixed cell reference?

A mixed reference locks either the row or column while allowing the other part to change. Examples include $A1 and A$1.

7. Why are dollar signs used in Excel formulas?

Dollar signs are used to lock cell references. For example, $B$2 keeps both the column and row fixed when the formula is copied.

8. How can I copy a formula to other cells?

Enter the formula in the first cell and drag the fill handle to the required cells. You can also copy and paste the formula.

9. How do I calculate a percentage in Excel?

For example, to calculate 10% of a value in A1, you can use:

=A1*10%

10. Why should I use cell references instead of typing numbers directly into formulas?

Cell references make formulas easier to update and reuse. If the value in the referenced cell changes, Excel can automatically recalculate the result.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar