Here's how to create custom formatting rules based on formulas in Excel:

1. Select the cells: Choose the cells you want to apply the conditional formatting rule to. You can select a single cell, a range of cells, or an entire table.

2. Go to Conditional Formatting: Navigate to the "Home" tab on the Excel ribbon. In the "Styles" group, click the dropdown arrow under "Conditional Formatting."

3. Select "New Rule": From the dropdown menu, choose "New Rule" to open the "New Formatting Rule" window.

4. "Use a formula to determine which cells to format": Under "Select a Rule Type," choose the option "Use a formula to determine which cells to format." This allows you to define a custom condition based on a formula.

5. Enter your formula: In the box labeled "Format values where this formula is true," enter the formula that will determine which cells get formatted. You can use various functions and cell references to create your custom logic.

- Examples:
- Highlight cells greater than 100: `=A1>100` (assuming your data is in column A)
- Format cells containing text "complete": `=A1="complete"`
- Change color based on positive/negative values: `=A1>0` (format for positive) and `=A1<0` (format for negative) - you can create separate rules for each condition.

6. Format the cells: Click the "Format" button to choose how you want to format the cells that meet your formula condition. You can change font styles, colors, borders, fill patterns, and more.

7. Click OK: Once you've defined your formula and chosen the formatting options, click "OK" to apply the conditional formatting rule to your selected cells.

Tips:

* You can create multiple conditional formatting rules for the same cells. Each rule will be evaluated sequentially, and the first rule that evaluates to TRUE will determine the formatting applied.
* Use relative cell references in your formulas so the rule adjusts correctly when copied to other cells.
* Experiment with different formatting options to create clear and visually appealing data presentations.

By using conditional formatting with formulas, you can automatically highlight important data points, identify trends, and improve the readability of your Excel spreadsheets.