42

I have set up conditional formatting in a set of cells of "Sheet2" on my workbook. I'd like to reuse this formatting on "Sheet1" (I've spent quite some time setting it up). Is there a way to do it?

I know that you can copy conditional formatting in a single sheet by selecting the new cells, but I don't recall it is possible to select cells across multiple sheets.

Thanks!

Improve this question

4 Answers 4

Reset to default
33
  1. Select conditional-formatted cell/range in Sheet2
  2. CtrlC (Copy)
  3. Select target cell/range in Sheet1
  4. CtrlAltV (Paste Special) > T (Format) > OK

Alternatively,

  1. Select conditional-formatted cell/range in Sheet2
  2. Click Copy Format button in tool bar
  3. Click "Sheet1"
  4. Select target cell/range
Improve this answer
2
  • 6
    This also copy over the background, font, etc.. It doesn't only copy over the conditional formatting. Commented Apr 11, 2016 at 10:30
  • It also somehow changed the format of empty cells - not all but most. Commented Jan 13 at 7:44
21

Alternate Answer where you don't want other formatting disrupted on the destination sheet

  1. Copy a cell from the original sheet to an UNused position in the destination sheet (not one with data in it).
  2. Open the Manage Rules option of Conditional Formatting
  3. Select Show formatting rules for: This Worksheet
  4. For each Rule, adjust the Applies to match the range you require. Easy to do, just:
    • Click the range button to the right of the Applies to
    • Click-drag-select from the top left cell to the bottom right cell
    • Click the range button to return to the Conditional Rules Manager
  5. Click OK or Apply to see the result

Try it, its way easier than it sounds!

Improve this answer
2
  • 1
    Yes, this ^^. Doesn't interfere with other formatting and very simple. Commented Apr 12, 2021 at 7:07
  • A little bit more work than the currently higher rated solution by @wilson but way more precise and no side effects. Might be worth trying wilson's solution first though, especially if there are many rules. Commented Jan 13 at 7:50
8

You can also select the cell with conditional formatting in "Sheet2"

Then click on "Format Painter" in your tool bar

Then select all the cells in "Sheet1" you like to have the same formatting.

The conditional formatting will be copied over

Improve this answer
2
  • 2
    Nice and fast!! Commented May 27, 2015 at 12:12
  • Has side effects though, formats empty cells that should not get formatted. Commented Jan 13 at 7:51
0

You can create a macro for the conditional formatting that you are creating and then execute this macro in each worksheet or workbook that you want.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.