Excel is much more than just rows and columns: it is a dynamic tool for analyzing and presenting data clearly. One of the most powerful features that enhances its usefulness is Conditional Formatting. Especially when used Microsoft Office for Mac 2016, Conditional formatting helps Mac users visually interpret data trends, anomalous values, and key metrics in an instant.

Whether you’re managing sales reports, budgets, school grades, or inventory sheets, conditional formatting can quickly transform raw data into intuitive visualizations, highlighting what matters most at a glance. In this guide, we’ll guide you through the use of conditional formatting in Excel 2016 on macOS, From the basics to advanced techniques.

What is Conditional Formatting?

Conditional Formatting allows you to automatically modify the appearance of a cell (color, font, border, etc.) based on its value or a specific condition. This function helps you to:

  • Highlighting critical values (for example, high expenses, expired dates)
  • Show data trends with color scales
  • Identifying duplicates or errors
  • Make spreadsheets more readable and engaging

Accessing Conditional Formatting on macOS

To access Conditional Formatting in Excel 2016 for Mac:

  1. Open Excel from Microsoft Office for Mac 2016.
  2. Select the range of cells that you want to format.
  3. In the menu at the top, go to Home > Conditional Formatting.
  4. Choose from predefined rules or create customized ones.

1. Use the Default Conditional Formatting Rules

Excel 2016 offers several pre-set formatting rules for a quick application.

A. Rules for highlighting cells

These rules allow you to format cells that meet specific criteria:

  • Older than / Younger than
  • Included among
  • Equal to
  • Text that contains
  • Data that occurs
  • Duplicate values

Example: To highlight sales above $10,000:

  • Select your sales data
  • Go up Conditional Formatting > Rules to Highlight Cells > Higher than
  • Enter 10000 and choose a formatting style (for example, light red fill with dark red text)

B. Top/Bottom Rules

Useful for quickly finding:

  • The 10 highest values
  • The lowest 10%
  • Values above or below the average

C. Barre Data

Displays colored bars in cells relative to their values, great for visual comparisons in rows.

D. Color Scales

Apply gradient color schemes based on the values of the cells. For example, low values in red, high values in green.

E. Set of Icons

It adds symbols such as arrows, traffic lights or shapes to visually represent the values.

2. Creation of Custom Conditional Formatting Rules

Excel allows you to go beyond the predefined formats by creating custom formulas.

Steps:

  1. Select the cell or range of cells.
  2. Go up Conditional Formatting > New Rule.
  3. Choose the style Classic > “Use a formula to determine which cells to format.”.
  4. Enter your formula (for example, =A2>5000).
  5. Choose a custom format (font, color, border, etc.).

Practical example:

Highlight expired invoices
Formula: =TODAY()>B2
Assuming that B2 contains the expiration date, this rule highlights the expired invoices.

3. Apply Conditional Formatting to Entire Rows

To format an entire row based on a condition in a column:

  1. Select the entire data range (for example, A2:D100).
  2. Go up Conditional Formatting > New Rule.
  3. Use a formula like: =$C2=”Expired”
  4. Apply a format (for example, red fill).

This will highlight the rows where the status column (Column C) indicates “Expired”.

4. Management and Modification of the Rules

After adding more formatting rules, manage them via:
Home > Conditional Formatting > Manage Rules
From here, you can:

  • View all the rules applied to a sheet or a selection
  • Modify the logic or formatting of the rules
  • Change the priority of the rules
  • Eliminating rules that are no longer necessary

Always make sure that your rules are in the correct order: Excel processes them from top to bottom.

5. Remove Conditional Formatting

To delete the formats:

  • Select the interval
  • Go up Conditional Formatting > Delete Rules
  • Choose “Delete rules from selected cells” or “Delete rules from the entire sheet”

Useful when you want to start over from scratch or remove temporary highlights.

6. Good Practices for Conditional Formatting

  • Limit the number of rules – Too many rules can slow down performance and reduce readability.
  • Use gentle color schemes – Red lights or neon colors can be distracting; use fill colors and soft edges.
  • Document your rules – Use comments in the cells or a legend to explain the meaning of the colors.
  • Test the rules – Test it first on small datasets.
  • Avoid conflicting rules – A rule can overwrite another one if it is not ordered correctly.

7. Real Applications of Conditional Formatting

Business:

  • Highlight expired accounts
  • Report low stocks
  • Monitor KPI (green = achieved, red = not achieved)

Education:

  • Coloring the votes below the threshold of passing
  • Identifying the perfect presence
  • Highlighting the status of the assignment's delivery

Staff:

  • Budget monitoring (expenses > budget)
  • Training records (days missed)
  • Habit Tracker (daily completion)

Project Management:

  • Highlight activities beyond the expiration date
  • Monitor priority levels (High, Medium, Low)
  • Visually show the use of resources