
Google Sheets offers multiple functions for web scraping. IMPORTHTML function is the most useful and straightforward function to import data. This function imports an entire table or list from web pages directly into the spreadsheet. Excel has multiple techniques for web scraping such as Power Query and VBA (Visual Basic Applications). This post will show you how to use the IMPORTHTML function for web scraping while comparing Excel’s web scraping techniques.
What is IMPORTHTML Function?
This function imports an entire table or list within an HTML page. To import data, you will need to specify the URL, the query type (either table or list), and the index of the table/list. If you want, you can use the locale code to specify the language and region importing the data.
Syntax:
=IMPORTHTML(URL, query, index, locale)
- URL: The webpage URL to import data, including protocol (e.g. ‘http://’).
- query: depending on the webpage data structure specify whether to import “table” or “list”.
- index: The index number (starts from 1) identifies which table, or list will be imported from the webpage HTML source.
- Locale [optional]: A language and region locale code to use when parsing the data. If unspecified, the document locale will be used.
Using IMPORTHTML function for Web Scraping
Suppose you want to import the list of major world payment currencies from Wikipedia in the Google spreadsheet. You can use the IMPORTHTML function to import that list from Wikipedia. To do so, select any cell (A1) and use the following formula.
Formula:
=IMPORTHTML(“https://en.wikipedia.org/wiki/Currency”, “table”, 7, “en_Us”)
Explanation:
- “https://en.wikipedia.org/wiki/Currency”: URL of the webpage to import the required data.
- “table”: The type of query is a table so it will import the table from the webpage.
- 7: It’s the index number of the table of the HTML page.
- “en_Us”: locale is English for the United States.
Formula Output:
| Rank | Currency | July 2024 |
| *World* | 100.00% | |
| 1 | United States dollar | 47.81% |
| 2 | Euro | 22.47% |
| 3 | Pound sterling | 7.00% |
| 4 | Chinese renminbi | 4.74% |
| 5 | Japanese yen | 3.62% |
| 6 | Canadian dollar | 2.42% |
| 7 | Australian dollar | 1.80% |
| 8 | Hong Kong dollar | 1.63% |
| 9 | Singapore dollar | 1.34% |
| 10 | Swiss franc | 0.92% |
| 11 | Swedish krona | 0.80% |
| 12 | Polish złoty | 0.70% |
| 13 | Norwegian krone | 0.64% |
| 14 | Thai baht | 0.40% |
| 15 | Danish krone | 0.37% |
| 16 | New Zealand dollar | 0.37% |
| 17 | Mexican peso | 0.31% |
| 18 | South African rand | 0.29% |
| 19 | Philippine peso | 0.28% |
| 20 | Chilean peso | 0.23% |
Example: Import Specific Data from a Table or List while Web Scraping
Consider a scenario where you want to import 10 rows from a webpage but the table has a large number of rows. You can do it by combining the IMPORTHTML with the QUERY function in Google Sheets. The QUERY function will allow you to limit the data by using the Limit clause. Select cell A1 and insert the following formula.
Formula:
=QUERY(IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population”, “table”, 1), “SELECT * LIMIT 10”, 1)
Explanation:
- IMPORHTML: Imports the entire table from the list of countries and dependencies by population webpage of Wikipedia. This table has 242 rows.
- QUERY: Queries the entire table and extracts only 10 rows.
- “SELECT * LIMIT 5”: SELECT clause extracts all columns (*) and the LIMIT clause limits the output to the first 10 rows.
- 1: It specifies the number of headers at the top of the table.
Formula Output:
| Location | Population | % of world | Date | Source (official or from the United Nations) | Notes | |
| * World* | 8,127,721,171 | 100% | 30 Aug 2024 | UN projection[3] | ||
| 1 | China | 1,409,670,000 | 17% | 31 Dec 2023 | Official estimate[5] | [c] |
| 2 | India | 1,404,910,000 | 17% | 1 Jul 2024 | Official projection[6] | [d] |
| 3 | United States | 335,893,238 | 4% | 1 Jan 2024 | Official estimate[7] | [e] |
| 4 | Indonesia | 281,603,800 | 4% | 1 Jul 2024 | National annual projection[8] | |
| 5 | Pakistan | 241,499,431 | 3% | 1 Mar 2023 | 2023 census result[9] | [f] |
| 6 | Nigeria | 223,800,000 | 3% | 1 Jul 2023 | Official projection[10] | |
| 7 | Brazil | 203,080,756 | 3% | 1 Aug 2022 | 2022 census result[11] | |
| 8 | Bangladesh | 169,828,911 | 2% | 14 Jun 2022 | 2022 census result[12] | |
| 9 | Russia | 146,150,789 | 2% | 1 Jan 2024 | Official estimate[13] | [g] |
Excel’s Web Scraping Techniques
Excel offers several basic to advanced techniques for web scraping, primarily through Power Query and VBA. You can use the Power Query to import data from different web pages. It’s easy to use for both basic and advanced users.
Using Power Query for Web Scraping in Excel
It can connect to web pages and import data like IMPORTHTML. However, it has both basic and advanced options that will allow you to decide how you want to import data into the sheet. It offers robust data transformation.
To use Power Query,
- Go to Data tab >> select From Web >> place the URL >> click OK.

Here, an index number is not required. It shows all the tables of the web page you can preview it from both table view and web view.
- Now, select the table from where you want to import the data then click on Load.

Power Query Output:
| Column1 | Column2 | Column3 |
| Rank | Currency | July 2024 |
| World | 100.00% | |
| 1 | United States dollar | 47.81% |
| 2 | Euro | 22.47% |
| 3 | Pound sterling | 7.00% |
| 4 | Chinese renminbi | 4.74% |
| 5 | Japanese yen | 3.62% |
| 6 | Canadian dollar | 2.42% |
| 7 | Australian dollar | 1.80% |
| 8 | Hong Kong dollar | 1.63% |
| 9 | Singapore dollar | 1.34% |
| 10 | Swiss franc | 0.92% |
| 11 | Swedish krona | 0.80% |
| 12 | Polish złoty | 0.70% |
| 13 | Norwegian krone | 0.64% |
| 14 | Thai baht | 0.40% |
| 15 | Danish krone | 0.37% |
| 16 | New Zealand dollar | 0.37% |
| 17 | Mexican peso | 0.31% |
| 18 | South African rand | 0.29% |
| 19 | Philippine peso | 0.28% |
| 20 | Chilean peso | 0.23% |
VBA (Visual Basic for Applications):
VBA will allow you all sorts of customization in web scraping. However, you will need prior knowledge of programming to use VBA.
Conclusion
Google Sheets’ IMPORTHTML is much easier and quicker to use though Excel’s techniques offer more advanced features with the flexibility to import data. IMPORTHTML will be best to import straightforward tables or lists. In complex scenarios, you may use Excel’s techniques for web scraping. Overall, the selection between Google Sheets’ IMPORTHTML and Excel’s web scraping techniques will depend on your needs and the complexity of the webpage data.

Must do the selection of what I need BEFORE clicking Load!