Do you know most financial work still happens in Microsoft Excel? Google Sheets doesn’t even come close. From simple things like a grocery list or daily tasks to massive enterprise datasets with thousands of columns and hundreds of sheets, Excel handles it all. And now, Microsoft has taken it up a notch by adding Copilot. Just imagine asking it to break down your monthly expenses, forecast next quarter’s revenue, or clean up messy data in seconds without touching a single formula. It’s basically like having a financial analyst built right into your spreadsheet.

In this tutorial, we’ll show you how to access the Copilot function in an Excel sheet, perform data cleaning, conduct data analysis, and use an alternate AI formula if you cannot pay for the Copilot function. 

By the end of this tutorial, you’ll be able to:

• Access the Copilot function in Microsoft 365 Excel
• Perform data cleaning 
• Conduct data analysis
• Use an alternate AI formula to automate tasks in Excel

Let’s dive into this exciting tutorial right away!

Step 1 -  Access the Copilot function in Microsoft 365 Excel

The Copilot function is available on both the desktop and the online versions. However, you must join the beta channel and update your Desktop Excel version before using the Copilot function. You also need a separate Copilot Pro subscription to enable Copilot functions in Excel. 

Open an Excel sheet on your computer. 

Type ‘=COPILOT’ in any cell. Type a prompt such as, ‘Tell me the 5 biggest companies in the US.’

Copilot will get busy and, within a second or two, display the list of the 5 biggest companies in the U.S. 

You can go on and start asking Copilot additional questions, and it will generate the data for the prompts. Let’s say you want to find out where these companies are headquartered. Type ‘=COPILOT’ and type your prompt within the quotation marks. Select the cells for the context and press ‘Enter.’

It will display the data within a second. 

Notice the hash at the end of the context. It covers the range of the cells to generate the data. 

Let’s say you want to change the original prompt from the 5 biggest companies to the 7 biggest companies. As soon as you change the prompt in the formula bar, it will regenerate the entire list and also update the headquarters information automatically. 

‍

Step 2 - Perform data cleaning

You can use Copilot for many other tasks in Excel. Professionals waste a lot of time doing menial tasks like data cleaning. With the Copilot function, they can save that time to focus on analyzing and interpreting the data. Let’s say you have an Excel file that lists the names and addresses of the employees. Instead of finding duplicate values manually, use Copilot to find and return any duplicate values. 

Type ‘=COPILOT’ and specify a prompt. 

Select the cells for the context, and it will return the duplicate values instantly. 

Step 3 - Conduct data analysis

Most of the tasks in Excel are data analysis. Let’s go through an example of what Copilot can do with data analysis. Let’s say you have a spreadsheet that has categories of products. You want to categorize the products into specific categories like food, transport, shopping, and others. 

To put the products into the specific category, we’ll need to use two prompts. Use the =COPILOT function with a prompt and select the Transaction Description cells. Then, specify the second prompt and select the categories cell. 

‍

Excel will categorize the transactions into their respective categories. 

If you think Copilot made a mistake or you changed the category name, you can click the generated text in the Category column and click the green ‘rerun’ button to regenerate the data. 

That’s it for this tutorial, AI enthusiasts. There is a lot you can do with the Excel Copilot function. Right now, it is a bit expensive, but once Microsoft rolls out this feature en masse, it might include this feature in the standard Microsoft 365 personal plan. Meanwhile, keep exploring the Copilot function and let us know if you want to add any specific examples in this tutorial.