Excel: Import data from any website

Have you found a site with a lot of data and want to parse it in Excel? Simple enough, we can copy and paste it, right? The answer is “maybe.” Sometimes it works perfectly, but other times, you want to get the data a bit more clean in your Excel file, so let’s look at how to import data from a website.

We’ll use the worldometers website with the list of countries to test the concepts with accurate data.

The wrong way

If you copy and paste, it would look in Excel for macOS.

The destination will be:

Ugly right?

There’s an easier (or correct) way to do it, and I’ll show you how. I’ll show you both Windows and macOS versions with different strategies.

Excel for Windows

Excel for Windows has a feature called “Import from Web”. It makes import simpler since you only have to:

First, selecting where we want the data to start is essential. Select the cell and go to “Data” and the “From Web”.

You’ll be presented with this. Excel needs the website where you want to extract the data.

Copy and paste the website.

Excel will then ask you a few questions. You’ll often select “Anonymous”, but if the website is inside an organization or protected, you may need to use the other options to enable Excel to reach it.

Depending on the website, it can take a few seconds.

After Excel looks at the website, it will display multiple tables since you may have various data tables. Select the one that you want to import and press “Load”.

Here’s what the data looks like in Excel.

It’s pretty simple.

Excel for macOS

Excel for macOS will enable you to import HTML. If you don’t know what that is, don’t worry. Just think of it as the code that displays your page. We need to get that information and provide it to Excel so that it can perform its magic and import the data.

First, save the website (right-click on the page):

Save it somewhere. It doesn’t matter where as long as you can access it later.

Now in Excel, let’s select “Data”, pull the dropdown, and select “From HTML”

After that, you’ll be asked to locate the file. Choose the file you saved before:

Notice that you’re “opening” the file, not importing it into your Excel sheet. It will open a new window with the data.

Now copy the data you need, and you can safely close this window and delete the HTML file.

It takes a few steps, but it beats transferring the data manually.

Final thoughts

If you know this trick, extracting data from the website is pretty straightforward. Excel is super powerful, and it has many features that, although not prominent, can save us a lot of time.

So if you end up copying and pasting a lot of data, consider it a sign that you should be using another strategy.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other SharePoint-related articles here.

Photo by Meagan Carsience on Unsplash

 

Manuel Gomes

I'm a previous Project Manager, and Developer now focused on delivering quality articles and projects here on the site. I've worked in the past for companies like Bayer, Sybase (now SAP), and Pestana Hotel Group and using that knowledge to help you automate your daily tasks

View all posts by Manuel Gomes →

Leave a Reply

Your email address will not be published.