Posey's Tips & Tricks
The New Copilot Function Vastly Improves Microsoft Excel, Part 2
Microsoft’s new Copilot function in Excel lets users generate, analyze and explain data directly from cell formulas, marking a major step toward full AI integration within spreadsheets.
Microsoft’s new Copilot function in Excel lets users generate, analyze and explain data directly from cell formulas, marking a major step toward full AI integration within spreadsheets.
In my previous article, I explained that a new Copilot related update for Excel allows Copilot to more completely understand your data, thereby allowing you to do some pretty amazing things. Now, I want to show you how to leverage these new capabilities.
Before I get started, I want to quickly mention that the techniques that I am discussing can currently be used by Microsoft 365 insiders who have a Copilot license. These capabilities will soon be made available to all Microsoft 365 copilot users.
Being that Excel’s new capabilities are tied to Copilot, their use centers around the Copilot prompt. Even so, things work a little bit differently than you might expect. Using Copilot in Microsoft Word for example, involves clicking on the Copilot icon, clicking the Message Copilot button and then entering a text based query. However, if you want to unlock the types of capabilities that I described in the previous article, then you will have to use Copilot as a function. Fortunately, this is a lot easier than it sounds.
There are two components that make up the Copilot function syntax. The first of these two components is unsurprisingly, the actual Copilot prompt. This is the string of text that tells Copilot what you want to do.
The second Copilot function syntax component is known as the context. The context is really nothing more than just a range of cells, or even a reference to a single cell. Although most Copilot function calls will include context, the context is technically optional. It’s also worth noting that if you are trying to do something complex, you can pass multiple prompts and multiple sets of context to a single Copilot function call.
The syntax for a basic Copilot function call is: =COPILOT(prompt,context). To give you a more concrete example, let’s pretend for a moment that you have a spreadsheet in which cells A1 through A100 contain a list of major cities in America. Now, let’s pretend that we wanted to populate column B with the states where those cities are found. To do so, you would enter the Copilot function into cell B1. The function call might look something like this: =COPILOT("What state is each of these major cities located in?",A1:A100).
Remember when I said that providing context is technically optional? In this case, you probably wouldn’t even need the context because Copilot is smart enough to recognize the list of cities without you having to explicitly point it out.
Before I move on, I wanted to quickly mention that Copilot can do more than just interpreting data. It can also interpret functions. If you have an Excel spreadsheet containing a complex function, you can ask Copilot to explain it to you. In such a situation, the prompt would consist of a simple request for Excel to explain what a function does in simplistic terms. The context would point to the cell containing the function.
So with that said, there are some best practices for working with the Copilot function and some things that you need to keep in mind.
The first thing that you need to know is that according to Microsoft, the Copilot function is not capable of looking up information on the Internet or extracting data from other documents that you may have. Earlier I provided a usage example in which Copilot determines which states various major cities are located in. The only reason why the Copilot function is able to complete this and other similar tasks is because the information (in this case, knowledge of geography) is embedded into the large language model. If you need for the Copilot function to act on data that is not a part of its large language model, that data will need to be added to the spreadsheet.
The second best practice is to structure your prompt carefully. When it comes to using the Copilot function, the way that you phrase your prompt is everything. When writing a Copilot prompt, be as clear and concise as you possibly can and be sure to include any important details that the Copilot Function needs to know.
Another important consideration is that like all large language models, Copilot can, and sometimes does, make mistakes. Even if the Copilot Function seems to provide you with the information that you need, it’s always a good idea to review the output to make sure that it is correct.
Finally, Copilot function calls are processed in the cloud. Because of this, Microsoft limits the number of calls that you can make. The current limit is 100 calls within a ten minute span and no more than a total of 300 calls per hour.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.