Color, Conditions, and Copilot: How to save time using conditional formatting with Copilot in Excel

Hello everyone, this is part 11. series of posts teao Shows some of the things you can 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 may include changing the background color of cells, font colors, or adding icons to highlight differences in the data. Conditional formatting lets you make your data stand out, highlight important details, and automatically apply rules as your data changes and grows.

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

How does Copilot improve this?

Using formulas to determine which cells to format is one of the most versatile conditional formatting tools. However, getting it set up to do exactly what you want can be difficult. Import formulas and syntax accurately without using tools or tips in the formula bar or checking intermediate results in cells. Copilot allows you to use language to describe exactly what you want to happen.

For example, this post (Answer: Conditional Formatting – Microsoft Community Hub), the customer knew exactly how to color the data. They wanted to flag specific information in different ways based on payment status and date. If you’ve utilized Copilot, you might have asked to “highlight rows in red if the date is before this month and the customer hasn’t paid.” If the date is in the next month and the customer hasn’t paid, we highlight the row in yellow.”

Excel screenshot with Copilot’s conditional formatting suggestions based on prompts.

In this example, Copilot understood my data table, interpreted the prompt, and then responded with two conditional formatting rules that I could review and apply. When the payment status is ‘No’, we can infer that the top-level rule has assigned red by comparing the current date to the due date to see if it is in the past. It can be inferred that the second rule compares the current date and the due date to determine if the payment status is ‘no’ in the future, thus assigning yellow. There was no need to know the logical sequence, necessary formulas, or exact syntax to achieve this result. I had to tell only Copilot what I wanted.

By reviewing and applying these custom formula conditional formatting rules, your worksheet will display the appropriate colors and adjust as your data changes.

GIF showing how Edit Data updates colors based on rules after applying conditional formatting generated by Copilot.

What else do you need to know?

You can also ask your co-pilot for other types of highlighting.

• Makes values ​​in ‘column name’ greater than ‘number’ white text on black background.
• Highlight cells in light blue for ‘column name’ containing ‘specific text’.
• Use bold font to highlight the top 10% of ‘Column Name’ values.
• Applies a red and green color scale to the values ​​in ‘Column Name’.

If you applied conditional formatting rules using Copilot, you can manage and edit existing rules by selecting: conditional formatting > Rule Management From the toolbar or ribbon

Conditional formatting menu screenshot

Conditional formatting in Excel is a versatile tool that helps you analyze and present data more effectively. Exploring the many features of conditional formatting with Copilot will help you unlock its full potential and make your spreadsheets more informative and visually appealing.