Query Editor interface in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Query Editor Steps

In Excel's Query Editor, what does the 'Applied Steps' pane show?

A A list of all the transformations applied to the data in order

B A summary of the original data source details

C A preview of the final output data only

D A list of all Excel formulas used in the workbook

💡 Hint

Think about how Query Editor tracks changes step-by-step.

The 'Applied Steps' pane shows each transformation or action you perform on your data in order. This helps you track and edit your data cleaning process.

❓ Function Choice

intermediate

Choosing the Right Query Editor Function

Which Query Editor function would you use to remove duplicate rows from your data?

A Replace Values

B Split Column

C Group By

D Remove Duplicates

💡 Hint

Think about which action directly deletes repeated rows.

The 'Remove Duplicates' function deletes rows that have the same values in selected columns, keeping only unique rows.

📊 Formula Result

advanced

Result of a Query Editor Transformation

You have a table with a column 'Price' containing values: 10, 20, 30, 40. You apply a Query Editor step to multiply 'Price' by 1.1. What will be the new values in the 'Price' column?

A 10, 20, 30, 40

B 11, 22, 33, 44

C 1.1, 2.2, 3.3, 4.4

D 110, 220, 330, 440

💡 Hint

Multiplying by 1.1 increases each value by 10%.

Multiplying each price by 1.1 increases it by 10%, so 10 becomes 11, 20 becomes 22, and so on.

🎯 Scenario

advanced

Fixing a Data Type Issue in Query Editor

You imported a column of dates but Query Editor shows them as text. Which step should you add to fix this?

A Change Data Type to Date

B Remove Duplicates

C Split Column by Delimiter

D Replace Values

💡 Hint

Dates need to be recognized as date type, not text.

Changing the data type to Date converts text values into proper date format for calculations and sorting.

❓ data_analysis

expert

Analyzing Query Editor Applied Steps

You have these Applied Steps in Query Editor: Source -> Filter Rows (Price > 20) -> Remove Columns (Date) -> Sort Rows (Price ascending). How many rows will be shown if the original table had 5 rows with prices 10, 15, 25, 30, 40?

A 2

B 5

C 3

D 4

💡 Hint

Filter rows where Price is greater than 20.

Only prices 25, 30, and 40 are greater than 20, so 3 rows remain after filtering.