Artificial Intelligence (AI) models have revolutionized how we work and make decisions today. These models streamline processes and increase efficiency by learning from data, identifying patterns, and making predictions.

Spreadsheets application, on the other hand, are an essential part of our daily lives, especially at work. Thus incorporating AI features into them has become more apparent than ever. This article explores eight different AI features in Microsoft Excel spreadsheet applications and how to use them for data analysis.

1. Insert (Capture) Data From Picture

This feature is particularly useful when dealing with physical copies of data, such as managing a local store and maintaining physical bookkeeping records. So rather than manually inputting the data, you can snap the data into your spreadsheet application with Insert data from Picture.

Insert data from Picture is different from the IMAGE function in Excel. This feature turns your image into data. This feature plays out in two ways:

  • Picture File, an image stored on your local computer in Excel.
  • Picture From Clipboard, an image copied to your computer’s clipboard.

To use this feature: Click Data > From Picture > Picture From File/Picture From Clipboard.

A spreadsheet showing the Insert (Capture) Data From Picture feature of Excel

After selecting the image, Excel analyzes the Picture with its AI capability, and your data get imputed in Excel.

2. Pattern Recognition With Flash Fill

Flash Fill uses pattern recognition to automatically fill values for you once it recognizes a pattern in the data based on your adjacent columns. This feature is similar to the Auto Fill feature in Excel, which you can use to automate data entry and cleaning.

Without writing complex formulas, users can transform data. Instead, you simply type your desired format in the adjacent column and watch Flash Fill work magic.

Press Ctrl + E on your keyboard to use Flash Fill or click on Data > Flash Fill.

A spreadsheet showing Excel 's Pattern Recognition, Flash Fill in action

If your Flash Fill isn't on, you can activate it by going to Tools > Options > Advanced > Editing Options, where you should check the Automatically Flash Fill box.

3. Analyze Data

Analyze Data in Excel is one of the most exciting and efficient tools in Microsoft Excel. This tool was previously called Ideas in Excel. It helps you see trends and patterns and analyze and summarize data. Using its natural language querying capabilities of Analyze Data, you can ask questions about your data to gain insights.

Without having to create complex calculations, you can use this tool to help in decision-making and drive business outcomes—thus potentially saving you minutes of work. You can use the tool by clicking Analyze Data on the Home tab of your Excel spreadsheet ribbon.

A spreadsheet that highlight Analyze Data

4. Recommend Charts

Data visualization allows you to clearly and concisely communicate insights about your data to your shareholder. However, as vital as they are, there are various challenges when it comes to data visualization. One is choosing the right Excel charts and knowing when to use them.

Excel offers users numerous chart types, each with its strengths and weaknesses. Unfortunately, going with the wrong chart can lead to your shareholder misunderstanding the data and making the wrong decision. That's where Recommended Charts come in.

Recommended Charts help you by displaying what chart works best for your data. This is handy when you are unsure of your data or need help figuring out which works best. With this feature, you can create powerful graphs and charts in Excel.

To use Recommended Charts: Select the Data and click on Insert > Charts Group > Recommended Charts.

A spreadsheet that shows Recommend Charts in action

5. Column From Example in Power Query

Power Query is a powerful data transformation engine built into Excel. This tool allows you to import data from different sources and clean it using its built-in functions. AI powers a few Power Query for Excel's functions and scripts. One is the Column From Example feature which allows users to create a new column based on your example.

You simply write what you want the new column to look like without writing the M code or transforming our data with Power Query in-built functions. And the result gets displayed based on your example. This is particularly important when your dataset requires complex data transformations.

A spreadsheet that shows Column From Example being used to join names together in Power Query

6. Geography Data Type in Excel

Organizations use geographical data today to get insights into customer behavior, market trends, and more. However, working with geographical data can be challenging. Excel's geographical data types solve this problem by allowing users to convert geographical data.

This allows the application to recognize and get insight into your data's country, city, state, and zip code. With this feature, you can get your data's unemployment rate, population, birth rate, area, GDP, and time zone(s). To retrieve rich geographical data into your spreadsheets, click on the cell, then Data > Geography.

A spreadsheet being used to show Geography Data Type in Excel being used to classify location data

7. Forecasting in Excel

Excel was initially designed as a financial application. Thus, it makes sense that it has forecasting capabilities. Excel does this using AI algorithms and from the values on your spreadsheets.

Forecast helps you predict things like future sales or trends. This can be done in Excel using various forecasting tools. One is your trend lines which are available as a parameter when creating a chart in Excel. Another is the moving average function and exponential smoothing in Excel, which smooth out fluctuations in data and forecast value as a weighted average of the historical data.

However, the groundbreaking one is the Forecast Sheet feature. This feature forecasts and allows you to customize your data by specifying a forecast start date, confidence Interval, seasonality, timeline range, and values range. To make a forecast, click Data > Forecast Group > Forecast Sheet.

A spreadsheet that shows stock being predicted using the Forecasting in Excel features

Excel Pivot tables allow you to look at the same data from different perspectives by summarizing your data. This is very important when working with large data where you can't see or make sense of everything.

Creating pivot tables in Excel is as easy as highlighting your data and selecting PivotTable. The Recommended PivotTable feature suggests various summaries and perspectives of your data based on the data provided to Excel. To use Recommended PivotTable:

  1. Select the table range.
  2. Go to Insert > Recommended PivotTable.
A spreadsheet that shows Recommended Pivot Table recommending pivot tables

Stay More Productive With Excel

As you can see, Excel currently has some AI capabilities in it. These features will increase your productivity and save time while working with Excel. And with the introduction of Copilot and the sudden burst of AI technological innovation today, you can expect more AI capabilities from Microsoft Excel.