Do you need to copy data from a website? If you want to import the entire content on a page, you can easily convert the webpage to a PDF document using any browser like Chrome or Safari. What will you do if you want to copy a specific table from a website and work on that? You can import an entire table directly from a webpage to Excel or Google Sheets worksheets.
Let’s find out how to use the table from a website and use it on Excel or Google Sheets.
How to Import Table from Website to Excel
Since Microsoft Excel has a built-in option to import any table from a website, you don’t need to waste time manually copying or typing the data. Let’s see how to quickly import a table from a webpage in Excel.
On your computer, launch Microsoft Excel and open a new file. Next, launch any browser and open the webpage you need to copy data from.
Now, on Excel, click Data from the top toolbar. Next, click From Web icon below the toolbar.
Then, you will see a pop-up window on your screen. Here, enter the URL of the webpage on the provided box and click OK button.
If you see another pop-up screen asking for confirmation for connecting to the web, click the Connect button.
After a few seconds, you will see a new window that lists all the tables/lists on the web page. If you click the table name on the left, you will see its preview on the right. Select the table you want to import and click the Load button.
Can you see the imported table on your Excel file?
Likewise, you can give any webpage URL and get the table and data in a few clicks.
Related: How to Insert Data from a Picture Directly to Excel and Save Your Time
How to Import Table from Website to Google Sheets
Unlike MS Excel, Google Sheets does not have an inbuilt feature for importing data or tables from websites. Instead, you can use the IMPORTHTML formula to easily import the table to Google Sheets from the web. Let’s see how to do that.
Import Data from a Webpage on Computer
On your browser, go to drive.google.com and open a new Google Spreadsheet. Next, open the webpage from which you need to copy data.
In the Google Sheet, click on the empty cell in the first row and enter the following formula.
=IMPORTHTML(url, query, index, locale)
- url – URL of the webpage
- query – the type of data that needs to be extracted (table/list)
- index – identifies the table/list in the HTML source. Index will start from 1. For example, if it is set to 2, it indicates the 2nd table/list on the webpage.
- locale – this optional field is used to specify the language and region code
In this example, we are extracting a table from a Wikipedia page, and the formula for that is:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_iOS_and_iPadOS_devices#iPhone","table",3)
You can either enter the formula directly on the cell or select the function from the menu by selecting Insert > Function > Web > IMPORTHTML.
You can see that the table has been successfully imported to Google Sheets.
You can use the automatic formula correction in Google Sheets in case you encounter any errors with the formulas.
Related: How to Create Realtime Crypto Portfolio Tracker with Google Sheets
Import Data from Website to Google Sheets on Your Phone
Not only on a computer, you can use the Google Sheets app on your smartphone to import data from the web. Let’s see how to easily fetch any table or data from a website to your phone using Google Sheets;
Launch the Google Sheets app on your phone and open a new spreadsheet or tab of an existing sheet.
Next, tap on the empty cell of the first row. Then, tap on the text box at the bottom and the fx icon shown on the bottom left of the screen. (You can also type the formula directly instead of navigating to the menu)
On the Insert function screen, scroll down to the bottom and tap Web. Next, tap IMPORTHTML from the list and fill in the URL, query, and index values.
That’s it. Now, you can see the table imported on the sheets. You can continue working on the table from your phone or your PC using Google Sheets web.
The only drawback of importing data from Google Sheets is you need to provide the index of the table by looking at it manually from the web page. It could be difficult for those with little to no idea about web pages. If you get stuck in such situations, you can use Microsoft Excel to directly select and import a table from a website.