You can get stock and geographic data in Excel. It's as easy as typing text into a cell, and converting it to the Stocks data type, or the Geography data type. These two data types are new, and they are considered linked data types because they have a connection to an online data source. That connection allows you to bring back rich, interesting information that you can work with and refresh.
Note: March 28, 2019: Data types have been released to all Windows Office 365 subscribers. If you are an Office 365 subscriber, make sure you have the latest version of Office.
Stocks
In the picture above, the cells with company names in column A contain the Stocks data type. You know this because they have this icon: . The Stocks data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for price, and change in price are getting extracted from the Stocks data type in column A.
Geography
In this example, column A contains cells that have the Geography data type. The icon indicates this. This data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for population, and gasoline price are getting extracted from the Geography data type in column A.
- Type some text in cells. If you want stock information, type a ticker symbol, company name, or fund name into each cell. If you want geographic data, type a country, province, territory, or city name into each cell.
- Then select the cells.
- Although it's not required, we recommend creating an Excel table. Later on, this will make extracting online information easier. To create a table, go to Insert > Table.
- With the cells still selected, go to the Data tab, and then click either Stocks or Geography.
- If Excel finds a match between the text in the cells, and our online sources, it will convert your text to either the Stocks data type or Geography data type. You'll know they're converted if they have this icon for stocks: and this icon for geography:
- Select one or more cells with the data type, and the Insert Data button will appear. Click that button, and then click a field name to extract more information. For example, for stocks you might pick Price and for Geography you might pick Population.
- Click the Insert Data button again to add more fields. If you're using a table, here's a tip: Type a field name in the header row. For example, type Change in the header row for stocks, and the change in price column will appear.
Note: If you see instead of an icon, then Excel is having a hard time matching your text with data in our online sources. Correct any spelling mistakes and when you press Enter, Excel will do its best to find matching information. Or, click and a selector pane will appear. Search for data using a keyword or two, choose the data you want, and then click Select.
More you can do
Discover more data with cards
After you convert text into the Stocks or Geography data types, an icon will appear in the cell. Click the icon to see the card. The card reveals a list of fields and corresponding values. Depending on the data, there could be numerous field/value pairs that you can see and work with.
For example, in this picture the card for France is shown. Capital is one of the fields available for France. And Paris is the value for that field. Leader(s) is another field, and the leader names are the values.
If you want to see more field/value pairs, scroll down inside the card.
If you're curious where the fields and values are coming from, you'll notice the 'Powered by' note at the bottom of the card.
Add fields with cards
You can also add fields from cards. With the card open, rest your cursor over a field. Then click the Extract button .
Stock Opname Perpustakaan
Write formulas that refer to data types
It is also possible to write formulas that use the values from the Stocks or Geography data types. This can be helpful if your data is not in a table. For example, type =A2 and then Excel's AutoComplete menu will appear, showing you the available fields for 'France.' You can also type a 'dot', for example: =A2. and that will show the menu as well. For more information, see How to write formulas that reference data types.
Tips and tricks
A quick way to open a card
For Windows, press Ctrl+Shift+F5.
For Mac, press Cmd+Shift+F5.
A quick way to add a column
Press Alt+Shift+F10 to open the Insert Data menu. You can also press the Ctrl key. Then use your arrow keys to move to a field, and press Enter to choose a field.
How to refresh the data
The Stocks and Geography data types are considered linked data types. This is because they refer to an online data source. Once you convert text to a linked data type, an external data connection is established in the workbook. That way, if the data changes online, you can update it by refreshing it in Excel. To refresh the data, right-click a cell with the linked data type and click Data Type > Refresh. That will refresh the cell you selected, plus any other cells that have that same data type.
If you want to refresh all linked data types and all data connections that may be in the workbook (including queries, other data connections, and PivotTables), click Data > Refresh All or press Ctrl+Alt+F5.
Can I unlink the cells?
Yes. Select a cell with a linked data type, then press CTRL+C to copy it. Press CTRL+ALT+V, and then choose Values. This will paste the data type into the new cell, but the data won't be refreshable.
Switch out the data
You can switch out the information for a Stock or Geography data type. Just right-click the cell, and click Data Type > Change... A pane will appear on the right. Search for the data you want, and then click Select to put that information in place of the original one.
Remove a data type
If you don't want the Stocks or Geography data type anymore, just right-click the cell, and click Data Type > Convert to Text. The data type is removed, there's no longer an online connection, and the value in the cell is converted to text. Keep in mind that if you convert the data type to text, any column or formulas that had extracted values from that data type will display the #FIELD! error.
Sorting and filtering tip
You can always sort and filter data in Excel by using the filter buttons on the column headers. (Turn on filter buttons by clicking Data > Filter.)
But here's a tip for cells with data types: Click the filter button above the cells with icons. Then decide how you want to sort or filter. If you want to do so using the displayed name, keep Display Value selected under Select field. If you want to sort or filter by another field from the linked data type, select that field under Select field.
In this example, we selected the field Area. Excel then used that as a basis for the filter check boxes below, and displays the populations for the countries we have in the table. Also good to know: Excel will change the Number Filter menus depending on the field you select under Select field.
Frequently asked questions
Are there other data types?
Excel has always had data types. But they've been traditional ones like text, number, and so on. The Stocks and Geography data types are unique because they are considered linked data types. Linked data types contain a connection to an online data source. At this time, Stocks and Geography are the only linked data types. We plan to support more in the future, based on customer feedback.
If I send a workbook to my coworker or friend, will these new data types work for them?
Yes, but they need to use Excel 2016 for Office 365 subscribers.
Note: This feature is being made available to customers on a gradual basis over several days or weeks. It will first be available to Office Insider participants, and later to Office 365 subscribers. If you are an Office 365 subscriber, make sure you have the latest version of Office.
Will this work in other versions of Excel?
At this time, only Excel 2016 for Office 365 subscribers supports the Stocks and Geography data types. Other versions of Excel may eventually support these data types, as we are continually improving Excel. If you open these new data types in a version of Excel that doesn't support them, #VALUE! will appear for those cells. And #NAME? will appear in place of any formulas that reference these data types.
Does the fill handle work with data types?
You cannot select a cell with a data type, and drag the fill handle down to link up text that's not already a data type. The same thing goes for the Format Painter. However, if you are using these data types in a table, you can type under the last row of the table. When you press Enter, Excel will automatically attempt to match your text with online data, and convert it into a data type. It will also do this if you aren't using a table. Just make sure the cell you are typing in has two or more cells above it that already use a data type.
Are there any other limitations to know about?
The Stocks and Geography data types are very new, and there are some traditional Excel features that will not function well with them. For example, if you try to create a PivotTable based on information from these data types, you'll get a #VALUE! error. And, using these data types with Power Pivot, Power Query, or even some charts may not work as expected.
Conflict desert storm download windows 7. Another example is with map charts. If you try to create a map chart based on data from the Geography data type, the map might not display correctly. We are working to improve this issue. In the meantime, please refer to the map chart article for more information.
Which languages are these data types available in?
At this time, these data types are only available if you have the English editing language added to your Office Language Preferences. We hope to support more languages in the future.
See also
Microsoft Excel is a spreadsheet program that is part of the Microsoft Office productivity suite. While its primary use lies in storing and comparing data in a tabular format, it provides some other useful tools as well, such as the ability to import data from other locations. Fortunately, Excel is compatible with a variety of different file types that can easily be imported into the Excel file format structure.
Download Data from the Web to Excel 2003
Step 1
Step 2
Select the 'Data' option from the toolbar at the top of the screen, select 'Import External Data,' and then select 'New Web Query.'
Step 3
Type the URL of your desired Web page into the text field next to 'Address' at the top of the window, then click the 'Go' button.
Step 4
Click the yellow arrow(s) located next to the information that you wish to download into Excel.
Step 5
Select the 'Import' button at the bottom of the window once all your required data has been downloaded.
Select the location on your worksheet into which the data will be downloaded, then click the 'OK' button.
Download Data from the Web to Excel 2007
Step 1
Step 2
Click the 'Data' tab at the top of the screen, then select the 'From Web' button in the 'External Data' section.
Step 3
Type the URL of your desired Web page into the text field next to 'Address' at the top of the window, then click the 'Go' button.
Step 4
Click the yellow arrow(s) located next to the information that you wish to download into Excel, then select the 'Import' button at the bottom of the window once all your required data has been downloaded.
Select the location on your worksheet into which the data will be downloaded, then click the 'OK' button.
Tip
- After you import your Web page data, an 'External Data' toolbar will open. From this menu, it is possible to edit your Web query, change any of your data properties or refresh the information that you have downloaded.
Video of the Day
Brought to you by Techwalla
More Articles
To make a web page query in Excel 2016, you click the From Web command button on the Data tab of the Ribbon or press Alt+AFW. Excel then opens the New Web Query dialog box containing the Home page for your computer’s default web browser (Internet Explorer 10 in most cases). To select the web page containing the data you want to import into Excel, you can:
- Type the URL web address in the Address text box at the top of the Home page in the New Web Query dialog box.
- Use the Search feature offered on the Home page or its links to find the web page containing the data you wish to import.
When you have the web page containing the data you want to import displayed in the New Web Query dialog box, Excel indicates which tables of information you can import from the web page into the worksheet by adding a yellow box with an arrowhead pointing right. To import these tables, you simply click this box to add a check mark to it.
Selecting the table of data to import on the Yahoo! Finance web page.
After you finish checking all the tables you want to import on the page, click the Import button to close the New Web Query dialog box. Excel then opens a version of the Import Data dialog box with only the Table option available where you can indicate where the table data is to be imported by selecting one of these options:
- Existing Worksheet (default) to have the data in the Access data table imported into the current worksheet starting at the current cell address listed in the text box
- New Worksheet to have the data in the Access data table imported into a new sheet that’s added to the beginning of the workbook
After you click OK in the Import Data dialog box, Excel closes the dialog box and then imports all the tables of data you selected on the web page into a new worksheet starting at cell A1 or in the existing worksheet starting at the cell whose address was entered in the text box in the Import Data dialog box.
Excel brings this data from the Volume Leaders table on the Yahoo! Finance web page into the worksheet as cell ranges rather than as an Excel Table. If you then want to be able to sort or filter this imported financial data, you need to select one of its cells and then select the Format as Table button on the Home tab to format them and add the requisite AutoFilter buttons. When formatting data as a table, you remove all external connections to the data on the website.
You can make web queries only when your computer has Internet access. Therefore, if you’re using Excel 2016 on a portable device that can’t currently connect to the web, you won’t be able to perform a new web query until you’re in a place where you can connect.