Microsoft Excel has gone through a number of changes over the years. The most impactful changes have been around improvements Microsoft made to the cloud-based version that launched with Office 365, At launch it was bare bones functionality. There have been a lot of improvements since then, allowing users to collaborate more efficiently and bringing it closer to feature parity with the desktop version.
The big thing now with Excel is being able to better visualize data quickly and easily, It now provides new and innovative ways to analyze information. The other is the integration of machine learning and artificial intelligence-based tools to analyze data and provide insights with the click of a button. For accountants, it is now a lot less about knowing how to use the tool, but more about whether you are asking the right business questions.
Here’s a look at four of the latest Excel enhancements and where data visualization fits into the picture.
1. XLOOKUP
“It’s a bit of a big deal and replaces the previous lookup function. It now allows you to look up data forwards and backwards in the spreadsheet,”. As outlined on the Microsoft support site, XLOOKUP can be used to find things in a table or range by row, such as the price by the item number or find a customer name based on their customer ID. The site notes that “you can also look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on”.
HOW TO: Syntax: XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
2. Importing data from pictures
“This feature allows you to import data from images, presentations and PDFs, such as data tables in annual reports into your spreadsheet without having to manually re-enter the information,” .
HOW TO: According to the Microsoft support site, there are three methods to use this function with a macOS (you can find the iOS/Android instructions here).
Method 1: Use an existing picture file. Click Data > Data From Picture > Picture From File.
Method 2: Take a screenshot. Take a screenshot of the table, then click Data > Data From Picture > Picture From Clipboard.
Method 3: Scan data using your smartphone. In Excel, right-click a cell, then click Scan Documents. Aim your smartphone camera at the data, then tap the button to take a picture. Then tap Save.
3. New data types
These let users convert data in cells into specific data types, such as stocks, geography, currency, etc. “When you click on the stock data type for example, it allows you to pull in widely available data into Excel, such as opening and closing prices, beta and other financial information without having to do external research,” .
HOW TO: As outlined on the Microsoft support site. type text in cells, for stock information, type a ticker symbol, company name or fund name into each cell. Then select the cells. With the cells still selected, go to the Data tab, and then click on a data type (e.g., stocks, geography). This will convert your text to the chosen data type. You’ll know its converted if the text has a linked icon in the left side of the cell. 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. Click the Insert Data button again to add more fields.
4. Analyze Data
This function allows users to understand data with high-level visual summaries, trends, and patterns using AI. “For example, when working with a large data set, a simple click will produce several visualizations that provide summaries, trends and patterns of that dataset,” “It will also allow you to ask questions of your data through natural language processing (NLP) queries and answer and produce a visualization without you having to write formulas.”
HOW TO: Click a cell in a data range then click the Analyze Data button on the Home tab. Analyze Data in Excel will analyze your data and return interesting visuals about it in a task pane.
THE BASICS YOU NEED TO KNOW
Accountants need to develop a better, stronger and more robust understanding of data analytics and the data tools available, “You don’t have to be a data expert, but you do need to be able to better understand the tools’ capabilities and what they can — or can’t — do for you.”
To create some form of visualization, he recommends that accountants familiarize themselves with the following tools:
- Conditional formatting
- How to create charts
- Generating trendlines and histograms
- How to use the forecast sheet tool
- How to use the analyze data tool to produce high-level graphical summaries
“Every CPA also needs to get at least a basic understanding of natural language processing and AI, and what they can do to develop insights and general actionable recommendations,”.