I like to apply conditional formatting when tracking metrics to quickly identify things such as top and bottom 3 months of performance, however, whenever I add a new month (typically as a new column) the formatting does not include the new data, so I need to manually change the Applies To range for each item. This can take a long time since there are a lot of metrics to track and this needs to be done each month. Using the Format Painter also doesn't seem to solve the issue since that will usually apply the same Rule to the new column but will only apply to that column. So for instance, I will have a rule highlight Top 1 that Applies To $C$3:$H$3 and if I add data to column J and use Format Painter, I will still have a rule highlighting Top 1 for cells $C$3:$H$3 but will then also have a rule highlighting Top 1 for cell J3.
Is there any way to make it so that the conditional formatting rule will automatically expand the Applies To range to include data entered in a new column?
Is there any way to make it so that the conditional formatting rule will automatically expand the Applies To range to include data entered in a new column?