Guides

Copilot in Excel Prompt Cookbook: 40 Prompts for Analysts, Plus a Verify Checklist

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 7 October 2026 · Last updated: 7 October 2026

Rani asks Copilot: *"What was revenue in Pune in July?"* The answer looks confident. But the Ruhi Organics export writes Pune three ways ("Pune", "pune", "Pune ") and has one duplicate order. The practice file's Check sheet shows the correct figure is ₹8,280, while a formula that matches only the exact word "Pune" gives ₹1,860. Good prompts plus a quick check give you the right number. This cookbook gives you both.

Open Copilot with the Copilot icon in the lower-right corner of Excel, pick edit mode to change the workbook, plan mode to review a plan first, or chat mode to ask questions without changes, and describe the task in plain words with the sheet, columns and output you want. Then verify: recalculate key numbers with SUMIFS or a PivotTable, count rows, click into new formulas and open cited web sources.

Download the practice file (copilot-prompts-for-analysts.xlsx)

Excel file, 11 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Before you start: what Microsoft's documentation says

• Where: select the Copilot icon in the lower-right corner of Excel. Copilot in Excel is available on Windows, Mac, the web, iPad, iPhone and Android.
• Three modes: *edit* (the default) changes the workbook directly; *plan* creates a plan for you to review first; *chat* (Microsoft also calls it chat only mode) answers questions without changing the workbook. Edit, plan and chat are available on Windows, Mac and the web; mobile apps have fewer modes.
• What it can do: edit sheets, write formulas, create charts and PivotTables, find insights, sort, filter and highlight, search the web with citations, bring data from other workbooks, and follow custom skills for repeat tasks.
• Licence: Microsoft lists a Microsoft 365 Personal or Family subscription with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible business or enterprise subscription.
• Limits: Microsoft's FAQ says Copilot can sometimes make mistakes, misinterpret information or produce inaccurate results, and advises against using it for decisions in sensitive areas such as finance, legal or medical topics. Copilot editing is only supported when Calculation Options are set to Automatic. You can undo changes or view prior versions.
• The COPILOT worksheet function was retired on 14 September 2026. Use the Copilot pane instead; the prompts below are written for the pane.

Sources: Get started with Copilot in Excel, Frequently asked questions about Copilot in Excel, COPILOT function, checked on 7 October 2026.

How to write a prompt that works

1. Name the place: the sheet, table and columns ("in the Orders table, using Clean City").
2. Name the output: a new column, a new sheet, a chart, or an answer only (chat mode).
3. Give the rule: thresholds, date ranges, what to do with blanks or duplicates.
4. Protect the original: "do not delete rows", "add a new column", "do not change anything yet".
5. Ask for the working: "show the formula" or "explain each step", so you can check it.

Prompts: get the data ready

1. Convert the data on the Orders sheet into an Excel table named Orders, with the first row as headers.
2. Find city names with extra spaces or different capital letters and add a Clean City column that writes each city one way. Do not change the original column.
3. Highlight duplicate Order IDs in yellow. Do not delete any rows.
4. *Chat mode:* Which columns have blank or unusual values? Give a count per column.
5. Add Year and Month columns based on Order Date.

Prompts: write and fix formulas

1. Add a Revenue column that multiplies Qty by Price for every row.
2. Write a formula for total revenue in Pune in July 2026, using the Clean City column, and explain each part.
3. Add a column that brings each customer's segment from the Customers sheet with XLOOKUP. Show "Not found" when there is no match.
4. *Chat mode:* Explain the formula in H2 step by step in simple English.
5. This formula returns #N/A: =VLOOKUP(C2,Customers!A:C,3). Explain why and give a fixed version.
6. Add a Size column: Small under ₹500, Medium ₹500 to ₹1,499, Large ₹1,500 and above.
7. Add a running total of revenue by order date.

Prompts: summaries and pivottables

