From writing social media copies to creating full-fledged video scripts, there’s no denying that AI has made mundane tasks easier for us, Performing Excel tasks is no exception either; you can use tools like ChatGPT to create Excel formulas and even analyze spreadsheet data.
However, what many might be unaware of are Excel’s built-in AI features. Using third-party tools like ChatGPT is an extra step, and if you want to stay on Excel and still automate certain tasks, the AI features in Excel are ideal.
So, let’s take a look at 8 such features and examples of using AI in Excel that you should master to streamline your work!
Table of Contents
8 AI Features of Excel
1. Flash Fill for data cleaning
Data cleaning via Flash Fill is one of the most notable artificial intelligence Excel examples. This Excel feature uses machine learning techniques to automatically fill in your spreadsheet with data after recognizing the underlying pattern, based on a few examples given by you.
To use Flash Fill, you can simply use ‘Ctrl + E’ after selecting your desired data range, or click Data > Flash Fill. Flash Fill works with dates, numbers, text strings, and more. It’s an excellent feature of AI in Excel that helps clean up messy data.
2. Power Query’s Column From Example for data cleaning
A powerful data transformation engine, Power Query is built into Excel. It allows you to import any data from any source and clean it up through the built-in functions. An example of this is the Column From Example, which is one of the best AI features in Excel. This particular feature will allow you to create a new spreadsheet column based on an example given by you.
All you need to do is write what you want the new column to comprise without transforming the data or writing the M code. The result will be displayed based on your given example. To use this, click Data > From Table/Range > Add Column > Column From Examples. Select your desired range and enter a column name, and that’s it.
3. Formula by Example for automatic formula application
Formula by Example is another example of what the powerful combination of AI and Excel can achieve. Using AI, Excel can now write formulas for you. It works similarly to the Flash Fill feature, meaning you only need to give it a few examples. Based on them, you can select your range and use the Formula by Example feature to let Excel do the rest.
To use this feature, write a few examples of the data, go to the cell below, and click on Data > Show Formula > Apply. This will fill out the rest of the data range automatically, based on the numbers or information you’ve given before.
However, if you find this a little complex, you can still use ChatGPT to create and use formulas on your Excel sheet.
New to AI tools? Check out Be10X’s Microsoft AI workshop that offers comprehensive learning on harnessing AI for automating tasks on Excel and other Microsoft applications, hassle-free.
4. Recommended PivotTables for data analysis
If you’re new to Excel PivotTables and Charts or don’t know how to analyze your data, AI has you covered. The Recommended PivotTables is another excellent AI feature of Excel that automatically analyzes your given data and provides suggestions. To use it, select your data/table range and click Insert > Recommended PivotTable.
Additionally, you can use the ‘insert’ button at the bottom of PivotTables in ‘Preview’ to add suggested PivotTables to your new or existing spreadsheet.
5. Analyze Data for data analysis
Working with new datasets, especially those you’re unfamiliar with, is always tedious. That’s where Excel’s Analyze Data feature comes into play, an AI-powered tool that automatically identifies data patterns to provide PivotTables and Charts. You can then insert your preferred Charts or tables into your sheet.
To use this, simply select your data and click on Home > Analyze Data. Excel will suggest Charts or PivotTables, and you can choose the ‘+’ button beneath each insight to add it to your sheet.
6. Forecasting
If you want to automatically generate a formula for predicting future values or present results on your sheet, Forecasting is your best bet. One of the best AI features in Excel, Forecast sheets help visualize data without having to do any of the hard work. All you need to do is put your time-based data in a tabular format that has columns with times/dates at consistent intervals, along with a column with values.
To use this, click Data > Forecast Sheet. A dialogue box will appear, where you can set seasonality, confidence intervals, and more. After selecting your preferred options, choose ‘Create’ and a new sheet will be inserted into the file, containing a copy of the formulas, your data, and a chart.
If you’re new to using Microsoft’s in-built AI features and want to master them for professional growth, attend the Be10X Microsoft AI workshop. Learn more here.
7. Geography Data Type for obtaining geographical data
Geographical data is essential for getting key insights into market trends, customer behaviors, and more. If your organization deals with such data, then you must master managing geographical data in Excel, which is easier said than done. Thankfully, Excel has an AI feature for this as well.
You can use the Excel geographical data types that help to recognize and get information about your data’s city, state, country, and zip code. Using this, you can get your data’s population, unemployment rate, birth rate, GDP, and more. To use this, select your desired cell and click on Data > Geography.
8. Insert Data from Picture for quick data inputting from images
When talking about AI features in Excel, it would be incomplete without mentioning one of the most handy features, Insert Data From Picture. If you often deal with paper-written or textual data that you have to manually enter into your spreadsheet, this feature is a must.
Simply click on Data > From Picture > Picture From Clipboard or Picture From File. Using this, you can quickly upload data from a picture taken on your phone or any other device, instead of having to manually enter all the data yourself.
Conclusion
In this day and age of technology, it’s all about working smarter, not harder. Hence, learning and mastering the key AI features in Excel is almost a professional necessity now. These features are designed to streamline your work, especially when you’re handling tasks via Microsoft Suite and need to get things done quickly.
However, if you’re new to such AI tools and features, things may seem daunting. At Be10X, we offer comprehensive AI workshops to help you understand the nuances of using AI technologies for automating Microsoft application tasks and expediting your workflow to be a star performer at work. Click here to learn more.
