Using Microsoft Excel is something that many people have had to do over the years, as it has been a requirement for many jobs or has been a useful tool for people at home. Excel can provide many functions for users, as it can collect and organize data in spreadsheets. Learning how to use Excel can be tricky, especially if you only use it occasionally.

These were the circumstances I faced, as I rarely ever needed to use Excel in my personal or professional life. But, eventually, I got a job that required me to look through a lot of data at different points of the month. I had to build large spreadsheets and incorporate data from different sources to tally up for my boss. It became so tedious trying to cross-reference everything by hand. When the data didn’t pull over correctly, I would find duplicates, which drove me nuts. Then, I learned about Power Query and things started looking less complicated.

Power Query is simple and built into Excel

There isn’t much in terms of ways it can’t work

If you’re bringing a ton of data into a spreadsheet, chances are it’s not going to be exactly organized the way you want right away. Power Query makes this simpler by loading data from different sources and letting you edit that data all in one place.

You can manually go about tallying and organizing your data, but it can take many steps, clicks, and functions. For Power Query, you don’t have to write any code.

This works by pulling data from different places, many of which are options within Excel. The most common would be pulling data from different Excel spreadsheets into one.

For work, I have to pull info from Trello and put it into Excel. I’m able to do this by exporting the data via a CSV file and then loading it into Excel. It’s not difficult at all to do this, as you can just click on Print and Export and then Export as CSV in Trello.

Once I have that option, I can use that file in Excel as part of Power Query. I typically have to combine it with data from an Excel spreadsheet, which makes for an actually easy transition.

Setting up your Power Query makes sense

Once you have the steps down, it takes no time

There’s so much you can do to clean your data. But first, setting up your Power Query takes just a little bit of time. Of course, it depends on how much data you’re importing to see how long it might actually take. But, in theory, once you get good at this, you can import a large amount of numbers and get on with it quickly enough.

You first need to select the Data tab and then select Get Data. From there, you can choose where you’re getting the data from the Get & Transform ribbon group. For me, I usually click Get Data > From File > From Text/CSV. But plenty of people will opt for From Excel Worksheet.

Excel then prompts you to choose where you’re importing the file from. Once you’ve done that, clicking Import will bring the data into the spreadsheet. You can select all of your data sets at the same time, so you don’t have to import everything one by one.

You get to see a preview of what you’re uploading, so you can confirm it’s exactly what you want to bring into your worksheet. If you click Transform Data, it will open it in the Power Query editor.

From there, your data will be entered into your Excel table. You can go into the Query Settings editor pane on the right side to adjust your data more.

Best ways to clean data through Power Query

It’s powerful and it makes sense

There are all kinds of features you can choose to use within Power Query. It lets you opt for sorting, deleting, filtering, and highlighting, to name a few. You can also remove and replace errors, which is a huge advantage, especially if you’re bringing data from different sources and they happen to overlap with functions.

Combining functions from one worksheet to another is bound to cause errors within the cells, so being able to go into the cells that have errors without having to search them out manually is helpful. One of the ideal options is to click Append in the editor and combine different columns so you can make them into one. This can help shrink your table and simplify it.

Splitting cells, in case cells populate with more information than is needed per column, is another good command to use for Power Query. I’ve had to use this when a person’s name and the project they are working on came together in one cell in a column.

In the editor within Transform, highlight the column that you want to split by a delimiter and click Split Column > By Delimiter. From there, you can select what the delimiter is by choosing it from a dropdown or typing it in with a Custom choice. Close & Load will help you split those cells and make them into different columns.

Power Query goes out of its way to record what it has done, so you can see the steps you took to get to where you are. With Applied Steps, which shows up within the Query Settings pane, it will walk you through what changes you’ve made. For example, if you change the name of a column, it will say Renamed columns. These come up automatically and you can rename the Applied Steps if you want to have a more personalized version.

The great thing about Applied Steps is that you can then just click on the icon for them, rather than having to do the function in the spreadsheet, and it will repeat the process for whatever you’re highlighting. It’s a remembered process that Excel logs for your worksheet that you can use again and again. You can build a process of steps as well, so the worksheet can follow a pattern if you are loading multiple sheets and then need to do the same kind of organization to each of them.

Power Query helps you sharpen your data

There is so much happening in Excel that it can be hard to keep track of it. Luckily, Power Query can remember what you’ve done and help you repeat it when you want to do it again. This feature helps you sort through data in a number of ways while combining and merging data from different sources. It’s an ideal way to utilize your Excel functions by letting you focus less time on manually cleaning the data.

OS

Windows, macOS

Supported Desktop Browsers

All via web app

Developer(s)

Microsoft

Free trial

One month

Price model

Subscription

iOS compatible

Yes