Free Training & Career Tips... Subscribe to Get Weekly Career Tips
By Subscribing You are Agreeing to Terms and Conditions
Microsoft Excel has used pattern recognition and machine learning for more than a decade. These features run from Flash Fill and Recommended Charts to Analyze Data and, more recently, Copilot. Combined with Power Query, Power Pivot and Power BI, Excel is a practical tool for business intelligence. BOTI’s Microsoft Office and computer skills courses teach teams to use these features with confidence.
Smart Spreadsheets: The Power of AI in Microsoft Excel
Microsoft Excel started as a spreadsheet for calculations. It now offers data analysis and suggestion features, many of them built on artificial intelligence (AI) and machine learning. These features make it quicker to clean data, spot trends and present results. This article explains how AI arrived in Excel, what each feature does, and how Excel and Power BI work together for business intelligence.
The Evolution of AI in Excel
Microsoft Excel has been around since 1985. Most of its AI features, however, have arrived since the early 2010s. They began quietly, with tools that save typing and suggest layouts. Over time they grew to include automated insights, natural language questions and an AI assistant.
Excel 2013 introduced Flash Fill and Recommended Charts, two early examples of Excel learning from your data. Flash Fill recognises a pattern in a few entries you type, such as splitting full names into first names, and fills in the rest of the column without a formula. Recommended Charts and Recommended PivotTables look at the selected data and suggest suitable ways to summarise and display it.
AI-Driven Capabilities in Excel
Data Analysis and Insights
Analyze Data (formerly Ideas): Microsoft introduced this feature in 2018 under the name Ideas and later renamed it Analyze Data. It uses machine learning to scan a range or table and suggest charts, PivotTables and short summaries of trends and outliers. In Microsoft 365 you find it on the Home tab. It saves time and can point out patterns you might otherwise miss, but you still need to judge whether each suggestion makes business sense.
Predictive Analytics
Forecasting: Excel’s Forecast Sheet, added in Excel 2016, predicts future values from historical data such as monthly sales. It uses a statistical method called exponential smoothing (ETS), the same method behind the FORECAST.ETS function. It produces a forecast line with upper and lower confidence bounds, which helps managers plan with a realistic range rather than a single number.
Data Cleaning and Transformation
Power Query: Power Query began as an add-in and is now built into Excel as Get & Transform on the Data tab. It detects data types automatically, removes blanks and duplicates, fills values down, and splits or merges columns. Its Column from Examples option works like Flash Fill: you show it the result you want and it writes the transformation step for you. Because the steps are saved, you can refresh next month’s data without repeating the clean-up.
Natural Language Processing
Natural Language Queries: Natural language processing (NLP) lets you ask questions about your data in plain English. In the Analyze Data pane you can type a question such as “total sales by region” and Excel returns a chart, table or answer based on your data. Questions work most reliably when the data has clear column headings and sits in an Excel table.
Copilot in Excel
Copilot is Microsoft’s AI assistant in Microsoft 365. It is available in Excel to users with a Copilot licence, for files saved in OneDrive or SharePoint. You can ask it to suggest formulas, add calculated columns, highlight values, build PivotTables and charts, or summarise what the data shows. It works on data laid out as a well-structured table.
Copilot speeds up the work, but it does not replace understanding the data. The Microsoft Copilot in Word, Excel, Outlook and Teams masterclass shows how to write useful prompts and check the results. Our article on using Copilot in Excel gives more examples.
Enhanced Collaboration
Co-authoring: Co-authoring is a cloud feature rather than an AI feature. It lets several people work on the same workbook at the same time when the file is stored in OneDrive or SharePoint. Changes appear for everyone as they are made, and version history lets you restore an earlier copy if something goes wrong. This matters when a team shares one reporting workbook.
Power BI: Extending Excel’s AI Capabilities
Power BI is Microsoft’s business analytics service, and it complements Excel. It handles larger datasets, builds interactive dashboards and shares reports across an organisation. It also includes AI features that look for patterns and explain what drives a result.
Seamless Integration
Power BI uses the same Power Query engine and the same data modelling language (DAX) as Excel’s Power Pivot. Skills learnt in one carry straight over to the other. You can import Excel workbooks into Power BI, and you can analyse published Power BI datasets in Excel PivotTables.
Advanced Visualizations
Power BI reports are interactive. Clicking a bar in one chart filters every other visual on the page, so users can explore complex data without building a new report for each question.
AI-Driven Insights
Power BI’s AI features include Quick Insights and AI visuals such as Key Influencers and the Decomposition Tree. These visuals analyse data to identify patterns, anomalies and the factors that drive a result. They help businesses ask better questions, which people then confirm and act on.
Natural Language Queries
Like Excel, Power BI supports natural language questions through its Q&A feature. Users type a question about their data and receive a visual answer, which makes reports easier to use for people who do not build them.
Business Intelligence with Excel
Many South African organisations do their business intelligence in Excel before, or alongside, Power BI. A typical Excel BI workflow has four steps:
- Connect and clean: use Power Query to pull data from systems exports, CSV files, databases or other workbooks, and save the clean-up steps so the report refreshes.
- Model: load the tables into the Excel Data Model (Power Pivot), create relationships between them, and write measures such as year-on-year growth.
- Summarise: build PivotTables and PivotCharts from the model, with slicers and timelines for filtering.
- Present: arrange the results into a one-page dashboard that managers can read in a few minutes, or move the model into Power BI when you need to share it widely.
Excel suits smaller datasets, ad hoc analysis and reports that a few people use. Power BI suits large datasets, scheduled refreshes and dashboards that many people view. To build these skills step by step, see Excel Pivot Tables, Dashboards and Power Pivot, Analysing Data with Excel and Microsoft Power BI Basics. Our guide to Power BI in South Africa explains where Power BI fits.
Advantages of Using AI in Excel
Increased Efficiency
AI features take over repetitive work such as reformatting columns, suggesting charts and drafting formulas. This reduces the time spent preparing data and leaves more time for interpreting it and acting on it.
Improved Accuracy
Features like Flash Fill and Power Query reduce manual retyping and copy-and-paste, which are common sources of spreadsheet errors. They do not remove the need to check. Always test a sample of the results, especially when a pattern has exceptions.
Enhanced Decision-Making
Analyze Data and Forecast Sheet turn historical data into trends, outliers and projected ranges. Managers can then base decisions on evidence rather than guesswork, as long as the underlying data is complete and current.
Accessibility
Natural language questions and automated insights make analysis more approachable for people who are not spreadsheet specialists. A line manager can ask a plain question and get a chart without writing a formula.
Better Collaboration
Co-authoring and cloud storage let several people update the same workbook at once. Real-time updates and version history reduce the confusion of emailing copies back and forth.
Scalability
The Excel Data Model handles far more rows than a normal worksheet. Moving to Power BI supports larger datasets and wider sharing again. A team can start in Excel and grow into Power BI without relearning its data preparation skills.
Using AI in Excel Responsibly
AI suggestions are only as good as the data behind them. Use these checks in practice:
- Keep data in proper tables with one header row and no merged cells.
- Check AI-generated formulas against a few rows you can calculate by hand.
- Treat forecasts as a range, not a promise, and update them as new data arrives.
- Follow your organisation’s rules on personal and confidential information before using cloud or AI features.
Conclusion
Excel’s AI features have grown steadily, from Flash Fill and Recommended Charts in 2013 to Analyze Data, natural language questions and Copilot today. Together with Power Query, Power Pivot and Power BI, they give organisations a practical route to business intelligence using tools their staff already know.
The features save time, but the value comes from people who know how to structure data, ask the right questions and check the answers. Data analysts, finance teams and managers all benefit from that combination of skills.
Frequently asked questions
Does Microsoft Excel use artificial intelligence?
Yes. Excel uses pattern recognition and machine learning in features such as Flash Fill, Recommended Charts, Analyze Data and Column from Examples in Power Query. Users with a Microsoft 365 Copilot licence can also use Copilot, an AI assistant that suggests formulas, charts and summaries.
Can you do business intelligence with Excel?
Yes. Power Query connects to and cleans data, Power Pivot builds a data model with relationships and measures, and PivotTables, slicers and charts turn the model into dashboards. For large datasets and wide sharing, the same skills carry over to Power BI.
What is the difference between Excel and Power BI for data analysis?
Excel suits ad hoc analysis, smaller datasets and reports used by a few people. Power BI suits large datasets, scheduled data refreshes and interactive dashboards shared across an organisation. Both use Power Query and DAX, so many teams use the two together.
Which BOTI course covers Excel and Power BI for data analysis?
BOTI’s Data Analytics Fundamentals with Excel and Power BI is a 3-day course covering data preparation, analysis and reporting in both tools. It runs in-house, at BOTI’s venue in Sunningdale, Johannesburg, or live online.
To build these skills in your team, book Data Analytics Fundamentals with Excel and Power BI. This 3-day course takes delegates from cleaning data to building reports in Excel and Power BI. Training runs in-house at your premises, at BOTI’s venue in Sunningdale, Johannesburg, or live online. Call BOTI on 011 882 8853 or email [email protected], or request a quote. This course is not accredited. It is delivered as a professional skills programme, and delegates receive a BOTI certificate of attendance.