Despite claims that AI has made Excel obsolete, the combination of AI and Excel has actually increased the program’s power and accessibility for extracting insights and querying data. That said, AI is not a complete substitute for genuine expertise. To fully leverage Excel’s potential with AI, properly structured data is essential. You’ll learn about both the opportunities and the responsibilities of using AI with Excel in this post.
For a demonstration of Excel’s AI-powered Analyze Data feature, we will utilize the Wholesale Customers dataset from the UC Irvine Machine Learning repository.
Starting Analyze Data
Unlike the similarly-named Data Analysis Toolpak, Analyze Data comes ready to use right out of the box. Just place your mouse anywhere in the wholesale_customers table and head to Home > Analyze Data to get started:
Instantly, you’ll be presented with a range of intriguing AI-generated insights. Click on any of them to have them inserted into your workbook.
Natural language querying
The power of Analyze Data becomes more apparent with its natural language querying. For instance, imagine you’re in a meeting with colleagues and need to swiftly retrieve the total sales for the Grocery department. Instead of spending time manually calculating the answer, you can directly pose the question to Analyze Data and obtain the desired information right away:
While querying this dataset is undoubtedly impressive, it does have certain limitations, primarily related to the data’s layout. For instance, if you attempt to ask Analyze Data for the total sales by region, you will instead receive the sum of all region numbers:
Analyze Data isn’t sure what to do because the data is presented in an improper format. Instead of consolidating all sales figures in one column, they are spread across multiple columns. As a result, Analyze Data cannot determine which columns contain the relevant sales figures that need to be summed up:
Data being in an unclean or “untidy” format is a significant obstacle in analytics. You may have encountered this issue in your own work without putting your finger on exactly what’s wrong. Developing a conceptual understanding of dirty data allows you to identify issues early on in your workflow. To delve deeper into the theory of “tidy data” and learn how to handle it effectively, check out this blog post:
For AI to unleash its full potential in uncovering insights, data must be in a machine-readable, “tidy” format where each variable resides in a single column. To address this, we will use Power Query to unpivot the columns from Fresh to Delicassen and rename them Department and Sales. Make sure to load the results of your query into an Excel table.
For a tutorial on how to unpivot this dataset in Power Query, check out this post:
With the data in a proper “tidy” format, querying the data to find total sales by region is a breeze:
What other insights can you glean with the power of Analyze Data? You can download the cleaned workbook, complete with AI-generated insights, here:
AI takes work, not magic
To the public, AI may appear magical and effortless, but analysts understand that it requires well-structured data and thoughtful effort. Analyze Data is an incredibly powerful tool, but it relies on data being in the correct “tidy” format to unleash its full potential.
Have you experienced Analyze Data in Excel? Share your thoughts in the comments below.
Leave a Reply