Blog Post

Excel Blog
2 MIN READ

New Conditional Formatting experience in Excel for the web

Avital Nevo's avatar
Avital Nevo
Icon for Microsoft rankMicrosoft
Jan 20, 2022

Excel for the web has evolved into a full-featured modern spreadsheet app that can effectively support your daily work. Today, that experience continues to improve with the new Conditional Formatting capability, making it easier for you to manage, edit, and create formatting rules based on specific criteria, to help you quickly spot trends and insight from your data.

 

Manage rules in a new side pane 

With the new side pane, you can manage the rules while seeing the formatting result on the grid. Simply hover the mouse pointer over a rule to see the range it refers to. 

Manage rules in a new side pane

 

You can manage the rules for the current selection or the entire sheet with simple dropdown. 

Manage rules in sheet or selection

 

Edit rules or add new ones 

In addition to managing rules, you can also edit or add new ones in Excel for the web. 

To edit a rule, click on the Edit button in the side pane.  

Edit rules

 

To add a new rule, click on the New Rule button in the side pane or use the Conditional Formatting menu in the ribbon. 

Add new rules

 

In the new rule editor, you will find the familiar rule types like those in Excel desktop. 

Rule types

 

Use the straightforward dropdowns to specify the criteria and choose a format. 

 

New rule types 

We have also added new rule types to Excel for the web, continuing to close the gap with desktop. 

  • Greater than or equals to 
  • Less than or equals to 
  • Not containing 
  • Beginning with 
  • Ending with 
  • Blanks/No blanks 
  • Errors/No errors 
  • Formula – you can now specify the formatting criteria using a logical formula in Excel for the web. This rule type gives you the added flexibility of formatting a range based on the result of a function or evaluate data in cells outside the selected range.

Formula rule type

 

More coming soon to Conditional Formatting in Excel for the web: 

  • Reorder rules with drag & drop 
  • Change the range the rule refers to in the rule manager 
  • Custom formatting 

Your feedback is important for us! Tell us what you think about the new Conditional formatting experience in Excel for the web by writing a comment on this blog post or Send us a smile or frown. You can also submit new ideas or vote for other ideas via Microsoft Feedback. 

 

Want to know more about Excel for the web? See What's new in Excel for the web and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter. 

 

Thank you!

 

Updated Jan 20, 2022
Version 1.0
Excel for the web
  • robertomonteiro's avatar
    robertomonteiro
    Copper Contributor
    Sep 06, 2024

    Conditional formatting in Excel Online, its so incredibly bad, unfortunately its a colossal downgrade from the offline version, and an incredibly number of options/controls are plainly missing. We can only hope Microsoft, will eventually fix this.

  • srsobieraj's avatar
    srsobieraj
    Copper Contributor
    Mar 17, 2023

    Avital Nevo 

    Are there any plan to make further updates here? I would like to be able to edit the default behavior of Icon Sets but this interface does not allow that.  For example, if a value is above/below zero then assign a green/red arrow respectively.  The desktop interface allows this condition to be set, but not the online interface.

  • cool2021's avatar
    cool2021
    Iron Contributor
    Aug 21, 2022

    Okay. So in my case the conditional color formatting does not work. I would actually have to write code to create new column  before export.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 21, 2022

    Yes, that should work.

    Alternatively, you could create the High/Medium/Low values in SAS or SPSS.

  • cool2021's avatar
    cool2021
    Iron Contributor
    Aug 21, 2022

    Okay. So if I have a column of data, say, column A, that is $ Average Spend Amount. I should not use the color code formatting to indicate amounts that are high, medium, low values. Instead, a need a new column with high, medium, low values for each record in column A. And, only then, could I export the dataset with column A and the high, medium and low values in the new column to a .csv datafile that could be imported into another software like SAS or SPSS etc. for further analysis.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 21, 2022

    A .csv file is a plain text file. It does not retain the color formatting of the original Excel workbook.

  • cool2021's avatar
    cool2021
    Iron Contributor
    Aug 21, 2022

    If I export this spreadsheet to a .csv, can I then import the colour formats into another software like SAS or SPSS and use the colour scales to filter on for further modeling and analysis?

  • XLismylife's avatar
    XLismylife
    Copper Contributor
    Mar 01, 2022

    I hope the issue of breaking up a rule applied to a range is being corrected.  If a range is selected for the rule application then we should be able to insert or delete rows without breaking the conditional formatting rule into multiple applied areas.  It is very frustrating having to constantly correct these in all my spreadsheets as I have come to rely heavily on conditional formatting.  It would also be nice to have the default of writing/editing a formula to be able to use the arrow keys to navigate through the formula instead of selecting other cells in the worksheet.  These are my two biggest frustrations with conditional formatting.

    • hb-webdev's avatar
      hb-webdev
      Copper Contributor
      Jan 20, 2025

      YES!!!! THIS IS INSANE!!! Why does my `A:A` range rule get broken up into `A1:A20,A21:A100` when i simply insert a new row!?! How utterly USELESS and RIDICULOUS!!!

  • Chris Newman's avatar
    Chris Newman
    MVP
    Mar 01, 2022

     Please add a Duplicate Rule button. Also, it would be great to be able to name your rules or provide a description that is viewable on mouse hover. Looks very promising!!