Web Query in Excel
When you select, copy and paste data from a website into an Excel document, you get static images and data. When you want that data to be updated – or refreshed – with the latest information, you create a Web Query in Excel.
Follow the process that I demonstrate in this video and you will learn how to gain access to current information without having to leave Excel!
Create a Web Query
- On the Data tab of the Excel 2007 Ribbon, select Get External Data – From Web.
- In the Dialog Box – that resembles a “Web Browser” – type in the URL of the Website you want to search. Notice that the “Browse” Button is missing. My Best Practice is to make a copy of and paste in the URL of the website you want to search.
- After you press the “GO” Button, you will see some “Yellow Arrow” markers on the website. These are the HTML Tables that you can select for your Web Query.
- When you however your mouse over the Yellow Arrows, you will see a Blue Border around the HTML Table. Click the Yellow Arrow and a “Green Check Mark” indicates that you have selected this table. Continue to make your selections and then click “Import.”
Import Web Query
The final step is to decide where to place your Web Query, I usually accept the default selection of cell $A$1. There is an “Properties” Button that you can access now – or later. Press the “Add” Button and after a few seconds, you have imported the data from your Web Query.
As you will see in the Video for this lesson, it it easy to go back and Edit your Web Query or Change the Properties of the Query – e.g. to automatically Refresh every 60 minutes. You can also choose to Refresh the Query now.
Best Practice Suggestions
- Use a separate session of Excel when setting Properties of your Web Query to automatically refresh. In this way, you will not slow down your work on another Excel project.
- Gather your favorite Website URLs and paste them as Hyperlinks on a Worksheet. Select the Hyperlink prior to initiating a new Web Query. This will save you time as the Web Query Dialog Box does not have a Browse Button!
Watch Video in High Definition
Follow this link to watch this video in High Definition on my YouTube Channel – DannyRocksExcels
Learn How to Master Excel in Minutes – Not Months!
