8 AI Features of Excel You Probably Didn’t Know About

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

The spreadsheet application, on the other hand, is an integral part of our daily lives, especially at work. Thus, the integration of AI features has become more obvious 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 an image

This feature is especially useful when dealing with physical copies of data, such as managing a local store and maintaining physical records. So, rather than manually entering the data, you can insert the data into your spreadsheet application with Insert Data from Image.

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

  • Image file, an image stored on your local computer in Excel.
  • Clipboard image, an image copied to your computer’s clipboard.

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

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

2. Shape 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 function is similar to the Autofill function in Excelthat you can use to automate data entry and cleaning.

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

Press CTRL+E on your keyboard to use Flash Fill or click Data > Instant fill.

A spreadsheet showing Excel's pattern recognition, Flash fill action

If your Flash Fill is not on, you can activate it by going to Tools > Options > Advanced > Editing Optionswhere you should check the Flash autofill box.

3. Analyze the data

Analyzing data in Excel is one of Microsoft Excel’s most exciting and powerful tools. This tool was previously called Ideas in Excel. It helps you see trends and patterns and analyze and summarize data. With its Data Analytics natural language querying capabilities, you can ask questions about your data to get insights.

Without having to create complex calculations, you can use this tool to help you make decisions and generate business results, which can save you a few minutes of work. You can use the tool by clicking Analyze data on the Home tab of your Excel spreadsheet ribbon.

A spreadsheet that highlights Analyze Data

4. Recommend graphics

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

Excel offers users many types of charts, each with its strengths and weaknesses. Unfortunately, using the wrong chart can cause your shareholder to misunderstand the data and make the wrong decision. This is where recommended charts come in.

Recommended charts help you by showing which chart works best for your data. This is handy when you’re unsure of your data or need help determining what works best. With this function you can create powerful charts and tables in Excel.

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

A spreadsheet that shows Recommend Charts in action

5. Example column 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 some Power Query functions and scripts for Excel. One is the Column From Example feature which allows users to create a new column based on your example.

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

A spreadsheet that shows the column in the example is used to join names in Power Query

6. Geographic Data Type in Excel

Organizations today use geographic data to gain insights into customer behavior, market trends, and more. However, working with geographic data can be difficult. Excel’s geographic data types solve this problem by allowing users to convert geographic data.

This allows the app to recognize and gain insight into the country, city, state, and zip code of your data. With this feature, you can get unemployment rate, population, birth rate, area, GDP and time zone(s) from your data. For retrieve rich geographical data in your spreadsheetsclick on the cell, then Data > Geography.

A spreadsheet used to display the type of geographic data in Excel used to classify location data

7. Forecast in Excel

Excel was originally designed as a financial application. So, it makes sense that it has forecasting capabilities. Excel does this by using AI algorithms and from values ​​in your spreadsheets.

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

However, the breakthrough feature is the Forecast Sheet feature. This feature forecasts and lets you customize your data by specifying a forecast start date, confidence interval, seasonality, time range, and value range. To make a forecast, click Data > Forecast Group > Forecast Sheet.

A spreadsheet that shows forecasted stock using the forecasting features in Excel

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

Creating PivotTables 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 the recommended pivot table:

  1. Select the table range.
  2. Go to Insert > Recommended pivot table.

A worksheet that displays the recommended pivot table recommending pivot tables

Stay more productive with Excel

As you can see, Excel currently has some AI functionality. These features will increase your productivity and save you time when working with Excel. And with the introduction of Copilot and the sudden explosion of AI technology innovation today, you can expect more AI capabilities from Microsoft Excel.

Leave a Comment