Every time I run a Copilot for Excel workshop, there’s one question that always comes up:
“How can I make Copilot remember my prompts so it can do my weekly reports automatically?”
Short answer?
You don’t.
Long answer?
That’s not what Copilot is built for. And you’ll save yourself a lot of headaches if you stop trying to make it something it’s not.
Let’s talk about why Copilot is the wrong tool for routine reporting, what kind of work it is meant for, and which tools in the Modern Excel stack you should actually be using for repeatable reporting workflows.
Copilot is built for creativity, not consistency
Generative AI tools like Copilot excel at (pun intended) creative, open-ended work: exploring, brainstorming, and discovering new insights. Think of it as a brainstorming partner, not a factory worker.
Let’s say you have a dataset of quarterly sales. You might ask Copilot:
- “Summarize which regions had the biggest change this quarter.”
- “Find anything unusual in the top-selling products.”
- “Can you visualize sales trends over time?”
This is where Copilot shines. It helps you see your data from new angles. It’s designed for ad hoc analysis, when you’re exploring, questioning, and trying to understand.
But when you ask Copilot to repeat a series of steps exactly the same way every week, filtering rows, adding calculated columns, or reshaping data, it’s like asking an artist to work an assembly line. That’s not what it’s meant to do.
Copilot is creative by design, not deterministic by nature. And when it comes to reporting, you don’t want creativity. You want consistency.
Power Query should be your backbone of Excel reporting
If you want Excel to follow the same steps every single time, there’s a built-in tool made exactly for that: Power Query.
Power Query lets you:
- Import data from multiple sources (Excel files, databases, websites, etc.)
- Clean and transform it (remove duplicates, change data types, merge tables)
- Refresh it with a single click
And it remembers every step. You can trace, audit, and repeat your data cleaning process without lifting a finger.
In short:
Copilot = “Help me think.”
Power Query = “Help me do.”
The beauty of Power Query is that it’s deterministic. You know exactly what will happen when you click “Refresh.” It doesn’t reinterpret your instructions; it just executes them faithfully, every time.
This makes it perfect for routine reports, ETL (extract-transform-load) pipelines, and data prep workflows that need to be bulletproof.
So, if your team is eager to “get into AI,” start here. Master Power Query first. Then you can safely bring Copilot into the mix for analysis, summaries, and creative exploration.
If you’d like a great introduction to Power Query for Excel, check out my book Modern Data Analytics in Excel:
Use Python in Excel for smarter, reproducible analysis
Once your data is structured, the next tool in your Modern Excel stack is Python in Excel.
Python brings repeatability and precision to the kinds of analytics that Copilot can’t reliably reproduce. It’s made for tasks where math, modeling, and consistency matter.
With Python in Excel, you can:
- Create forecasts and time-series models
- Automate calculations across large datasets
- Build custom charts and visualizations
- Run simulations and advanced statistics
And unlike Copilot, Python code doesn’t “interpret” your instructions differently each time. Once you write a Python cell, it does the same thing every refresh. That makes it ideal for analytical repeatability… the key to trustworthy reports.
For example, you might build a small Python script that calculates month-over-month growth or projects next quarter’s sales. You can test it, verify it, and know it will run identically every time the workbook updates.
This doesn’t mean Copilot is useless here. Copilot can help write your Python code, explain it, or spot mistakes. But the logic itself should live in your script, not your prompt history. That’s how you scale reliability.
If your team’s reporting needs go beyond simple aggregations—if you’re forecasting, modeling, or visualizing trends—Python in Excel is your next stop after Power Query.
For some quick wins with Python in Excel, check out my short course on Gumroad:
Automate and deliver with Power Automate
Once Power Query gives you clean data and Python delivers accurate analysis, it’s time to close the loop: Power Automate.
Power Automate is where reporting becomes a system. It connects Excel to the rest of your workflow, making sure your insights actually reach people on time, every time.
You can set flows that:
- Move data between systems
- Email reporting and KPIs to your team or post it to Teams or SharePoint
- Log completion or archive results for auditing
The result? Reports that run themselves. No copy-pasting, no missed steps, no human errors.
And because every flow is visual and trackable, you can always see what happened, when, and why. That’s the kind of accountability Copilot can’t provide.
Power Automate turns your Excel reports into living workflows. Once your structure is built and tested, you can schedule, distribute, and monitor it, all without manual intervention.
Looking to get started with Power Automate: Check out my LinkedIn Learning course:
Copilot comes after the foundations
Once you’ve built your Modern Excel foundation with Power Query handling your data prep, Python managing your analysis, and Power Automate keeping everything on schedule… then it’s time to bring Copilot back in.
At this stage, Copilot becomes a creative assistant again. You can use it to:
- Draft insights from your refreshed data
- Suggest new KPIs or comparisons
- Generate visuals and summarize patterns
- Turn results into executive summaries or reports
But here’s the truth too many people miss: AI won’t fix sloppy spreadsheets or bad habits.
If your workbooks are full of merged cells, inconsistent column headers, or manual copy-paste steps, no AI model can save you from chaos. It’ll just automate your mistakes faster.
That’s why building strong Excel foundations isn’t optional. You can’t skip the groundwork and expect Copilot to fill in the gaps. The best AI tools amplify what’s already there; if your Excel fundamentals are weak, AI will only magnify the mess.
Before your team learns to prompt Copilot, make sure they can build clean tables, write structured formulas, and manage data with Power Query. Once the basics are solid, Copilot becomes a genuine multiplier, not a magic wand.
Build your Modern Excel learning plan
If you want your organization using AI in Excel the right way, not just what sounds trendy, let’s talk.
I’ll help your team get a tailored learning plan that fits your actual workflows and goals. Together, we’ll build your foundation with Power Query, expand your analytical range with Python in Excel, and streamline everything with Power Automate.
Then, once the structure is in place, we’ll layer in Copilot where it adds the most value: helping your people think faster, explore smarter, and analyze creatively.
📩 Get in touch below to build a training plan that sets your team up for success with the entire Modern Excel stack.