Apply Conditional Formatting to Multiple Cells with a Single Formula

Add to Favorites
Author: | Edits: don

How to use a single formula to apply conditional formatting to multiple cells at once in Excel.

This saves you the time of changing a formula for each cell and then individually adding the conditional formatting to it.

Steps

Create the formula that you want to use within the worksheet so that you can make sure that it is working.

667c9f6bdb39e315a7bcccec0c475e76.png

Remember that a formula for conditional formatting must evaluate to TRUE or FALSE. Our formula does just that:

ce9d5e3ecf9060c85fee3d13d4e6f006.png

Now, figure out if the formula will apply to multiple columns, or rows, or both. In this example, I want to copy the formula down column A so that each cell will be highlighted if cell A1 has a value in it but the current cell is empty. To address this, the cell references need to be made absolute or relative.

I need A1 to remain the same when the formula is copied so I need to change that to $A$1 which makes that cell reference absolute; it will not change when the formula is copied.

I need A2 to change when the cell is copied down the column so I could leave it as it is or change it to $A2 putting the dollar sign only in front of the column but not the row; this means the column will never change but the row will change when it is copied down.

772726139c52c7e78023f481804f36b7.png

For more on cell references, view our tutorial on absolute and relative cell references in Excel.

Once your formula is ready to go, you can now add the conditional formatting:

  1. Select all of the cells for which you want to apply the formatting:
    81365e171b2dfc03cfb763259c17e55d.jpg
  2. Go to the Home tab and click Conditional Formatting > New Rule...
    29de748f45bdde9c5cbf704f929e995b.jpg
  3. Select Use a formula to determine which cells to format and paste the formula that you just created.
    251235a7ee62e0a3b20c0d8e230cfffe.png
  4. Click the Format button and choose the desired look for the cells.
    481dbb5f55f29e301bab6752cf361b28.png
    Hit OK for the Format window and then OK again for the next window and you should be good to go.
  5. Test it out:
    0cff18a8a82dd72cc1b5eb32cc7b82ca.jpg

Alternatively, you could have applied the conditional formatting just to cell A2 and then selected that cell and dragged the quick-fill-handle down to copy the conditional formatting to the other cells; that works nice when there is no content currently in the destination cells.

Notes

The most important thing here is to get the formula right before you try to apply the conditional formatting. Often times, people mess-up the absolute/relative cell references in the formula and this is why the conditional formatting doesn't behave as desired.

Make sure to download the attached file for this tutorial so you can work with this example in Excel.


Excel Function: AND(), ISBLANK(), NOT()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Custom Threshold Highlighting Trick in Excel
Tutorial: How to highlight data in Excel based on the number you input into a cell and the comparis...
Quickly Copy Cell Formatting to Other Cells in Excel
Tutorial: How to copy all of the formatting of a single cell to other cells in the spreadsheet witho...
Join Parts of a List With a Single Formula in Excel - (1 Minute Tutorials)
Tutorial: How to quickly combine any part of an entire list of values into a single value in a sing...
Excel Workbook Reset with a Single Button Click [Easy Version]
Tutorial: How to easily clear or reset an entire workbook, specific worksheet, or just a few cells ...
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Macro: Determine if a cell in Excel contains a formula or function with this UDF (user defined fu...
Copy Data or Formatting to Multiple Worksheets in Excel
Tutorial: Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple...
Tutorial Details
Excel Function: AND(), ISBLANK(), NOT()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course