IMPORTHTML for web tables in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at an e-commerce company.

📋 Request: Your manager wants you to track the latest product prices from a competitor's website to compare with your own prices.

📊 Data: You have access to the competitor's public webpage that lists their products and prices in a table format.

🎯 Deliverable: Create a Google Sheet that automatically imports the competitor's product table from their website and displays it for easy comparison.

Sample Data

Product	Price	Availability
Wireless Mouse	$25	In Stock
Bluetooth Headphones	$45	Out of Stock
USB-C Charger	$30	In Stock
Webcam HD	$60	In Stock
Gaming Keyboard	$80	Limited Stock

1

Step 1: Open a new Google Sheet to start your analysis.

Expected Result

A blank Google Sheet ready for data import.

2

Step 2: Identify the URL of the competitor's webpage that contains the product table.

Example URL: "https://example.com/competitor-products"

Expected Result

You have the exact webpage URL to use in the formula.

3

Step 3: Use the IMPORTHTML function to import the product table from the webpage.

=IMPORTHTML("https://example.com/competitor-products", "table", 1)

Expected Result

The first table from the webpage is imported into your sheet, showing product names, prices, and availability.

4

Step 4: Check that the imported data matches the competitor's product list and prices.

Expected Result

You see a table with columns Product, Price, and Availability matching the competitor's webpage.

5

Step 5: Format the imported data for better readability: bold headers, adjust column widths.

Expected Result

The table looks neat and easy to read.

6

Step 6: Set up a comparison column next to the imported prices to enter your own prices for each product.

In column D, enter your prices manually for each product.

Expected Result

You have a side-by-side view of competitor prices and your prices.

7

Step 7: Create a formula to calculate the price difference between your price and the competitor's price.

=VALUE(SUBSTITUTE(B2, "$", "")) - D2

Expected Result

A new column shows the numeric difference between competitor price and your price for each product.

Final Result

Product               | Price | Availability  | Your Price | Price Difference
----------------------+-------+---------------+------------+-----------------
Wireless Mouse        | $25   | In Stock     | $23        | 2
Bluetooth Headphones  | $45   | Out of Stock | $50        | -5
USB-C Charger         | $30   | In Stock     | $28        | 2
Webcam HD             | $60   | In Stock     | $65        | -5
Gaming Keyboard       | $80   | Limited Stock| $75        | 5

✓You can see which products you price higher or lower than the competitor.

✓The IMPORTHTML function keeps your sheet updated automatically when the competitor updates their webpage.

✓Price differences help identify where you can adjust prices to stay competitive.

Bonus Challenge

Create a conditional formatting rule to highlight products where your price is higher than the competitor's price.

Show Hint

Use a custom formula like =E2>0 to format cells in the Price Difference column with positive values.