Excel get data from multiple web pages

In this article, I will show you how to extract data from multiple web pages into Excel. While working on an Excel worksheet, sometimes you may need to extract data from a web page. Sometimes you may need to extract data from multiple web pages. In this article, I will show you step-by-step procedures to extract data from multiple web pages into Excel. Follow the steps carefully to increase your excel skills.

Table of Contents hide

Download Practice Workbook

Step-by-Step Procedures to Extract Data from Multiple Web Pages into Excel

Step 1: Connect to the Web

Step 2: Open Power Query Editor

Step 3: Edit Query Using Advanced Editor

Step 4: Add Custom Column

Step 5: Remove Prefix from Extracted Data

Step 6: Final Result

Things to Remember

Conclusion

Related Articles

Download Practice Workbook

Please download the workbook to practice yourself.

Extract Data to Excel.xlsx

Step-by-Step Procedures to Extract Data from Multiple Web Pages into Excel

Here, you can see the dataset I will consider for this article. The dataset has two columns, B and C called Year and URL. The dataset is about Hollywood movie data at the box office. I will use those URLs to extract data from multiple web pages into Excel following the steps given below.

Step 1: Connect to the Web

This is the first and essential step of the whole procedure. I will connect the dataset to the web.

• First, select the C5 cell and copy the URL.

• Then, go to the Data tab in your toolbar.
• After that, select the From Web option.

• Hence, a window will show up.
• Then, paste the copied URL and press Ok.

Read More: How to Scrape Data from a Website into Excel (2 Easy Methods)

Step 2: Open Power Query Editor

In this step, I will open the power query editor. Here, the power query editor will help me to navigate data and open the table in a selected cell. Follow the following procedures.

• When the Navigator tab is opened, you can preview the table on the right side of the tab.
• Then, click on the Transform Data option.

• Then, in the Query Settings bar, select the Name option.

• Then, change the name to Movies.

• Moreover, go to the Home tab and select the Close and Load option.
• Then, from the drop-down menu select the Close and Load To option.

• Consequently, a window will pop up.
• Then, select the Existing worksheet option.
• Meanwhile, press Ok.

• As a result, Excel will show the data table in the E column.

Read More: How to Import Table from Website to Excel (2 Easy Ways)

Step 3: Edit Query Using Advanced Editor

In this step, I will Edit the query options to extract data from multiple web pages in excel. Follow the following steps one by one.

• From the Queries and Connections bar, select the Movies1 option.
• Then, right–click on the option.
• After that, select the Edit option.

• After that, select the Advanced Editor option.

• You will find the following window.

• Replace the code with the following code.

(URL) as table =>
let
Source = Web.Page(Web.Contents(URL)),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Header", type text}, {"Weight class", type text}, {"", type text}, {"2", type text}, {"3", type text}, {"Method", type text}, {"Round", Int64.Type}, {"Time", type time}, {"Notes", type text}})
in
#"Changed Type"

• Then, the following window will appear. We will let the window remain the same.

• Change the name of the function to fxMovies1.

• After that, click on the Close and Load option.

• As a result, you will find the function fxMovies1.

Read More: Excel VBA to Scrape Table from Website (With Easy Steps)

Step 4: Add Custom Column

In this step, I will add a custom column. Follow the steps described serially. If there is any problem, have a look at the illustrations.

• First, select any of the cells of the C cell.
• Then, go to the Data tab and under Get and Transform Data option, select the From Table or Range option also.

• Consequently, Excel will show the following table.

• Then, go to the Add Column option.
• After that, select the Custom Column option.

• Then, a window will show up.
• Change the new column name into FetchMovies.
• Then, write the following custom column formula.

=fxMovies1([URL])

• Hence, press Ok.

• Then, this window will show up.
• Select the Continue button here.

• In the Privacy Levels window, select the option shown in the image.
• Then save it.

• As a result, Excel will show the following table. Click the indicated icon then.

Read More: Automated Data Scraping from Websites into Excel

Step 5: Remove Prefix from Extracted Data

In this portion of this article, I will show how to remove prefixes from the data table. Follow the following procedures.

• Uncheck the option shown in the following picture.
• Then, press the Ok option.

• Then, Excel will show the following table in preview.

• Meanwhile, in the Home tab, select the Close and Load option.

Read More: How to Use Web Scraping with Excel VBA (3 Suitable Examples)

Step 6: Final Result

Here, I will show the final result. Hopefully, you have found interest in the whole procedure.

• At last, you will see all the movie names after the Close & Load command. Here, I’m showing you a segment of the movie list as I cannot fit all of them in one picture.

This is the total process to extract data from multiple web pages into Excel.

Things to Remember

• You should be more careful to follow the entire steps as the steps are lengthy.

Conclusion

In this article, I have tried to explain how to extract data from multiple web pages into Excel. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.

How do I get data from multiple websites in Excel?

Can you scrape data from multiple websites?

Can Excel automatically pull data from a website?

How do I scrape data from a website into Excel?