Smartsheet Tips: Using Conditional Formatting and Formulas Together

Blog

Smartsheet Tips: Using Conditional Formatting and Formulas Together

Conditional formatting works great if you're looking to highlight rows based on data or values already contained in the sheet (e.g. a red background color can appear on rows when the "status" is "overdue"). Issues can arise when you want to apply the formatting based on a comparison between two cells in the sheet. For example, if you have a "target cost" column and an "actual cost" column, and want to apply a red background color to rows that exceed the estimate. This can't be done with conditional formatting rules alone.

That’s why we recommend using conditional formatting's greatest ally - formulas! Specifically, you will want to use an IF formula to perform the comparison between the two values, and then apply the formatting rule based on the result of the formula. 

Here’s how to use conditional formatting and formulas together: 

  1. Insert a new checkbox column into the sheet.

 


 

  1. In the top row, use an IF formula to check the box IF your criteria is met. To go along with our example above, here's a formula that would check the box if the "Actual Cost" exceeds the "Target Cost" in row 1: =IF([Actual Cost]1 > [Target Cost]1, 1, 0).

 


 

  1. Drag-fill the formula into all other rows in the column. Don't worry about adding the formula to new rows that are created -- Smartsheet's auto-fill feature will take care of this for you.

     
  2. Set up the conditional formatting rule to apply a red background color to rows if the box is checked.

 

    

For more information on conditional formatting and formulas, see our related blog tips:

 

Comments

Auto Calculation?

Hi Allie, Really great Tip here - am a new smartsheet user and loving it already! Is there a formula I can use to include in a calculation ONLY rows with a specific checkbox ticked? I have a sheet I use for quotations. I put all the products that would meet the scope requested in this sheet - so many are only options. I would like the total at the top of the sheet to 'sum' only the sales prices from rows with the 'Included' column checkbox ticked. I hope that makes sense! Look forward to hearing from you in due course, Jay

RE: Auto Calculation?

Smartsheet Community Manager's picture
Hello Jay, Yes! Use the SUMIF formula as follows, =SUMIF(CB:CB, 1, Sales:Sales) –Cheers, The Smartsheet Team

Filtering out Saturday & Sunday

When we enter our start and finish dates, we would like it if our calendar view did not reflect us working on Saturdays and Sundays. Is there a way to filter out the weekends?

Conditional formatting

I tried using the ADD of conditional formatting to get specific results. Please see below how I put it together: if REMARKS is 'FOR REVIEW' and FINAL PRICE is less than [Profit]1 then apply this format to the entire row. If you see there at the part where it says 'less than', instead of putting values, I put the title of the column since the values will change from time to time on this column. However, smartsheet does not recognize this.

Sum by cell color (not rows)?

How can I sum all cells in any column if the cell is red or green? I have a simple spreadsheet for loan amounts STARTED or SETTLED in the month (with 12 colums for each month in 2017), and we manually colour the amount green for the month the loan is started,, then red for the month the loan is settled.

Sum by cell color (not rows)?

Diana Ramos's picture
Hi Mary, Great question. You can sum all cells in a column by color if you use conditional formatting logic to assign a value to a cell in a hidden column. Here is a Smartsheet Community post that further details how to do this: http://bit.ly/2lESXeR. Hope this helps. Thanks, Diana

Add new comment