Microsoft has always used data types in Excel such as text, number, et cetera to help you build complex data models in spreadsheets. But one of the key benefits of having the cloud-powered version of Excel in Microsoft Office 365 is your ability to leverage the global data stored in the Microsoft Cloud in real time.
Amaxra blogged about this productivity-enhancing ability in the past with Map Charts in Excel. Much like that feature, the October 2018 update of Excel in Office 365 enables you to pull real-time and constantly updated financial market and geographic data from the cloud into your spreadsheet. If you’re like me, then you are already thinking of all the ways that this new feature is going to change the way you look at your business data.
Uses for real-time data in Excel for Office 365
If your organization has Office 365, then you’ll notice an option under the Data tab in Excel for “Stocks and Geography.” Unlike data types such as numbers that can be modified to operate as percentages, dates, or currency, the new “Stocks and Geography” data types are automatically linked to online sources. This means that as long as your device has a connection to the internet, the data inside any cells marked with either the Stocks or Geography data type will be automatically updated in real time with the latest data delivered by Microsoft’s extensive global cloud infrastructure.
The near-ubiquity of Excel in business and its relative ease of use compared to other complex data modeling software opens up infinite possibilities for all kinds of organizations. One practical application for the Stocks data type would be for financial analysts to create an Excel workbook that updates a stock watchlist showing current prices and the changes expressed as a percentage in real time all in less than ten minutes. Academics and actuaries can use the Geography data type to build predictive models around population density and commodity prices using Excel in a few minutes rather than contracting a software design firm to build a custom solution. And when you upload these Excel spreadsheets on your team SharePoint site in Office 365, you can securely give or limit who in your organization has access to the cloud-powered data. It really is that simple!
Although not all Office 365 users have received the new update (Amaxra as a certified Gold-level Partner of Microsoft actively participates in the Office Insiders program to see previews of upcoming feature enhancements), the updates are coming and should be available to Office 365 Business users by the end of October 2018. If you’re already on the latest update, then here’s how you can try out the new linked data feature in just three easy steps:
1) Create a new table in Excel
In this example we’re using country names “France,” “Spain,” and “Sweden” to pull Geography data about each country’s population. We’ve chosen to use the country names, but this feature also works if you use the names of cities, provinces/states, etc. To try this example with Stock information, you can type in either the full company names, their ticker symbols, or even the name of funds to get started. Once you have the names of the countries typed into the cells, select those cells then click on the Insert tab in the Excel ribbon and click on the Table icon:
2) Assign a linked online data type in Excel
Select the cells that you want to convert to a data type. For our example, we want to get population data for the specified countries. So, we select cells A2-A4 in our Excel spreadsheet, then go the Data tab in the ribbon and click the Geography icon:
3) Add a new column to get real-time online data
Excel finds a match between the text in the cells and the online data, adding an icon next to the text to let you know that Office 365 has automatically linked your text to cloud-based information. In our “Geography” example, you can see that each of the names of the countries in cells A2-A4 now contains a map icon. If you see a “?” rather than the appropriate icon, then Office 365 is having trouble finding data that matches your text. Click the “?” icon and a search box will appear, enabling your to type in some keywords to help you get the data you want.
Click the Add Column button to select the real-time online data you want to add in Column B that gives context to your data in Column A. In our example, we want to find out more information about the population in each of the countries we listed, so we scroll down and click Population from the menu:
Limitations to linked data in Excel for Office 365
While testing the Stocks and Geography data types in Excel during the Office Insiders preview, Amaxra did notice some minor limitations to the feature. For example, we could never create a PivotTable based on information from either of the new data types without seeing a #VALUE! error in the cells. We attributed it to the fact that it’s a new feature designed exclusively for cloud-powered Office 365 deployments and as such there would just be some legacy Excel features that wouldn’t play nice with them. It’s a given that Microsoft will eventually come up with a solution, so none of the Excel experts here at Amaxra are too worried about it.
Linked data types in Excel for Office 365 is an innovation that every business—from the one-person consultant all the way up to global enterprises—can leverage to make the best data-driven strategic decisions. From now on, Excel users will never waste time having to refresh data in a workbook because their data will automatically refresh. It’s a new way of thinking but Amaxra consultants can assist you in creating tables like this to take your business to a new level.