Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Extract Data from Multiple Web Pages into Excel

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.


Download Practice Workbook

Please download the workbook to practice yourself.


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.

dataset to extract data from multiple web pages into excel


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.

Connecting to web to extract data from multiple web pages into excel

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

Connecting to web to extract data from multiple web pages into excel

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

Connecting to web to extract data from multiple web pages into excel

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.

Open the Power query to extract data from multiple web pages into excel

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

Open the Power query to extract data from multiple web pages into excel

  • Then, change the name to Movies.

Open the Power query to extract data from multiple web pages into excel

  • 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.

Open the Power query to extract data from multiple web pages into excel

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

Open the Power query to extract data from multiple web pages into excel

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

Open the Power query to extract data from multiple web pages into excel

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, rightclick on the option.
  • After that, select the Edit option.

Edit query to extract data from multiple web pages into excel

  • After that, select the Advanced Editor option.

Edit query to extract data from multiple web pages into excel

  • You will find the following window.

Edit query to extract data from multiple web pages into excel

  • 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"

Edit query to extract data from multiple web pages into excel

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

Edit query to extract data from multiple web pages into excel

  • Change the name of the function to fxMovies1.

Edit query to extract data from multiple web pages into excel

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

Edit query to extract data from multiple web pages into excel

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

Edit query to extract data from multiple web pages into excel

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 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.


Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo