Excel AI Tools: built-in AI technology in Excel

In our second article looking at Excel AI tools, we’re going to look at existing features and functionality in Excel that use AI technology to some extent. Make sure you also check out our earlier article on the new experimental LABS.GENERATIVEAI function. This allows you to send prompts from within Excel to a generative AI model and then return the results from the model back to your worksheet.

Does Excel really have AI tools?

AI is based on the principle that human intelligence can be defined in a way that a machine can easily mimic it and execute tasks, from the most simple to those that are more complex. When we use Excel, there are many tasks that we do on a day-to-day basis that are fairly simple and repetitive. A good example is opening a file from a specific location, performing some data cleansing and updating a report. We can certainly get AI to help in these types of situations, using the Power Query feature in Excel, for example. Another example is using the Analyze Data feature in Excel to help speed up the process of extracting information from data.

So, Excel has a number of AI-like features that use machine learning algorithms to automate certain tasks and natural language processing to search the web for information related to the data you have selected. However, it doesn’t use some of the more complex AI techniques such as expert systems.

Overview of Excel AI tools

There are some built-in tools in Excel that use AI-like capabilities. Here are some examples :

• Data cleaning and preparation: Power Query can help you identify and fix errors, outliers, missing values, duplicates, and inconsistencies in your data.
• Data extraction: Data extraction is the process of converting data from one format to another. AI can help Excel users extract data from sources such as images, PDFs, or web pages. For example, you can use AI to convert a picture of a printed data table into an editable spreadsheet with the Insert Data from Picture feature. You can also use AI to import data from web pages with the Get Data from Web feature in Power Query for Excel.
• Idea generation: Excel can analyze your data and generate insights and suggestions for you. You can use the Analyze Data feature to ask questions, create charts, or find trends and patterns in your data. For example, if you have a table of sales data, you can ask Excel to show you the best-selling products, the average revenue per month, or the correlation between price and quantity.
• Predictive modelling: Predictive modeling is one of the most common uses of AI in Excel. It allows users to forecast future outcomes based on historical data and trends. For example, you can use AI to predict sales, revenue, customer churn, or inventory levels. Excel has built-in features such as Forecast Sheet and Data Analysis Toolpak that can help you create predictive models easily.

Let’s take a look at these Excel AI tools in more detail.

Data cleaning and preparation

Many organizations now recognize that having high quality data is a pre-requisite for doing business. However, we rarely get clean data to work with all the time, or it may be clean, but not in the right format, so there is often a need for data cleaning and/or preparation.

Excel has a built-in tool called Power Query that can help with data cleaning and preparation tasks. With Power Query you can connect to data sources, import data, clean and shape it and then load it into Excel or a data model so that you can create charts and reports. Check out our earlier article for an introduction to Power Query.

Some of the Power Query features that use AI-like capabilities for data cleaning and preparation include:

• Data Profiling: Power Query can automatically profile and analyze the data in a source, identifying patterns, distributions, data quality issues, and potential anomalies. While not AI in the traditional sense, this automated analysis provides insights into the data’s characteristics.
• Column Suggestions: When performing transformations, Power Query can suggest transformations or actions based on the data in columns. For example, it might recognize date columns and offer date-related transformations, or suggest common cleaning operations for text data. This can be seen as a form of intelligent assistance.
• Query Dependencies and Optimization: Power Query can optimize the execution of transformations and queries by folding some operations back to the source database. This involves intelligent decision-making about where to perform each step of the transformation, with performance considerations in mind.
• Query Diagnostics: Power Query offers diagnostics and profiling tools that can help identify performance bottlenecks, errors, and issues within your data transformation steps. While not strictly AI, these tools can assist in identifying and resolving problems.

While Power Query does incorporate some intelligent features, it’s important to note that its primary function is data transformation and manipulation. It doesn’t possess the deep learning capabilities or autonomous decision-making associated with AI systems.

Data extraction

Data extraction is the process of converting data from one format to another. AI can help Excel users extract data from sources such as images, PDFs, or web pages.

