Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Duplicate Conditional Formatting Rules

Latest post Fri, Mar 28 2008 11:11 AM by Anonymous. 5 replies.
  • Tue, Mar 25 2008 7:20 PM

    Duplicate Conditional Formatting Rules

    It would be very helpful to be able to duplicate conditional formatting rules as a basis for making a new rule, or to get the original rule copied into a new sheet in the workbook.  Having to make every rule up from scratch means a lot of clicking around to set formats and such which could be easily copied instead

    • Post Points: 21
  • Wed, Mar 26 2008 4:35 AM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,447
    • MVP

    Re: Duplicate Conditional Formatting Rules

    How about Copy>Paste or Copy>Paste special...>Formats?

    Or am I missing something? (XL2007)

    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    Filed under:
    • Post Points: 37
  • Wed, Mar 26 2008 10:12 PM In reply to

    • Alex J
    • Top 50 Contributor
    • Joined on Wed, Jan 9 2008
    • Posts 21
    • Points 349

    Re: Duplicate Conditional Formatting Rules

    For true "porability", I use Named Formulas.

    Otherwise, when you select a cell with Conditional Formatting, and other cells with no Conditional Formatting, and then engage Conditional Formatting function, the CF is applied to all the selected cells. Using relative/absolute referencing (or a named formula) can REALLY simplify the definition of the CF.

    Regards, Alex J

    • Post Points: 5
  • Thu, Mar 27 2008 3:47 PM In reply to

    Re: Duplicate Conditional Formatting Rules

    For a selected range of cells, I want to duplicate an existing conditional format and change it's formula and format just slightly to become a new rule of conditional format.

     As it is now, each new conditional format must be built from scratch for a given selection range.  that means the formula and formatting start cleared and I want to start them from where the last one left off.

    This is the sort of task that is only really painful if you have lots of sheets and need to add a new conditional format to all of them based off another existing conditional format.

    I will look into named formulas, thanks for the suggestion.

    • Post Points: 21
  • Thu, Mar 27 2008 6:50 PM In reply to

    • Rick Williams
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Melbourne, Australia
    • Posts 172
    • Points 2,485

    Re: Duplicate Conditional Formatting Rules

    @Nick: Paste Special -> Formats works fine if there are no other formats for the cell you wish to preserve, such as number formats, borders, or other aspects of the format you are not adjusting conditionally.

    @Anonymous: A technique I often use to copy conditional formats (in Excel 2003) is to:

    1. Select the cells you want the CF applied to
    2. hold Ctrl, and select at least one cell within the range that is already contains the CF to be applied
    3. Then Format->Conditional Formatting
    4. Here you will see the conditional formats about to be applied
    5. When you click OK, the CFs will be applied across the whole selection
    6. Then Select just the 'newly' formatted cells and make the desired changes.

    Note that this only works when the cells to wish to copy the CF to don't already contain conditional formats. If the selected range contains more that one definition for any of the three rules, the Conditional Formatting box will show these rules as being blank.

    Rick Idea
    Melbourne, Australia

    • Post Points: 21
  • Fri, Mar 28 2008 11:11 AM In reply to

    Re: Duplicate Conditional Formatting Rules

    Thanks Rick, that trick is a good standby for expanding the range of conditional formats into new cells, I used it a lot in Excel2003.  I have since become somehat averse to this technique, as it tends to punch holes in the cell ranges of existing conditional formatting used in my tables.  By pasting with fomratting, you can sometimes end up with a mess of duplicate rules on balkanized ranges of cells.  I almost always paste values specifically because of this little annoyance.

    I appologize for neglecting to specify I am working in Excel2007 and the place I would like to be able to duplicate an existing rule is in the new manage rules dialogue.

    Here is an example case:  I am managing several parallel tables across multiple sheets, using conditional formatting to color rows according to the valule of a column, Status.  If I have 5 diffferent conditional formats on the table and I want to add two new states of Status, I would like to be able to duplicate an existing formatting rule for a starting point, change the text that I am matching for and change the color of the formatting, and then this new rule will apply to the same cell range in the table as the original rule. 

    As it stands now, I make a new rule that is blank, switch to an existing rule and edit it to copy the formula, close this rule, edit the new blank rule and past the formula, change the little bit of text in the formula for the new token, set up the new formatting from scratch, then colse the rule and copy the cell range from an existing rule to the new rule.  Then I switch to the next sheet and do it all over again.  This all just feels a little convoluted to me, and is much more manually intensive that it needs to be.

    My request is for a duplicate button in the manage conditional formats dialog of Excel2007.



    -jason  (I suppose I should make an account)


    • Post Points: 5
Page 1 of 1 (6 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.