Connecting to Excel Workbooks and Named Ranges in Power Query: A Practical Foundation Guide

Introduction

Picture this: your company's sales team sends you a monthly Excel workbook with revenue data spread across multiple sheets — one sheet per region, plus a summary tab. Every month, you copy-paste that data into your analysis file, reformat the columns, clean up the headers, and rebuild your pivot tables from scratch. It takes two hours. Then someone updates last month's file, and you have to do it all over again.

This is the problem Power Query was built to solve. Power Query is a data transformation engine built into Excel and Power BI that lets you connect to data sources, shape that data into the form you need, and refresh everything automatically when the underlying data changes. Instead of copy-pasting, you build a reusable pipeline. Instead of two hours every month, you hit Refresh and grab a coffee.

In this lesson, you'll build that foundation. We'll walk through exactly how Power Query connects to Excel workbooks — both the file you're already working in and external workbooks sitting on your hard drive or a shared folder. Along the way, you'll learn about named ranges, one of Excel's most underused features, and discover why they're your best friend when building reliable data pipelines.

What you'll learn:

• What Power Query is and how it fits into your Excel workflow
• How to connect to tables and sheets in the current workbook
• How to connect to an external Excel workbook as a data source
• What named ranges are and how to create them
• How to use named ranges as stable, reliable connection points in Power Query

---

Prerequisites

You'll need Excel 2016 or later (Power Query is built in natively). If you're on Excel 2010 or 2013, you can download the free Power Query add-in from Microsoft. You should be comfortable with basic Excel navigation — opening files, creating sheets, entering data. No prior Power Query experience required.

---

What Is Power Query, Really?

Before we touch a menu, let's build a mental model. Power Query is essentially a middleman that sits between your raw data and your finished analysis. You tell it where to find data, what to keep, and how to reshape it. It remembers those instructions as a series of steps. Every time you click Refresh, it replays those steps from scratch against the current data.

Think of it like a recipe. You don't cook the dish by hand every time — you write down the recipe once, and anyone (or any machine) can reproduce it reliably. Power Query is your recipe card for data transformation.

When you connect to a data source in Power Query, you're working inside the Power Query Editor — a separate window with its own interface. Every action you take there gets recorded as a step in what's called a query. That query lives inside your workbook and can be refreshed on demand.

The output of a query is typically loaded into an Excel table on a worksheet, or into Excel's Data Model (which powers pivot tables and Power Pivot). For now, we'll focus on loading results to a worksheet table — that's the most common starting point.

---

Setting Up Our Practice Scenario

Let's create a realistic starting point so you can follow along. We'll build a small dataset representing quarterly sales data from a fictional company called Northbrook Supplies.

Open a brand new Excel workbook. Create three sheets by right-clicking the sheet tab area:

• Rename Sheet1 to Q1_Sales
• Add a new sheet and name it Q2_Sales
• Add another sheet and name it RegionCodes

On the Q1_Sales sheet, enter this data starting in cell A1:

SalesRep       | Region  | Product       | Revenue  | Units
-------------- | ------- | ------------- | -------- | -----
Jordan Mills   | East    | Widget Pro    | 14200    | 142
Casey Rivera   | West    | Widget Lite   | 8750     | 175
Alex Chen      | North   | Widget Pro    | 19600    | 196
Sam Patel      | South   | Widget Max    | 23100    | 154
Morgan Lee     | East    | Widget Lite   | 6300     | 126

On the Q2_Sales sheet, enter similar data with slightly different numbers — perhaps the same reps with updated revenue figures. The point is to have two sheets with the same structure.

On RegionCodes, enter:

RegionCode | RegionName  | Manager
---------- | ----------- | --------
East       | Eastern US  | Taylor Brooks
West       | Western US  | Jamie Nguyen
North      | Northern US | Drew Santos
South      | Southern US | Quinn Okafor

Save this workbook as NorthbrookSales.xlsx somewhere easy to find, like your Desktop.

---

Connecting to a Table in the Current Workbook

Power Query works best when your data is formatted as an Excel Table (not just a range of cells). An Excel Table is a structured data container — Excel knows it has headers, tracks its boundaries automatically, and gives it a name. This distinction matters because Power Query connects to named objects.

Converting Your Data to an Excel Table

Click anywhere inside the data on Q1_Sales. Then press Ctrl+T (or go to the Insert tab and click Table). Excel will detect the boundaries of your data and ask if your table has headers — it does, so make sure that checkbox is checked. Click OK.

