Conditional formatting with formulas unlocks a powerful way to customize how your Excel spreadsheet appears based on specific conditions. Here's how you can leverage this functionality:

1. Select the Cell Range:

- Decide on the cells you want to format conditionally. This could be a single cell, a specific range, or an entire column/row.

2. Access Conditional Formatting:

- Navigate to the "Home" tab on the Excel ribbon.
- In the "Styles" group, click the dropdown arrow next to "Conditional Formatting."

3. Create a New Rule:

- Several pre-defined formatting options are available, but for formula-based formatting, choose "New Rule."

4. "Use a formula to determine which cells to format" option:

- In the "New Formatting Rule" window, under "Select a Rule Type," ensure the option "Use a formula to determine which cells to format" is selected.

5. Enter Your Formula:

- In the box labeled "Format values where this formula is true," enter the formula that defines your condition.
- The formula should evaluate to TRUE for cells that meet your formatting criteria.
- You can use various Excel functions and cell references within your formula. For example:
- `=A1>100` - Formats cells where the value in column A is greater than 100.
- `=B2="Completed"` - Formats cells where the value in B2 exactly matches the text "Completed" (including quotes).
- `=C3<=TODAY()` - Formats cells where the date in C3 is less than or equal to today's date.

6. Format Cells based on the Condition:

- Click the "Format" button to choose how you want to visually distinguish cells that meet your formula condition.
- You can change font color, fill color, borders, and more.

7. Click OK to Apply the Rule:

- Once you've defined your formula and formatting preferences, click "OK" to apply the conditional formatting rule to your selected cell range.

Examples of Formula-Based Conditional Formatting:

- Highlight cells with negative values: `=A1<0` (format with red font color)
- Emphasize cells exceeding a target value: `=B2>C2` (format with bold font and green fill)
- Identify duplicate entries: `=COUNTIF($A$1:$A$10,A1)>1` (format with yellow fill to warn about duplicates in column A)

By combining formulas with conditional formatting, you can create dynamic and informative spreadsheets that visually guide users and highlight important data points.