How to Copy Data from Website to Excel Automatically

Tired of mindlessly copying and pasting data from a website into your Excel spreadsheet, row by row? There's a much smarter way to work. This guide will show you how to set up a direct, automatic connection between a website and Excel, so your data updates itself without any manual effort.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Manually Copying Data is a Dead End

If you're tracking competitor pricing, monitoring stock market data, or gathering sales leads from a directory, you’ve likely spent countless hours highlighting tables on a website, hitting Ctrl+C, and hoping the formatting doesn't break when you paste it into Excel. This process isn't just tedious, it's a massive drain on your productivity.

Here’s the problem with manual data entry:

• It’s incredibly time-consuming. What might take you an hour of manual gathering could be done in seconds with the right setup. This is time you could be using for actual analysis, not data fetching.
• It's prone to human error. Did you miss a row? Did you accidentally paste data into the wrong column? Small mistakes are easy to make and can completely compromise your report.
• The data is instantly stale. The moment you finish copying, the data on the live website could have changed. Your report is already out of date, making it useless for real-time decision-making.
• It doesn't scale. Copying data from one page is a chore. Tracking ten pages is a nightmare. This manual process simply can't keep up as your data needs grow.

Meet Your New Best Friend: Power Query in Excel

The solution to this problem is already built into modern versions of Microsoft Excel. It's called Power Query (you may also see it called "Get & Transform Data"), and it’s a powerful engine designed to pull data from a huge variety of sources, including websites.

In simple terms, Power Query lets you point to a website URL and say, "Hey, see that table on this page? I want that data in my spreadsheet." Better yet, it remembers this connection, allowing you to refresh the data with a single click - or even schedule it to update automatically.

The best part? You don’t need to know how to code. If you can click a few buttons in the Excel menu, you can use Power Query to automate your Sisyphusian copy-and-paste nightmares and turn them into 30 seconds of setup.

Step-by-Step Guide: Automatically Pulling Website Data into Excel

Let's walk through the process of pulling data from a website into an Excel sheet. For this example, we’ll grab a list of the largest companies by revenue from a Wikipedia page - a perfect use-case for a public, well-structured table of data.

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 Target Data

First, find the web page containing the data you want. The ideal source will have your target data contained within an HTML <table>. These are usually the neat, grid-like tables you see on websites. Complex, interactive sales tables a la Salesforce might not work for this simple method, but for many financial sites, an article with a leaderboard style display, directories, or reference pages, it works perfectly.

For our example, we'll use this Wikipedia URL with a nice, clean table of data:

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

Copy the URL of the page with the table. You'll need it in the next step.

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

Open a new or existing Excel workbook. In the top ribbon, click on the Data tab. Look for the "Get & Transform Data" section on the far left. In this section, you'll see a button labeled From Web. Click this.

(If you're using an older version of Excel, you might find this under Data > Get External Data > From Web).

Step 3: Paste the Website URL

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

Excel will then connect to the website in the background and analyze its contents to look for data tables.

Step 4: Use the Navigator to Find and Select Your Table

After a few moments, the Navigator window will appear. This is Power Query’s way of showing you all the structured data tables it found on the page.

On the left-hand side, you'll see a list of items, often named "Table 0," "Table 1," etc., along with a potential "HTML tables" view or even named tables pulled directly from the site. Click on each one, and you’ll see a preview of that table’s data on the right.

Go through the list until the preview on the right shows the exact table you want. Once you've found it, click the checkbox next to its 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 straight into your Excel sheet as is. If the preview table looks perfectly clean, this is a quick and easy option.
• Transform Data: This is the more powerful choice. It opens the Power Query Editor, which is a dedicated interface for cleaning and preparing your data before it gets to Excel. You can remove columns you don't need, filter out certain rows, change text to numbers, and much more.

For our purposes, our Wikipedia table is pretty clean to begin with, and you can choose either option with little difference from the start... so go ahead and press Load. Almost instantly, Excel will populate a new worksheet with your web-scraped data, properly formatted in a clean table.

The Final Touch: Automating the Data Refresh

You’ve successfully imported the data, but here’s how we make it truly automatic. The data is now linked to the website, meaning you can update it whenever you want.

To configure your automated update settings, follow these simple steps:

1. Make sure your cursor is selected somewhere inside your new data table.
2. Go to the Data tab in the Excel ribbon.
3. Click the little dropdown arrow next to Refresh All and select Connection Properties.

A dialog box called "Query Properties" will open. Here’s where a little bit of magic happens. You have a few options in the "Refresh control" section:

• Refresh every X minutes: Check this box and set a time interval (e.g., 60 minutes) to have Excel automatically poll the website for new data in the background.
• Refresh data when opening the file: A popular choice. This ensures you always have the latest data every time you begin work.

Select your preferred option, click "OK," and you're done. You have now created a live, automated data connection. No more copy-pasting required!

What if It Doesn’t Work? Common Roadblocks

Power Query is fantastic, but it's not foolproof. Here are a couple of common scenarios you might run into.

The Website Requires a Login

If the data is behind a password-protected member area, you can’t just use the public URL. Power Query does have authentication settings (you can explore these in Data > Data Source Settings), where you can input credentials. However, this may be complex to configure correctly and can potentially create a security flaw for your data's protections.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Data Doesn't Load from A Table

Many modern websites use JavaScript to load data dynamically after the page opens. Since Power Query just reads the initial HTML of the site, it might not "see" the data if it needs a few live seconds to load in the first place.

You Need Data from Multiple Platforms, Not Just One Website

The single biggest headache for modern professionals isn't pulling data from one clean table, it's manually collecting and attempting to stitch together data from a half-dozen different platforms like Shopify admin, Google Analytics, social media ads managers, and your CRM just to build a cohesive report. Power Query becomes exponentially harder to implement when dealing with such diverse data structures.

Final Thoughts

For pulling data from simple HTML website tables on the live web, Excel's built-in Power Query is an incredibly potent feature that can reclaim an unfathomable amount of hours from your otherwise hectic schedule. It will help eliminate soul-crushing, ultimately tedious data entry tasks to help you focus on the insights available instead of endlessly reformatting your scraped data.

For marketing teams, and just about anyone in sales struggling to understand data, your true analytics challenges are almost never confined to just one clean website. It’s when your data is splintered across a dozen different apps like Google Analytics, Shopify, HubSpot, or live ad platforms. And that's why we built Graphed. We connect to all your important apps instantly and let you create reports in plain English, bringing all of your scattered metrics into a beautiful singular view, perfect for even your non-tech-oriented C-Levels without spending hours wrangling CSV files.