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

Chapter 2

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.

Chapter 3

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.

Chapter 4

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.

Chapter 5
Chapter 6

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.

Chapter 7
Chapter 8

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.

Chapter 9
Chapter 10

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.

Chapter 11

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.

More Leila Gharani summaries

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.