Excel has always been one of the most important tools for data calculation and evaluation. However, it also has a notorious reputation for being a tedious tool to work with, especially if you’re new to Excel or have less time on your hands.
If you have a spreadsheet filled with product prices and quantities, and you need to evaluate the total sales generated, the chances of getting it done within a few minutes are slim.
At least, that’s what it used to be. Thanks to the recent advent of AI, it has become increasingly easier to master Excel and get things done seamlessly.
For instance, you can use ChatGPT to write Excel formulas and readily use them on your spreadsheets and even copy-paste spreadsheet data to analyze what you need to.
Table of Contents
However, what you might be unaware of is Excel’s in-built AI capabilities. While tools like ChatGPT can create formulas, Excel’s in-built AI extends its functionality by letting you transform data, perform quick data analysis, build charts, and more, without having to do much.
So, if you’re wondering how to use AI in Excel, we’ve curated this blog to help you understand just that. We’ll take a deep dive into using AI in Excel, from common examples to the most notable methods of implementing AI for Excel tasks.
Let’s dive right in!
Can we use AI in Excel?
Yes, absolutely. Excel houses various features that utilize AI and machine learning to automate tasks for the data you need. These include real-time geographical descriptions, stock information, dynamic array functions, and more.
We will delve deeper into how exactly you can use AI in Excel in the following section.
8 key methods of using AI in Excel
So let us get to the point, how to use AI in Excel? Here are 8 ways in which you can use AI to expedite your Excel tasks:
1. Using ‘Analyze Data’
Let’s say you’re working with a vast dataset and want to create a chart. Excel’s Analyze Data feature makes this a piece of cake, using AI to automatically suggest charts. For example, if you have a spreadsheet with data, select the complete data range and click on ‘Analyze Data’ in the Analysis section.
On the Analyze Data panel, you can choose from various charts. Excel also provides intelligent suggestions for choosing a chart based on the information on your spreadsheet.
2. Listing real-time stock prices
Another great use case of AI for Excel is listing stock prices. Let’s say you have data about stocks on your sheet:
Now go to the Insert tab and select Table.
The ‘Create Table’ window will appear. Select the ‘My table has headers’ box and proceed. Now, you can choose the range of your data table and choose ‘Stocks’ in the Data Types section. This will result in the stock shortcodes turning into their full names, and then you can add a column for the desired data. For this instance, let’s pick currency and 52-week high as the data we need for the stocks.
Using AI, Excel will generate the appropriate results to fill in the column:
3. Using ‘Dynamic Array’ functions
Excel’s dynamic array functions simplify getting desired results using just one formula. This will help you create more robust spreadsheets quickly, using fewer formulas and ensuring minimal errors. An example of this is using the SORT function to sort lists, the UNIQUE function to remove duplicates from lists, and the FILTER function to get only the data you need from the list. If you’re new to Excel, you can even use ChatGPT to get the formulas for these functions.
For instance, let’s take a list of names from row B5 to B11, and you need to sort the names alphabetically. Simply ask ChatGPT, “write an Excel formula to sort data alphabetically from row B5 to B11” and you will get the formula: =SORT(B5:B11). You can copy and paste this formula in row C5 for the adjacent column and get the desired results.
If you’re new to AI tools like ChatGPT and want to master them for professional expertise, Be10X provides a comprehensive AI tools workshop that will help you upskill and is hassle-free. Click here for more.
You can do the same with UNIQUE and FILTER functions, as shown below:
4. Insert Data From Picture
If you want to input data into a spreadsheet but only have it in physical copies (lists on paper, physical bookkeeping, etc.), you don’t need to manually do so. Excel’s ‘insert data from picture’ is an AI-driven feature that inputs data automatically from an image file.
On your spreadsheet, click on Data > From Picture > Picture from File/Picture From Clipboard.
Using this, you can either choose the local image file on your phone/PC or from the clipboard after taking the picture. After that, Excel will analyze the image using AI and input it instantly onto your sheet.
5. Flash Fill
Similar to Excel’s Auto Fill feature, Flash Fill uses AI-driven pattern recognition to fill values after recognizing patterns in the data based on the adjacent columns created by you. This eliminates the need to use complex formulas and simply uses AI to get the job done. Just click ‘Ctrl + E’ on your keyboard or click Data > Flash Fill to use this feature.
For instance, in the sheet below, the first and last names have been inputted. Excel’s Flash Fill uses the AI to automatically fill in column C after the column name and row C2 have been inputted.
6. Recommend Charts
As mentioned earlier, Excel’s AI capabilities also include recommending charts based on your data. Instead of creating a chart yourself and not choosing the right chart type, you can simply use the Recommend Charts feature to let Excel display what charts work best, based on the data you’ve provided. Simply click on Data > Insert > Charts Group > Recommended Charts.
7. Forecast Sheet
As you may already know, data forecasting is a tough nut to crack. However, Excel’s built-in forecasting tool uses AI to make things simpler. The Forecast Sheet feature forecasts and lets you customize your data by just inputting a forecast start data, seasonality, confidence interval, values range, and timeline range. For this, click on Data > Forecast Group > Forecast Sheet.
8. Recommended PivotTable
If you’re working with massive data where everything necessarily makes sense, Excel’s Recommended PivotTable is here to help. This tool uses AI to let you look at the data from various angles and summarize it for easy reference. Simply select your table range and click on Insert > Recommended PivotTable. Using AI, Excel will suggest various perspectives and summaries of the data, allowing you to better understand and act on it.
Conclusion
Gone are the days when working on Excel used to be hard. Thanks to AI, you can use the tool’s built-in features to automate tasks and get things done faster. As we’ve highlighted here, Excel has numerous AI features to help you increase productivity and use the tool to its full capability without scratching your head.
AI Tool of the Day
✴︎
#75 Juice.ai
Moreover, if you’d like to leverage ChatGPT to be a smarter professional when using Excel, Be10X offers a holistic workshop that covers the key aspects of using ChatGPT with Excel. Click here to know more.
