29 Comments

Summary:

This is the first in a series on some of the more advanced functionality of Numbers, my favorite spreadsheet app. If you’re new to spreadsheets, or just want to make them look a little less like Microsoft Excel, this article should be right up your alley. […]

Numbers

This is the first in a series on some of the more advanced functionality of Numbers, my favorite spreadsheet app. If you’re new to spreadsheets, or just want to make them look a little less like Microsoft Excel, this article should be right up your alley. In this article, we’ll talk about conditional formatting.

What is conditional formatting?

The real power behind spreadsheets is not just how well they can crunch data, but how you can visually present the information in a variety of ways. Sometimes, however, you may want to give more attention to a particular area, or hide certain data if it’s not relevant. You often see conditional formatting applied to financial spreadsheets where positive values are in black or green, but negative values are always in “the red.” So how do you do that? It’s where conditional formatting, or formatting based on certain criteria, comes into play.

Here’s a quick example. Let’s say I have something that shows you my net worth for each month of the year. (These numbers are entirely made up, for demonstration purposes.) Assuming that these numbers were coming from a different spreadsheet, I could likely have one of three possible values for each month. If I were doing well, the value would be a positive number. If I were feeling the toils on the economy and not so great at managing money, the value might be negative. The last option would be if we haven’t gotten to a particular month yet, for example, in September 2009 the value reads “$0” as no data yet exists to calculate that value.

Net Worth without Conditional Formatting

Net Worth without Conditional Formatting

Applying some formatting

Based on this example, you can see that before I begin to do any formatting, I have a mix of positive values, negative values, and some that read “$0” because we haven’t yet entered data for those months. To apply formatting, I would select the first cell next to January.

In the Inspector under the Cells tab, you’ll see an area that is labeled “Conditional Format” with zero rules applied. If I click “show rules” I will get a new window that allows me to start stacking on a series of rules that I want to compare this cell against.

For this example, I’m going to pick “Choose a rule” and select “Greater than.” If I enter “0” as my value, I’m telling Numbers that I want to apply special formatting to any value that is greater than zero. This would be where my net worth was in the positive numbers. If I click the “edit” button, I can now start layering on formatting. To keep things simple, I’m just going to change the text color to green and press “done.” You do have options here for a variety of other formatting choices, including bold or italics and changing the cell background. Looking back at your document, you should see that the value has now turned green!

Now I’m going to apply another rule, this time to address negative net worth. This rule is going to be “less than 0” and I am going to format the text to be a red color with bold styling.

Finally, I am going to apply one last rule, which will address the “0” values for upcoming months. Usually, you will not see a net worth perfectly at “0,” so I have used this assumption to keep our “0” values open for this next example. If a value is “0,” we’re assuming its because we are at a month in which nothing has happened yet, so we will simply want to “hide” this value until an actual value (positive or negative) can take its place. To solve this, we’re going to format the cell to take values that are “0” and turn the text white, so it will simply blend in with our background and therefore be hidden.

One down. A few to go.

Once we are finished here, we can close this window and go back to our spreadsheet. Now we’ve laid out the set of conditional formatting rules, but they currently only apply to the net worth listed in January. The next step most would take is to simply grab the little dot on the right of the cell and drag down to “fill” down, but do not do this. You will inadvertently fill down the value of that cell and not its formatting.

Net Worth with Conditional Formatting Applied

Net Worth with Conditional Formatting Applied

The last trick up my sleeve is how to copy formatting between cells and that is to use a feature called “Copy Style.” This is similar to the “Format Painter” of Microsoft Excel. Simply highlight the cell we were working on, go to the Format menu and select “Copy Style.” You can then highlight the remaining cells, go back to the Format menu and select “Paste Style” and you should end up with something that looks like the image to the right.

The Copy/Paste Style is a very useful tool and is found in all of the iWork applications. There is even a quick button to access it that you can add to your toolbar (open the View menu and then click “Customize Toolbar”).

