Don’t forget to share this post

Knowing your way around Excel formulae is a helpful skill, especially if you work with large datasets and multiple worksheets at once. In another article, we showed you the two basic procedures to transfer data from one Excel worksheet to another automatically, as a simple alternative to manual copy/paste. Although the Copy and Paste Link and Worksheet Reference functions can make your life easier, Excel has integrated features that require even less effort.

In this article, we’ll show you how to use Power Query in Excel to import data from various sources, combine and merge tables according to your preferences; all without having to learn any specific code.

What is Excel Power Query?

Power Query is a Microsoft Excel tool that simplifies the two main tasks of any spreadsheet user: importing and sorting data. Before showing all the applications, let’s briefly explore how they work.

How do Power Queries work in Excel?

Power Queries are commands set up in Excel that can be broken down into the following steps:

  • Import or connection: You can connect data stored in the cloud, specific service, or local environment. Moreover, you can connect from multiple sources to combine all of your data in one place.
  • Editing or transformation: You can change the data according to your preferences, as these will not affect the original source.
  • Automate or load: After completing your query, you can then load it into a worksheet or Data Model and update it regularly, without the need to repeat that same process.

So, is Power Query easy to use? Yes. Power Query doesn’t require knowledge of coding or programming and since 2016, it has become an integrated tool in Excel, so there is no longer a need for third-party installation. Like any automation tool, its most attractive feature is the ability to achieve the same results in half the time.

How to use Excel Power Query?

Before showing how you can use Power Query in Excel, note that it only works in the following Excel versions:

  • Power Query for Excel 2010 and 2013: To use it, you need this Microsoft add-in. Unfortunately, there are no add-ins available for macOS and you can only refresh the existing query, but not create a new one or edit.
  • Power Query for Excel 2016, 2019, or 365: Depending on the version, Power Query will appear under as “Get Data & Transform” or “Get Data” button. This integrated feature allows you to create and edit queries in Windows and Mac.

This article will show you how to use Power Query for Excel 365 on Mac.

How to import data using Excel Power Query?

Importing your data using Power Query is simple, as Excel provides a great variety of data connections that are accessible from your toolbar. Now that you know what Power Query is and the type of data format it can import, let’s look at how you can import data from an Excel spreadsheet.

The Beginners Guide to Excel Version Control
The Beginner's Guide to Excel Version Control

Discover what Excel version control is, the version control features Excel has to offer, and how to use them to share, merge, and review Excel changes

READ MORE

How to import data from a single sheet in Excel Power Query?

Imagine that you want to analyze the car sales made by an employee in 2021. Using Power Query, you can import the data without affecting the source file at all.

  1. 1. Open a new Excel spreadsheet and go to Data > Get Data (Power Query). As explained, there are several options depending on the source type. Here, I will select “Get Data (Power Query)” to import a spreadsheet stored locally.
Excel Power Query Get Data
Excel Power Query - Get Data
  1. 2. Select “Excel workbook”.
Excel Power Query Select Data source
Excel Power Query - Select Data source
  1. 3. Once you have selected your preferred data source, choose your “Connection settings”. Here, I can select the local file by clicking on “Browse”.
Excel Power Query Connection settings
Excel Power Query - Connection settings
  1. 4. For a quicker search, type the source filename in the search bar. Here, I’ve typed ‘SALES’. Select the file, click “Get Data”, and then the “Next” button located on the bottom right-hand corner of the original window.
Excel Power Query Select file and Get Data
Excel Power Query - Select file and Get Data
  1. 5. You should now be able to see the data preview window. If you are using Windows, then you can transform or edit your data at this point. However, Mac will only allow you to load data and then apply any changes needed.
Excel Power Query Load data
Excel Power Query - Load data

Once you load the data, Excel automatically saves the query in your system. Every time you want to import or format (for Windows), click the “Refresh” icon in the top-right corner to repeat the steps. This will eliminate repetitive manual work and unnecessary human ‌errors.

How to import data from multiple sheets in Excel Power Query?

As mentioned before, Power Query can import data from multiple files at the same time. This is very useful when you don’t want to import all the data from a larger file, and would like to import specific sheets. Here is how you can get data from multiple sheets.

  1. 1. Repeat steps 1-3 from the previous section.
  2. 2. Search for your general folder rather than a specific sheet. Here, I’ll search for my ‘SALES2021’ file. Click on “Get Data” and then “Next” located in the bottom right-hand corner.
Excel Power Query Get data from Multiple Sheets
Excel Power Query - Get data from Multiple Sheets
  1. 3. As you can see, you can now select the specific sheets of data you’d like to import. I have selected two sheets corresponding to the sales employees I want to compare. Once selected, click “Load”.
Excel Power Query Load data from Multiple Sheets
Excel Power Query - Load data from Multiple Sheets
  1. 4. You should now have a new Excel workbook with the two loaded sheets. Below is an example of how Excel has converted my data into a table, each in a separate tab.
Excel Power Query Table format

In Windows, Power Query also gives you the option to load to a pivot table, pivot chart or simply create a connection for the ‌query. This last option does not import data, but it’s still a useful way of speeding up any data transformation process.

Now that you know how to load data from a single sheet and multiple sheets, let’s see how to edit the query in Excel.

How to Combine Multiple Excel Files Into One

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE
How to Combine Multiple Excel Files Into One

How to edit External Data Range Properties in Excel?

A great way to edit future imports using Power Query is to change the settings for the external data range.

  1. 1. Go to Data > Properties to edit any of the following elements.
Excel Power Query External Data Range Properties
Excel Power Query - External Data Range Properties

Below are summaries of the three most useful properties to consider for future queries:

  • Query Definition: Leave the “Save query definition” box ticked and change the name to something that will be easier to remember. Don’t forget that the point of Power Query is to reduce the number of steps in data transformation processes.
  • Refresh Control: What’s most important here is the “Refresh data when opening the file” option. If you choose this option, then any change you make to the original file will automatically affect the destination file.
  • Data Formatting and Layout: If you select “Insert cells for new data, delete unused cells”, any new rows of data added to your source file will be transferred and formatted to coincide with the rest of your Power Query table, and any unused cells will be deleted.

It’s crucial to understand how Excel can transform your data automatically, ‌to boost productivity. Although the most basic features of Power Query in Excel work in a similar way in Windows and Mac, further automation features may vary to a greater extent.

To avoid version incompatibility and create your own automated spreadsheet workflow regardless of your operating system, check out the benefits of using Layer as an alternative to Excel Power Query.

How to remove a connection in Excel?

If you would like to remove a Power Query connection in Excel, follow the steps below:

  1. 1. Go to Data > Queries & Connections.
Excel Power Query Queries Connections
  1. 2. Click on the query you’d like to delete and select “Remove”.
Excel Power Query Workbook Queries and Connections
Excel Power Query - Workbook Queries and Connections
  1. 3. Excel will prompt you with an alert message to make sure you wish to proceed.
Excel Power Query Remove Query Alert message

If you do not use a workflow automation tool, Power Query is a great way to not only automate updates, edits, or repetitive tasks but also to track any modifications made to the file.

How to automate Google Sheets tasks?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

In this article, we have shown you how to use Excel Power Query at a basic level in Excel 2016, 2019, and Office 365 for Mac and Windows. It’s a powerful query tool that can import a variety of data sources in terms of file type and storage systems. In a few simple steps, you can automate your most common repetitive tasks, avoid future manual errors, and increase the efficiency of your business.

If you want to learn more advanced methods to automate your workflow in Excel, read our article on How To Use Macros in Excel To Automate Tasks.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Mar 15 2022, Updated Sep 23 2022