Learn Excel’s built-in artificial intelligence feature

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood:
Welcome to the CPA Australia Excel Tips Podcast. My name is Neale Blackwood and today I wanted to discuss artificial intelligence and Excel. Now, there's been a lot of coverage in the media and social media about the ChatGPT interface, which allows you to ask questions about pretty much anything, but did you know Excel has its own AI interface? It's in the new version of Excel and it's called Analyse Data. It's an icon that's on the far right-hand side of the home ribbon in Excel. But before we type in, there is a data warning.

This feature does require an internet connection. So the data you are using is being sent outside your organisation. Now, it could be going to a third party or Microsoft. And checking the Microsoft privacy policy in terms of AI didn't really help. It wasn't clear on how the data was used, so I don't know if the data is anonymized or how it works. So I wouldn't share any sensitive data with this feature. This feature does work best with formatted tables. If you haven't already looked at formatted tables, I've done a podcast and an article on it, so you can check out INTHEBLACK website for that. The keyboard shortcut to convert a list into a formatted table is control T.

The formatted table does have different colouring, so it is obvious that it is a formatted table. So you can click anywhere inside the formatted table and click that Analyse Data icon on the far right-hand side of the home ribbon. And pretty much instantly, it only takes a couple of seconds, you will have some insights into your data. Some of the columns in your data may not be worth including in your analysis. So there is the ability to exclude certain columns. So there is a little cog icon.

This is developing fairly quickly. So in the article, the terminology has changed. I only wrote the article a couple of months ago. So now, the terminology's changed, but there is a little cog icon, and you click on that and it gives you a list of all of the columns within your table, and you can untick the ones that you don't want to use. When you then click update, it goes through and does the analysis again, and so you'll get different insights based on the columns that you've excluded.

It does give you suggested questions, as well, on the task pane. So it opens up a task pane on the far right-hand side of the screen and then it just lists various reports and charts. So the first one it showed me was sales value by state and customer category. Well, they're two columns, so I clicked on the create pivot table button. It opened up a brand new sheet called Suggestion One. And in that sheet, it had a pivot table already created, ready to go. So I was impressed with that. So then I went back to the task pane. You basically click back onto the data sheet and then the task pane will appear again.

There were other charts listed underneath the report. So there was a bar chart created that was a pivot chart. And so again, I clicked on that and, again, a brand new sheet was created with a pivot table on it and a pivot chart. In terms of pivot charts, pivot charts have to be related to a pivot table. So you can't have a pivot chart in isolation. It does need a pivot table to go with it. It did even bring up an observation. It said, "Unit price is relatively even across state," and it averaged the unit price column. Now, this isn't entirely correct because that is just a value in a column.

The unit price needs to be looked at in a weighted average way rather than just an average way. So you still have to use some common sense when reviewing the data because, as I said, it's not perfect yet. It's just looking at the data as a table. So in this case, it didn't understand that it really needed to do a weighted average rather than just a straight normal average on that unit price column. It created a lot of reports without me having to actually do anything. All I did was click the button and then it created the reports, but you can also ask it questions. Now, it gives you some suggested questions, which you can use, or you can write your own. So I tried a few. So I did top 10 customers in New South Wales and it did work. And again, it's fairly quick. In the video, if you want to look at the companion video, you'll see how fast it is.

I didn't do any editing when that was creating reports and things, so you'll see in real time how fast the interface is. It's pretty quick. And it did create a report, but the report was based on quantity. Now, that's a valid column to use. So it was showing the highest quantity there. And then I did another one that, this time, was the top five product codes for widgets in Victoria. Now this one, when you think about it, because the data, it works really well with columns, product code was a column, but widgets and Victoria were actually entries in a column. They weren't column headings, but it still worked it out. It does understand the entries within a column.

And again, it produced a report that was correct in terms of quantity. So again, it was using quantity. But I found it was really easy to fix it because all you needed to do was add the sales word in there, because there is a sales value column. And so the top five product code sales for widgets in Victoria gave me exactly the report that I wanted, it was sorted, I had two filters on the pivot table. I was impressed with the output there. And the beauty of the pivot table is, once the pivot table's created, that's it, there's no artificial intelligence associated with the pivot table. It's created it and then it's just ready to use, like a normal pivot table. It just refreshes, as per normal.

And just a note on sorted pivot tables, the pivot table was sorted by the value and, when you update that, like change the filter or refresh it, then that sorting still applies. It doesn't have to get reset. There is some terminology you can use with the feature. One of them is insights. So for example, you can ask for, what are the margin insights? So there was a margin column, and so it will give you a list of insights that are associated with the margin. In the case of the data that I used, it gave me a few outliers, which could be useful for things like auditing. So it gave me some margin outliers that, once you created the pivot chart, it had actually identified three separate dates, which were outside, if you like, the normal parameters that it was looking at.

I note, when you first use it, it does have a listing in the task pane. I think it gives you the top three or four, but down the bottom, there is a little option that you can click to see all of the results. And some of those came up with some interesting features, as well. Some of those were actually a normal Excel chart. They weren't a pivot chart. So I hadn't seen that before. When I'd been using it before, it had only shown pivot charts. I don't know if it's something new or if it's just I hadn't used it enough, but it'll now create just a normal chart based on the data. And these were frequency charts, which are used a lot by auditors just to check for normal distributions and things like that. So as well as the term insights, you can also use the words over time. So you can ask to see, for example, the sales over time.

Now, it does do it by date. In a lot of cases, you might want to add the words monthly or quarterly, which it also understands. You can ask to see different time periods. One thing I was interested in was whether it understood about an invoice and whether ... because the data had multiple lines per invoice. I tried to test it with the invoice, but the first test failed, and it was probably my fault, because I asked it for the highest value invoice in New South Wales. Now, invoice number is actually a number. The invoice column was actually a number. So when I asked for the highest value invoice, it gave me the maximum invoice number in that column, so basically the last invoice for New South Wales. So I changed it again and just added sales. So I asked for the largest sales value invoice in New South Wales and it worked correctly. It did add up the individual lines to come up with the total for the invoice number, which was what I was after. There is a limitation with the feature. It will only work on a single table.

Now, if you've got Power BI, you might have seen the question feature in Power BI, and that will work across multiple tables, but Analyse Data in Excel only works on a single table. So even though you can relate tables together in Power Pivot with Excel in the data model, that isn't really picked up by the Analyse Data yet. Hopefully, that's something that will be coming. So overall, I think it's a great feature. I said my only reservation is the fact that the privacy isn't that well-covered in terms of what happens to the data that goes out. So at the moment, I'd say be very careful with the data you're using with this. So you can maybe even check with your IT section to maybe get them to ask Microsoft what happens to the data with the Analyse Data feature. Hope you found that useful. Thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.