How to Use the Excel COPILOT Function on Text Data

TL;DR

Use Excel’s COPILOT function by entering =COPILOT with a quoted prompt and selecting cells as context, allowing free text to become structured output. It can organize paper mill handover notes into Topic, Risk, Owner Role, Due Date, and Next Step, but its AI-generated results must be checked. Prompt wording also affects row count and output shape, so read on for practical formulas, examples, and limitations.

Transcript

So, Excel just added a brand-new function,  COPILOT. This function doesn't calculate like the others. It thinks, which makes  it perfect for cases like this. Here I have a table with shift handover notes in a  paper mill. So, the shift manager has jotted down their notes at different times. They've  used abbreviations and free text. For example, "S... Read More

Key Insights

• The COPILOT function does not calculate like other Excel functions, it thinks. It interprets the meaning of free text and returns structured results, making it suited to shift handover notes, client notes, or interview notes rather than exact numeric work.
• The syntax alternates prompt and context: you type =COPILOT, write the prompt inside quotes, then point to the cells that supply the grid context. You can keep adding more prompt segments and more cell ranges, separated by the Excel separator, before closing the bracket.
• Prompt wording changes the result significantly. Asking Copilot to 'summarize the results' returned fewer rows than the source table, while changing it to 'summarize each note' produced one output row per note, restoring sync with the table.
• COPILOT output is dynamic and recalculates with the data, so editing a note changes the returned next step instantly. Because results can differ every run or when the model changes, copying and pasting as values is the way to freeze an outcome.
• Sentiment analysis works by feeding review text plus cells naming the desired output columns. Asking for 'Category' forces one answer per review, while switching the word to 'Categories' lets the function return multiple labels, such as Quality and Staff for one mixed review.
• COPILOT combines with standard Excel functions. Wrapping FILTER with LEN greater than 50 first restricts the input to reviews longer than 50 characters, and that filtered array is then passed into COPILOT as its context.
• Fuzzy matching against a reference list is possible: prompting Copilot to normalize HR job titles to the closest official title, and to return nothing when there is no match, produced Senior Analyst at 90 percent confidence and Technical Lead at 100 percent, and returned nothing for a nonsense entry.
• The function's limits are concrete: it needs a Copilot license, is not reliable for math or finance, knows only what you feed it rather than the whole workbook or the internet, is bounded by the model's cutoff date, and allows roughly 100 calls per 10 minutes.

Questions & Answers

Q: How do you use the Excel COPILOT function on text data?

Start with =COPILOT, add a detailed prompt inside quotes, and then select the cells containing the text as context. You can alternate additional prompt segments and cell ranges before closing the bracket and pressing Enter. The function can interpret free text and return structured results directly in the Excel grid.

Q: What can the Excel COPILOT function do with free-text notes?

It can transform unstructured notes into columns such as Topic, Risk, Owner Role, Due Date, and Next Step. In the paper mill example, it interpreted a slippery-floor note as medium risk, set the due date to before 2:30, and identified cleaning up the spill as the next step. Because the output is AI-generated, it must be checked for accuracy.

Q: How do you extract the next step from notes with COPILOT?

Tell COPILOT what the notes represent and ask it to provide the next step for each note, then select the note cells as context. The demonstration returned instructions to clean up a spill before 2:30 and swap a forklift battery before the night shift. Adding a requirement to file the report caused the dynamic result to update accordingly.

Q: Why does COPILOT return fewer rows than the source table?

The prompt may not specify that every source item needs its own result. Asking COPILOT to “summarize the results” produced fewer rows than the table, while changing the instruction to “summarize each note” returned one row per note. Precise wording is therefore important when the output must stay aligned with the source data.

Q: How can COPILOT return multiple structured columns?

Give COPILOT the full source table as context, then ask it to summarize each note into named columns supplied from cells in the grid. The example requested Topic, Risk, Owner Role, Due Date, and Next Step. For a note about black liquor solids dropping to 61%, COPILOT assigned high risk and the Recovery Boiler Operator as the owner role.

Q: How do you perform sentiment analysis with the Excel COPILOT function?

Provide context explaining that the text contains reviews, select the review cells, and ask COPILOT to return sentiment and category for each review. A positive car wash review about a clean location and fast service was categorized as Service Quality. A negative review about an unreachable owner was categorized as Customer Service.

Q: Are Excel COPILOT results dynamic and consistent?

The results are dynamic, so changing a source note can immediately change the returned answer. However, results may differ each time the function runs or when the model changes. If you want to preserve an acceptable result, copy it and paste it as values.

Q: What are the requirements and limits of the Excel COPILOT function?

The function is available in Microsoft 365 only when the company has a Copilot license. Its output is AI-generated and should not be trusted without checking, especially for work that must be exact. The page also identifies limits including roughly 100 calls per 10 minutes, changing results on recalculation, and unreliability for math and finance.

Summary & Key Takeaways

• Excel added a COPILOT function that interprets text rather than computing numbers. Given a table of paper mill shift handover notes written with abbreviations and free text, it instantly converts each note into a structured plan with Topic, Risk, Owner, Due Date, and Next Steps columns. The output is AI-generated, so it has to be checked for accuracy.

• The function takes alternating prompt and context arguments. A prompt explaining that the notes are handover log entries, plus a highlighted cell range, returns the next step per note, such as cleaning up a spill before 2:30 or swapping a forklift battery before the night shift. Editing a note updates the answer dynamically.

• Multi-column output requires precise wording. Asking it to summarize the results returned fewer rows than the table, breaking sync, while summarizing each note produced one row per note. A cross-check showed a note about black liquor solids dropping to 61 percent classified as high risk, owned by the Recovery Boiler Operator.

• Sentiment analysis on car wash reviews returned positive, negative, and mixed labels with categories such as Service Quality and Customer Service. Wrapping the input in FILTER with LEN greater than 50 limited the analysis to longer reviews, and a follow-up prompt asked Copilot to return the original text plus its reasoning for each sentiment.

• Cleaning inconsistent data is a third use case: messy HR job titles were normalized to a reference list of official titles with a confidence level, returning nothing for entries with no match. The presenter recommends the function for text-heavy work and cross-checking ideas, but not for anything that has to be exact.

• Pros are strong text summarizing and organizing, working directly in the grid so it updates with the data, and saving hours on inconsistent labels. Cons are the Copilot license requirement, unreliability for math and finance, changing results on recalculation, limitation to supplied data, a model cutoff date, and a cap near 100 calls per 10 minutes.