Copilot 1

Hello, and welcome to this guide on using Copilot in Excel! This post explores how to use Copilot in Excel to execute commands and automate tasks directly within your workbook. We’ll dive into several exercises that show how Copilot can assist us in creating calculated columns, extracting values, and much more. Let’s get started!

Video

Before We Begin

Copilot is Microsoft’s AI brand, and it is available in many different places and with various licensing arrangements. For example, you can access Copilot for free from a browser by heading to copilot.microsoft.com. It is also available as a mobile app. In practice, both of these operate similar to other LLMs such as ChatGPT. But, what we are talking about in today’s post is the availability of Copilot inside of Excel. You access it with the Home > Copilot button:

When clicked, it opens the Copilot pane on the right:

Excel Copilot is a feature in Excel designed to assist users in performing various tasks by allowing natural language commands. Think of it as a helper that can understand your instructions and execute them, such as creating formulas, inserting columns, or extracting specific data from your spreadsheet. Once licensed, it works in Excel desktop and Excel online. Documents need to have auto-save enabled and be stored in Microsoft’s cloud platform.

Please note that Copilot in Excel is a licensed product and might not be available in all versions of Excel. If you don’t see the Copilot button on your Home tab, your version might not support it. For more information about availability and usage, check out this Microsoft page.

Step-by-Step Guide

Exercise 1: Creating Calculated Columns

In our first exercise, we have a table with account names and their respective actual and budget values. We’ll use Copilot to create a calculated column for variance.

Here is the data:

Add a Variance Column with Copilot

Open the Copilot from the Home tab. Type a simple prompt: “Add a variance column” into the Copilot pane:

Copilot suggests a formula to calculate the variance between actual and budget values. It provides a description and preview as well:

You can refine your prompt as needed, and continue the conversation … but if the preview looks good, click “Insert Column” and the column is added to your table:

The advantage here is that this is a formula, meaning it will auto-fill down the column as you add new values to your table.

Exercise 2: Calculating Extended Price

In our second exercise, we have data for transaction IDs, quantities, and prices. We’ll use Copilot to add a calculated column.

Add an Extended Price Column:

Copilot prompt: “Add an extended price column.”

Copilot results:

Inserted into worksheet:

Exercise 3: Extracting Data from a Single Column

In the third exercise, we have a single column containing both the full name and address. We’ll use Copilot to extract specific information.

Extract First Name:

Copilot prompt: “Extract first name.”

Copilot results:

Insert column:

Add a City Column:

Copilot prompt: “extract city:

Copilot results:

Insert column:

These columns will dynamically update as new data is added.

Key Takeaways

Using Copilot in Excel, we can easily add calculated columns, perform data extraction, and automate repetitive tasks in our workbooks. By simply typing what we want in natural language, Copilot provides formulas, previews, and even a direct insertion feature to make our Excel experience smoother and more efficient. If you have any questions, suggestions, or alternatives, please share by posting a comment below … thanks!

Sample File

Feel free to download the sample Excel file.

Frequently Asked Questions (FAQ)

1: What is Excel Copilot?
Excel Copilot is a feature that allows users to execute commands and automate tasks using natural language prompts within Excel.

2: Is Copilot available in all versions of Excel?
No, Copilot is a licensed product and may not be available in all versions of Excel.

3: Do I need to turn on AutoSave to use Copilot?
Yes, AutoSave must be turned on to use Copilot effectively.

4: Can I undo actions performed by Copilot?
Yes, if you insert a column or perform an action with Copilot that you don’t like, you can always undo it.

5: Are the formulas generated by Copilot dynamic?
Yes, the formulas generated by Copilot are dynamic and will automatically adjust as new data is added to the worksheet.