Copilot 3

This is the third post in the Copilot in Excel series. In this series, we’re diving into some exciting features of Excel’s Copilot and how it can help us streamline our workbooks. If you’ve ever found yourself needing assistance with editing or wanting to ask Excel a quick question, Copilot has your back. In this post, we’ll explore three different exercises that demonstrate how to use Copilot’s Edit and Ask prompts effectively. Let’s jump right in!

If you haven’t already, feel free to check out our Copilot 1 and Copilot 2 posts to get up to speed and also learn about the availability, versions, and licensing for Copilot in Excel.

Video

Step-by-Step Guide

Exercise 1: Highlighting the Top Three Scores

In our first exercise, we have a list of names and their corresponding scores.

Our goal is to highlight the top three scores. Unsure how to accomplish this? No problem—Copilot to the rescue!

We open the Copilot pane by clicking Home > Copilot.

Copilot prompt: “highlight the top three scores”

Copilot results:

Copilot identifies the data range (B8 to C22) and suggests using a conditional formatting rule.

We click Apply and it automatically applies the “Top 3 Items” rule to our range:

Dynamic Updates with Conditional Formatting: One of the great benefits here is that this conditional formatting is dynamic. If any scores change, the formatting updates automatically. For example, if a student retakes a test and the score changes, the formatting adjusts accordingly.

Customizing the Rule: If we want to tweak this further, we can manage the conditional formatting rule by going to Home > Conditional Formatting > Manage Rules. Here, we can change the number of highlighted scores or modify the formatting—perhaps changing the fill color from yellow to orange. Copilot sets up the rule with common sense defaults, and we can make adjustments as needed!

Exercise 2: Sorting and Filtering Data

Next up, we’re looking at sorting and filtering our data. We have a data table as shown below:

Now we want to filter our table to show only the top five scores or scores above a certain threshold. Copilot makes this a breeze.

Copilot prompt: “filter the top 5 scores”

Copilot response:

Apply:

We can just as easily prompt other filtering and sorting requests as desired.

Exercise 3: Explaining Complex Formulas

Our third exercise is all about understanding complex formulas. Sometimes, we encounter a formula in a workbook and have no idea what it’s doing. Copilot can help break down and explain these formulas for us.

For example, we encounter a formula in B8:

We aren’t quite sure what the SUMIFS function does, or what the arguments mean. So, we can ask Copilot. First, we copy the formula. Then we can prompt Copilot with “Explain this formula:” and paste the formula into the prompt.

Copilot prompt: “explain this formula: =SUMIFS(Table3[Amount],Table3[DeptNum],100)”

Copilot response:

This feature is particularly helpful when working with workbooks created by others or revisiting your own complex formulas after some time.

Conclusion

And there you have it! These are just a few ways Copilot can help us edit, filter, sort, and understand data in Excel using natural language prompts. Whether you are a beginner looking for guidance or an experienced user seeking to save time, Copilot is a valuable tool to have in your Excel toolkit.

If you have any suggestions, questions, or alternatives, please share by posting a comment below … thanks!

Sample File

You can download the sample file used in this tutorial here.

FAQ

1. Can I customize the conditional formatting rules suggested by Copilot?

Yes, you can! Once Copilot sets up a conditional formatting rule, you can manage and edit the rules through the “Conditional Formatting” command to suit your needs.

2. Is it possible to use Copilot to filter data?

Absolutely. You can prompt Copilot to apply filters based on your criteria by specifying it in your natural language prompt.

3. Can Copilot sort data in both ascending and descending order?

Yes, Copilot can sort data in either order. Just specify your preference in the prompt.

4. How does Copilot explain formulas?

Copilot breaks down each component of the formula and explains what it does, making it easier to understand complex functions and their arguments.

5. What if Copilot doesn’t understand my prompt?

Try rephrasing your question or breaking it down into simpler parts. Copilot is designed to understand natural language, but sometimes clarity can help.