Hi everyone, this is part 11 in a series of posts to show you some of the things that are possible to do with Copilot in Excel. 

What is conditional formatting?

Conditional formatting in Excel is a powerful tool that allows you to apply specific formatting to cells that meet certain criteria. This can include changing the cell’s background color, font color, or adding icons to highlight differences in data. By using conditional formatting, you can help data to stand out and emphasize what’s important, applying your rules automatically as your data changes and grows.

Excel already offers a variety of conditional formatting options you can apply manually to help you call attention to your data: https://support.microsoft.com/en-us/office/fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

How does Copilot make it better?

Using a formula to determine which cells to format is one of the most versatile conditional formatting tools. However, it can be challenging to set it up to do exactly what you want. You must get the formula and syntax exactly right without having the tools and tips of the formula bar or seeing interim results in cells. With Copilot, you can simply use language to describe exactly what you want to happen.

For example, in this post (Re: Conditional formatting – Microsoft Community Hub), the customer knew exactly how they wanted to color their data. They wanted to flag certain info in different ways based on a payment status and a date. If they utilized Copilot, they could have asked to “Highlight rows in red when the date is this month or earlier and the customer has not paid. Highlight rows in yellow when the date is next month, and the customer has not paid.”

Screenshot of Excel with conditional formatting suggestion from Copilot based on the prompt.

In this example, Copilot understood my table of data, interpreted my prompt, and responded with two conditional formatting rules for me to review and apply. I can deduce that the top rule assigns a red color by comparing the current date to the due date and checking if it’s in the past when the paid status is ‘no’. I can deduce the second rule assigns a yellow color by comparing the current date to the due date and checking if it’s in the future when the paid status is ‘no’. I did not need to know the logical order, needed formulas, or exact syntax to get this result. I only had to tell Copilot what I wanted.

After I review and then apply these custom formula conditional formatting rules, my worksheet shows the appropriate colors and will adjust when I change the data.

Gif showing application of Copilot generated conditional format and then how editing data updates the coloring based on the rule.

What else should I know?

You can also ask copilot to do other types of highlighting:

Make values in ‘Column Name’ greater than ‘number’ have white text on a black background.
Highlight cells in light blue for ‘Column Name’ that contain ‘Specific Text’
Highlight the top 10% of values in ‘Column Name’ using bold font
Apply a Red and Green color scale to the values in ‘Column Name’

When you have applied any conditional formatting rules using Copilot, you can manage and edit existing rules by choosing Conditional Formatting > Manage Rules from the toolbar or ribbon.

Screenshot of the Conditional Formatting menu

Conditional formatting in Excel is a versatile tool that can help you analyze and present your data more effectively. By exploring the various capabilities of conditional formatting in combination with Copilot you can unlock the full potential of this feature and make your spreadsheets more informative and visually engaging.

​Microsoft Tech Community – Latest Blogs –Read More