Artificial Intelligence in Office 365 – Part 2: Microsoft Excel
Who thinks, Artificial Intelligence AI is still a distant future technology, is wrong. AI is being used in more and more everyday areas, including in the Office 365 Suite. This means that completely new functions are suddenly available. O365 is at home in Microsoft's Azure cloud and uses AI services such as Azure Machine Learning or capabilities from the Cognitive Toolkit. In a multi-part series, we show how artificial intelligence in Office 365 supports people and companies in their work. Part 2 deals with the spreadsheet app Excel.
Where would the world be if Excel didn't exist? The spreadsheet, whose roots go back to the 80s, is used by companies and organizations around the world for all possible and impossible tasks: reporting and controlling, personnel and budget planning, order management, accounting or CRM - and for many, everything in one. Microsoft Excel was designed to handle data structured in tabular form. That's exactly what it can do incredibly well and that's the secret of its success. As part of the Office 365 Productivity suite, Excel also has access to the Artificial Intelligence resources from the Microsoft Azure cloud.
With AI, new functions are possible in Excel. Tables can be created from images, the data contained is enriched and AI analysis reveals patterns hidden in the data.
picture to table – automatically create tables from images
The cognitive ability of computer vision is used in the Excel mobile app. With the function Data from images Insert can – as the name suggests – generate Excel tables from images. Excel accesses the camera and marks the area in which it recognizes the original data with a red frame. You take the image, Microsoft AI analyzes the content and converts it into a table. If Excel recommends manual review for certain fields, it will mark those fields in red and the contents can be corrected by hand. Et voilà – in the last step, Excel inserts the data into the empty spreadsheet.
This achievement of the AI is impressive: the entire process took less than a minute and made manual typing completely unnecessary. You can turn a blind eye to the fact that the AI did not record all the numbers correctly. Machine vision, along with three other capabilities, is one of the key technologies that, according to Microsoft, are central to the development of artificial intelligence AI:
- See: The ability of computers to “see” and recognize what is depicted in an image or video.
- Listen: The ability of computers to “hear,” understand people’s spoken words and convert them into text.
- Language: The ability of computers to “grasp” the meaning of words, taking into account the many nuances and complexities of language (such as slang expressions or idioms).
- Knowledge: The ability of a computer to “think logically” by understanding the relationship between people, things, places, events, and the like. For example, if a search result for a film also provides information about the actors and other films in which they have appeared, or if you are automatically presented with the documents that you recently shared with the other meeting participants during a meeting. These are examples of computers thinking logically by reasoning about what information is related to other information.
This information comes from the e-book “A Future Computed – the social significance of artificial intelligence AI” – an exciting read that deals with numerous questions and challenges that we as a society are confronted with today.
Stocks and Geography – Automatically enrich data with contextual information
Does your Excel spreadsheet contain either data relating to shares relate or geographical nature these can be “marked” as special data types. They are special because the data is linked to online sources in the background and Excel automatically provides a large amount of additional information. Defining the data type is quick: simply mark the column or data range and select the appropriate category under [Data] and [Data types] using the ribbon. A column with cantons, for example, is marked with a small map symbol, which can be used to display contextual information such as population, coat of arms or main town. This additional information can now be inserted in additional columns or displayed in other cells. These references are dynamic and the cell value with a population changes when the canton name in the original cell is changed.
The game works exactly the same with the “stocks” data type. The contextual information includes the current market value, change from the previous day, trading venue and currency. Excel cells no longer simply contain text or numbers, but with the new data types contain an entire “data set” from the two categories. The intelligent service that makes this possible is Microsoft Knowledge Graph - which is also used by the Bing search service.
«Compared to the world of twenty years ago, many things are completely self-evident to us today that we only knew from science fiction films back then. A lot can really change in just two decades.” – Brad Smith, President Microsoft Corporation and Harry Shum, Vice President and Head of Artificial Intelligence and Research Microsoft Corporation
Excel ideas – intelligently evaluate data and gain insights
Another AI-powered function has the simple name Excel “Ideas”. Ideas (formerly “Insights”) looks at the contents of tables, analyzes the values and recognizes patterns. Excel then automatically suggests possible diagrams that visualize these characteristics of the data table and make them immediately understandable. The following example is a simple list of sales figures spread over different years, cantons and different materials such as wood, metal and concrete. The ideas function is located under [Start] in the menu ribbon. All you have to do is select a cell in the data table, press the idea button and Excel will generate a series of useful suggestions.
Excel makes suggestions from four insight classes:
- Rank: Highlights the element that is significantly larger than all others in its class and highlights it.
- Trend: Highlights when there is a consistent trend pattern across a time series of data
- Runaway: Highlights outliers in time series
- Majority: Finds cases where a majority of a total value can be attributed to a single factor.
In total, the ideas function makes around 20 suggestions in the example table. There are some really exciting ones, as the two screenshots show. Sales for the canton of St. Gallen have increased significantly over the years and the canton of Zug generates significantly more with wood than with other materials. Using the plus sign at the bottom left, all ideas can be inserted directly into the spreadsheet as PivotCharts.
When working with ideas, it is important that the cells are formatted correctly. For example, if cells are formatted as text but contain dates, Excel treats these values as normal text. In addition, column headings should not appear more than once and should not be stretched across multiple columns (e.g. across merged cells).
Find out more about artificial intelligence in part 1 and discover Power point. The third part deals with the same style Microsoft Word.