Excel on the web - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst working remotely using Excel on the web.

📋 Request: Your manager wants a monthly sales summary report that can be accessed and updated online by the team.

📊 Data: You have a sales data table with columns: Date, Salesperson, Region, Product, Units Sold, and Unit Price.

🎯 Deliverable: Create an online Excel workbook with a summary sheet showing total sales per month and region, using formulas that update automatically when data changes.

Sample Data

Date	Salesperson	Region	Product	Units Sold	Unit Price
2024-01-05	Alice	North	Widget	10	15
2024-01-15	Bob	South	Gadget	5	20
2024-02-10	Charlie	East	Widget	8	15
2024-02-20	Alice	North	Gadget	7	20
2024-03-05	Bob	South	Widget	12	15
2024-03-15	Charlie	East	Gadget	6	20
2024-03-25	Alice	North	Widget	9	15
2024-04-10	Bob	South	Gadget	4	20

1

Step 1: Create a new sheet named 'Summary' in the Excel workbook on the web.

No formula needed.

Expected Result

A blank 'Summary' sheet is ready for the report.

2

Step 2: In the 'Summary' sheet, list the months in column A starting from A2 as '2024-01', '2024-02', '2024-03', '2024-04'.

Manually enter the month labels as text.

Expected Result

Column A has month labels for January to April 2024.

3

Step 3: In row 1 starting from B1, list the regions: 'North', 'South', 'East'.

Manually enter region names in B1, C1, D1.

Expected Result

Row 1 has region names as headers.

4

Step 4: Calculate total sales for each month and region using SUMPRODUCT with conditions on Date and Region.

In cell B2 enter: =SUMPRODUCT((TEXT(SalesData!$A$2:$A$9,"yyyy-mm")=$A2)*(SalesData!$C$2:$C$9=B$1)*(SalesData!$E$2:$E$9*SalesData!$F$2:$F$9))

Expected Result

Cell B2 shows total sales for North region in January 2024, which is 150.

5

Step 5: Copy the formula in B2 across to D2 and down to row 5 to fill all month-region combinations.

Use fill handle or copy-paste to fill B2:D5 with the formula.

Expected Result

Summary table shows total sales per month and region, updating automatically with data changes.

6

Step 6: Format the summary table with currency format for sales values and bold headers for clarity.

Select data range and apply currency number format and bold font to headers.

Expected Result

Summary table is easy to read with currency formatting and bold headers.

Final Result

       |  North  |  South  |  East  
-------------------------------------
2024-01|   150   |   100   |    0   
2024-02|   140   |    0    |  120   
2024-03|   195   |  180    |  120   
2024-04|    0    |   80    |    0   

✓North region had steady sales in January, February, and March.

✓South region sales peaked in March.

✓East region sales occurred mainly in February and March.

✓The summary updates automatically when sales data changes online.

Bonus Challenge

Add a dynamic chart on the 'Summary' sheet that updates automatically when new data is added.

Show Hint

Use Excel on the web's Insert > Chart feature and select the summary table as the data source.