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.

Practice

1. What is the main purpose of the Query Editor interface in Excel?

easy

A. To protect worksheets with passwords

B. To write complex Excel formulas manually

C. To create charts and graphs from data

D. To clean and prepare data visually without writing formulas

Solution

1. Step 1: Understand Query Editor's role

   The Query Editor is designed to help users clean and prepare data visually.

2. Step 2: Compare options with Query Editor features

   Options B, C, and D describe other Excel features, not Query Editor's main purpose.

3. Final Answer:

   To clean and prepare data visually without writing formulas -> Option D

4. Quick Check:

   Query Editor = Visual data cleaning [OK]

Hint: Query Editor cleans data visually, no formulas needed [OK]

Common Mistakes:

• Confusing Query Editor with formula writing
• Thinking Query Editor creates charts
• Assuming Query Editor manages worksheet security

2. Which of the following actions can you perform directly in the Query Editor interface?

easy

A. Remove duplicate rows from a table

B. Write VBA macros to automate tasks

C. Insert pivot tables into the worksheet

D. Create conditional formatting rules

Solution

1. Step 1: Identify Query Editor capabilities

   Query Editor allows data cleaning tasks like removing duplicates.

2. Step 2: Eliminate unrelated options

   VBA macros, pivot tables, and conditional formatting are done outside Query Editor.

3. Final Answer:

   Remove duplicate rows from a table -> Option A

4. Quick Check:

   Query Editor = Remove duplicates [OK]

Hint: Removing duplicates is a common Query Editor task [OK]

Common Mistakes:

• Confusing Query Editor with VBA editor
• Thinking pivot tables are created in Query Editor
• Assuming formatting rules are set in Query Editor

3. You load a table into Query Editor and apply a filter to show only rows where the "Status" column equals "Complete". What will be the result after loading back to Excel?

medium

A. Only rows with "Status" = "Complete" are shown

B. The table is deleted from Excel

C. All rows remain, including those with other statuses

D. The filter is ignored and no changes happen

Solution

1. Step 1: Understand filtering in Query Editor

   Filtering in Query Editor limits data to matching rows.

2. Step 2: Effect of loading filtered data back

   Only filtered rows (Status = Complete) appear in Excel after loading.

3. Final Answer:

   Only rows with "Status" = "Complete" are shown -> Option A

4. Quick Check:

   Filter in Query Editor = Filtered data loaded [OK]

Hint: Filters in Query Editor limit data loaded back [OK]

Common Mistakes:

• Assuming all original rows remain
• Thinking filter deletes the table
• Believing filter is ignored on load

4. You tried to change a column's data type to "Date" in Query Editor but got an error. What is the most likely cause?

medium

A. You need to restart Excel before changing data types

B. You forgot to save the workbook before changing data type

C. The column contains text values that cannot convert to dates

D. Query Editor does not support changing data types

Solution

1. Step 1: Identify why data type change fails

   Changing to Date fails if values can't convert to dates.

2. Step 2: Rule out other options

   Saving workbook, restarting Excel, or Query Editor limitations are unrelated.

3. Final Answer:

   The column contains text values that cannot convert to dates -> Option C

4. Quick Check:

   Invalid data blocks type change [OK]

Hint: Check data values before changing type to Date [OK]

Common Mistakes:

• Blaming Excel save or restart
• Thinking Query Editor can't change types
• Ignoring invalid data values

5. You have two tables loaded into Query Editor: "Sales" and "Products". You want to combine them to show sales with product names. Which Query Editor feature should you use?

hard

A. Append Queries to stack tables vertically

B. Merge Queries to join tables on a common column

C. Filter Queries to remove unwanted rows

D. Group By to summarize sales totals

Solution

1. Step 1: Understand combining tables in Query Editor

   To join related data from two tables, use Merge Queries.

2. Step 2: Differentiate from other options

   Append stacks tables vertically, Filter removes rows, Group By summarizes data.

3. Final Answer:

   Merge Queries to join tables on a common column -> Option B

4. Quick Check:

   Combine tables by merging [OK]

Hint: Use Merge to join tables on matching columns [OK]

Common Mistakes:

• Using Append instead of Merge for joining
• Confusing Filter with combining tables
• Trying Group By to combine tables