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.

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

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.

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.

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.

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 to FetchMovies.
• Then, write the following custom column formula.

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

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.

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

Download Practice Workbook

Please download the workbook to practice yourself.

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