MS Excel 2011: Extend Conditional Formatting to Additional Rows, Columns

Problem: I created a Conditional Formatting rule in Microsoft Excel, but it only applies to the portion of the sheet I selected when I initially created it. How can I extend this formula to cover more of my sheet?

Solution: To modify which columns and rows your formula applies to, do the following:

Note: I’m assuming that you’re using Classic Conditional Formatting rules. If you aren’t, the steps may be a little different.

• In the Home section of the toolbar click Conditional Formatting > Manage Rules
  In the top, next to “Show formatting rules for: ” select “This Sheet”
• You should now see all the Conditional Formatting rules that apply to your spreadsheet
• Find the rule you want to extend
• You should see the Rule (how it knows what to select), the format you selected to have applied when that condition is true, and the section of your sheet that rule applies to in the “Applies to” column. We’re going to be modifying this last column to extend the selection.
• Recall that Excel sheets are laid out in a grid format, so selection ranges will usually be declared in a rectangular format. In the “Applies to” column, we want to set a beginning column and row and an ending column and row. - Example: Sheet1!$A$2:$F$2 means that I want the formula applied on Sheet 1 between A2 and F2 – effectively a single row
• Example: Sheet1!$A$2:$F$81 means that I want the formula applied on Sheet 1 between A2 and F81 – a very tall rectangle
• Using the above examples, you should be able to extend or modify your existing Conditional Formatting rules in a standard rectangular selection