You can tell Copilot to ‘Create a spreadsheet‘ and it should give you a download link to a .xlsx Excel file. However, this can be a bit buggy. See ‘Can Copilot create spreadsheets?’ in the FAQ for more guidance.
Description
What? Upload a spreadsheet and ask Copilot to do something with the data in it.
Why? If you’re not confident with Excel formulas, it can take time to pull out specific values from a spreadsheet. You can upload a spreadsheet to Copilot and tell it what you want it to do, and it can quickly extract relevant data.
What do I need to know?
- Any data you upload to Copilot will be stored in your OneDrive, in a folder called ‘Microsoft Copilot Chat Files’. Be careful that you are not uploading and storing data in a way that will contravene data retention and GDPR rules. You can delete files from this folder once you’ve finished your Copilot chat.
- Be specific about the columns / rows you want Copilot to work with. Due to the weaknesses of large-language models when working with numerical data, Copilot will often generate Python code and run this in the background to analyse the spreadsheet. This Python code requires precise values about which cells to analyse. You’ll know that Copilot is generating code when it displays the message ‘Executing task’.
- This recipe works best with simple spreadsheets. Copilot seems to have a ‘token limit’ (a limit to have much data it will process) but frustratingly, it is not very transparent about this. If you upload large spreadsheets or documents, it will only analyse part of them, but it will not state how much it has looked. Other tools (ChatGPT, Claude) are currently better at working with large data sets. Check any information Copilot gives you carefully as it can generate plausible-seeming information that isn’t actually in the spreadsheet. If accuracy is crucial, it’s probably better to ask Copilot for an Excel formula that will do what you want, rather than asking it to analyse data directly.
Example Video
📹 Video – Work with spreadsheets example (media.coventry.ac.uk)
Instructions
Step 1: Find a spreadsheet that has some data you want to work with.
Step 2: Go to Copilot (office.com/chat) and sign in with your Coventry account. If you’re already using Copilot, just select the ‘New chat’ button at the top right.
Step 3: Select ‘Add a file’ (the paperclip 📎 icon) at the bottom right of the text box, and then in the file browser select the spreadsheet on your computer. Don’t press submit yet!
Step 4: Copy and paste the following prompt into the Copilot message box, then select the ‘Submit’ button (the arrow at the bottom-right):
 🤖 Prompt (click to expand, then copy and paste into Copilot)
I’m going to give you a spreadsheet. First, create a table with a copy of the data from the spreadsheet. Then ask me what I want you to do with the table – for example, do I want you to extract or remove certain data, analyse the existing data, update specific cells, or create a new column with certain values? Wait for my response. Then update the table according to my instructions
Step 5: Copilot should ask you what you want to do with the data in the spreadsheet. Type your instructions in the text box, e.g. ‘Combine the values in the first and second column into a single column. Create a new table with this new data’. Then select ‘Submit’.
Step 6: Copilot should follow your instructions and create a new table with the relevant data. If it doesn’t, just repeat your instruction to ‘Create a table with the information I requested’. If you want to download the table, tell Copilot ‘Create a spreadsheet with the table’. It should produce a link to a .xlsx Excel file. Sometimes this can be a bit buggy – see ‘Can Copilot create spreadsheets?’ in the FAQ for further guidance.
Step 7: Check the Copilot-generated table / spreadsheet carefully against the original spreadsheet to see if it has generated any false information.
Important: Always check Copilot’s output. Generative AI tools can hallucinate (create plausible but incorrect information)