How to import data from the web in Excel: If you manually copy and paste data from a web page into Excel, you might find that all the data gets pasted into one cell.
That's annoying, and requires more copying and pasting.
So, use the web query tool to save time, and make your worksheets more dynamic!
You can use web query to get refreshable (or static) data from your company's intranet or the internet, like (single or multiple) tables, or all the text on a webpage. Here's How
Just click File > Import External Data > Create New Web Query
Copy the URL of the web page you want to display and paste it into the Address box.
That should be it! Excel will import table data directly, and cleanly into your worksheet. (Web query won't retrieve any photos or graphics)
If you use Internet Explorer, you can also import data to excel directly from your web browser.
Just right click on the webpage, and then select "Export to Microsoft Excel" on the shortcut menu.
This should display the New Web Query dialog box in Excel.
*Web browsers can change all the time, so personally, I prefer method one.
Incorporating data from the web with this feature not only saves time, but it can make your worksheets more dynamic.
The next time you find a table of useful data online, try incorporating it into an Excel worksheet with the web query tool!
Missy Woodruff is currently a Junior Staff Accountant, and enjoys blogging about tax deductions at MightyTaxes.com. She is happiest while listening to the Hamilton soundtrack while travelling with her family and dog, Zoe.
I have created spreadsheets to automatically list and sort daily updated data from the web, automatically refreshing whenever the workbook is opened.
Some suppliers display categories over a few pages (e.g. the address ending in "&page=2", "&page=3" etc.)
I formulated the workbook to collect data from a certain number of pages, but find that if any page has been removed, an error message "Unable to open [web address].
Internet site reports that the item you requested could not be found"
is displayed and the update will not continue until "OK" is clicked.
Is there a way to override this, so the workbook will just record "no data" for the missing page(s) and continue collecting the rest of the data from the other pages?