How to Use Copilot in Excel to Save Hours of Work

Step-by-step

Copilot is an AI assistant inside Excel. You ask for something in plain words, such as a total column or the trends in your data, and it does the work. The example uses a workbook of projected bank revenues by location and month.

1. Check you have access

Copilot needs a Microsoft 365 plan that includes it. If you have it, a Copilot button appears at the far right of the Home tab.

2. Put your data in a table

Copilot only works with Excel tables, which tell it where the data starts and ends. Select the data (A2:M8 in the example), go to Insert > Table, make sure My table has headers is ticked and click OK. If you prefer the original look, choose None under Table Styles: the filter buttons show it’s still a table.

3. Save the file to OneDrive

Copilot also needs the workbook in the cloud. Turn on AutoSave at the top left and choose your OneDrive. When AutoSave is on, the Copilot button becomes available.

4. Open Copilot

Click any cell in the table and click Copilot. A pane opens on the right with suggested prompts and a box to type (or speak) your own.

5. Ask for a total column

Type Add a column that calculates the total revenue for each location and send it. Copilot explains the formula it will use (a sum of January to December). Hover over Insert column to preview where it will go, then click it.

6. Ask about trends

Type Are there any trends for each location? Copilot analyses the table and suggests insights, such as correlations between locations. Click Add all insights to grid to put them on a new sheet, with charts and PivotTables.

7. Tidy up and keep going

Rename the sheets (for example Revenues and Insights). Try other requests too, like highlighting or filtering locations above a certain total. Close the pane when you’re done.

Go further

My Excel Tips & Shortcuts: 100+ Powerful Hacks course collects a hundred time-saving techniques like this one.