1. Create a PivotTable on a new sheet with Clean City in rows, Month in columns and sum of Revenue in values.
2. Summarise revenue and number of orders by product, sorted from highest revenue to lowest.
3. *Chat mode:* What share of revenue comes from the top 3 customers? Show the calculation.
4. Compare July and August revenue by city and add the change as a percentage.
5. Make a table of average order value by city.
6. Count the number of different customers in each city.

Prompts: find insights

1. *Chat mode:* What are the three most important patterns in this table? Show the numbers behind each one.
2. *Chat mode:* Are there unusual orders in Revenue? List them and say why they look unusual.
3. *Chat mode:* Which city grew fastest from July to August?
4. *Chat mode:* Which day of the week has the highest revenue?
5. *Chat mode:* Using only this data, what could explain the change in revenue from July to August? Say clearly what the data cannot show.
6. *Chat mode:* What questions should I check before I present this data to my manager?

Prompts: charts and dashboards

1. Create a column chart of revenue by city, sorted from highest to lowest, with data labels.
2. Create a line chart of revenue by week.
3. Build a Dashboard sheet with four KPI cards (revenue, orders, average order value, different customers) and two charts, linked to formulas.
4. Add a slicer for Clean City to the PivotTable.
5. Make all charts on the Dashboard use the same colours and remove the gridlines.
6. *Chat mode:* Which chart would best show each product's share of revenue, and why?

Prompts: format and protect

1. Highlight orders with revenue above ₹1,500 in light green.
2. Add data validation so City can only be Pune, Nashik, Kolhapur, Nagpur or Solapur.
3. Format Price and Revenue as rupees with no decimals.
4. Freeze the header row and autofit all columns.

Prompts: plan and what-if

1. *Plan mode:* Plan the steps to build a monthly sales report from this table. Do not change anything yet.
2. If prices go up by 5% and quantities fall by 2%, what happens to total revenue? Put both assumptions in input cells.
3. Create a forecast of monthly revenue for the next three months and state the method and its limits.

Prompts: other workbooks, the web and documentation

1. Bring the monthly targets from Targets_2026.xlsx in my OneDrive and compare actual revenue with target by city.
2. Search the web for the 2026 public holidays in Maharashtra, list them with sources, and flag orders placed on those dates.
3. Add a Notes sheet that lists every sheet, what it contains and the main formulas, so a colleague can take over this file.

Illustrative output

Prompt (chat mode): What share of revenue comes from the top 3 customers? Show the calculation.

Illustrative reply:
  The top 3 customers by revenue are Shraddha, Raja and Rani.
  Their revenue divided by total revenue gives their share.
  (Check: the practice file's Check sheet calculates this share with formulas.)

In the practice file, the correct share is 62.7%, with the duplicate row removed. If Copilot's answer differs, ask how it treated the duplicate order and the city spellings.

The verify-the-output checklist

1. Recalculate the key numbers yourself. Use SUMIFS, COUNTIFS or a quick PivotTable for the two or three numbers that matter. The practice file's Check sheet shows how.
2. Count rows before and after. If Copilot cleaned or filtered data, =ROWS() on the old and new tables must match what you expect.
3. Click into every new formula. Check the ranges cover all rows (a whole table column is safest) and that conditions point at the right column, such as the clean city, not the messy one.
4. Look for duplicates and blanks. A duplicate row or a blank quantity quietly changes totals. Test with COUNTIF and COUNTBLANK.
5. Check units and dates. Rupees vs thousands, percent vs decimal, and dates stored as text are common slips.
6. Spot-check three rows by hand. Pick one big, one small and one odd row and work them out on a calculator.
7. Check PivotTables' source and refresh. Make sure the source covers the whole table and refresh after changes.
8. Ask Copilot how it got the answer. In chat mode ask for the steps or formula; if it cannot explain it, do not use it.
9. Open the sources for web answers. Copilot cites web sources; open them and confirm the facts and the date.
10. Keep an undo path. Work on a copy or use version history, and remember Copilot editing needs Calculation Options set to Automatic.
11. Follow your organisation's data rules. Check what data you are allowed to use with Copilot before you paste or open sensitive files.