Standard Excel tools can usually get the job done, but they often lack the resilience and flexibility needed for complex data and transformation tasks. Power Query doesn’t just perform these tasks faster—it does them better. Here are five actions where you should ditch Excel’s ribbon tools for the Power Query Editor.
Before you start: Loading your data
Select any cell within your dataset, and in the Data tab, click “From Table/Range.”
Once you’ve finished implementing your Power Query steps, click “Close & Load” in the Home tab to return the data to a new worksheet.
Use conditional columns (instead of nested IF statements)
Standard Excel logic often relies on long, manual strings of text that are difficult to build and even harder to fix.
The scenario
You need to categorize sales into “small,” “medium,” and “large” based on value.
Usually, you’d add a new column and use a nested IF formula:
=IF([@Sales]>1000,”Large”,IF([@Sales]>500,”Medium”,”Small”))
However, one misplaced comma can break the entire string, and auditing the logic in a long formula bar is a headache.
Related
Stop writing nested IFs and IFS formulas in Excel: Use SWITCH instead
Write cleaner Excel logic by eliminating repetitive and lengthy formulas.
How to use conditional columns
After loading your data into the Power Query Editor, in the Add Column tab, click “Conditional Column.”
In the dialog box, name your new column, and set your logic using the drop-down menus. In this case, the first rule is to return the word “Large” if a value in the Sales column is greater than 1,000.
Unlike in standard Excel formulas, you don’t need to put your text in double quotes here.
Then, click “Add Clause” and repeat the process for the mid-range condition.
Finally, enter the Else value as Small and click “OK.”
The new conditional column then appears, and you’re ready to close and load the new table into your worksheet.
Why conditional columns are better than nested IF statements
This method creates a visual applied step that anyone can audit at a glance. You don’t have to write any code, and if your thresholds change, you simply click the gear icon in the Applied Steps pane to update the rules without touching a single parenthesis.
Merge queries (instead of using lookups)
Linking two different datasets is a core Excel task, but relying on cell-based formulas often leads to broken references and slow performance.
The scenario
You have a Sales table and a Product Master list located in separate worksheets, and you need to pull the unit price from the Master list into your Sales table.
While an XLOOKUP can do this, it becomes a liability if the source file is moved, the column headers are deleted, or the dataset grows significantly.
How to merge queries
The first step is to store the first table in Power Query’s memory. To do this, load the table into the Power Query Editor, and under the Close & Load arrow, click “Close & Load To.”
Then, in the dialog, select “Only Create Connection,” and click “OK.”
At this point, the “Queries and Connections” pane opens with the connection you just created.
Now, repeat this process for the other table so that both connections are visible in that pane.
Right-click the Sales table in this pane, and click “Edit” to return to the Editor.
Now, click “Merge Queries” in the Home tab, choose the Product Master table from the drop-down list, select the matching SKU column in both previews, and click “OK.”
Next, click the “Expand” icon in the header of the new column, check the column you want to bring over from one table to the other (in this case, “Price”), and click “OK.”
When you click “Close & Load,” the Sales query is still stored as a connection only in the Queries and Connections pane. So, right-click it, click “Load To,” select “Table,” and choose “New worksheet.”
When you click “OK,” the merged table is loaded into a new worksheet.
Related
Need to Stack Data from Multiple Excel Sheets? Use Power Query Append
Don’t combine Excel tables manually.
Why merging queries is better
Lookup functions are essentially cell-level solutions, but merging queries in Power Query is a table-level solution. Here’s why that matters in complex workbooks:
- It’s not destructive: Merging queries creates a new, transformed version while keeping your source tables untouched.
- It handles multiple results: If you have 10 different pieces of data to pull from a master list, in Power Query, you merge once and simply check the boxes for all 10 columns you want to expand.
- It performs more seamlessly: Power Query only runs when you click “Refresh,” making it much faster for datasets with thousands of rows.
Standard Excel tools make it difficult to transform wide data into the tall format required for PivotTables and modern analysis.
The scenario
You receive a report with categories in the first column, but the months are separate headers. To use this data in a PivotTable, you need those months in a single vertical column.
In regular Excel, you’d have to manually copy, paste, and transpose each month—a tedious process that you have to repeat every time new data arrives.
How to use the unpivot tool
After loading your wide table into the Power Query Editor, select the columns you don’t want to flip (holding Ctrl as you click the headers if there’s more than one), right-click one of those column headers, and click “Unpivot Other Columns.”
Power Query instantly transforms the data, creating an attribute column and a value column.
Double-click the headers to rename them to something like Month and Revenue, and make sure they are correctly formatted by clicking the number format icons next to the column names.
The updated dataset is now ready to be closed and loaded back into your workbook.
Why unpivoting is better
The unpivot tool is dynamic and non-destructive. In standard Excel, if you added a new column, you would need to restructure the worksheet manually. However, Power Query automatically detects any new month headers added to the source data and flips them into your vertical list during the next refresh.
Related
5 Ways to Improve Data Structure in Microsoft Excel
Getting things organized in your spreadsheets is worth the effort.
Use “from folder” connections (instead of manual copy-pasting)
Manually copying data from multiple files is one of the most common causes of human error in Excel spreadsheets.
The scenario
Every Monday, when you receive the latest regional sales report, you open the file, copy the data, and paste it at the bottom of the master worksheet. However, this is a slow process, and it’s easy to accidentally skip a row or double-paste some data.
How to import data from a folder
First, ensure all your source files are in a single, dedicated folder on your computer. To keep the process clean, ensure this folder contains only the files you intend to merge.
Then, in Excel, click Data > Get Data > From File > From Folder.
Browse to your dedicated folder, and click “Open.”
Expand the “Combine” drop-down menu and select “Combine & Transform Data.”
Select a sample file or sheet to use as the template and click “OK.” Ensure all workbooks use the same worksheet tab names and column headers, as the sample file dictates the structure for the entire merge.
Once you’re happy with the outcome, click “Close & Load.”
Why importing data from a folder is better
Combining Excel workbooks using Power Query means you never have to open a regional file again—instead, next week, you can simply drop the new reports into the folder and click “Refresh” in your master Excel file.
There are many ways to split columns using the standard Excel interface, but they come with problematic baggage that can cause problems down the line.
The scenario
You have a column of full names that you need to split into first names and last names.
In the standard Excel ribbon, you might use the Text to Columns wizard, but the results are static, meaning if you add new names or update an existing one, the split won’t update. Alternatively, you could use the TEXTSPLIT function, but this creates inconsistent columns if a name contains a middle initial.
How to split columns
After loading your data into the Power Query Editor, right-click the header of the column you want to split, and in the Split Column menu, click “By Delimiter.”
Choose “Space” as your delimiter.
If you want to handle middle names consistently, select “Right-most delimiter” to ensure you only ever create two columns.
When you’re done, click “OK,” rename the columns, and then close the Editor and load the resultant dataset into your Excel workbook.
Any null values will show as blank cells in the final Excel table. If you’d prefer to fix this within the Power Query Editor, right-click the column header and select “Replace Values” to fill it with a zero or N/A automatically.
Why splitting columns in the Power Query editor steps is better
Unlike the ribbon tool, Power Query’s column split updates whenever you click “Refresh,” handling new rows of data instantly. Also, it’s more structurally stable than the TEXTSPLIT function, creating actual table columns that don’t rely on empty “spill” space. Finally, if you ever need to change the logic, you can simply click the gear icon in the Applied Steps pane rather than rewriting formulas or re-running a wizard.
Once you’ve mastered these five actions, you can further streamline your processes by learning more essential Power Query commands for data transformation to handle even the most complex datasets with a single click.
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
