One of my most common uses of AI is to help troubleshoot an errant Excel formula. If you are anything like me, then all of your data (work and personal) eventually finds its way into a spreadsheet. My wife jokes that I have a spreadsheet for everything. And she is not wrong. She even got me an Excel ornament for our Christmas tree!

Since everything lives in Excel I frequently have formulas that are not doing what I need to them to do or need a new formula to analyze something in a different way. I find Copilot to be quite a gifted Excel artist. Below is a safe, repeatable workflow that avoids sharing sensitive data and gives the stepwise formula debugging tactics I use for my spreadsheets.

For this workflow I am using Copilot Windows app; not to be confused with In‑App Excel Copilot (which I will denote as Copilot-AppEx). Microsoft has chosen to refer to all its different versions of AI as Copilot, so for clarity:
• Copilot Windows app is the standalone Copilot application accessed from the taskbar (if you pin it) or through the start menu (the app icon is called “Copilot” or “Copilot, Microsoft 365 Copilot”). Copilot Windows app has its own User Interface (UI) that pops up with “What can I help you with today?” when you open it. When I refer to Copilot I mean the Windows app version unless otherwise noted.
• The In‑App Excel Copilot (Copilot-AppEx) you can only access inside of Microsoft Excel when your workbook is saved in OneDrive or SharePoint. I currently find Copilot-AppEx in the top right corner of the Excel Home ribbon. Clicking Copilot-AppEx opens a dialogue box inside of Excel on the right side.

Ok, hopefully that is all clear. here is a typical work flow:

1. The biggest challenge is getting the necessary information into Copilot so it can diagnose and suggest a path forward. For this I almost always create a dummy data set that mimics this issue I want to work on with Copilot . There are several reasons for this:
   a. I do not want to share any sensitive information with Copilot or Copilot-AppEx. A future blog post is coming on the details of this, but for now my advice is to never copy sensitive information into any chat prompt (i.e., patient, proprietary, regulated data, etc.). Always use dummy or anonymize data in your prompting unless you are crystal clear how your AI is using your information.
   b. It’s best to keep the data set as simple as possible to answer my question. Copilot quickly gets into complex formula suggestions, so the simpler the data set the easier I find to keep solutions focused on the issue at hand.
   c. Limit each request to a single problem formula. Fix the most urgent issue, once you have a solution populate it into your real dataset to ensure it works and then move on to the next formula if you have multiple issues. When I troubleshoot multiple formula problems Copilot suggests multiple solutions at once that get too confusing to track and increases the probability that Copilot starts running down unproductive rabbit holes.

And boy, can it generate rabbit holes. While I do enjoy learning the myriad different ways Excel can get to the same answer, we are at work and need to remain productive 😊

2. Enter the dummy data into the Copilot prompt:
   a. You can either drag/drop the whole spreadsheet (ensure only dummy or anonymized data is contained in the worksheet); or
   b. Copy/paste the cells involved in the issue. Just be aware when going this route that when you paste your cells into Copilot they do not look like Excel in the prompt dialogue window but Copilot is smart enough to keep track of cell names (A1, B2, etc.) and you can refer to the specific cells during your dialogue.
3. Describe your question or issue in the prompt, the desired outcome and hit return.
   a. Microsoft limits how much Copilot actually sees in your spreadsheet cells. So I usually include the full formula (if one exists) to the prompt as Copilot just sees the calculated value of the cell. Here is an example prompt from a recent Copilot Excel troubleshooting for a cost spreadsheet that contained various project CDMO costs over time (this spreadsheet had two tabs COST and LAB):

“In the COST tab cell D2 I am trying to sum the 2024-Q1 costs in the LAB tab but the formula I have entered: =SUMPRODUCT((LAB!$E$2:$N$2>=D3)(LAB!$E$2:$N$2<=D4)(LAB!E20:N20)) is returning a #value response. What needs to be corrected?”

4. Copy the Copilot suggested solution back into my dummy dataset and see if it worked. Copilot will typically return multiple solutions to test. I find it helpful to read the entire thought process provided before the solutions to ensure it is on the right track.
   a. If Copilot has drifted and the solutions are off base go back to #3 and rephrase your prompt to be more clear on what you want.
   b. If the solution looks reasonable, input it into your dummy dataset and see if it works. I prefer to first verify solutions in the dummy dataset so I do not risk messing up my real dataset. Plus, if the solution does not work it’s easier to describe the issue to Copilot using the same dummy Excel architecture than to introduce another variable of different Excel structure (rows, columns, cells, etc.)
5. From this point on it becomes a two-way discussion with Copilot on the issue and working through the proposed remedies to ensure I understand what is being proposed.
6. If you get a solution that works, great! Next problem. If the solution did not work I update Copilot on the result and usually copy paste the results into Copilot so it can see what is happening. Here is an example of a prompt for a solution that did not work using the same problem described in #3.

“Ok, it looks like the issue is the “N/A” I am using when there is no value entered in LAB tab cell J20: =IF((SUMPRODUCT($D$3:$D$19, J3:J19))=0,”N/A”,SUMPRODUCT($D$3:$D$19, J3:J19)). Is there a better place holder than “N/A” when a row is blank or a different formula that will not result in an error for the COST tab cell D2 equation if N/A is returned?”

7. Go back at #3 and continue iterating until the issue is resolved.

This iterative type of a process has proven to be very successful. Frequently, Copilot gives me a novel solution that fixes the issue. But the more surprising part is how many times the solution does not come from Copilot but rather via the back and forth of working through the problem with a different perspective that gives me a new idea to implement and solve the issue.

