## Stay on Top of Enterprise Technology Trends

Get updates impacting your industry from our GigaOm Research Community

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.

### 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.

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!

Numbers are the Supreme Court of science. However Godel proved that we may not prove everything. There are Physics Foibles!!

Hello!

Same problem as Steve and Conan here too:

quote

“Anyone figured out how to have relative references in Conditional Formatting?

I want B1 to be green if greater than A1, and B2 to be green if greater than A2, etc. But when I fill down, the conditional formatting of all cells in column B point to A1.”

end quote

Anyone, please?

Hey Numbers spreadsheet gurus here…HELP!!!

(please? :-))

Going nuts trying figure out how to build a function that will round UP (or DOWN) when the value is less than a while number.

Any takers?

Thanks so much,

David

Is it possible to apply conditional formatting to durations?

B6 has the following formula =SUM(D6:L6) where the values in D through L are times (hours and minutes).

I would like to be able to highlight in red any totals that exceed 30 hours. When I try to apply conditional formatting to B6, it doesn’t work.

Any suggestions or solution would be gratefully appreciated.

I’m using Numbers ’09

Can you put conditional formatting onto a cell, based on the value of another cell (e.g. if cell x is bigger than y, then colour cell z red)

I can only see how to use the value of a specific cell for conditional formatting.

I meant in the conditional formatting window, not the rules window.

Anyone figured out how to scroll down in the rules window once you add enough rules to reach the bottom of the screen? Most I can get on my 17″ MBP is 14 before it goes below the Dock.

I have the same problem as Steve:

[quote]

Steve on January 5th, 2010 at 12:03 pm

Anyone figured out how to have relative references in Conditional Formatting?

I want B1 to be green if greater than A1, and B2 to be green if greater than A2, etc. But when I fill down, the conditional formatting of all cells in column B point to A1.[/quote]

I can’t believe iWork engineers have not though about that..

How about conditional formatting in the other direction… e.g.: if a cell is red then add 1 to the value in another cell.

Anyone know how to do that?

-wg

Chris, I have been working on a self-propagating spreadsheet in Numbers over the last few months. My role in my company is changing to more of a managerial role. The spreadsheet was very helpful in saving time on my projects but I am not able to do devote the amount of time I’d like to wrap up its development. Do you do freelance work or could you recommend someone who does? Please do let me know.

Anyone figured out how to have relative references in Conditional Formatting?

I want B1 to be green if greater than A1, and B2 to be green if greater than A2, etc. But when I fill down, the conditional formatting of all cells in column B point to A1.

OK so as it turns out the colors in my sheet were chosen based on a value in a particular cell so I just did ‘categorize by this column’ and it worked perfectly for me. thanks!

How do I delete rows that are not of a particular color, besides manually selecting them?

Very informative thanks. I’m trying to enter needle sizes e.g. 11/80 into numbers and it either shows it as a 0 or as a decimal. Is there anyway to format it to show text entered as is? I can’t figure out why it is processing these entries as formulae when I haven’t entered an = sign…:(

Just format the cell value as Text.

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 …..”

Hey Guys, I have come across this blog and I found it very cool. I’m searching for some orientation towards conditional functions on iWork Numbers.

What I want to do is add ONLY certain numbers, for example: 1,2,2,2,3,3,2,1,1,3,1,2 … I’m working with a list of 1000 numbers, so I want Numbers to give me the SUM of the number os boxes with 1, 2 and 3. I’m trying to setup 3 different SUM functions to run through table and ADD according to what I want.

Any thoughts on this?

Cheers!

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.

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.

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.

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.

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.

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!

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.

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!

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.

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.

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 â€¦..â€