Automate your workflow with Nanonets
Request a demo Get Started

There are over 2 Billion websites and over 50 billion web pages on the internet. They all contain information in different formats: text, video, images, or tables.

If you ever want to scrape data from a website to Excel, copy-pasting the webpage content is the easiest option. But it’s not the best way to do that, as the data would not be formatted properly. (The time spent in making the data usable can be considerable).

That’s where web scraping comes in. Web scraping converts unstructured website data into a structured Excel format in seconds, saving you time and effort.

This blog will explore three ways to scrape data from websites and download it to Excel. Whether you're a business owner, analyst, or data enthusiast, this blog will provide the tools that effectively scrape data from a website and turn it into valuable insights.


Three ways to scrape data from a website to Excel

We will deep dive into the three ways to scrape data from a website to Excel.

Using an automated web scraping tool

If you want to scrap data from a website to Excel automatically and instantly, try a no-code tool like Nanonets website scraper. This free web scraping tool can instantly scrape website data and convert it into an Excel format. Nanonets can also automate web scraping processes to remove any manual effort.

Here are three steps to scrape website data to Excel automatically using Nanonets:

Step 1: Head to Nanonets' website scraping tool and insert your URL.

Scrape data from website to Excel with Nanonets
Scrape data from website to Excel with Nanonets

Step 2: Click on 'Scrape and Download'.

Click on Scrape and Download to start web scraping
Click on Scrape and Download to start web scraping

Step 3: Once done, the tool downloads the Excel file with the scraped website data automatically.


Scrape data from Websites to Excel with Nanonets™ Website Scraping Tool for free.


Using Excel VBA

Excel VBA is powerful and can easily automate complex tasks, such as website scraping to Excel. Let’s see how to use it to scrape a website to Excel.

Step 1: Open Excel and create a new workbook.

Step 2: Open the Visual Basic Editor (VBE) by pressing Alt + F11.

Step 3: In the VBE, go to Insert -> Module to create a new module.

Step 4: Copy and paste the following code into the module:

Sub ScrapeWebsite()
'Declare variables
Dim objHTTP As New WinHttp.WinHttpRequest
Dim htmlDoc As New HTMLDocument
Dim htmlElement As IHTMLElement
Dim i As Integer
Dim url As String
'Set the URL to be scraped
url = "https://www.example.com"
'Make a request to the URL
objHTTP.Open "GET", url, False
objHTTP.send
'Parse the HTML response
htmlDoc.body.innerHTML = objHTTP.responseText
'Loop through the HTML elements and extract data
For Each htmlElement In htmlDoc.getElementsByTagName("td")
'Do something with the data, e.g. print it to the Immediate window
Debug.Print htmlElement.innerText
Next htmlElement
End Sub

Excel Module for Website Scraping

Step 5: Modify the URL in the code to the website you want to scrape in the Excel workbook.

Step 6: Run the macro by pressing F5 or clicking the "Run" button in the VBE toolbar.

Step 7: Check the Immediate window (View -> Immediate Window) to see the scraped data.

The website data should have been scraped into the Excel workbook.


What should you consider while using VBA to scrape data from a webpage?

While Excel VBA is a potent tool for web scraping, there are several drawbacks to consider:

  • Complexity: VBA can be complex for non-coders. This makes it difficult to troubleshoot issues.
  • Limited features: VBA can extract limited data types. It can’t extract data from complex HTML structures.
  • Speed: Excel VBA can be slow while scraping large websites.
  • IP Blocking Risks: There is always a risk of IP getting blocked when scraping large data websites.
💡
Overall, while VBA can be a useful tool for web scraping, it is important to consider the above drawbacks and weigh the pros and cons before using it for a particular scraping project.

Looking to scrape data from websites? Try Nanonets™ Website Scraping Tool for free and quickly scrape data from any website.


Using Excel Web Queries

Excel web queries can scrape website data easily. It imports web pages as text files into Excel. Let’s see how to use Excel web query to scrape web pages to Excel.

Step 1: Create a new Workbook.

Step 2: Click on the Data tab at the top. Then click on the “Get & Transform Data” section and then click on “From Web.”

Step 3: Enter the URL in the "From Web" dialog box.

Step 4: Click the "OK" button to load the webpage into the "Navigator" window.

Step 5: Select the table or data you want to scrape by checking the box next to it.

Step 6: Click the "Load" button to load the selected data into a new worksheet.

Step 7: Repeat the above steps to scrape additional tables or data from the same webpage.

Step 8: To refresh the data, right-click on the data in the worksheet and select "Refresh."

Scrape website data using Excel web queries

What are the drawbacks of using Excel web query to extract webpage data to Excel?

  • Web queries can’t scrape data from dynamic webpages or webpages with complex HTML structures.
  • Web queries rely on the webpage's HTML structure. If it changes, the web query may fail or extract incorrect data.
  • Web queries can extract unformatted data. For example, data may be extracted as text instead of a number or date.

While discussing data extraction techniques, it's crucial to streamline the entire data journey, from scraping to analysis. This is where Nanonets' Workflow Automation comes into play, revolutionizing how teams operate. Imagine seamlessly integrating scraped data into complex workflows within minutes, using AI to enhance tasks, and even involving human validation for precision. With Nanonets, you can connect the dots from data gathering to actionable insights, making your processes more efficient and your decisions smarter. Learn more about transforming your operations at Nanonets' Workflow Automation.

Learn More

Automating webpage data extraction

Excel tools like VBA and web query can extract webpage data, but they often fail for complex webpage structures or might not be the best choice if you have to extract multiple pages daily. Pasting the URL, checking the extracted data, cleaning it, and storing it requires a lot of manual effort, particularly when this web scraping task must be repeated manually.

Platforms like Nanonets can help you automate the entire process in a few clicks. You can upload the list of URLs into the platform. Nanonets will save tons of your time by automatically:

  • Extracting data from the webpage - Nanonets can extract data from any webpage or headless webpages with complex HTML structures.
  • Structuring the data - Nanonets can identify HTML structures and format the data to retain table structures, fonts, etc., so you don’t have to.
  • Performing Data cleaning - Nanonets can replace missing data points, format dates, replace currency symbols, or more in seconds using automated workflows.
  • Exporting the data to a database of your choice - You can export the extracted data to Google Sheets, Excel, Sharepoint, CRM, or any other database you choose.

If you have any requirements, you can contact our team, who will help you set up automated workflows to automate every part of the web scraping process.


Eliminate bottlenecks caused by manually scraping data from websites. Find out how Nanonets can help you scrape data from websites automatically.