If you are familiar with Conditional Formatting from Excel, nothing has really changed except the limit to the number of rules you can apply. Microsoft Excel previously limited users to applying three rules to a cell (usually that was more than enough) and, as far as I’ve found, Numbers imposes no limit. In the next article, we’re going to dig a little deeper into the power of Numbers with how to do horizontal and vertical lookups. If you have any Numbers-specific topics you would like me to cover, please use the comments below and let’s get the discussion started!

  1. Is it possible to use an equation as the rule? I am trying to apply this formatting to a grading spreadsheet so that it shows any score above the mean as bold, but I can’t get it to recognize an equation into the rule entry box.

    Share
    1. Why when I import a commercial xls file with Numbers does it tell me in it’s warnings that
      “Unsupported conditional formatting on ‘n’ cells was removed …..”

      Share
  2. Yes you should be able to. When you are in the Conditional Format window, there is a little icon that looks like a blue button with a box and an arrow. Clicking this brings up the formula editor, in which case you can either input the formula, or reference the cell that outputs that mean. This *may* be a feature in iWork ’09 only however, I don’t have an older version to test. Let me know if that works for you!

    Share
    1. You actually cannot use equations in the conditional format rules by clicking on the blue arrow. You can only make a reference to another file. However, this other file CAN have a formula.
      So, via triangulation, you can do anything.

      Share
  3. I was also looking for the equation-based formatting as cell-value-only formatting is little more than a gimmick. I’ve created numerous powerful spreadsheets in Excel and frequently use this feature. Key to this is the ability to reference other cells. E.g. highlight a cell in column B if it is empty, unless the same row in column A is also empty – this then highlights where you only have one value, leaving zero or two values unhighlighted. I see no evidence of formula support at all in Numbers ’08.

    Share
  4. Hi, Allister!

    You should be able to achieve this in Numbers ’09. (I say should only because I was a bit confused by your request… but my brain isn’t in a spreadsheet’y mood at the moment). I did just compare between Numbers ’09 and Numbers ’08 and ’08 does not allow you to enter in formulas for conditional formatting rules and Numbers ’09 does. I hope this helps and thanks for the feedback!

    Share
  5. Each time I try to input an equation-based rule in Number ’09 in Conditional Formatting, the formula vanishes after I press Enter. I’ve tried it with both “=” and without “=,” but neither way works.

    What I’m trying to input is the following (just an example):

    Greater Than: ((SUM(Table1::B:B))/10)

    I can’t seem to get it to work.

    Share
  6. Chris, try this. Change the background colour of cell C2 to red if cell B2 has a value greater than 10. That’s the most basic. If that works, then set the colour of cell B3 to green if the greater of B2 and B3 is bigger than 10. Obviously play with values in B2, B3 to prove it works. If you can do both those, then proper conditional formatting has arrived. Of course it gets fun when you copy that formatting. Even in Excel you have to do it just right for the formulae in the formatting to adjust properly.

    Share
  7. [...] Michael on June 7, 2009 The Apple Blog has a nice write up on conditional formatting in Numbers. The real power behind spreadsheets is not [...]

    Share
  8. I’m having the same problem with trying to use a formula in the conditional formatting window. Nothing seems to work, and if there is a way to work around this I haven’t been able to figure it out. I just want the formatting of one cell to be based on the value of a different cell. Any help would be greatly appreciated.

    Share
  9. I am having some problem with conditional formatting. I want to mark green A2 if the value is greater is A1 and red if less, but I can’t find any way of doing that. Any help would be appreciated.

    Share
  10. The fundamental problem is that the condition is assumed to be tested against the cell being formatted, not just on the results of a true/false evaluation as such.
    So if the true condition was to check the results of a formula, then all that would be necessary would be the formula itself, and NOT starting with an implied (“the current cell is greater/less etc than ….”).
    In other words, in its current form, you can’t set a value for the current cell if the results of some formula are true: the results of that formula (if you manage to enter one!) are compared to the current cell being formatted and the format applied if that comparison is true.

    Share

Comments have been disabled for this post