Copilot in Excel: Formula Suggestions

Overview

Excel formulas are powerful, but they can be intimidating. Many government professionals work with spreadsheets daily yet avoid writing formulas because the syntax is unfamiliar or the logic is complex. When they inherit spreadsheets from colleagues, the formulas inside are often opaque, making it risky to modify anything. Copilot in Excel changes this by letting you describe what you need in plain English, explain formulas you do not understand, and fix errors without memorizing syntax.

This video shows you how to use Copilot to create, understand, and troubleshoot formulas in Excel.

What You’ll Learn

• Creating Formulas: How to describe a calculation and let Copilot write the formula
• Understanding Suggestions: How Copilot presents formulas and how to review them
• Explaining Formulas: How to get plain-English explanations of complex existing formulas
• Troubleshooting: How to use Copilot to identify and fix formula errors

Script

Hook: Stop guessing at formulas

When was the last time you stared at an Excel formula and had no idea what it did? Or needed a calculation but could not figure out the right combination of functions to get there? Or saw a #VALUE! error and did not know where to start fixing it?

You are not alone. Excel has over four hundred functions, and most people use fewer than ten. The rest sit unused — not because they are not useful, but because the syntax is hard to remember and the nesting gets complicated.

Copilot in Excel lets you describe what you need in plain English and builds the formula for you. In the next seven minutes, you will learn how to create, understand, and fix formulas without memorizing a single function.

Prerequisites and setup

The prerequisites for Copilot formula features are the same as for other Copilot features in Excel. You need a Microsoft 365 Copilot license. Your data must be formatted as an Excel table — select your data range, go to Insert, and click Table. AutoSave must be enabled, which means your file needs to be saved to OneDrive or SharePoint.

Copilot in Excel is fully supported in GCC, GCC High, and DoD government cloud environments. Once your data is set up, click the Copilot button in the Home tab to open the Copilot panel.

Asking Copilot to create formulas

Instead of figuring out syntax yourself, describe what you want and Copilot builds the formula.

Open the Copilot panel and type “Add a column that calculates the difference between Budget and Actual.” Copilot identifies the columns, generates a formula, and shows you a preview of the calculated values before you accept.

Requests can be complex. Try “Create a column that flags rows where spending exceeds 110 percent of the budget.” Copilot generates an IF formula. Ask for “A column that calculates the number of days between submission date and approval date” and Copilot writes a date difference formula using your column names.

Here is a government scenario. You manage a program budget spreadsheet. You ask Copilot, “Add a column that shows the percentage of budget obligated.” Copilot creates the formula and formats it as a percentage. Then you ask, “Flag any program where remaining balance is less than ten percent of planned budget.” Copilot writes a conditional formula identifying programs at risk. Need a VLOOKUP or nested IF? Describe the logic in plain English and Copilot translates it.

Understanding formula suggestions

Copilot does not insert formulas blindly. It shows you a preview of the proposed formula and calculated results so you can verify before accepting. If the results look wrong, reject and refine your request.

Copilot uses structured references — your table column names — rather than cell references like A2 or B3. Instead of =B2-C2, you see =[@Budget]-[@Actual], making formulas readable.

If the first suggestion is close but not right, iterate. Tell Copilot, “Only include rows where status is Active” or “Use Fiscal Year instead of Calendar Year.” Reference your column names explicitly and describe logic step by step for the best results.

Explaining existing formulas

Government teams frequently pass workbooks from one person to the next, and the formulas inside can be mysterious. Copilot explains them in plain English.

When you encounter a complex formula, select the cell and ask Copilot “Explain this formula” or “What does this SUMIFS formula do?” Copilot breaks down each component. For nested formulas, it explains from the inside out — the VLOOKUP first, then the error handling, then the conditional logic. Each layer is described clearly.

Here is a government scenario. You take over a budget tracking workbook from a colleague who left the agency. One column has a formula combining INDEX, MATCH, and multiple IF conditions. Instead of spending thirty minutes deciphering it, ask Copilot to explain it. Copilot tells you it looks up the funding source from a reference table based on program code, checks whether the obligation type is direct or reimbursable, and returns the appropriate rate. Now you understand what it does and whether you need to modify it.

Over time, Copilot becomes a learning tool. As it explains formulas, you start recognizing patterns — learning what SUMIFS does, how INDEX MATCH works, and when to use IF versus IFS.

Troubleshooting formula errors

When you see #VALUE!, #REF!, #N/A, or #DIV/0! errors, ask Copilot for help. Type “Why is this formula showing an error?” and Copilot examines the formula and data to identify the cause. It might tell you a text value exists in a numeric column and suggest wrapping the calculation in an IFERROR function.

Here is a government scenario. After a data restructure, formulas in your procurement tracker show #REF! errors. Copilot identifies that a column was renamed and the formulas reference the old name. It suggests updated formulas using the new column name.

Prevention tip: Copilot uses structured table references that adapt when you rename columns or add rows, reducing errors from the start.

Close: Formulas without the frustration

Let us recap. Copilot in Excel lets you create formulas by describing what you need — no syntax memorization required. It explains complex formulas in plain English so you understand inherited spreadsheets. And it troubleshoots errors by identifying causes and suggesting fixes.

Here is what to do next. Think of a calculation you have been doing manually or a formula you have been avoiding. Open the spreadsheet, make sure the data is in a table, and ask Copilot to create the formula. Then find the most complex formula in a workbook you use regularly and ask Copilot to explain it.

Formulas are one of Excel’s most powerful features. Copilot makes them accessible to everyone.

Sources & References

• Generate formula columns with Copilot in Excel — Guide for creating formula columns using Copilot
• Get started with Copilot in Excel — Prerequisites and getting started guide
• Copilot adoption resources — Adoption guidance and government cloud availability