How to Connect Data in Excel

Stop manually copying and pasting information into your spreadsheets. If you're exporting the same CSVs every week and piecing reports together by hand, there’s a much more efficient way to work. Excel has powerful, built-in tools that let you connect directly to your data sources, creating a live link that you can refresh with a single click. This article will show you how to use Excel's Get & Transform (Power Query) feature to connect data from other Excel files, CSVs, and even websites, turning your static reports into dynamic dashboards.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Connect Data Instead of Copy-Pasting?

While copy-paste is quick for a one-off task, it's a terrible process for recurring reports. Manually moving data is tedious and, more importantly, prone to human error. A slight slip in a copy-paste selection can lead to missing rows, incorrect calculations, and flawed conclusions. Connecting your data source directly solves several problems at once:

• It Saves Time: Once a connection is set up, you never have to repeat the import process. A weekly report that took you 30 minutes of downloading, copying, and formatting can now be updated in seconds.
• It Reduces Errors: By automating the data import process, you eliminate the risk of manual copy-paste mistakes. You're always pulling the complete, original data set, ensuring your analysis is built on a reliable foundation.
• Your Data Stays Current: Connected data can be refreshed anytime. With one click on the "Refresh All" button, all your charts, pivot tables, and reports will update with the latest information from your source files.
• It Creates a Single Source of Truth: When your analysis file is directly linked to your source data, you ensure consistency. There are no questions about which version of the CSV was used or if someone pasted the wrong numbers.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Introducing Get & Transform (Power Query)

The magic behind Excel's data connection capabilities is a tool called Power Query. In recent Excel versions (Excel 2016, a.k.a. Microsoft 365), it’s been renamed and integrated directly onto the "Data" tab under the "Get & Transform Data" section. If you're using an older version like Excel 2010 or 2013, you can install it as a free add-in from Microsoft.

Think of Power Query as a dedicated workshop for your data. Its job is to:

1. Connect: Reach out to an external source (another spreadsheet, a folder of CSVs, a database, a website) and establish a link.
2. Transform: Clean and reshape the data before it ever enters your worksheet. This can include removing blank rows, splitting columns, filtering out unnecessary information, or unpivoting data.
3. Load: Place the clean, formatted data into an Excel sheet as a table or feed it directly into a PivotTable.

Working this way is a game-changer. Your raw data files remain untouched while Power Query provides a repeatable set of instructions for preparing the data for analysis.

Step-by-Step: Connecting to External Data Sources

Let's walk through connecting a few of the most common data sources. The process is very similar for each, starting on the "Data" tab in Excel’s ribbon.

How to Connect to Another Excel File

This is perfect for when you need to combine data from multiple workbooks, like pulling regional sales summaries into a master company P&L file.

1. Navigate to the Data tab.
2. In the "Get & Transform Data" section, click on Get Data > From File > From Workbook.
3. An explorer window will open. Find and select the Excel file you want to connect to and click Import.
4. A "Navigator" window will appear, showing you all the worksheets and named tables within that source workbook. It’s always best practice to connect to a Table if possible, as it’s a more structured object than a whole worksheet. Click the table or sheet name to see a preview on the right.
5. You now have two main options at the bottom of the window:
6. Once you're done transforming, click Close & Load in the Power Query editor to bring the data into your spreadsheet.

The imported data will appear in a new sheet, formatted as a green Excel table. You've now created a durable link to that source file.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

How to Connect a CSV or Text File

This is probably the most common use case for marketers, analysts, and business owners. Most platforms — from Google Ads and HubSpot to Shopify and QuickBooks — let you export raw data as a CSV file.

1. Go to the Data tab.
2. Click Get Data > From File > From Text/CSV.
3. Locate the CSV file on your computer and click Import.
4. Excel will show you a preview window. It automatically analyzes the file to determine the basics, like the character encoding and the delimiter (usually a comma). You can change these settings here if Excel gets it wrong, but its detection is generally very accurate.
5. Again, you have the option to Load directly or Transform Data. Always lean towards transforming your data to ensure everything is clean first — especially with CSV files, which often contain messy formatting.
6. In the Power Query Editor, you might perform common cleaning steps like using "Trim" to remove extra spaces or changing a "Date" column from a text type to a proper date type. When you’re ready, click Close & Load.

No more manually opening CSVs and trying to re-format them every single time. Every step you take in the Power Query editor is saved as a rule that will be reapplied automatically on every refresh.

How to Connect to a Web Page

Need to pull public data from a table on a website? Excel can do that, too. This is great for tracking stock prices, currency exchange rates, or tables of statistics from sites like Wikipedia.

1. Go to the Data tab.
2. Click Get Data > From Other Sources > From Web.
3. A dialog box will appear. Paste the full URL of the web page containing the table you want to import and click OK.
4. Excel will analyze the page's HTML structure and present you with a list of all the tables it found. The "Navigator" window lets you click on each suggested table to preview it.
5. Once you've identified the correct table, click Transform Data to open Power Query for any necessary cleaning before loading it into your sheet.

This powerful feature lets you pull structured web data directly into your analysis without ever having to write a single line of code.

Managing and Refreshing Your Data Connections

Creating the connection is just the first step. The real value comes from being able to easily update your data and manage your queries.

Keeping Your Data Fresh

To refresh your connected data and pull in the latest information from the source, you have a few options:

• Right-click Refresh: Simply right-click anywhere inside the data table on your worksheet and select Refresh from the context menu.
• Refresh All: Go to the Data tab and click the large Refresh All button. This will update every single data connection in your entire workbook at once.
• Queries & Connections Pane: For more granular control, go to Data > Queries & Connections. This opens a side panel listing all your queries. You can hover over any query and click the refresh icon next to its name.

Editing Existing Connections

What if you need to change something about the connection or transformation? Maybe the folder where you store your CSVs moved, or you need to add an extra filter step. You can easily edit your queries:

1. Go to Data > Queries & Connections to open the side panel.
2. Find the query you want to edit, right-click it, and select Edit.
3. This will reopen the Power Query Editor, where you can modify any of the "Applied Steps" listed on the right-hand panel. You can change the source, add new transformation steps, or delete old ones without having to start over.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Setting Automatic Refresh Properties

For some reports, you might want the data to refresh automatically. You can tell Excel to update all connections every time the file is opened. This is great for dashboards that need to be up-to-the-minute as soon as you look at them.

1. In the "Queries & Connections" pane, right-click on the query you want to automate and choose Properties.
2. In the dialog box that appears, look for the "Refresh control" section.
3. Check the box for "Refresh data when opening the file." You can also set it to refresh at a timed interval (e.g., every 60 minutes). Click OK to save the setting.

Final Thoughts

Learning how to connect data directly in Excel is one of the most significant skills you can develop for improving your reporting workflow. By leveraging the Get & Transform (Power Query) tools, you can automate your data gathering process, eliminate manual errors, and spend your time analyzing insights rather than moving data around.

While Excel is fantastic for wrangling individual files, the process can become clumsy when you need to pull data from a dozen different cloud platforms like Google Analytics, Facebook Ads, Shopify, and Salesforce all at once. For that, we built Graphed to be the easiest solution. We connect to all your marketing and sales sources in seconds and let you build real-time, interactive dashboards just by describing what you need in plain English - no manual connections, refreshes, or Pivot Tables required.