From big improvements to its cloud-based version to data visualization opportunities and the use of AI, Excel has come a long way
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, says CPA Michael Wong, senior product manager at Oyster HR. “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,” says David Elsner, president of DHE Consulting and adjunct professor, Schulich School of Business. “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.
- 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,” says Elsner. 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])
- 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,” says Wong.
HOW TO: According to the Microsoft support site, there are three methods to use this function with a macOS (instructions for iOS/Android are available as well).
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.
- 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,” explains Elsner.
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.
- 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,” says Elsner. “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, says Elsner. “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,” advises Elsner.
Getting the most out of your experience
When using these tools, it is important to use the type of visualization that works best in a given situation. Wong says: . “If you are trying to tell a story about relative proportions between a few categories, for example, you would use a pie chart. If you are telling a story over time, most often a line chart would make the most sense. Make sure you choose the right visualization to help tell your story. It’s not so much about what software you use, but the story you are trying to tell.”
Second, understand that the data visualization is only as good as the data itself, says Wong. “Before you run away and create fancy charts and dashboards, take a step back and ask if the data is accurate, reliable and from a trustworthy source.”
The good news is that even a simple tool like Excel can now do enough so that everyone with a computer can do analytics, says Elsner. “You better be up to speed, however, because everyone is going to use these tools. If you aren’t willing to use them, your competitor will be more than happy to.”
Keep learning
Once you have the skills discussed in this article under your belt, continue to grow your Excel knowledge with CPABC's professional development program, and earn CPD credit while you're at it. CPABC's Advanced Excel Data Magic - Managing, Analyzing, and Reporting course will be delivered virtually June 26-27, 2023, and Advanced Excel will be offered virtually July 11-12, 2023.
Denise J. Deveau is a Toronto-based freelance writer specializing in business and technology related topics.
Originally published by CPA Canada's news site.