There are two main features that perform data extraction in Excel:

• Insert Data from Picture: turn images that have table data into data that you can edit in Excel
• Get Data from Web: detect tables embedded in a web page’s HTML and write the data into an Excel worksheet

Insert Data from Picture

This is a great time-saving feature.  You can give it a try by saving the image below to your local drive and then clicking on the Data ribbon menu and then the From Picture button in the Get and Transform Data options.  Select the image that you just saved and click OK.

When you click OK, the image is processed and the data is extracted. After a couple of seconds, the data will be extracted and you can choose to insert it into your worksheet immediately or check it first by clicking on the Review button.

Clicking the Review button will highlight the items in the data that Excel is not sure about. You can then go through them one by one and visually check that the data is correct.

Once you have reviewed all the items, you can click on the Insert Data button – the data will then be inserted into the worksheet.

You can also extract data from a screen capture of a table from a website or take a picture of some printed data (an invoice for example). 

Import Data from Web

This is another great time-saver! If you’ve needed to grab data from web pages in the past, you may have tried copying and pasting from tables into Excel. This works in some cases but is not particularly reliable. The Import Data from Web tool is another of the Get and Transform Data options on the Data menu ribbon in Excel.

All you need to do is provide a URL to a web page that hosts the data you need in an HTML table, and Excel will do the rest. For example, if I want to get a list of the highest grossing movies of all time adjusted for inflation, I can use the following URL:

https://www.boxofficemojo.com/chart/top_lifetime_gross_adjusted/?adjust_gross_to=2022

You can choose to transform the data before loading it – if you do, the Power Query editor is opened and you can perform transformations there.

Here is the data loaded into Excel (well done to those that guessed that Gone With The Wind would be number 1 on the list!).

Idea generation

The Analyze Data feature in Excel generates ideas about data – it suggests questions that you may want to ask your data, as well as generating suggestions for tabular and visual summaries that you may want to use. Users can also ask their own questions about their data using natural language instead of having to know how to write formulas or use pivot table or charts.

A typical example is a set of sales data. You can use the Analyze Data feature to give you ideas about how to interrogate the data, such as “Which region had the highest total sales in 2022?”.

When I did some testing, I found that the automatically generated questions or ideas were not particularly useful.  I had more success when asking my own questions. In the screenshot below, you can see that I entered one of my own questions in the prompt box: “Total Revenue by year”. The suggested answer was a PivotChart showing ‘Total Revenue by Year’:

The usefulness of the suggestions is likely to improve over time as the learning algorithms get better, so this feature is definitely worth keeping an eye on.

Predictive modelling

The Forecast Sheet is a feature in Microsoft Excel that allows users to create predictive forecasts based on historical data. This feature uses time-series data to generate forecasts for future values, making it useful for scenarios where you want to predict trends or patterns over time.

I did a simple test using a dataset of global average temperature anomalies – you can see the results below:

As you can see, the Forecast Sheet feature is simple to use. It just needs a dataset in the correct format (a date column and a value column) and it will then produce forecasts for future periods.

It’s important to note that the Forecast Sheet feature uses statistical algorithms to generate forecasts, and the accuracy of the forecasts depends on the quality and nature of your historical data. It’s also important to understand that forecasting results might not always be entirely accurate, especially if there are sudden changes, anomalies, or irregular patterns in the data. You therefore should have at least a basic understanding of what is going on under the hood – you can read more in this guide to forecasting.

Conclusion

In this article we’ve looked at Excel AI tools. Excel has a number of built-in functions that use AI-like technology.  These are mostly machine learning algorithms to help automate certain tasks.

So, whilst the features we’ve looked at here are not typically classified as AI tools, they do incorporate smart features and automation that could be considered AI-related.

Some of the features that we’ve looked at, especially the ones related to idea generation or predictive analysis are likely to get better quite quickly, especially when Microsoft’s new AI assistant for Office starts being rolled out.

Article by

Andy McDonald

Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.

Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.