Query Editor interface in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a clean and organized sales report by product category and month, showing total sales and average sales per transaction.

📊 Data: You have raw sales data with columns: Date, Product, Category, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create a clean table using Excel's Query Editor that groups sales by Category and Month, showing total sales and average sales per transaction.

Sample Data

Date	Product	Category	Quantity Sold	Sales Amount
2024-01-05	Shirt	Clothing	3	75
2024-01-15	Jeans	Clothing	2	100
2024-02-10	Blender	Appliances	1	150
2024-02-20	Toaster	Appliances	4	80
2024-03-05	Novel	Books	5	50
2024-03-15	Notebook	Books	10	30
2024-01-25	T-shirt	Clothing	6	120
2024-02-28	Microwave	Appliances	1	200

1

Step 1: Load the sales data into Excel and open the Query Editor by selecting the data and choosing 'From Table/Range' in the Data tab.

No formula needed; just load data into Query Editor.

Expected Result

The data appears in the Query Editor interface ready for transformation.

2

Step 2: Add a new column to extract the month from the Date column.

Add Column > Custom Column with formula: Date.MonthName([Date])

Expected Result

A new column named 'Month' shows the month name for each row, e.g., 'January', 'February'.

3

Step 3: Remove unnecessary columns to keep only Category, Month, and Sales Amount.

Select columns Category, Month, Sales Amount; right-click > Remove Other Columns.

Expected Result

Only the columns Category, Month, and Sales Amount remain.

4

Step 4: Group the data by Category and Month to calculate total sales and average sales per transaction.

Home > Group By: Group by Category and Month; Aggregations: Sum of Sales Amount as Total Sales, Average of Sales Amount as Average Sales

Expected Result

A grouped table showing each Category and Month with Total Sales and Average Sales columns.

5

Step 5: Load the transformed data back into Excel as a new table.

Click 'Close & Load' in Query Editor.

Expected Result

A new table appears in Excel with grouped sales data by Category and Month.

Final Result

Category   | Month    | Total Sales | Average Sales
--------------------------------------------------
Clothing   | January  | 295         | 98.33
Appliances | February | 430         | 143.33
Books      | March    | 80          | 40.00

✓Clothing had the highest total sales in January with $295.

✓Appliances sales peaked in February with $430 total.

✓Books had consistent sales in March with an average of $40 per transaction.

Bonus Challenge

Add a column in Query Editor to calculate the sales per quantity sold (Sales Amount divided by Quantity Sold) and include it in the grouped summary as average sales per item.

Show Hint

Create a custom column with formula [Sales Amount] / [Quantity Sold] before grouping, then include Average of this new column in the Group By step.