How to Use Copilot in Excel

Microsoft’s Copilot artificial intelligence is being integrated into all major Microsoft products, and that includes Microsoft 365 (Office) applications, like Microsoft Excel. Copilot can help you write emails in Outlook, or pen a letter in Word, but in Excel it’s decidedly different. There, Copilot can interact more readily with spreadsheets, help organize the data, and better label and format it for easier consumption and more obvious analytics.

Photo: Jacob Bobo 

You’ll need a Microsoft 365 subscription and a Copilot Pro subscription to use Copilot in Word. For more help on how to use Word in Copilot, check out our setup guide, here.

When you’re all set up, though, here’s how to use Copilot in Excel to handle a range of tasks. You can even use voice commands for most of it. 

How to use Copilot in Excel for easy formatting

There’s little more satisfying than setting up custom formatting to make a worksheet look more presentable. Even more so when it also highlights the important insights and datapoints that need to be addressed. While that has historically been very time consuming, Copilot can speed up the process. Here’s how.

Step 1: Open the worksheet and select the Copilot ribbon icon in the top-right. It’ll open a new Copilot chat window. If it warns that Autosave is turned off, select the button to turn it on, as well as which cloud storage medium you’d like to use, then continue.

Step 2: If you know what kind of formatting is needed, Copilot can be given a specific prompt. Copilot can be used to highlight cells which have a certain value, or to alternate colors through all the cells in a certain column. Alternatively, cells can be selected manually before asking Copilot to make conditional formatting changes to those particular cells.

Photo: Jon Martindale/Microsoft 

Step 3: Once a conditional formatting has been selected, press Enter or select the arrow button. Copilot will then summarize the changes it’s going to make within the Copilot window, explaining what kind of color coding it will perform, and which cells it will complete the action on.

If you like the changes Copilot has made, you can leave it as is or ask it to make further changes or additions. If you really don’t like what it’s done and you’d rather it just hadn’t done it, select the Undo button and it’ll revert to the way it was before.

How to use Copilot in Excel to identify insights

Finding insights from data is one of the best ways to use Copilot in Excel. Copilot doesn’t even need much of a prompt, just serve it up the data and ask it to “Understand” it. Here’s how to do it.

Step 1: Open the worksheet and select the Copilot ribbon icon in the top right. It’ll open a new Copilot chat window. As before, if it warns that Autosave is turned off, select the button to turn it on, as well as which cloud storage medium you’d like to use, to continue.

Step 2: Select the Understand button. Alternatively, type “Understand” into the prompt box and hit Enter.

Photo: Jon Martindale/Microsoft 

Step 3: Wait for Copilot to finish its analysis. When it’s done, it’ll suggest insights as charts, summaries, trends, or PivotTables, depending on the data provided it and the potential insights found within it.

If no obvious insights are found, Copilot may ask for additional instruction. In that case, either type out what you want it to look for which cells it should focus on in the prompt window or select one of the suggested options.

Photo: Jon Martindale/Microsoft 

Step 4: If you’re happy with the insights that Copilot has highlighted, then you can use that data for your own analytics and calculations. Alternatively, Copilot can be prompted to extract further information, or to better distill the data. Copilot cane produce additional sheets and tables, visualize the data in a graph, or spit out sums and results based on any calculations you ask it to complete.

If you want to add any charts or data it outputs to your sheet(s), you can copy it manually by selecting it with the mouse and pressing Ctrl+C or selecting the Copy button. To add charts or graphs directly to the worksheet, select the Add to a new sheet button.

Tips for finding insights

Finding insights within data can sometimes be as much about predicting what might be found as it can be about sifting through what data is there. To that end, Copilot can be used to look for a range of insights.

For example, in a workbook of ages, dates, and names, Copilot can be asked to find how many people were born before a certain date. Or to highlight the cells where someone has a certain name and date correlation.

For sales data, Copilot can be used to look for trends or to spot shortfalls. Ask Copilot how many units were sold of a particular product in a specific month, or to highlight best sellers that might be worth a restock.

Think of Copilot as a local data analyst that you can ask questions and make requests of to extract useful insights from data. While it’s not yet perfect, Copilot can do a good enough job to find some interesting insights and save some time in the process.

How to use Copilot to generate formulas in Excel

Want to perform a calculation based on a data set but don’t quite know how to do it? That’s where Copilot’s mathematical wizardry can really help. It can generate formulas, so that conclusive information can be easily extracted, outputting it into a new row or column. Here’s how to do it.

Step 1: Open the worksheet and select the Copilot ribbon icon in the top-right. It’ll open a new Copilot chat window. If it warns that Autosave is turned off, select the button to turn it on, as well as which cloud storage medium you’d like to use, to continue.

Step 2: To have Copilot work on the whole document or create a formula around uncertain data, select Create – Suggest a formula column from the Copilot Window. You can also type Show a suggestion for a formula column in the prompt window and press Enter.

Photo: Jon Martindale/Microsoft 

Alternatively, select a range of cells, then type in what kind of calculation you want Copilot to do.

Step 3: Copilot will then make formula suggestions with an explanation about how each of them would work and what kind of results it expects to get. Select which works best for your data.

Step 4: Once a formula has been chosen, select the Insert Column button. If further formula options are needed, select the suggested prompt Give me another formula. Alternatively, you can type something to that effect in the prompt window and press Enter.

Step 5: This process can be repeated for additional formula calculations, as well as having Copilot work on the new columns it has created, or other cells from the worksheet.

Other Copilot tips to try in Excel

Copilot is being further developed all the time, so look out for additional insights or ways you can use it in the future. Here are a few other ways you can take advantage of it right now:

• Just like in Word, Copilot in Excel can summarize documents. Ask it to summarize the contents of the worksheet, and if needed, provide a text-based explanation for what the data shows.
• Copilot can be asked to re-try anything it does. It’s not an exact science, and sometimes asking it to regenerate the results again can yield different, or better output. If in doubt, give it another go.
• Copilot supports Python code within Excel, too. This can be used for more complicated analysis. When selecting a function for Copilot, choose Advanced Analysis followed by Get deeper analysis results using Python. It’ll then give some suggestions about what it can do, including creating Python-based formulas, and generating new results based on those calculations.
• Copilot can also interact with documents directly, which can be really useful for accessibility. Ask it to create new columns or rows, move data around, or change the formatting of individual cells.
• You can use voice commands in Copilot, too. To do so, after selecting the Copilot ribbon, select the small microphone symbol in the prompt window and it will use speech to text translation to input commands to Copilot.

Conclusion

Copilot is an incredibly useful tool in Excel and only getting better by the day. As AI improves, so will the ways it can help save you time, whether it's organizing your data or pulling out the important pieces. If you want more AI tips, keep an eye on Micro Center News as we add more Office 365 and Copilot guides. And be sure to check out the links below for the latest in AI news from PC Mag’s #1 tech retailer.