Using Conditional Formatting in Numbers

Spreadsheets are fantastic for calculating numbers. Apple's Numbers (part of iWork) makes this simple (as we saw in this post) and somewhat fun. In this post, we'll take those easy calculations one step further and apply some conditional formatting.

Conditional formatting is just that... formatting of a specific cell (or range of cells) that varies depending on the condition of the data within that cell. Conditional formatting is used to visually enhance your spreadsheet experience (indicating whether something is good or bad). An example that's easy for everyone to grasp is a classroom environment. In this example, I have a series of students with four test scores. In addition to the test scores, I also have that student's average for the four scores to the right. Now that I have the averages in the form of a number, I want to visually see how the students performed in the class by using colors. I will do this by applying the following three conditional formatting rules:

  • Average score greater than 90% will be colored green
  • Average score between 80% and 90% will be colored yellow
  • Average score less than 80% will be colored red and the text will be bold

To apply conditional formatting, we first need to select the cell (or range of cells) where you want to apply the formatting. On the Inspector, click on the Cells button, then click on the "Show Rules..." button in the "Conditional Format" section (you will see how many rules currently apply to this cell or range of cells here). At this point, you will begin to build your conditional formatting rules. To add more than one rule to a cell or range of cells, click the [+] button to the right of the first rule.

Here is a video showing you how to create the above example:

At the end of the video, you noticed that I made a change to one of the test score values. I did this intentionally to illustrate that the conditional formatting is applied in real-time. In other words, as the values or conditions change, the formatting will also change.

Pro Tip: as you become more familiar with applying conditional formatting, couple this feature with the slider feature, and really watch the sparks fly!


Posted on August 13, 2014 and filed under How To, Mac.