Blog Post

Excel Blog
3 MIN READ

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

KayceeSue's avatar
KayceeSue
Icon for Microsoft rankMicrosoft
Oct 31, 2024

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.

Updated Oct 31, 2024
Version 1.0

3 Comments

  • Helene22's avatar
    Helene22
    Copper Contributor

    This is very disappointing.  When teaching staff the feature where you can add conditional formatting to entire rows for several criteria the words used were "gamechanger".  Trying to sell Copilot to staff will now be more difficult.  This feature was, absolutely, a gamechanger.

  • HRD01's avatar
    HRD01
    Copper Contributor

    you made worst on conditional formatting doing between dates and gave me wide results please return the value back to its original.

     

  • Jamies666's avatar
    Jamies666
    Brass Contributor

    Just remember -

     

    New (2024 nov ) Excel is now set to ask if it should alter your workbook (for efficiency) every time you open a file 

    especially if you have preset CF on some columns 

    the efficiency is possibly to have the workbook altered so you don't have to close the "offer", the data having been altered already!

     

    And 

    for data checking, remember that what is shown may be an approximation of what is held as a floating point value

    so what is displayed as 0.00  may not be =0.

     

    You should test for the absolute of the difference between a value and a testing for value to be under a maximum difference -

    such as IF(ABS(value-0)<0.0000000001,"Nearly what is wanted"," too much difference")

     

    And also remember Excel often takes a text value to be a scientific format entry, or a date entry