Skip navigation
This discussion is archived

Can't Fill Conditional Formatting Rules?

3441 Views 5 Replies Latest reply: Jan 10, 2011 2:08 PM by Tor Lundgren RSS
Tor Lundgren Level 2 Level 2 (235 points)
Currently Being Moderated
Jan 8, 2011 10:16 AM
It seems crazy, but it sounds like Numbers doesn't have the capability of filling conditional formatting rules?

In other words, if I have a large table that I was to format according to a set of rules referencing a relative value, I can't create the rule in the first row and then fill down.... right?

As an example of what I have in mind, I have numbers for severals years (2001, 2002, 2003, etc.) and then a column with the average of all the years on the right. I would like to make the numbers green if greater than average and red if less than average. And once I've created the rule for the first row, I'd like to drag it down and do it for all of the rows in the table.

Is this possible?
MacBook Pro, MacBook Air, Mac mini, iPhone 4, Mac OS X (10.6.6)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Jan 8, 2011 11:48 AM (in response to Tor Lundgren)
    If I understand well, it's really simple.

    In a cell, insert a formula calculating the average value.

    Then define your rules upon this cell.



    In A1 is the formula :

    =AVERAGE(B)

    Yvan KOENIG (VALLAURIS, France) samedi 8 janvier 2011 20:48:37
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.6.6)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Jan 9, 2011 11:34 AM (in response to Tor Lundgren)
    OK, I read too fast the original question.

    There is no way to apply a kind of "Fill Down" to conditional formatting.
    You're right, you must define the rules row by row.

    If I remember well, some askers wrote here that they filed a request thru the feedback channel.
    I may just hope that we will get the new iWork soon.

    Yvan KOENIG (VALLAURIS, France) dimanche 9 janvier 2011 20:34:31
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.6.6)
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Jan 9, 2011 5:02 PM (in response to Tor Lundgren)
    Tor Lundgren wrote:
    Next, I'd like to copy that conditional rule down, but have the condition refer to the appropriate average cell, not the average from the first row, which is in fact what happens when I try to copy and paste the cell formatting:

    Obviously, I could just do the rules carefully for each row, but in my real data, I have a lot of rows, and if I decide to change the conditions at some point, it would be a real pain to redo.


    Changing the cell references in the conditions in this case takes four clicks of the mouse for each group of cells to be changed:

    With the conditional rules set for all rows, and the Conditional Format window open, as shown in your second image:
    Conditional Format-1

    Select B3:E3.
    Click on the F2 reference in the first rule.
    Click on cell F3 in the table.
    Click on the F2 reference in the second rule.
    Click on F3 in the table.

    Select B4:E4...etc.

    BTW: What format do you want to see in the cell if the contents are equal to the average value in column F? You've not covered that possibility in the two conditions.

    Regards,
    Barry

    PS: Skitch provides four varieties of HTML code which will display your image in your message. For this reasonably sized image, I used the HTML - FULL code.
    B

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.