How to use Copilot in Excel

Copilot in Excel is a powerful tool that can help you create, generate, and edit Excel spreadsheets. You can use natural language to describe your tasks, explore different scenarios, and learn from Copilot's suggestions and explanations. In this guide, we’ll show you how to get started with Copilot to improve what you can do in Excel.

Create

Copilot in Excel (currently in Preview) must have data formatted as a table. Some features such as prompting with microphone or accessing Copilot Labs are not yet available. This is the same with Excel Online – the web application. Copilot in Excel’s version of Create is not the same as Word where it generates text. As Excel is data-driven, it cannot create fake data for you, rather it uses data you already have and are granted access to.

Copilot in Excel is accessed via the Copilot button in the top right corner of Excel. If you do not currently have a table, Copilot can give you example data to use.

Once data is formatted as a Table we can start creating…or generating in the case of Excel. That’s right – if you’ve not been confident with creating formulas in Excel, Copilot is here to help! Let’s say I have a table of marketing campaigns and I wished to see which campaign was most successful when comparing the total users targeted against the number of engaged users.

Using natural language, I can ask Copilot in Excel to create columns that show exactly this and Copilot will respond with both the formula and a button to create the column. Copilot provides a reason for the column to create, a breakdown of the formula including how to build a formula such as this, and then an option to add this column. An excellent feature and top tip: if you hover over ‘Insert Column’, Copilot shows you what the column will look like before you insert it.

Edit

Using Copilot in Excel can help you edit your data too. 

I can ask Copilot in Excel to highlight data, either columns, rows or any fields that are important. For example, “Highlight the lowest 5 revenue fields”.

Now this example is time-saving because Copilot can analyse the data for you to find the lowest 5 values in seconds instead of you having to do it manually. It’s important to remember that Copilot is a tool that should save you time, not add to it. Typing out a prompt such as “Highlight £6,980 in the revenue column”, takes longer to do than clicking the field and clicking highlight.

The edit function of Copilot in Excel can also help you to sort and filter data. Of course, a PivotTable makes these functions accessible but if you wished, Copilot can “Sort the budget column largest to smallest” and it’ll do it for you.

Understand

Data on its own is just data, it’s what we do with it that turns it into knowledge and action. Copilot in Excel can understand your data which drives your data-driven outcomes. One of the prompts I can ask Copilot in Excel is to “Plot the total money spent vs earned over time”. Copilot will analyse the data, recognise a date column, a budget column, and a revenue column. Then Copilot suggestes a graph type for me and options to insert this visualisation.

You can see some additional suggestions from Copilot and in a matter of seconds, I’ve created a sheet of important insights to take into my meeting, as opposed to raw data that is being sorted and filtered live. Copilot in Excel helping to understand data encourages the discovery of new trends, summaries, and insights.