How to Link Excel to Internet Data

Manually copying and pasting tables from a website into Excel is a classic time-waster. The formatting breaks, the numbers come in as text, and the data is stale the moment you paste it. This guide will show you how to create a live, refreshable link between an Excel spreadsheet and data on the internet, so your numbers always stay up to date automatically.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Linking Excel to Web Data?

Creating a direct connection to internet data turns your static spreadsheet into a dynamic dashboard. Instead of constantly repeating a manual process, you build a connection once, and your spreadsheet becomes a more reliable tool.

• Save a Ton of Time: Say goodbye to manual data entry. Stop wasting 15 minutes every Monday morning copying and pasting the same weekly report from a website. Set up a connection once and refresh it with a single click.
• Improve Data Accuracy: Every time you manually copy data, you risk introducing an error. A direct data link pulls the information exactly as it appears on the source website, reducing the chance of human error.
• Always Have Current Information: Are you tracking competitive pricing, stock market data, or sports statistics? A live link ensures you're making decisions based on the latest information, not data you pulled three days ago. Set it to refresh automatically when you open the file.

The Quickest Method: Excel's "From Web" Feature

Modern versions of Excel (Microsoft 365, Excel 2021, 2019, and 2016) have a built-in feature that makes linking to web data incredibly straightforward. This approach works best for data that is already organized into a clean HTML table on a webpage.

Let's walk through an example. Say we want to pull the list of largest companies by revenue from Wikipedia into our spreadsheet.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Guide to Using "From Web"

1. Find Your Data Source URL: First, find the webpage with the table you want to use. For our example, we'll use Wikipedia's list of companies by revenue. Copy the full URL from your browser's address bar.
2. Open Excel and Go to the Data Tab: In a new or existing Excel workbook, navigate to the main ribbon at the top and click on the Data tab.
3. Select "From Web": Within the "Get & Transform Data" section of the Data tab (it's usually on the far left), click the icon that says From Web. A dialogue box will pop up.
4. Paste Your URL: In the dialogue box, paste the URL you copied earlier and click OK. Excel will now connect to the webpage and analyze its content.
5. Choose Your Table in the Navigator: After a few moments, the Navigator window will appear. On the left side, it will show you all the tables Excel detected on the page. Click on each one - a preview of the data in that table will appear on the right. Select the table that contains the data you want to import. In our Wikipedia example, we'll find one that clearly lists the companies, revenue, and other details.
6. Load the Data into Excel: At the bottom of the Navigator window, you'll see a Load button. Click it. Excel will instantly pull the data from the website and place it into a new worksheet in your workbook. The data will be formatted as an official Excel Table, complete with filter-and-sort capabilities.

And that's it! You now have a live connection. But what good is a connection if you can't refresh it?

How to Refresh Your Web Data

The best part of this method is keeping the data current. You have two primary ways to do this:

Manual Refresh

Whenever you want to pull the latest data from the source website, just right-click anywhere inside your new data table, and select Refresh from the context menu.

Automatic Refresh

To really automate things, you can tell Excel to update the data on a schedule or every time you open the workbook.

1. Right-click inside your data table and choose Table > Edit Query. The Power Query Editor will open.
2. On the Home tab of the Power Query Editor, click the bottom half of the Close & Load button and then select Close & Load To...
3. This brings up the Import Data dialog. Here you select the table in your worksheet and click Properties...
4. In the "Query Properties" window that appears, you can check boxes for options like "Refresh every [X] minutes" or "Refresh data when opening the file." This is perfect for setting up a hands-off reporting dashboard.

The More Powerful Method: Using Power Query to Clean Web Data

Sometimes, the data you pull from a website isn't perfectly formatted. It might have extra columns you don't need, missing information, or numbers formatted as text. This is where Power Query comes in. Power Query (the tool behind the "From Web" feature) allows you to clean, shape, and transform your data before it ever gets loaded into your spreadsheet.

Let's follow the same steps as before, but with one key difference.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Guide to Transforming Web Data

1. Follow Steps 1-4 from the first method: Go to Data > From Web and paste your URL.
2. Select "Transform Data": In the Navigator window, after you've found and selected your table, instead of clicking "Load," click the Transform Data button. This will open the Power Query Editor.

The Power Query Editor is an incredibly powerful tool for data preparation. Here are a few simple transformations you can apply:

• Removing Columns: The table from Wikipedia might have columns for "References" or other details you don't need. Simply right-click the header of a column you want to remove and select Remove.
• Filtering Rows: Don't want all the data? Just like in Excel, you can use the filter arrows on the column headers to include or exclude specific rows. For example, you could filter the list to only show companies headquartered in a specific country.
• Changing Data Types: Power Query is pretty smart about guessing data types, but sometimes it gets it wrong. If a column of numbers is being treated as text (often left-aligned), you can click the icon in the column header (e.g., "ABC" for text, "123" for whole numbers) and change it to the correct type, like Currency or Whole Number.
• Splitting Columns: Have a column containing "City, State"? You can select that column, go to the Home tab, click Split Column, and choose to split by a delimiter (like a comma) to easily create separate columns for City and State.

The real magic of Power Query is the "Applied Steps" pane on the right. Every single click you make - removing a column, filtering a row, changing a data type - is recorded as a step. When you refresh your data later, Excel will automatically run through those exact same transformation steps on the new data before loading it. This ensures your final table is always perfectly clean and structured exactly the way you need it.

When you're finished transforming the data, click the "Close & Load" button in the top-left corner. Your cleaned, transformed data will load into your worksheet, ready for analysis.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Common Challenges and Quick Fixes

Linking to web data isn't always a perfectly smooth process. Here are a few common hiccups and how to approach them:

• The data isn't in a nice HTML table: This is the most common issue. The "From Web" feature is designed to find structured <table> tags in a webpage's HTML. If the data is displayed using JavaScript or CSS in a non-tabular format, Excel might not be able to "see" it. In these cases, you might be out of luck with this simple method.
• The website requires a login: When you connect to a site, Power Query provides several authentication options (on the left side of the "Access Web content" pane) to handle logins. However, complex login systems might still present a challenge.
• The website owner changes the table structure: If the source webpage undergoes a redesign and the table's structure changes (e.g., columns are renamed or reordered), your query may break upon refresh. You'll need to go back into the Power Query Editor to fix the broken steps.
• Respect the website's rules: Always be mindful of a website's terms of service before programmatically fetching data. Automating too many requests too quickly can put a strain on their servers. Be a good neighbor.

Final Thoughts

Connecting Excel to live internet data is a skill that can save you hours of mundane work and reduce errors. By using the simple "From Web" feature for clean tables or Power Query to transform messier data, you can build dynamic, automated reports directly in the spreadsheet tool you already know and love.

But when you're connecting and cleaning data from dozens of different marketing and sales platforms, not just public websites, managing individual queries can get complex. At that point, you're not just pulling a table from the web, you're trying to blend data from Google Analytics, Facebook Ads, Shopify, and your CRM. That’s where we built Graphed to help. We automate the entire connection and cleaning process so you can instantly create unified dashboards using natural language. Just ask, "Show me a chart of my Facebook Ads spend versus my Shopify sales by campaign this month," and Graphed builds the report for you with real-time, cross-platform data.