Copilot in Excel: Conditional Formatting with Complex Functions

Posted on:  09/03/2024
Copilot in Excel: Conditional Formatting with Complex Functions

Copilot in Excel: Conditional Formatting with Complex Functions

Hello. Chris Menard here. I have a fabulous topic for you today: Conditional Formatting with Complex Functions using Copilot in Excel.

This feature is something I've been eagerly awaiting since Copilot was released in November 2023. Now, I can utilize conditional formatting with complex functions in Copilot. In this example, I want to highlight any row where the state (column E) is Georgia and the FY24 value is over 1,000 with a yellow background.

Setting Up Conditional Formatting

To achieve this, we need to do three things: select the data, know how to use conditional formatting, and understand the AND function in Excel. But let's say I know nothing about conditional formatting or the AND function, only how to select the data.

Selecting data for conditional formatting

I'll start by selecting Copilot. In the prompt box at the bottom, I'll type: "Make any row with the state of Georgia and FY24 over 1,000 in a yellow background." I misspelled "background," but that’s okay. Let's run this.

Typing the command in Copilot prompt box

Understanding Copilot's Response

One of the new features of Copilot is that instead of just applying the conditional formatting, it explains what it’s going to do and lets you apply it. Here's what it says:

  • The fill color will be yellow.
  • The font color is going to be black.

It even gives me a sample of it. Below, it shows the complex function it’s using within conditional formatting, which in this case, is the AND function.

Copilot explaining the conditional formatting rule

Applying the Rule

Once I click "Apply," the rule is set. To test it, I’ll change a value to "GA" and "1001" and watch the row turn yellow instantly.

Testing the conditional formatting rule

Reviewing the Conditional Formatting Manager

If you go to Conditional Formatting and then to Manage Rules, you'll see the rule that was created using conditional formatting.

Conditional Formatting Manager showing the created rule

This is an excellent feature, and I hope you enjoyed this guide. My Copilot training class is available online, covering all the apps used in Copilot along with other training classes.

Feel free to subscribe to my channel and let me know in the comments if you like this cool feature. Have a wonderful day!

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories