Excel Web Scraping Tutorial: Import Data, Images & Links Easily. Absolutely Zero Coding Required!
Leila Gharani
14 min, 20 sec
A comprehensive guide on using Excel's upgraded Web Connector to import data from a web page into Excel, making the process easier and dynamic.
Summary
- Excel's Web Connector can now import data from web pages without HTML tables, including text, links, and images.
- The process involves using Power Query's 'Add Table Using Examples' feature to define the data pattern to extract.
- A custom table is created in Excel that dynamically updates with web page content changes.
- The HYPERLINK and IMAGE functions in Excel are used to make links clickable and to display images directly in the spreadsheet.
- The process is tested by adding a new course block to the web page and verifying its dynamic addition to the Excel table.
Chapter 1
![Introduction to the capabilities of the upgraded Excel Web Connector for importing web data.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTAsInB1ciI6ImJsb2JfaWQifX0=--f3e9d926fc588ce73d75458ca239ed3e1b072e63/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_12.jpg)
Introduction to the capabilities of the upgraded Excel Web Connector for importing web data.
- Excel's Web Connector has been upgraded to make importing data from the web much easier.
- The new functionality is described as a game-changer for extracting web page data into Excel.
![Introduction to the capabilities of the upgraded Excel Web Connector for importing web data.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTAsInB1ciI6ImJsb2JfaWQifX0=--f3e9d926fc588ce73d75458ca239ed3e1b072e63/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_12.jpg)
Chapter 2
![Discussion of the difficulties in manually extracting data from web pages without HTML tables.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTIsInB1ciI6ImJsb2JfaWQifX0=--2ea9068f2216ec689a6d3a045744fd400b986825/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_53.jpg)
Discussion of the difficulties in manually extracting data from web pages without HTML tables.
- Extracting data from web pages with clickable blocks and no HTML tables presents challenges.
- Manually copying and formatting the data into Excel, especially with links, is tedious.
![Discussion of the difficulties in manually extracting data from web pages without HTML tables.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTIsInB1ciI6ImJsb2JfaWQifX0=--2ea9068f2216ec689a6d3a045744fd400b986825/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_53.jpg)
Chapter 3
![An overview of how the Power Query Connector simplifies data extraction from web pages.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTQsInB1ciI6ImJsb2JfaWQifX0=--5ca921d6f952060f7f5655b11cbc3dbc25d461da/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_102.jpg)
An overview of how the Power Query Connector simplifies data extraction from web pages.
- The Power Query Connector eliminates the manual workload by dynamically importing web data into Excel.
- Updated web content can be refreshed in Excel via the Power Query, bringing in new links and images.
![An overview of how the Power Query Connector simplifies data extraction from web pages.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTQsInB1ciI6ImJsb2JfaWQifX0=--5ca921d6f952060f7f5655b11cbc3dbc25d461da/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_102.jpg)
Chapter 4
![Step-by-step process of setting up the Power Query Connector to import web data into Excel.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTYsInB1ciI6ImJsb2JfaWQifX0=--bc58385cc5a34b8d2bba841b7a9af2c298f432b4/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_158.jpg)
Step-by-step process of setting up the Power Query Connector to import web data into Excel.
- Copy the URL of the web page and paste it into Excel's Data > From Web option to start the import process.
- Power Query automatically suggests tables based on identified patterns on the web page.
![Step-by-step process of setting up the Power Query Connector to import web data into Excel.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk4OTYsInB1ciI6ImJsb2JfaWQifX0=--bc58385cc5a34b8d2bba841b7a9af2c298f432b4/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_158.jpg)
Chapter 5
Chapter 6
![Using the 'Add Table Using Examples' feature to precisely extract the desired web data.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MDAsInB1ciI6ImJsb2JfaWQifX0=--f9ded7f346966490f609b5117ffe98ebaa113070/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_288.jpg)
Using the 'Add Table Using Examples' feature to precisely extract the desired web data.
- To capture specific data like course links and images, 'Add Table Using Examples' is used.
- By providing examples, Power Query learns to pick up the pattern and create a custom table.
![Using the 'Add Table Using Examples' feature to precisely extract the desired web data.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MDAsInB1ciI6ImJsb2JfaWQifX0=--f9ded7f346966490f609b5117ffe98ebaa113070/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_288.jpg)
Chapter 7
Chapter 8
![Formatting the imported data in Excel for usability, including clickable links and visible images.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MDQsInB1ciI6ImJsb2JfaWQifX0=--0d6769844eeecf32eff9ff83511ad6319affa165/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_470.jpg)
Formatting the imported data in Excel for usability, including clickable links and visible images.
- The initial data import into Excel does not have clickable links or visible images.
- Using the HYPERLINK and IMAGE functions makes links clickable and displays images in Excel.
![Formatting the imported data in Excel for usability, including clickable links and visible images.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MDQsInB1ciI6ImJsb2JfaWQifX0=--0d6769844eeecf32eff9ff83511ad6319affa165/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_470.jpg)
Chapter 9
Chapter 10
![Testing the dynamic update feature of the Power Query table by adding a new web page block.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MDgsInB1ciI6ImJsb2JfaWQifX0=--41e002e8093499e7dfd8709d9338ae7e4e877258/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_719.jpg)
Testing the dynamic update feature of the Power Query table by adding a new web page block.
- A new course block is added to the web page to test the dynamic update functionality.
- Upon refreshing the Power Query table, the new block with a clickable link and image appears in Excel.
![Testing the dynamic update feature of the Power Query table by adding a new web page block.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MDgsInB1ciI6ImJsb2JfaWQifX0=--41e002e8093499e7dfd8709d9338ae7e4e877258/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_719.jpg)
Chapter 11
![Wrapping up the tutorial and promoting the 'Master Power Query Beginner to Pro' course.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MTAsInB1ciI6ImJsb2JfaWQifX0=--229671bcefa2d61cd4d77b8ccb95616b13f67299/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_820.jpg)
Wrapping up the tutorial and promoting the 'Master Power Query Beginner to Pro' course.
- The tutorial concludes by highlighting the power of Power Query for Excel data manipulation.
- The 'Master Power Query Beginner to Pro' course is promoted for viewers interested in learning more.
![Wrapping up the tutorial and promoting the 'Master Power Query Beginner to Pro' course.](https://www.videogist.co/rails/active_storage/representations/redirect/eyJfcmFpbHMiOnsiZGF0YSI6NTk5MTAsInB1ciI6ImJsb2JfaWQifX0=--229671bcefa2d61cd4d77b8ccb95616b13f67299/eyJfcmFpbHMiOnsiZGF0YSI6eyJmb3JtYXQiOiJqcGciLCJyZXNpemVfdG9fbGltaXQiOls3MjAsbnVsbF19LCJwdXIiOiJ2YXJpYXRpb24ifX0=--c9426325207613fdd890ee7713353fad711030c7/3569_820.jpg)
More Leila Gharani summaries
![Don't Use ChatGPT Until You Watch This Video](https://www.videogist.co/rails/active_storage/blobs/redirect/eyJfcmFpbHMiOnsiZGF0YSI6MTUxMjc5LCJwdXIiOiJibG9iX2lkIn19--dbd42eaf5a3cd93fec61b21aab6c6c97d9df5648/hqdefault.jpg)
Don't Use ChatGPT Until You Watch This Video
Leila Gharani
The video provides detailed strategies for crafting effective prompts to optimize interactions with ChatGPT.