Copying conditional formatting in Excel

Conditional formatting is a wonderful feature in Excel. It allows you to format cells based on conditions such as making a cell red if they are negative. Check the web link below for the tip on conditional formatting.

Today’s tip is about how to copy existing conditional formatting. The example below shows that some cells in column B that are formatted to have a red font if they are less than zero.

If we add more columns, Excel 2010 does something pretty slick. It assumes you want to continue the conditional formatting and does it for you automatically.

If your version of Excel doesn’t do this or if you applied the formatting to some cells and want to expand it to a new range, you have a few options.

  • You can copy and paste the formatting. Copy a cell which has the conditional formatting you want and select the new cells. Choose Paste Special and then Formats.
  • Select the cell that has the conditional formatting and click the Format Painter. Click on the cell or cells and the conditional formatting will now match.

  • The third way is different for older versions versus Excel with the Ribbon. See the sample below, where the conditional formatting is only applied to B3:B5.
    1. For pre-Ribbon versions: To apply it to the entire column (B3:B9), just select the cells you want formatted, making sure that at least one cell in the range already has the conditional formatting you want to copy. Choose conditional formatting and the formula should already be populated. Click ok and you are done!
    2. For Excel 2007 and 2010, highlight a cell in the range that has the conditional formatting you want to copy. Choose conditional formatting and Manage Rules. The existing rule should be there, you just need to change the range by clicking the button and selecting the new range.
This entry was posted in Office. Bookmark the permalink.