Microsoft Copilot expands to simplify data analysis in Excel
Microsoft recently announced that its AI assistant, Copilot, is being further integrated into Excel, allowing users to analyze data, create charts, and generate insights using natural language commands. This aims to make complex spreadsheet tasks more accessible.
Today's framework: BAB — Before · After · Bridge
Create a Smart Family Budget Analysis and Chart
You'll end up with a structured, easy-to-read table of your monthly finances and a clear visual summary of where your money goes.
The prompt
Before: I am currently staring at a messy list of our family's monthly expenses. It is hard to see where the money is going, and I feel overwhelmed trying to decide what we can actually afford to save for our upcoming summer holiday.
After: I want to have a clear, organized breakdown of our spending categories, a calculation of our remaining monthly savings, and a simple text-based chart showing which categories are our biggest expenses.
Bridge: Using the logic of the new Excel Copilot, please analyze the following raw expense list: Housing ($1,500), Groceries ($600), Dining Out ($300), Utilities ($200), Transport ($150), Subscriptions ($50). Our total monthly take-home pay is $4,000. Organize this into a logical table, calculate the total spent versus total saved, and provide three specific suggestions for saving an extra $200 this month for our holiday.
Result: A formatted data table with a spending breakdown and a text-based chart showing expense distribution.
How to use it
1.Copy the prompt and paste it into ChatGPT or Gemini.
2.Replace my example numbers with your actual monthly spending and income.
3.Review the calculated savings and the text-based chart the AI generates.
4.Copy the results into a blank Excel sheet or Google Doc to track your holiday savings goal.
Things to watch for
·AI is great at math but always double-check the final totals with a calculator.
·Do not include sensitive information like bank account numbers or your full name.
·You can ask the AI to change the categories if they don't match your lifestyle.
Why this prompt works
This prompt uses the BAB (Before, After, Bridge) framework to give the AI context. By describing the 'Before' (feeling overwhelmed), the AI understands the need for clarity. The 'After' defines the exact goal, while the 'Bridge' specifically asks the AI to act like a data assistant. This structure ensures you get a structured table and actionable advice rather than just a simple list of numbers.
Example AI response
Monthly Budget Analysis Table:
- Housing: $1,500 (37.5%)
- Groceries: $600 (15%)
- Dining Out: $300 (7.5%)
- Utilities: $200 (5%)
- Transport: $150 (3.75%)
- Subscriptions: $50 (1.25%)
Total Expenses: $2,800
Monthly Income: $4,000
Monthly Savings: $1,200
Spending Chart:
[##########] Housing
[#### ] Groceries
[## ] Dining Out
[# ] Utilities/Other
Cost-Saving Suggestions for Summer Holiday:
1. Reduce 'Dining Out' to once a week to save $150.
2. Review 'Subscriptions' and cancel one unused service to save $15.
3. Try 'Meatless Mondays' to trim $35 off the grocery bill.
Sample only — your AI's answer will be different. That's the fun part.
Try a variation
For Small Business Owners
Before: I have several project expenses and client payments for my freelance business that are all mixed up.
After: I need a professional summary showing my net profit for the month and a list of my top three business costs.
Bridge: Please analyze these figures: Client A ($2,000), Client B ($1,500), Software ($100), Marketing ($300), Equipment ($500). Create a profit-and-loss table and suggest a tax savings amount of 25%.
Result: A professional business expense table with net profit calculations.
For Personal Health Tracking
Before: I've been tracking my daily steps and calories for a week on sticky notes, but I can't tell if I'm improving.
After: I want a weekly progress report that shows my average steps and identifies my most active day.
Bridge: Analyze these daily steps: Mon (5k), Tue (7k), Wed (4k), Thu (10k), Fri (8k), Sat (12k), Sun (6k). Create a table comparing these to a goal of 8k steps a day and highlight the wins.
Result: A weekly health activity log with performance insights.
For Home Renovation Planning
Before: I have a $5,000 budget for a kitchen refresh but I'm worried about overspending on materials.
After: I need a prioritized shopping list that fits my budget and shows what percentage of the money goes to each item.
Bridge: Break down these costs: Paint ($200), New Tiles ($1,500), Cabinet Handles ($300), Lighting ($500), and New Appliances ($2,500). Tell me if I am over budget and create a list sorted from most expensive to least.
Result: A prioritized renovation budget table with percentage breakdowns.
Power tips
Ask the AI to 'format this as a table' if it gives you a plain list.
Paste in your own categories like 'Pet Care' or 'Hobbies' to customize the results.
Follow up by asking 'Can you turn this into a 12-month projection?' to see long-term savings.
If you use Excel, ask the AI to 'Write the Excel formula for the savings percentage'.