- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Using Web Queries to Get Data onto a Sheet
A web query allows you to link a range to text from a web page. As the web page updates, the data on the sheet also updates. To retrieve data from a web page, follow these steps:
- Go to Data, Get External Data, From Web. The New Web Query dialog box opens.
- From the Address field at the top, navigate to the desired web page and it will load. If Excel is able to retrieve data from the web page, a yellow box with a black arrow appears near the data.
- Place your cursor over the box and a frame appears around the data that box is tied to. Not all areas of a web page are retrievable. If you find a box that has the data you want, click the box and it turns into a green box with a black check mark.
- Select as many sections as you need, then click the Import button at the bottom of the dialog box.
- From the Import Data dialog box, select the cell where you want the data to appear. If you don’t like the current cell address shown, you can click on the sheet and the dialog box updates with a new sheet address. Click OK.
- After a few seconds, depending on your Internet connection, the data appears on the sheet.
By default, the data refreshes every 60 minutes. You can refresh manually by clicking Data, Connections, and from the Refresh All drop-down, select Refresh. Or you can configure the automatic refresh time by going to Data, Connections, Properties or right-clicking the data and selecting Data Range Properties. Either way opens the External Data Range Properties dialog box where you can set the refresh time by selecting Refresh Every x Minutes and changing the value in the field, as shown in Figure 3.11.
Figure 3.11. Use a web query to return an automatically updating summary of stock information for MSFT.