How to Select Data Source in Excel

Tired of endlessly copying and pasting data from other files into your Excel report every week? There's a much smarter way to work. By connecting Excel directly to your source data, you can build refreshable reports that stay up-to-date with a single click. This guide will walk you through how to select and connect various data sources in Excel, transforming your static spreadsheets 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 to a Data Source Instead of Copying and Pasting?

While copying and pasting is quick for a one-time task, it becomes a major bottleneck for recurring reports. Manually updating your data is tedious, prone to human error, and ensures your analysis is always a little bit out of date. When you connect directly to a data source, you create a live link. This means:

• Reduced Manual Work: Stop the repetitive download-and-copy cycle. Update your entire report with one click of the "Refresh" button.
• Improved Accuracy: You eliminate the risk of copy-paste errors, like grabbing the wrong column or missing a few rows. The connection pulls the data exactly as it is in the source.
• Always-Current Data: Your reports and dashboards can reflect the latest information available, making your analysis timely and more relevant.
• A Single Source of Truth: Everyone on your team works from the same refreshable data, ending the confusion caused by multiple spreadsheet versions floating around.

In short, it's the difference between doing data entry and doing data analysis. Let's get into how to set it up.

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

Meet Your Data Hub: Get & Transform (Power Query)

Modern versions of Excel (Excel 2016 and newer, and Microsoft 365) have a powerful, centralized tool for handling data connections called Get & Transform Data. You might also hear it referred to by its original name, Power Query.

You can find this on the Data tab of the ribbon. This section is your command center for connecting to virtually any kind of data you can think of.

Think of Power Query as a pre-processor for your data. Before data even touches your worksheet cells, Power Query lets you connect to it, clean it up (like removing extra columns or fixing date formats), and shape it into a perfect, analysis-ready table. While we won't go deep into Power Query's transformation capabilities here, just know that every connection you make starts in this part of Excel.

How to Connect to Common Data Sources: Step-by-Step

Let's walk through how to connect to some of the most common data sources you're likely to encounter. The process is very similar for each, making it easy to learn.

From Another Excel Workbook

This is probably the most frequent use case. You have a master data file that gets updated regularly, and you want your summary report to reflect those changes automatically.

1. Navigate to the Data tab, click Get Data > From File > From Workbook.
2. An explorer window will open. Locate the Excel file you want to connect to and click Import.
3. The Navigator window will appear. On the left side, it will show you all the available sheets and any formatted Excel Tables within that workbook. Sheets are represented by a grid icon, and tables have a blue header bar icon.
4. Click on a sheet or table to see a preview on the right. (Tip: If your source data is in a formatted Excel Table, it’s almost always better to connect to the table rather than the whole sheet. It’s more precise and less likely to break if the sheet layout changes.)
5. At the bottom of the Navigator window, you have two main choices:
6. For this example, click Load. Excel will create a new worksheet and load your data into a green-and-white formatted table.

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

From a CSV or Text File

Exporting data from analytics platforms, CRMs, or ad managers often results in a CSV (Comma Separated Values) file. Connecting to it directly saves you from re-uploading it every time you get a new export.

1. Go to the Data tab, click Get Data > From File > From Text/CSV.
2. Find and select the .csv or .txt file you want to import, then click Import.
3. Excel will open a preview window. It automatically tries to detect things like the delimiter (the character that separates your columns, usually a comma) and the data types for each column (e.g., text, number, date).
4. You can review these settings and change them if Excel guessed incorrectly, but the defaults are usually reliable.
5. Just like with an Excel file, you can choose to Load the data directly or Transform Data in Power Query first. Click Load to import it into a new table.

From a Web Page

Did you know Excel can scrape data directly from a website? This is incredibly useful for tracking things like stock market data, sports statistics, or any information displayed in a structured table on a public webpage.

1. Find the URL of the page containing the data you want to import. Copy it to your clipboard.
2. In Excel, go to the Data tab and click the From Web icon.
3. In the dialog box that appears, paste the URL and click OK.
4. Excel will scan the webpage for any HTML tables it can recognize. The Navigator window will appear, listing all the tables it found.
5. Click on each table name to preview it. Some pages may have hidden formatting tables, so you might need to click a few to find the right one.
6. Once you've found the table you want, select it and click Load. Excel will pull that data into a new, refreshable table in your workbook.

From a Relational Database (like SQL Server or Azure SQL)

For more advanced users working with corporate data, you can connect Excel directly to a database.

1. Navigate to the Data tab, click Get Data > From Database.
2. Select the type of database you're connecting to (e.g., From SQL Server Database).
3. Enter the server details, such as the server name/address. The database name is sometimes optional.
4. You may be prompted to enter your credentials (username/password) or use your current Windows authentication to sign in.
5. Once connected, the Navigator will show a list of all databases on that server. Expand the one you need and select the specific tables or views you want to import. You can select multiple tables at once.
6. Click Load to bring the tables into your workbook, each on its own sheet.

Managing and Refreshing Your Data Connections

Connecting your data is only half the battle. The real magic comes from refreshing it to get the latest updates. Excel makes this easy.

Go to the Data tab and you'll see a section called Queries & Connections.

• Refresh All: This is the button you'll use most often. One click tells Excel to go back to every data source you've connected to, check for new information, and update all the corresponding tables in your workbook.
• Queries & Connections Pane: Clicking this button will open a panel on the right side of your screen. It lists every data connection in your file. Here, you can right-click an individual query to refresh just that one, or you can go into its properties.

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 an Automatic Refresh Schedule

You can even make Excel refresh its data automatically. In the Queries & Connections pane, right-clicking on a query and selecting Properties will open a menu with powerful options.

In the "Refresh control" section, you can set the data to:

• Refresh every X minutes.
• Refresh data when opening the file.

Enabling "Refresh data when opening the file" is a simple way to make sure your report is always current whenever you open it.

Final Thoughts

Connecting directly to data sources in Excel is a fundamental skill that separates basic spreadsheet users from efficient data pros. By building refreshable reports, you eliminate hours of manual entry, reduce errors, and ensure your decisions are based on the latest available data. It's a small investment in setup time that pays huge dividends in productivity.

While mastering these connections is great for centralizing data from files and databases, marketing and sales teams often face a messier reality: their data is scattered across a dozen different cloud platforms. Manually stitching together insights from Shopify, Google Analytics, social media ads, and your CRM is where the real manual work begins. That's why we built Graphed. We connect all your marketing and sales data sources in one-click, allowing you to instantly build real-time dashboards and get answers just by asking questions in plain English - no manual connections or Power Query required.