Using Conditional Formatting in Numbers

Conditional formatting in spreadsheets helps highlight user data. All you need to do is set conditions around values in one or a group of cells. When the conditions are met, you can mark cells, format text, or both. This is to emphasize the data.

In this article, Mytour will guide you on how to use conditional formatting in Numbers on Mac.

Note: The conditional formatting feature in Numbers is called Conditional Highlighting.

Before adding rules for conditional formatting, here is a list of data types and corresponding conditions you can use in conditional formatting:

- Number: equal to, not equal to, greater than, greater than or equal to, less than, less than or equal to, between, and not between.

- Text: is, is not, begins with, ends with, contains, and does not contain.

- Date: yesterday, today, tomorrow, in this, in next, last, exact, before, after, between, on, before date, after date, and in range.

- Time Range: similar to the number options above.

- Empty Cell: empty or not empty.

Numbers are the most common type of data we use in spreadsheets. In this guide, we will use a product inventory spreadsheet as an example to set conditional formatting for numbers. The data includes numbers for prices, costs, and inventory, but we will focus on inventory.

Suppose to quickly find the number of product inventories for a specific product, let's say under 50 products, we use Numbers to highlight these cells in red. Follow the steps below:

Step 1: Select the cells in your spreadsheet. To select a group of cells, simply click on the first cell and then drag over the rest to select the entire column or row.

Step 2: Click on the Format button at the top right corner to open the sidebar if it's closed.

Step 3: Select Cell at the top of the sidebar.

Step 4: Click on Conditional Highlighting.

Step 5: Click on Add a Rule.

Step 6: Select Numbers and choose Less than.

Now, you can customize your rule on the sidebar to apply the formatting. Enter a number (in this example, 50) into your condition box, then select a format from the menu (in this example, Red fill). The qualifying values will be highlighted in red. Click on Done to complete the process.

In addition to numbers, text is also a widely used data type in spreadsheets. In this guide, Mytour uses a student grade sheet as an example. Suppose you want to highlight students who scored an F on a test. You can follow these steps:

Step 1: Select the cells in your spreadsheet. To select a group of cells, simply click on the first cell, then drag over the remaining cells to select the entire column or row.

Step 2: Click on the Format button at the top right corner to open the sidebar if it's closed.

Step 3: Select Cell at the top of the sidebar.

Step 4: Click on Conditional Highlighting.

Step 5: Click on Add a Rule.

Step 6: Select Text and then select Is.

Next, customize your rule in the sidebar. Enter the text (in this example, F) into the condition box (text is), then select your format from the menu (in this example, Yellow fill). The values that meet the condition will be marked in yellow. Click Done to complete the process.

Dates are also a common format in spreadsheets, primarily used in personal budgets or project management spreadsheets.

One of the ideal solutions for using conditional formatting for dates in Numbers is to mark overdue payment dates. In the example below, Mytour will create a rule to mark overdue payment dates with red text.

To set conditional formatting for dates in Numbers, follow these steps:

Step 1: Select the cells in your spreadsheet. To select a group of cells, simply click on the first cell and then drag over the rest to select the entire column or row.

Step 2: Click on the Format button at the top right corner to open the sidebar if it's closed.

Step 3: Select Cell at the top of the sidebar.

Step 4: Click on Conditional Highlighting.

Step 5: Click on Add a Rule.

Step 6: Select Date and then choose After the date.

To make this rule easier to set up, instead of entering a value in the condition box, such as a number or text, we will select the cells.

Click on the button inside the box where you'll enter the condition value, then select the cells containing dates. Next, click on the green checkmark. Now all overdue payment dates will be marked in red. Click on Done to finish.

Time intervals are not a common format type in spreadsheets, but if you are managing a project or tracking specific tasks, the time interval format may be useful to you.

In the example below, Mytour will use a simple project management table to track time intervals for tasks each week. Suppose to mark and track the days we spent 1 or more hours on a specific task in green, just follow the steps below:

Step 1: Select the cells in your spreadsheet. To select a group of cells, simply click on the first cell and then drag across the rest to select the entire column or row.

Step 2: Click on the Format button at the top right corner to open the sidebar if it's closed.

Step 3: Select Cell at the top of the sidebar.

Step 4: Click on Conditional Highlighting.

Step 5: Click on Add a Rule.

Step 6: Select Durations and then choose Greater than or equal to.

Next, customize your rule. Enter the duration (in this example, 2h) into the condition box (greater than or equal to) and choose a format from the menu (Green Fill). Click Done to finish.

Suppose you want to set conditional formatting rules for blank cells in spreadsheets such as grade sheets, inventory quantity tables, etc., to be marked with sky blue. Follow these steps:

Step 1: Select the cells in your spreadsheet. To select a group of cells, simply click on the first cell and then drag across the remaining cells to select the entire column or row.

Step 2: Click on the Format button at the top right corner to open the sidebar if it's closed.

Step 3: Choose Cell from the top of the sidebar.

Step 4: Click on Conditional Highlighting.

Step 5: Click on Add a Rule.

Step 6: Choose Blank then select Is blank.

Simply choose a formatting type from the menu as there is no value to add in the condition, then click Done to finish.

While basic formatting options like bolding text or coloring cells are sufficient for marking your spreadsheet, you can also create custom formatting for unique marking if desired.

To create custom formatting, first set up your conditional formatting rules by following the steps outlined above. When you get to the formatting selection from the menu, scroll down in the box, find and click on Custom Style.

Next, you can format text by making it bold, italic, underline, or strikethrough. Additionally, you can use different colors for text other than coloring cells.

This article has just guided you on how to use conditional formatting in Numbers on Mac. Hopefully, this article has provided you with some new useful information. If you have any questions or need further clarification, readers can leave their comments below the article. You can also refer to the article on how to convert NUMBERS files on Mac to Excel here.