23 Artificial Intelligence in Excel
One of the hottest topics in technology in modern times is the proliferation of artificial intelligence (AI) functionality in our daily lives. Excel has utilized elements of AI for several years, and the latest releases continue to integrate AI throughout Excel and other Microsoft applications.
In the realm of Excel, artificial intelligence is about utilizing advanced algorithms and machine learning techniques to automate data analysis, reduce errors, and improve efficiency. AI in Excel enables the software to understand complex patterns in data, make predictions, and suggest optimized solutions. While one could simply ask Copilot or another generative-AI tool to deduce which AI features exist in Excel, it may be easier to read this short summary.
Artificial Intelligence in Excel
Recommended Charts and PivotTables
Some introductory artificial intelligence functionality has been in Excel for years. For example, when inserting a chart, Excel will provide a suggestion of appropriate charts based on your data. Recommended Charts and Recommended PivotTables are examples of AI because they use algorithms to analyze the data you select and suggests the most suitable chart type for effectively visualizing that data. By understanding the data’s nature and relationships, the AI identifies patterns and trends, recommending the chart that best communicates the insights in a meaningful way.
Flash Fill
Excel data entry AI is a game changer regarding data input as it saves time and reduces the chance of errors. This is facilitated through functionality that replaces human data entry with machine learning features like AutoComplete, AutoCorrect, AutoFill or Flash Fill, which works by recognizing patterns of filled data and filling out the rest. Try using AutoFill by typing Sunday in one cell, then type Monday in the next cell. Select the two cells and drag the fill handle to see Excel’s artificial intelligence fill the neighboring cell with “Tuesday”. Usually Flash Fill starts automatically, and you only need to provide a pattern.
- Insert a new column adjacent to the column with your source data.
- In the first cell of a newly added column, type the desired value.
- Start typing in the next cell, and if Excel senses a pattern, it will show a preview of data to be auto filled in the cells below.
- Press the Enter key to accept the preview.
Insert Data from Picture
Another AI data entry feature is the Insert Data from Picture feature which allows users to insert data from a screen capture on their clipboard, or an image file from their computer. One can even screen capture a table from a web site and convert the picture into Excel data. Copying data from websites into Excel will never be the same! Try it below!
Import data from a sample image file or clipboard Right-click the following image and “Save image as…” a local copy, then click Data > From Picture > Picture From File …. (or Clipboard) and follow the on-screen instructions to convert the picture to data.
Analyze Data
Analyze Data in Excel (formerly known as Ideas) empowers users to understand their data through natural language queries that allow users to ask questions about their data without having to write complicated formulas. In addition, Analyze Data provides high-level visual summaries, trends, and patterns. Simply select a cell in a data range > select the Analyze Data button on the Home tab. Analyze Data in Excel will analyze your data and return interesting visuals about it in a task pane.
Linked Data Types
Linked data types connect to reputable sources of data, such as Bing, Power BI and more, so users can access information about a variety of subjects without ever leaving Excel. Real-time stock quote integration, geography and currencies are three new data types that use AI to connect to online data in return real-time data into Excel for further analysis. To get current data for the data types, right-click a cell with the linked data type and select Data Type > Refresh. That will refresh the selected cell, plus any other cells that have that same data type.
Without artificial intelligence, users must write a formula for every value they want. However, the AI dynamic arrays feature allows users to write a formula, hit the enter key, and immediately see an array of values. Using dynamic arrays, any formula that returns an array of values will seamlessly “spill” into neighboring unoccupied cells, making it as easy to get an array of values returned as it is to work on a single cell. Users can immediately harness the power of dynamic arrays by using one of the new FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, SINGLE, and RANDARRAY functions to build spreadsheets that would previously have been nearly impossible.
ChatGPT for Excel add-in
Several AI functions began appearing in some versions of Excel 365 for Windows in 2024. If you have the right version of Excel, try typing =ai in the formula bar, and if the AI functions are available you’ll see something like this.
One might be surprised to see the reference to OpenIA in the screentip! The current version of Excel utilizes the OpenAI server/API to connect the AI functionality to their data. This might be a short-term solution until a complete Microsoft Copilot solution is released.
Current AI functionality in Excel requires add-on functionality to be installed, such as the ChatGPT for Excel add-in which introduces five key AI functions — AI.ASK, AI.FILL, AI.TABLE, AI.EXTRACT, and AI.TRANSLATE.
The AI.ASK function allows users to query their data in natural language. This simplifies complex data analysis by interpreting questions and providing answers directly within Excel. It can be used to write text, summarize, analyze data, and more—like having a virtual assistant inside Excel! It’s Clippy on Steroids!
For example, the formula:
=AI.ASK(“Identify the highest selling model of Ford vehicles in 2024”)
will return the result:
As of 2024, the highest selling model of Ford vehicles is the Ford F-Series, particularly the Ford F-150. The F-Series has consistently been one of the best-selling vehicle lines in the United States for many years, and it continues to dominate the market in 2024.
Copilot in Excel
The future of artificial intelligence integration in Excel rests with Microsoft’s Copilot functionality, which was released in late 2024, but might not be live for all Excel users right away. Web versions of Excel are more likely to reveal AI functionality before the desktop version licenses are updated.
Copilot in Excel is built into your workbook, enabling users to iterate with it quickly and easily. This integration allows users to continually prompt as they work, making it feel like users have an Excel expert right by their side. Copilot in Excel can help you go from a blank page to a finished document in a fraction of the time it would take to compose text on your own. Copilot can now reason over structured data, not just tables, and it can complete tasks such as adding filters or splitting text. It can respond to prompts to help users analyze data trends. For example, in the following illustration, a prompt was asked to determine “How many different “Dealership” are there (in the worksheet)?
The result returned was:
Another prompt asked, “Which Salesperson has the most sales?” The result returned was:
While a user could write formulas with functions to extrapolate similar answers, the Copilot prompting functionality generated answers in a fraction of the time!
In addition to answering prompts, Copilot can identify insights, generate formulas, highlight, sort & filter, as well as advanced functionality such as helping to write Python code, and provide text insights to streamline and enhance data interpretation. With AI-powered features, Excel users can now analyze large datasets, identify trends, and make informed decisions more efficiently.
