How to Get Excel to Pull Data from a Website

Manually copying and pasting data from a website into Excel is manageable for a few lines but a nightmare for anything more. If you need to track changing data - like stock prices, product inventories, or a list of sales leads from a public directory - that copy-paste routine quickly turns into a full-time job. Thankfully, Excel has a powerful, built-in tool that lets you create a live connection to a website, so your data updates automatically. This tutorial will walk you through exactly how to set it up.

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 Pull Data from a Website into Excel?

Creating a direct link between a website and your spreadsheet is more than just a time-saver, it unlocks new ways to analyze real-world information. Instead of working with a static, outdated snapshot of data, you're working with a living dataset.

Here are a few common scenarios where this is incredibly useful:

• E-commerce and Retail: Track competitor pricing and promotions directly from their websites.
• Finance and Investing: Pull the latest stock prices, cryptocurrency values, or exchange rates from financial news sites into your analytical models.
• Marketing: Monitor web analytics dashboards, social media stats, or lists of industry blogs for content opportunities.
• Operations: Track shipping statuses, raw material costs, or public data feeds that impact your operations.

By automating the data collection part of the process, you can spend less time gathering information and more time analyzing it, spotting trends, and making informed decisions.

Using Power Query: Excel's Secret Weapon for Web Data

The magic behind pulling web data into Excel is a feature called Get & Transform Data, which is powered by a tool called Power Query. If you're using Excel 2016, Excel 2019, or a Microsoft 365 subscription, this functionality is already built-in and waiting for you on the Data tab.

Power Query is essentially an engine designed to connect to different data sources (websites, databases, other files), clean and reshape the information, and then load it into your spreadsheet. You don't need to write any code, you just click through a series of user-friendly windows to tell Excel what you want.

Let’s walk through the exact steps to connect to a website and pull in a data table.

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

Step-by-Step Guide to Importing Web Data

For this example, we'll pull a table showing world population data from Wikipedia. It’s a classic example of a well-structured HTML table perfect for this task.

Step 1: Copy the URL of the Website

First, find the page with the data you need. Open your web browser, navigate to the page, and copy the full URL from the address bar. For our example, we'll use this Wikipedia page:

https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population

Just copy that link to your clipboard.

Step 2: Navigate to 'Get Data From Web' in Excel

Now, open a blank or existing Excel workbook.

1. Click on the Data tab in the Excel ribbon at the top of the screen.
2. On the far left, look for the Get & Transform Data section.
3. Click the button that says From Web. A small icon of a globe on a page should accompany it.

This will open a dialog box asking for a URL.

Step 3: Enter the Website URL in Excel

In the "From Web" dialog box that appears, you'll see a field to enter the URL. Paste the link you copied in Step 1 into this box.

For most public websites, the Basic option will work just fine. Click OK. Excel will now connect to the website and start analyzing its structure to find any data tables.

Step 4: Select Your Data in the Navigator

After a few moments, the Navigator window will appear. This is Excel's way of showing you all the structured data it found on that webpage. On the left side, you'll see a list of items, often with names like "Table 0," "Document," or the actual titles of the tables from the page.

You can click on each item to see a preview of the data on the right. In our Wikipedia example, you'll find a few tables. Click on the one named "countries and dependencies by population" to preview the population data.

Once you've identified the correct table you want to import, you have two choices at the bottom of the window: Load or Transform Data.

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

Step 5: Choose to 'Load' or 'Transform' the Data

This is an important fork in the road. Your choice depends on how clean the source is and what you plan to do with it.

• Load: This option loads the data directly into a new worksheet table exactly as it appeared in the preview. If the data is clean, well-formatted, and ready to use, this is the quickest option.
• Transform Data: This is the more powerful option and the one we recommend getting familiar with. It opens the Power Query Editor, a separate window where you can clean, filter, reshape, and generally prepare your data before it ever lands in your Excel.

For learning purposes, let's click Transform Data.

A Quick Guide to Cleaning Data with the Power Query Editor

Welcome to the Power Query Editor. It may look intimidating at first, but it is surprisingly intuitive. You can use simple button clicks—no formulas needed—to perform powerful data cleaning tasks. Everything you do here is repeatable, so when you refresh the data later, all the same cleaning steps are automatically applied.

Here are a few common cleaning steps:

Removing Unnecessary Columns

Does the table include columns you don't need? Simply click the header of the column(s) you want to remove (hold Ctrl to select multiple), right-click, and select Remove Columns. Boom, they're gone.

Filtering Your Data

Just like in Excel, you can filter your data. Click the dropdown arrow on any column header to access filtering options. You can uncheck values you want to exclude or use more advanced logic, like filtering rows where a number is "greater than" a certain value.

For example, in our population data table, maybe we only want to see countries with a population over 100 million. We'd click the filter on the 'Population' column, go to Number Filters > Greater Than..., and enter 100000000.

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

Checking Data Types

Power Query often guesses the data type for each column (e.g., Text, Whole Number), but it is good to double-check. The data type is indicated by a small icon to the left of each column header (e.g., "123" for whole numbers, "ABC" for text, a calendar for dates). If a number column is mistakenly set as text, you can click that icon and change it to the correct type.

Once you're happy with how your data looks, click the Close & Load button in the top-left corner. The clean, formatted data will be loaded into your Excel sheet as a special Excel table colored in green or blue.

Keep Your Data Live: How to Refresh Your Web Connection

You now have a query that pulls data directly from a website. This is a vast improvement over copying and pasting, but the real magic is that this connection is live. All your cleaning and transforming steps are saved, so you are always looking at fresh data whenever it updates without going through that process again.

To refresh your data and pull the latest version from the website, there are several methods:

• Manual Refresh: Simply right-click anywhere in your data table and choose Refresh from the context menu. Or go to the Data tab and click Refresh All.

Final Thoughts

There you have it. Using Excel's built-in "Get Data from Web" feature fundamentally changes how you work with online data, saving you from tedious manual work and giving you access to constantly updated information. This powerful combination of Power Query's data connection with its easy, repeatable cleanup steps lets you create reliable and dynamic reports.

While this approach works brilliantly for one or two websites, often our most valuable business answers lie scattered across a dozen different platforms—like Google Analytics, our CRM, Shopify, and our Ads Managers. That is where a tool like Graphed comes to help. It helps you connect to all those platforms in minutes, then uses natural language to build a dashboard in real-time, all without ever writing a formula. It's about getting back your time so you can focus on the insights, not the data wrangling.