How to Automatically Update Data in Excel from Website

Manually copying and pasting data from a website into Excel gets old, fast. It’s tedious, prone to human error, and the moment you finish, your data is probably already out of date. This article will show you how to set up a direct, live connection between a website and your Excel spreadsheet, so your data updates automatically.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Bother Automating Data from Websites?

If you're still doing the copy-paste-update routine, you're spending more time on data collection than on data analysis. Automating the process of pulling data from a website directly into Excel offers a few major advantages:

• Saves Immense Amounts of Time: The most obvious benefit. A task that takes you 15 minutes every morning can be done automatically before you even have your coffee. Setting it up once saves you countless hours down the line.
• Improves Data Accuracy: Every time you manually copy data, you risk introducing an error. A typo, an extra space, or a missed row can throw off your entire analysis. Automation removes the risk of these manual mistakes.
• Keeps Your Data Fresh: Your decisions should be based on the latest information, not last Tuesday's numbers. An automated connection ensures your spreadsheet reflects the most current data available on the website, whether it’s hourly stock prices, daily inventory levels, or weekly project statuses.
• Easier to Share and Standardize: When your report is connected to a live source, anyone on your team with access to the file can refresh it to see the latest numbers. This creates a single source of truth and eliminates the confusion of having multiple outdated versions of a spreadsheet floating around.

The Easiest Method: Excel's Built-in Power Query

The best way to create a live link between a website and your spreadsheet is by using a powerful, built-in Excel feature called Power Query (you might also see it labeled as "Get & Transform Data" in your Excel ribbon). It’s designed specifically for this kind of work and is available in all modern versions of Excel (Excel 2016 and newer, including Microsoft 365).

Here’s the step-by-step process for pulling web data into your spreadsheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Find the URL of Your Data Source

First, pinpoint the exact webpage that has the data you need. The ideal source for this method is a webpage that presents data in a structured HTML table — think Wikipedia pages, financial data sites, or public government statistics pages. For this example, let's use a Wikipedia page listing the population of U.S. states.

Copy the full URL from your browser's address bar. You'll need it in the next step.

Step 2: Go to the "Data" Tab in Excel

Open a blank Excel workbook. On the top ribbon, find and click the Data tab. On the far left of the Data ribbon, you will see a group of tools called "Get & Transform Data." This is the Power Query section.

Click on the button that says "From Web."

Step 3: Paste the Website URL

A small dialog box will pop up asking for the URL. Paste the URL you copied in Step 1 into the box and click "OK."

Excel will now connect to the website and analyze its structure to find any available data tables. This might take a few seconds.

Step 4: Use the Navigator to Select the Right Table

After a moment, a new window called "Navigator" will appear. On the left side, it will show a list of all the tables Excel found on the webpage. Click on each table one by one, and Excel will show you a preview of its contents on the right side.

Scan through the previews until you find the exact table containing the data you want. Once you've found it, click the checkbox next to that table's name.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Load Your Data into Excel

At the bottom of the Navigator window, you'll see a few options. The two main ones are "Load" and "Transform Data."

• Load: This option will immediately pull the data from the website and place it directly into a new worksheet in your Excel file. The data will appear in a formatted Excel table. For clean, simple data, this is often all you need.
• Transform Data: This is a more advanced option that opens the Power Query Editor. The editor is an incredibly powerful tool that lets you clean and shape your data before it even hits your spreadsheet. You can remove unneeded columns, filter out certain rows, change data types, split columns, and much more. While a deep dive is for another day, just know that this is where you go if the web data isn't perfectly formatted.

For now, let’s keep it simple. Click the "Load" button.

Congratulations! The data from the website is now sitting nicely in your Excel worksheet, formatted as an official Excel Table.

Step 6: Setting Up the Automatic Refresh

You've successfully pulled the data in, but the key is to make it update automatically. Here's how to control when and how your data connection refreshes:

First, click anywhere inside the data table you just created. You'll notice a new "Query" tab appears on the Excel ribbon. Click it.

Within the Query tab, click on "Properties" (sometimes you need to click "Refresh All" dropdown for the 'Connection Properties' option).

Alternatively, you can right-click anywhere in your data table, navigate to Table > External Data Properties.

This opens the "Query Properties" window, which gives you complete control over the refresh schedule. The key options are:

• Refresh every X minutes: This is perfect for data that changes frequently throughout the day, like currency exchange rates or breaking news feeds. Simply check the box and set the interval (e.g., 60 minutes) for how often you want Excel to automatically re-check the website for new data.
• Refresh data when opening the file: This is the most common and practical setting for many reports. If you check this box, Excel will automatically refresh the data every single time you open the workbook. This ensures that you're always starting your work with the very latest information, without you having to remember to do anything.

Select your desired refresh schedule, click "OK," and save your file. Your automated reporting system is now active.

Handling More Complex Situations

Sometimes, data isn't neatly organized in an HTML table, or it’s hidden behind a login page. While Power Query is surprisingly flexible, some scenarios require a different approach.

• Data Behind Logins: If the data requires a username and password, Power Query has options for different credential types (in the "From Web" connection setup), but it can be finicky. Sometimes this works, sometimes it doesn't, depending on the site's security.
• Data Loaded with JavaScript: Many modern websites load data dynamically after the page opens. Sometimes Power Query can handle this, but for complex sites, it might fail to see the data. In these "headless browser" scenarios, you might need a dedicated web scraping tool.
• Website with No Formal API: For websites without clean tables or an official data API, connecting can be more challenging. Advanced users might use programming languages like Python with libraries such as Beautiful Soup or Selenium to scrape the data and save it to a CSV file, which Excel can then read automatically. But this requires coding knowledge.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

A Quick Note on Excel Versions

It's important to know that the "Get & Transform Data" (Power Query) features are built directly into Excel 2016, Excel 2019, Excel 2021, and Microsoft 365. If you're on one of these versions, you have everything you need.

For those using older versions like Excel 2010 or Excel 2013, the good news is you can still use this functionality. You just need to download and install the official Microsoft Power Query add-in first. A quick search for "Power Query download for Excel [your version]" will point you to the right place.

Final Thoughts

Pulling live data from a website into Excel doesn't have to be a manual grind. Using the built-in Power Query tools, you can automate this entire process in minutes, freeing you up to focus on finding insights rather than copying data. This simple setup turns your static spreadsheet into a dynamic dashboard that stays current on its own.

While connecting Excel to a single website is a great step forward, marketing and sales reporting often requires stitching together data from a dozen different places — Google Analytics, your social media ads, Shopify, your CRM, and more. This is where we built Graphed to simplify things even further. Instead of setting up individual queries, you can connect all your data sources with just a few clicks. Then, you can build live, cross-platform dashboards just by asking questions in plain English — no wrestling with formulas or settings required.