Ever had to regularly go through the laborious process of manually entering data from an online data source? Then try using the powerful Web Query feature in Excel that enables you to automate this task. Read on to learn how.


STEP 1: Create a Web query In order to create a new Web query, you must first be connected to the Internet. Then, open the Excel Worksheet where you would want to see your imported data and follow these steps to create a new Web query. Click on the "Data" tab and then go to "Get External Data I From Web". In the "New Web Query" dialog box that appears, type in the website that you wish to import the data from in the "Address" field. Scroll through the website to select a particular table of your choice. Click on the yellow box with the black arrow icon next to the tables you wish to select.

STEP:- 2
Depending on your needs, you can get data from a Web page and keep it static on the worksheet or you can enter data in your worksheet that will be updated with the latest data on the Web page, Follow these steps to refresh your data: Click on the "Data" tab and then go to "Refresh AUI Refresh AU"to refresh all the data on your worksheet. Select "Refresh AUI Refresh" to refresh only a selected portion of your worksheet.

To set the time for automatic refreshing of your data, go to "Refresh AUI Connection Properties". In the "Connection Properties" dialog box that opens, under the "Refresh control" section, check mark the "Refresh every" check box, Also check mark the "Refresh data when opening the file" option, Enter the interval at which you want your data to be retrieved from the Web, For instance, you can have your data refreshed every 10 minutes, Click "OK"

STEP 3: Conditionally format the sheet With conditional formatting, the greatest benefit is that it allows viewers to easily identify the data at one glance. In case you are dealing with currency exchange and want to seize the best chance for doing so, here's how you can use conditional formatting to check for a particular surge in a currency rate.

Select the cell that you wish to format. Depending on your condition, select the option of your choice from the drop-down list. For example, if you want to see when there is a change in currency cross rate of the UK £ with the US $ as value of 1.95 and above.Then click on the "Home" tab and then go to"Conditional Formatting I Highlight cell Rules". In this case, click on the "Greater than" option.

In the "Greater than" dialog box, enter the cross rate value" 95".options from the available drop-down list to format the cell according to your choice when it meets the condition.
The "Conditional Formatting" feature can also be used to format the cross rate if in case it does not meet the expected value in the cell, using the "Lower than" option. The cell which has been conditionally formatted ishighlighted because it met the condition requested. The formatting is applied even after the data is refreshed.