Excel: how to permanently apply conditional formatting to the whole spreadsheet?

Solution 1:

The solution is to use a dynamic defined range. I usually use the offset function. You can find some help here. When you have defined the dynamic range, you can use as a range in your conditional formatting.

When you click "Apply" and then "Ok", the formatting should be applied to that range.

If you return to the conditional formatting, you'll see that the dynamic range is actually translated to the row-column notation. However, I've noticed that if you add a row to your dynamic range (by filling in a blank cell), this is automatically propagated to the conditional formatting.

Solution 2:

One workaround is to make your data a dynamic named range. This will allow you to apply the conditional formatting to the named range by name, which will remain constant, while the range the name applies to may change as you add or remove data.

How to set up a dynamic named range:
Go to the Formula ribbon and click Name Manager. In the Name Manager, click New... to create your named range. Give it a descriptive name (no spaces) like MyData. In the Refers to field you can use a formula to define the named range. Something like

=INDIRECT("Sheet1!$A$1:$Q$"&COUNTA(Sheet1!$A:$A))

will refer to all data in A:Q assuming there are no blanks in your data in column A. Now you can use the name MyData to refer to all your data, even if you add or delete rows.

All that's left to do is to redefine your conditional formatting rule with MyData in the Applies to field.