Query Editor interface in Excel - Step-by-Step Guide

Introduction

The Query Editor interface in Excel helps you clean and shape your data before using it in your workbook. It solves the problem of messy or unorganized data by letting you filter, transform, and combine data easily.

When you receive a large list of sales data with extra columns you don't need

When you want to combine data from multiple tables into one clean table

When your data has inconsistent formats like dates or numbers that need fixing

When you want to remove duplicate rows from a list of customer names

When you need to filter out records that don't meet certain criteria before analysis

Steps

Step 1: Click

- Data tab on the Excel ribbon

The Data tab options appear below the ribbon

💡 Look for the 'Get & Transform Data' group

Step 2: Click

- Get Data button in the 'Get & Transform Data' group

A menu with data source options appears

Step 3: Select

- From File > From Workbook (or another source you want)

A file picker window opens to choose your data file

Step 4: Choose

- Your data file and click Import

The Navigator window shows available tables or sheets

Step 5: Click

- Transform Data button in the Navigator window

The Query Editor interface opens with your data loaded

Step 6: Use

- Query Editor ribbon and panels to filter, remove columns, change data types, or add steps

Your data preview updates to show changes immediately

Step 7: Click

- Close & Load button on the Home tab of Query Editor

The cleaned data loads into a new worksheet in Excel

Before vs After

Before

A table with 100 rows including extra columns like 'Notes' and inconsistent date formats

After

A clean table with 80 rows, only needed columns, and dates formatted correctly

Settings Reference

Applied Steps pane

📍 Right side of Query Editor window

Shows each change made to data so you can track or undo them

Default: Empty when new query starts

Data Type selector

📍 Column header dropdown in Query Editor

Sets the correct data type for each column

Default: Detected automatically

Filter dropdown

📍 Column header in Query Editor

Filters rows based on criteria you choose

Default: All values shown

Remove Columns

📍 Home tab in Query Editor ribbon

Deletes unwanted columns from your data

Default: No columns removed

Common Mistakes

Closing Query Editor without clicking 'Close & Load'

Changes are not saved or loaded back into Excel

Always click 'Close & Load' to apply changes to your workbook

Changing data types after loading data into Excel instead of in Query Editor

Data may not refresh correctly when source updates

Set data types inside Query Editor before loading data

Deleting columns directly in Excel instead of in Query Editor

Data refresh will bring back deleted columns

Remove unwanted columns inside Query Editor to keep changes permanent

Summary

Query Editor lets you clean and shape data before using it in Excel

Use it to filter, remove columns, fix data types, and combine data

Remember to click 'Close & Load' to save and load your cleaned data