I will agree this is a clunky approach with a lot of extra copy/paste steps. However, I choose this method for several reasons:

1. I control what data gets shared with Copilot. I do not want any sensitive information being shared and by using dummy datasets, copy/paste, etc. I am always in control of what gets sent to Copilot for review/action.
2. It forces me to evaluate any and all changes being made to my spreadsheet and decide how and where to apply the change.
3. This forces me to understand at some level what the formula I am using is actually doing. Excel formulas get extremely complex and I want to understand all the formulas that are being used in my spreadsheet.

I tried some limited experimenting with Copilot-AppEx but ran into several issues that make it untenable as of this writing.

1. Before Copilot-AppEx will open, it requires that your worksheet is saved in OneDrive (OD) or SharePoint (SP) so Copilot can read and write directly to the file. All my work files are stored in a work database that is not OD or SP, which means I have to move my dummy data to OD/SP rather than work on it my normal work environment. Not a dealbreaker and something I would overlook if Copilot-AppEx worked great, but alas, on to point #2.
2. At this stage Copilot-AppEx is clumsy, I am sure this will improve but for now it’s painful to use on a laptop. The UI is squeezed into a tiny space on the right and the Copilot-AppEx bubble suggestions block the majority of the thought process detail that is essential to understanding what Copilot-AppEx is proposing. On my laptop I had to scroll line by line to read it.
3. The whole point of using Copilot-AppEx is that it can go directly to your data and manipulate but when I asked it to correct a formula I got this response from Copilot-AppEx “I do have access to the contents of your file, but I can’t directly view the Excel grid or cell references like B2 visually. Instead, I see the raw data and structure.” This is essentially what Copilot Windows app reads so you need to provide the same level of prompt instruction inside a very tiny dialogue box.
4. Copilot-AppEx does not allow you to select which model of AI is used. Whereas Copilot gives you several options. My experience is that the choice of model has a significant impact on the quality of results returned.

To be fair, several of these issues are things Microsoft is actively working on and should improve quickly. For example, to address Point#2 Microsoft is rolling out in-grid explanation and formula features that aim to make Copilot AppEx reasoning visible next to your data. UI density remains an issue on laptops so it should be better suited to run on large desktop monitors.

Ideally Copilot gets to the point that it can see everything in your spreadsheet, has a seamless UI regardless of the device, and you can have an active dialogue about what the data is saying while the AI makes manipulations and suggestions as you go. But we are not there yet.

However, that limitation does not take diminish what is currently available. Two years ago, when I had an Excel issue, I would spend hours on Google reading posts, watching YouTube videos, etc. hoping someone had posted a solution to my exact issue using the same Excel version I was using. Frequently, I could not find exactly what I was looking for and had to settle for something half as good as what I wanted.

But AI has changed all of that. Now I can go super deep in real time with Copilot on just about any Excel tangent my heart desires. Often this requires some coaxing to get it moving in the preferred direction but so far with enough patience and perseverance I have achieved 100% resolution of my Excel issues, and learned several new tricks in the process. To help you get started with your Excel troubleshooting here are my SBAI Tips for this blog.

SBAI Tip#4 A checklist you can copy-paste into the Copilot prompt to assist building your Excel resolution prompt, just update each bullet with your specific information:
• Purpose: one sentence describing desired output
• Add Dummy data (copy/paste cells or drag/drop the workbook)
• Original formula: paste full cell formula text
• Description of the issue and/or an example of the desired result

SBAI Tip#5 Summary workflow:

1. Prepare a small dummy dataset that reproduces the issue (6–12 rows, clear headers)
2. Ask a single, focused question (one formula at a time) using the SBAI Tip#4 checklist
3. Copy the suggested solutions back into your dummy data workbook and test
4. Report the results to Copilot and iterate until resolved

Quick Post Note. I write the first draft of every blog without any AI input. Only when the draft is completed do I have Copilot review for readability and accuracy. This way I am forced to clarify my thinking first and try to leave my personal style intact in the final draft. I only use AI to clean up inaccuracies, clarify points that seem unclear and remove redundant text. During this review Copilot was quick to point out the benefits and future hopes of Copilot-AppEx.

AI is changing so fast that most certainly something in the above post has already changed by the time you are reading this. Hopefully, Copilot’s feedback is a positive sign of the improvements Microsoft is rolling out in Copilot-AppEx.

I expect in-app use of AI to become the norm for professional use of AI in our jobs, not a standalone AI user interface separated from the app (Windows Copilot). Here are some of Copilot’s best points of where Microsoft is heading to make the Copilot-AppEx more user friendly:

• “Microsoft has added explicit in grid Copilot capabilities in 2025, including a COPILOT worksheet function and an “Explain formula” feature that surfaces formula breakdowns inline in Excel; these features make in app assistance more powerful than earlier versions.
• Recent Copilot/Excel updates increasingly allow Copilot to generate formulas, charts, Python analyses, and to modify the workbook directly when the file is stored in the cloud. If you describe Copilot as unable to see formulas at all, note that this is platform- and permission-dependent: the Windows app Chat (Win+C) may not have the same direct grid-level access as the in app Copilot when the workbook is cloud-backed.
• When using Copilot Windows app on local files, it may not have direct grid-level access to your workbook or cell formulas; the in app Excel Copilot has broader grid access when the workbook is cloud-backed.”