You'll see the data get styled with alternating row colors (you can change the style later if you don't like it). More importantly, Excel has now named this table. To see and change the name, click anywhere inside the table, go to the Table Design tab (it appears in the ribbon when you're inside a table), and look at the Table Name box on the far left. Change the name from something like Table1 to Q1_SalesData. This name is how Power Query will refer to this table.

Do the same for the data on Q2_Sales, naming that table Q2_SalesData.

Opening Power Query to Connect to Your Table

With your cursor still somewhere in the Q1_SalesData table, go to the Data tab in the ribbon. Look for the Get & Transform Data section. Click From Table/Range.

The Power Query Editor window opens. You'll see your Q1 sales data displayed in a grid. On the left side is the Queries panel, showing a query named Q1_SalesData. On the right side is the Query Settings panel, showing the query name and — crucially — a list of Applied Steps.

Those Applied Steps are the recipe we talked about. Right now you'll see two steps already recorded: Source and Changed Type. Power Query automatically detects your table as the source and makes an educated guess about each column's data type.

Tip: The Changed Type step Power Query adds automatically can sometimes cause problems if your data types change month to month. For now, leave it — but know that you can delete or modify any step by right-clicking it in the Applied Steps panel.

For this lesson, we're not going to transform the data just yet — we're focused on the connection itself. Click Close & Load in the Home tab of the Power Query Editor. This loads the results into a new worksheet in your workbook as an Excel table.

Congratulations — you've built your first Power Query connection.

---

Connecting to an External Excel Workbook

Now let's tackle the more common real-world scenario: your data lives in a different workbook, perhaps one that gets updated by another team member or dropped into a shared folder on a schedule.

Create a new Excel workbook — this will be your analysis workbook. Save it somewhere convenient. Now we'll connect it to NorthbrookSales.xlsx.

Go to the Data tab, click Get Data, hover over From File, and click From Excel Workbook. A file browser window opens. Navigate to and select NorthbrookSales.xlsx, then click Import.

The Navigator Window

After a moment, you'll see the Navigator window. This is one of the most important windows in Power Query's workflow — take a moment to understand what you're looking at.

The left panel shows a tree structure of everything Power Query found inside NorthbrookSales.xlsx:

• The workbook itself at the top
• Individual sheets (Q1_Sales, Q2_Sales, RegionCodes)
• Any named Tables you created (Q1_SalesData, Q2_SalesData)

Click on Q1_SalesData in the left panel. You'll see a preview of the data on the right. This preview is Power Query showing you what it found — nothing has been loaded yet.

Notice that if you click on the sheet Q1_Sales instead of the table Q1_SalesData, the preview looks different. The sheet-level connection shows the raw contents of the entire sheet, including some metadata rows that Power Query adds. The table-level connection shows your clean, structured data directly.

This is an important distinction. Connecting to a sheet gives you everything on that sheet — including stray cells, formatting artifacts, and blank rows. Connecting to a named table gives you exactly the structured data you defined. Always prefer connecting to tables when you can.

Select Q1_SalesData and click Transform Data (not Load — we want to open the Editor first so we can inspect the connection). The Power Query Editor opens with the Q1 data loaded. Click Close & Load to bring it into your analysis workbook.

---

Understanding Named Ranges — and Why They Matter

So far, we've been connecting to Excel Tables. Tables are great, but there's another Excel feature that works beautifully with Power Query: named ranges.

A named range is exactly what it sounds like — a range of cells that you've given a name. Instead of referring to the range Q1_Sales!$A$1:$E$6, you call it Q1_SalesRange. The name travels with the cells even if you move them around (within limits), and critically, Power Query can connect to named ranges just like it connects to tables.

Here's when named ranges become especially valuable: imagine a lookup table on a hidden configuration sheet — something like a list of valid product codes, or a single cell containing a reporting date. You don't want to expose this as a full Excel Table, but you need Power Query to reliably find it every time. A named range is the perfect tool.

Creating a Named Range

Let's create a named range for the RegionCodes data in NorthbrookSales.xlsx. Open that workbook.

Click on the RegionCodes sheet. Select the data including headers — cells A1 through C5 (the three columns, five rows including the header).

Now go to the Formulas tab in the ribbon and click Name Manager, then click New. In the Name field, type RegionLookup. Leave the Scope set to Workbook (this means the name is accessible from anywhere in the workbook, not just one sheet). The Refers to field should already show =RegionCodes!$A$1:$C$5. Click OK, then Close.

Alternatively, the fastest way to create a named range: select your cells, then click directly into the Name Box — that's the small box to the left of the formula bar that normally shows the cell address like "A1". Type RegionLookup and press Enter. Done.

Save NorthbrookSales.xlsx.

Connecting to a Named Range in Power Query

Now switch back to your analysis workbook. Go to Data → Get Data → From File → From Excel Workbook and select NorthbrookSales.xlsx again.

In the Navigator window, look carefully at the left panel. You'll see your sheets and tables — but now you should also see RegionLookup listed. Power Query recognizes named ranges automatically. Click on RegionLookup to preview it.

Watch out: Power Query sometimes displays named ranges with a slightly different icon than tables. Both are valid connection points — named ranges are shown with what looks like a small grid icon without the distinctive table styling. Don't let that confuse you.

Select RegionLookup and click Transform Data. You'll see the three columns of region data. Notice that Power Query automatically named your query RegionLookup — it inherits the name from the named range. Click Close & Load.

Now you have two queries in your analysis workbook, both pulling from the external NorthbrookSales.xlsx file: one for Q1 sales data, one for the region lookup table.

---

Refreshing Connections and Why File Paths Matter

One of Power Query's most powerful features is refresh — but it comes with a gotcha that trips up every beginner.

When you connect to an external file, Power Query stores the full file path to that workbook. If you move NorthbrookSales.xlsx from your Desktop to a project folder, Power Query will throw an error on refresh because it's looking in the wrong place.

You can see and change the file path by going to Data → Queries & Connections in your analysis workbook. Right-click your query and choose Edit. In the Power Query Editor, look at the very first step in Applied Steps, called Source. Click on the small gear icon next to Source. A dialog box appears where you can update the file path.

Best practice for real-world work: Store your source data files and your analysis workbook in the same folder, and reference the source file using a relative path or a path you won't change. Even better — in a team environment, put source files in a shared network folder or SharePoint, and connect to that location. Everyone's refreshes will always point to the same up-to-date file.

To refresh all queries in your workbook at once, go to Data → Refresh All. To refresh a single query, right-click it in the Queries & Connections panel and choose Refresh.

---

Hands-On Exercise

Now it's your turn to build something from scratch without step-by-step hand-holding.

Scenario: Your manager has given you a new Excel file called BudgetTargets.xlsx. It contains one sheet called Targets with these columns: Region, Q1_Target, Q2_Target, Q3_Target, Q4_Target. The finance team updates this file each quarter.

Your tasks:

1. Create BudgetTargets.xlsx with at least five rows of realistic data (make up the numbers).
2. On the Targets sheet, convert the data to an Excel Table named BudgetTable.
3. Also create a named range called Q1Targets that covers only the Region and Q1_Target columns (columns A and B, including the header row).
4. Open your analysis workbook and connect to BudgetTargets.xlsx using the Navigator. Load BudgetTable as one query and Q1Targets as a second query.
5. Make a small change to a number in BudgetTargets.xlsx, save it, then go back to your analysis workbook and refresh both queries. Confirm the updated value appears.

If you get a file path error during refresh, practice finding and fixing it using the method described in the previous section.

---

Common Mistakes & Troubleshooting

"I don't see my named range in the Navigator." This usually means either the named range scope was set to a specific sheet rather than the whole workbook, or the name contains spaces or special characters that confuse Power Query. Named range names must start with a letter or underscore, can't contain spaces, and can't look like a cell address (like Q1 on its own). Try renaming it using the Name Manager (Formulas → Name Manager).

"My data has extra blank rows or weird columns when I connect to a sheet." This happens when you connect to a sheet instead of a table or named range. The sheet-level connection captures everything, including Excel's metadata. Solution: go back to the source workbook, convert your data to a proper Excel Table, and reconnect to the table instead of the sheet.

"After refresh, my column types changed and downstream formulas broke." Power Query's auto-detected Changed Type step applies types based on the first few rows of data it sees at query-build time. If your data changes structure, this can misfire. In the Power Query Editor, you can right-click the Changed Type step in Applied Steps and delete it, then manually add a type-conversion step with explicit settings.

"Power Query says it can't find the file." The file was moved, renamed, or the path contains a drive letter that's different on another machine. Fix this by editing the Source step in the query and updating the file path. If you're sharing work across machines, consider using a UNC path (like \\ServerName\SharedFolder\NorthbrookSales.xlsx) instead of a local drive letter.

"I accidentally loaded my query to a new workbook instead of loading it to the existing one." In the Close & Load button, there's a small dropdown arrow. Click the arrow and choose Close & Load To… instead. This opens a dialog that lets you choose exactly where the results go — a new sheet, an existing sheet, or just the Data Model without a visible table.

---

Summary & Next Steps

Let's take stock of what you've built in this lesson.

You now understand that Power Query is a reusable, refreshable data pipeline — not a one-time copy-paste. You know how to convert raw cell ranges into proper Excel Tables, which are the stable, named connection points that Power Query prefers. You can connect to data inside the current workbook and in external workbooks. You understand what named ranges are, how to create them, and why they're useful as lightweight, flexible connection points for lookup data and configuration values. And you know how to refresh connections and fix the most common file path error.

These skills form the foundation for everything else in Power Query. The connection step is always the first step — and now you can do it confidently.

Where to go next:

• Shaping and transforming data in the Power Query Editor — now that you can connect to data, learn how to filter rows, rename columns, split text, and perform calculations inside the editor
• Combining multiple queries with Append and Merge — once you're loading Q1 and Q2 sales data as separate queries, the natural next step is stacking them into a single unified table using Append Queries
• Working with dynamic file paths — learn how to store a folder path in a named cell in Excel and reference it inside Power Query, so one change updates all your connections simultaneously
• Connecting to other data sources — the connection skills you've built here transfer directly to CSV files, databases, SharePoint lists, and web sources