Excel Lab Task – Sales Report with Advanced Formulas & Conditional
Formatting
This lab will guide students in creating a Sales Report using IF formulas, nested IF,
conditional formatting, and other Excel features. The tasks are designed to be moderately
challenging so that students can complete them independently using the provided dataset.
Dataset/Problem Statement
Steps
1. Add a Status column
In column E (Status), write a formula to check if Sales (column C) is greater than
or equal to Target (column D).
If yes, display "Achieved", else "Not Achieved".
Formula (E2):
=IF(C2>=D2,"Achieved","Not Achieved")
Copy this formula down for all rows.
2. Calculate Difference
In column F, calculate how much sales are above or below the target.
Formula (F2):
=C2-D2
Copy down the formula.
3. Highlight Achieved Sales (Conditional Formatting)
Select the Sales column (C2:C11).
Apply conditional formatting to highlight cells green if Sales ≥ Target.
4. Highlight Not Achieved Sales (Conditional Formatting)
Select the Sales column (C2:C11).
Apply conditional formatting to highlight cells red if Sales < Target.
5. Calculate Total Sales
At the bottom of Sales column (e.g., C12), calculate the sum of all sales using:
=SUM(C2:C11)
6. Calculate Total Target
At the bottom of Target column (e.g., D12), calculate the sum of all targets using:
=SUM(D2:D11)
7. Find the Highest Sales
In a separate cell, find the highest sales value using:
=MAX(C2:C11)
8. Find the Lowest Sales
In a separate cell, find the lowest sales value using:
=MIN(C2:C11)
9. Count Achieved Products
Count how many products achieved their target:
=COUNTIF(E2:E11,"Achieved")
10. Count Not Achieved Products
Count how many products did not achieve their target:
=COUNTIF(E2:E11,"Not Achieved")
11. Calculate Percentage of Target Achieved
Add a new column G.
Calculate percentage sales compared to target in G2:
=C2/D2
Format this column as percentage. Copy down the formula.
12. Mark Below 80% Performance
Add a new column H.
Show "Yes" if percentage achieved (column G) is less than 80%, else "No".
Formula (H2):
=IF(G2<0.8,"Yes","No")
Copy down the formula.
13. Sort Products by Sales
Sort the table by Sales column in descending order (highest first).
14. Create a Bar Chart of Sales
Select Product names and Sales columns.
Insert a simple bar chart to visualize sales for each product.
15. Highlight Top 2 Sales
Use conditional formatting on Sales column to highlight top 2 values with a
special colour (e.g., light green).
Dataset/Problem Statement
Steps:
1. Calculate Sales Achieved
In column F (Sales Achieved), multiply Units Sold (column C) by Unit Price (column D)
to find total sales.
Formula (F2):
=C2*D2
Copy this formula down for all rows.
2. Add a Status column
In column G (Status), write a formula to check if Sales Achieved (column F) is greater
than or equal to Sales Target (column E).
If yes, display "Target Met", else "Target Not Met".
Formula (G2):
=IF(F2>=E2,"Target Met","Target Not Met")
Copy this formula down for all rows.
3. Calculate Difference
In column H (Difference), calculate how much sales exceeded or fell short of the target.
Formula (H2):
=F2-E2
Copy this formula down for all rows.
4. Calculate % Achieved
In column I (% Achieved), calculate Sales Achieved as a percentage of Sales Target.
Formula (I2):
=F2/E2
Format this column as Percentage. Copy down the formula.
5. Mark Below Target
In column J (Below Target), display "Yes" if percentage achieved (column I) is less
than 90%, else "No".
Formula (J2):
=IF(I2<0.9,"Yes","No")
Copy this formula down for all rows.
6. Highlight Sales Achieved
Select the Sales Achieved column (F2:F11). Apply conditional formatting:
Green fill if Sales Achieved ≥ Sales Target.
Red fill if Sales Achieved < Sales Target.
7. Sum of Units Sold
At the bottom of the Units Sold column (C12), calculate the total units sold.
Formula:
=SUM(C2:C11)
8. Highest Sales Achieved
In a separate cell, find the highest value in the Sales Achieved column (F2:F11).
Formula:
=MAX(F2:F11)
9. Lowest Sales Achieved
In a separate cell, find the lowest value in the Sales Achieved column (F2:F11).
Formula:
=MIN(F2:F11)
10. Count Target Met
Count how many items met their sales target.
Formula:
=COUNTIF(G2:G11,"Target Met")
11. Count Target Not Met
Count how many items did not meet their sales target.
Formula:
=COUNTIF(G2:G11,"Target Not Met")
12. Sort by Sales Achieved
Sort the entire table by Sales Achieved column (F) in descending order (highest first).
13. Create a Pie Chart
Create a pie chart showing total Sales Achieved (column F) by Category (column B).
14. Highlight Top 3 Sales Achieved
Use conditional formatting on Sales Achieved (F2:F11) to highlight the top 3 sales values
with a special fill colour.
15. Add Bonus Eligibility
In column K (Bonus Eligibility), mark "Yes" if Sales Achieved is at least 110% of Sales
Target, else "No".
Formula (K2):
=IF(F2>=E2*1.1,"Yes","No")
Copy this formula down for all rows.
