Uses for Copilot in Excel
It's early days for Copilot but already there are quite a few things it can help you with. They fall into two categories:
Category 1: Tasks that it performs on or using data in your Tables.
Copilot can work with data inside your spreadsheets
and make changes you instruct it to, including:
- Add calculated columns containing formulas:
For example, in the image below you can see I've asked it to add a column that calculates the Discount as a percentage of Sales, and you can see it returns a formula and an explanation of how the formula works. All I have to do is click the '+ Insert column'
button to add it to the Table.
- Add conditional formatting:
For example, I can ask it to highlight sales > 100000 and Copilot applies conditional formatting to the Sales column:
However, it can't yet highlight a whole row.
- Clear formatting or content
Notice it removed the number formatting too, so instead of removing the conditional formatting, it simply cleared formats. The Table style still remains because this is not a cell format as such.
- Add or remove headers or total rows:
I asked it to add a Total row for the Profit column. However, it appears to only be able to add the Table total row which defaults to a total in the last column:
- Insert or delete rows or columns:
Note: The #DIV/0! is returned because the sales and discount columns are empty.
- Sort and filter your tables:
It can also sort based on multiple columns.
- Analyse data and automatically generate PivotTables and
charts to get insights:
Unlike the Analyze Data tool, Copilot will currently only return one insight at a time. I have seen some demos where asking it to 'Analyze this data' would return a sheet containing multiple charts and PivotTables, but it looks like Microsoft have throttled it for now. So, I asked it if there are any outliers in my data:
From here I can add it to a sheet and work with it like a regular chart.
These are just the Excel features, but remember Copilot is available across all Office apps, not just Excel.
Category 2: AI responses to questions you ask.
You can ask it questions unrelated to your data (as long as you have a cell in any table selected to activate the Copilot pane). For example, there might be a formula in a file you inherited that you
don't understand. You can copy the formula into the question field and ask Copilot to explain how it works.
Think of this as having ChatGPT inside Excel. Use it to ask for help to find shortcuts, write VBA and more.
Future Copilot Features
There are more features planned, including the ability to write and insert Python in Excel, which can be used for forecasting, analysis, charting, data cleaning and more.
Side note: If you haven't already tried Python in Excel, check it out here:
And while there haven't been any announcements around Power Query, Power Pivot and Office Scripts, I'd be surprised if these aren't also on the drawing board.
Where is Copilot
Copilot in Excel is available on the Home tab of the Ribbon and active when the file is saved on OneDrive, or you are working in Excel Online:
Tip: if Copilot is greyed out, make sure your file is set to AutoSave:
Data Layout Requirements
Copilot in Excel requires your data in an Excel Table so that it can easily identify what to work with. Don't worry if you don't know how to insert a Table, Copilot can do that for you:
Working with Copilot
To start working with Copilot, select any cell in your Table and click the Copilot icon on the Home tab. This
opens the task pane on the right:
Tip: left click and
drag the task pane header to un-dock it into a floating window.
To get started, you can select from the options listed in the prompt guide, or the suggested prompts. Or dive straight in and enter a custom prompt. And if you ever want to get the Prompt Gude back, click the stars in the bottom left of the task pane.
Copilot Limitations
While Copilot can certainly help you improve productivity, it's a long way from doing all your work for you, which means you still need to learn how to use Excel, for now.
Plus, there are still quite a few constraints like:
- Limited to Tables: It can only see data in a Table in the current
workbook. It can't yet work with PivotTables, charts, Power Query, Power Pivot, objects etc.
- Files on OneDrive: Copilot will only work in Excel Online or where your files are saved on OneDrive and Autosave is enabled.
- Data Size Limits: if you ask it to anlyse too much data
e.g. >1.5M cells, it will tell you it can't. This is in line with the Analyze Data tool already in Excel and which Copilot's 'Get Insights' functionality appears to be built on.
- Can't See Formulas: if you
ask it to explain a formula in a column (or cell), it will tell you the column doesn't contain a formula! The workaround is to copy the formula into Copilot, but then you can also do that with ChatGPT.
- Special Characters: It appears unable to recognise special characters. For example, I had it add a column to calculate the discount which it called 'Discount %'. If I ask it a question
specifically about this column it ignores it and returns information about other columns with similar names instead e.g. 'Discount Band' or 'Discounts'. I suspect this is a bug that will be fixed in time.
- It's Slow: if you don't know how to perform a task, it's no doubt quicker than reaching out on a forum and waiting for an answer or looking for answers through search engines. But
it's no replacement for learning the skills yourself. I found it painful waiting for it to interpret my question, then offer the solution and finally apply it to my file. This is one issue I expect will improve over time.
- Can't Multitask: it often struggles to perform more than one instruction at a time. For example, you can't ask it to add a column containing a formula and format the data in that column in one prompt. You have to split the requests into two prompts.
- Follow Up Questions: It's not as good as ChatGPT at keeping track of the questions you have already asked and answers it has already given. When asked to analyze the data for insights, it will give a response. If you then ask it to look for more
insights, it will return the same response.
- Limited Functionality: It can't do everything (yet). If you're a beginner, you're going to get a lot more out of Copilot in Excel than those with more advanced skills.
The above limitations will be more of an issue to some than others, and many of them will be
temporary as Copilot improves.
Copilot Licensing Requirements
Currently, Copilot is available for an annual commitment to Enterprise users with Microsoft 365 E3 or E5 licenses for an additional US$30 per month with a minimum userbase of 300 seats.
An E3 license is US$36 per user/month and an E5 license is US$57
per user/month.
Longer term it is expected that Copilot will be available to consumer and education customers, but there is no ETA. More information here.
Copilot
Data Privacy and Security
There's no getting away from the fact that to use Copilot, you must send your data to the cloud. The file must be saved on OneDrive. Copilot uses the same security, compliance and privacy policies set up in Microsoft 365. You can read more about it here.
Alternatives to Copilot
There's no denying Copilot will revolutionise the way you work in Excel, but we don't all have deep pockets to pay the license fees. If you want to get started leveraging AI to improve productivity, check out these free or cheaper alternatives:
LABS.GENERATIVEAI Function – this is the next best thing to having ChatGPT inside Excel. It's a function that enables you to write formulas that can reference data in cells and bring back data, answers to questions and more.
Analyze Data – this is essentially the same tool Copilot is using when you ask it for insights about your data, except it's free!
8 AI Tools in Excel – including suggested PivotTables, charts, forecasting, write formulas, data cleaning and more.
Learn With Us
