Copilot for Excel: How to debug formulas

If you’ve ever struggled with a malfunctioning Excel formula or tried to adapt a VBA script from a tutorial to fit your needs, you know how challenging debugging can be. Copilot for Excel is designed to ease these frustrations by offering corrections, alternative approaches, and deeper insights into Excel’s logic.

But while Copilot can assist in refining and troubleshooting existing code, sometimes starting from scratch is more effective. Whether you’re debugging or building anew, Copilot provides the guidance needed to enhance your Excel proficiency and ensure your projects are both robust and efficient.

You can follow along with the exercise file below:

In this first example, I’m attempting to calculate a running total of the Sales column and am encountering some issues. I believe I need to use mixed referencing, but I can’t seem to get it to work (I know this is a very basic example, but let’s be honest—we all have moments when even simple Excel problems become challenging!). In this case, I’m going to ask Copilot to examine the formula and tell me what’s wrong:

Copilot takes an unexpected turn here, introducing a new column without tweaking our existing formula, but instead proposing a completely new one. In every instance I’ve observed, Copilot tends to use structured table references in its outputs, not the alphanumeric references we were using. This makes sense, as it’s required your data be in a table for use with Copilot. Thus, it’s essential to familiarize yourself with these before proceeding with Copilot.

In a slightly more complex example, consider trying to rank sales figures by region. Perhaps a colleague had previously found the below solution, but you notice it ranks your data in reverse order, where the lowest sales figure is ranked as number one. You want the opposite.

=SUMPRODUCT(([Region] = [@Region]) * ([Sales] < [@Sales])) + 1

In this scenario, you could either ask Copilot to debug your existing formula or copy and paste your entire formula into Copilot and request it to reverse the rank by group. However, based on my experience, Copilot may become confused with more complex columns and calculations that you want to debug. You might actually have better luck by directly requesting the calculated column you need.

=COUNTIFS([Region],[@Region],[Sales], ">" & [@Sales]) + 1

In this case, I decided to start from scratch to see what Copilot could offer. Interestingly, it provided a solution that was not only different but also more readable than the previous one. It’s crucial to avoid the sunk cost fallacy when developing code with Copilot. Often, Copilot will present a completely different approach that more effectively addresses the problem. Just because you’ve invested considerable effort in a previous solution doesn’t mean you should stick with it, especially if Copilot suggests a more efficient alternative.

At the time of writing, Copilot was primarily configured to assist with operations within a table, such as creating a calculated column. However, if you’re interested in creating a more summary or aggregation-type formula—for instance, calculating the average sales per region—you also have options available. To utilize these features, ensure your mouse is positioned within any table. Copilot’s functionality is activated only when the data is tabled.

If you have a specific formula you need help with, you can simply copy and paste it and request assistance. For example, let’s say we are having issues getting an AVERAGEIF() formula to work:

Here again, you have the option to write the formula from scratch rather than debug it. In this case, Copilot will assume that you want to add the resulting column to the table. You probably don’t want that, but you can copy and paste the result elsewhere in the workbook.

As you can see, there are essentially two methods to obtain formula assistance with Copilot. You can either collaborate with Copilot to create a formula from scratch, or seek help in true debugging of an existing one.

Many of us know from experience that starting from scratch is often simpler. However, sharing your current work—along with the errors and what you intend to achieve—can provide valuable insights into your data, relevant variables, and more.

Remember, this debugging process and its trade-offs aren’t confined to table calculations and aggregations! In theory, you can also seek assistance for debugging code, such as VBA macros or Python scripts.

Of course, the more unique your script is, the more likely it is that Copilot will require some guidance on your specific needs. Therefore, in cases like this, you might find it beneficial to work with Copilot to refine what needs fixing, rather than starting from scratch:

While we’re at it, why not consult Copilot for its perspective on whether to debug a formula versus building one from scratch? You can also explore its opinions on how to debug with it.

These inquiries to Copilot in Excel can extend beyond questions strictly related to your table. Engaging with Copilot in this manner will certainly enhance your understanding of how the tool operates and thinks.

What questions do you have about getting debugging help with Copilot in Excel? Do you find it easier to ask Copilot for debugging assistance, or do you prefer to rewrite the formula from scratch? If you have discovered any interesting use cases or examples, please share them in the comments. Also, if you’re interested in getting your team proficient with Copilot for Excel, consider enrolling in my corporate workshop linked below: