Excel is still the workhorse of offices across Uganda, from SACCO loan registers in Mbarara to sales reports in Kampala head offices. Many people know the basics but get stuck on complex formulas, messy data or turning numbers into insight. This is where AI helps. Learning how to use AI for data analysis in Excel can save you hours each week, whether you use the AI features built into Excel or a chatbot alongside it. This guide shows you practical workflows with examples you can try today.
Your AI options for Excel
1. Built in Excel tools
- Analyze Data (previously called Ideas) in Excel for Microsoft 365 looks at a table and suggests summaries, trends and charts. You can also ask it simple questions in plain English.
- Copilot in Excel is Microsoft's AI assistant, available with certain Microsoft 365 plans. It can create formulas, highlight patterns, build PivotTables and explain data when your data is in a proper Excel table.
- Flash Fill is an older pattern recognition feature that fills a column once you type a couple of examples, such as splitting full names.
2. General AI chatbots
Tools such as ChatGPT, Gemini, Claude and Microsoft Copilot on the web can explain functions, write formulas, create VBA macros and suggest how to structure analysis. Some can also read uploaded spreadsheets. They work with any version of Excel, including older desktop versions common in many offices.
Step one: prepare your data
AI works best with clean, well structured data. Before you ask for help:
- Put your data in one table with a single header row.
- Give each column a clear name, such as "Date", "Branch", "Product", "Amount UGX".
- Remove merged cells and blank rows in the middle.
- Keep one type of data per column, so dates are dates and numbers are numbers.
- Convert the range into an Excel table using Ctrl + T. Many AI features require this.
Using AI to write formulas
This is the most common and useful way to use AI with Excel. Describe what you want in plain words, mention your column letters, and ask for the formula.
Example prompts
- "In Excel, column A has dates, column B has branch names (Kampala, Jinja, Gulu) and column C has sales in UGX. Write a formula to total sales for Jinja in March."
- "I have customer phone numbers in column D, some starting with 07 and some with 256. Write a formula to convert them all to the 2567 format."
- "Write a formula that marks a loan as Overdue if the due date in column F is before today and the balance in column G is more than zero."
The AI might suggest SUMIFS, IF, TEXT or XLOOKUP functions. Paste the formula into one cell, check the result manually on a few rows, then copy it down.
Ask for explanations too
Do not just copy formulas. Add "explain how it works step by step." Over time you will learn the functions yourself, which makes you faster and more valuable at work.
Using AI to clean messy data
Data exported from systems, Google Forms or Mobile Money statements is often untidy. AI can help with:
- Standardising text: "Kla", "KAMPALA" and "kampala " all becoming "Kampala".
- Splitting columns: separating first and last names, or dates and times.
- Removing duplicates while keeping the latest record.
- Fixing dates stored as text in mixed formats.
A useful prompt: "Give me step by step instructions in Excel to clean this column of district names so spelling is consistent. Use Power Query if that is easier." Power Query, found under the Data tab, is excellent for repeatable cleaning, and AI can walk you through it.
Using AI to summarise and analyse
With Analyze Data
- Click any cell in your table.
- Go to the Home tab and click Analyze Data.
- Review the suggested charts and PivotTables.
- Type a question such as "total sales by branch" or "which month had the highest expenses".
- Insert any useful result into your workbook.
With Copilot
If your organisation has Copilot enabled, open it from the ribbon and try prompts like "Show total revenue by product category", "Highlight the top 10 customers by sales" or "Add a column calculating profit margin." Review what it creates before relying on it.
With a chatbot
If you cannot upload the file, describe its structure. For example: "I have a table of monthly expenses for a school with columns Month, Category, Amount. What analysis would help the bursar spot overspending, and how do I build it in Excel?" The AI will suggest PivotTables, percentage changes and charts, with steps.
Using AI to build charts and dashboards
Ask AI which chart fits your goal. A line chart suits trends over months, a bar chart suits comparing branches, and a stacked column can show expense categories. You can ask: "Suggest a simple one page dashboard layout in Excel for weekly sales by branch, with steps to create it using PivotTables and slicers."
Using AI for macros and automation
If you repeat the same steps every week, AI can write a VBA macro. Describe the task: "Write a VBA macro that copies the Sales sheet, removes rows where Status is Cancelled, and saves it as a new file named with today's date." Always test macros on a copy of your file first, and only enable macros you understand or trust.
Accuracy and privacy: important cautions
- Check results. AI can produce formulas that look right but contain errors. Test with rows where you know the answer.
- Do not upload sensitive data to public AI tools. Payroll, client details, national ID numbers and bank information should be removed or replaced with sample data first.
- Follow company policy. Your employer may have approved tools for business data.
- Understand before presenting. If a manager asks how you got a figure, you should be able to explain it.
A simple practice exercise
- Create a table with 30 rows of sample sales: Date, Branch, Product, Quantity, Price.
- Ask AI for a formula to calculate Total = Quantity x Price.
- Ask for a PivotTable showing totals by branch and month.
- Ask which chart best shows the result, then build it.
- Ask AI to write three insights a manager would care about, then check each against your data.
Frequently asked questions
Do I need Microsoft 365 to use AI with Excel?
Built in tools like Analyze Data and Copilot need Microsoft 365, and Copilot may need an extra licence. Free chatbots work alongside any version.
Will AI replace Excel skills?
No. AI speeds you up, but you still need to understand your data, check results and explain them. People who combine Excel skills with AI tend to stand out.
Can AI work with Google Sheets too?
Yes. Most formulas are similar, and Google offers its own AI features in Sheets with some Workspace plans.
Conclusion
AI turns Excel from a tool you struggle with into one that works for you. Start with clean tables, ask clear questions, use AI to write and explain formulas, and always check the results. Protect sensitive data, and keep learning the functions behind the answers. With a little practice, analysis that once took a day can take an hour.