Import Website Data into Excel: Easily Format with Power Query

Tired of copy-pasting website data into Excel? We’ve all been there, scrubbing through pages and manually entering information, only to realize it takes forever. But did you know Excel can import website data for you, right from the URL? With just a few clicks and some Power Query magic, you can pull data into Excel automatically and format it to meet your needs. Let’s break it down.

Why Website Data Needs to Be in a Table Format

Before jumping into importing website data into Excel, there’s one critical thing to check—the data needs to be in table format. Excel works best when the data is already organized, such as the tables you typically find on Wikipedia. If the website you’re using has data in lists or another form that’s not easily digestible by Excel, you’re likely to hit a wall. For instance, say you want to import a list of countries. A well-organized Wikipedia table will load perfectly into Excel, while a simple bulleted list might give you a headache.

How to Check for Table Formatting

• Go to the website where your data lives. Look for organized rows and columns, not just scattered text.
• If you’re unsure whether it’s a table, Excel can show you in the later steps. But starting with clean data saves time and prevents errors.

Pulling Website Data Using Power Query in Excel

Here’s where the fun begins. To import data from a website URL, you’ll use Excel’s Power Query tool. Don’t worry—it’s easier than it sounds. Follow these steps to get started:

Step 1: Access the Data Tab and Select “From Web”

1. Open Excel and navigate to the Data tab.
2. Under the Get & Transform Data section, click From Web.
3. A box will pop up asking for the URL. Paste your website link here and hit OK.

Step 2: Choose the Data You Need

Excel will load a “Navigator” box, where it’ll show all data from the URL. You’ll notice a few options like:

• HTML tables: Displays tables from the webpage.
• Suggested tables: Excel’s recommendation of useful data.
• Text: Other textual elements from the page like headers or menus.

Select the dataset you need. For example, if you’re pulling a “List of Countries”, you’ll see it clearly labeled under HTML tables. Click it, and you’ll see a preview of the data.

Step 3: Load or Transform Your Data

Here’s where you can customize things:

• Load Data Directly: Click “Load” to send the data straight into Excel. No changes, no fuss.
• Transform Data First: Double-check and edit your data in Power Query before it lands in Excel. I usually recommend this step to avoid future issues. Errors are easier to fix before loading into the spreadsheet.

FEATURED TUTORIAL: How to Create a Pivot Table from Multiple Workbooks in Excel

Cleaning Data in Power Query

Power Query is like Excel, but better for handling messy data. Once your data is in Power Query, you can clean and format it however you like.

What You Can Do in Power Query:

• Remove Columns/Rows: Right-click on columns you don’t need and choose “Remove”.
• Fix Errors: Check for missing values (often marked as “null”) or weird characters. For example, if a column has percentages but the formatting is off, you can fix that by changing the type to “Percent”.
• Standardize Text: Convert text to uppercase, lowercase, or even proper case.

Say you’re importing country data, and one row says “null” because the world itself doesn’t belong to a continent. You can decide whether to leave it or clean up your dataset manually.

Once everything looks good, click Close & Load. Boom! Your clean data lands in Excel as a usable table.

Styling and Refreshing Your Imported Data

Now that your data is in Excel, it behaves just like any other table. You can:

• Change table styles.
• Add pivot tables.
• Remove headers or modify content.

But here’s a cool thing: if your source data on the website updates frequently, Excel can stay in sync.

Automatic vs. Manual Refresh

1. To enable automatic updates, go to Properties from the Query menu. Check the box for “Enable Background Refresh”.
2. Set a refresh interval based on your needs. For stock prices or real-time sales data, you can update as often as every minute.
3. For manual updates, go to Data > Refresh All, and Excel will fetch the latest version of your data.

FEATURED TUTORIAL: How to Import Contacts from Excel to Outlook

Stay Ahead with Automated Data Import

Gone are the days of manually keying in data and losing hours to copy-paste hell. With Excel’s web data import and Power Query, staying organized has never been easier. Whether you’re tracking stock prices, creating sales reports, or simply pulling tables from Wikipedia, this method saves time and reduces errors.

Got questions or tips of your own? Drop them in the comments below, and let’s make Excel work for you!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.