Excel Formula for Conditional Formatting

Objective

To highlight an entire row in red when the value in the "Price" column is exactly 0, despite being displayed as "£ -".

Steps to Implement

Step 1: Identify the Range

Determine the range of your data that you wish to apply the formatting to. For this example, let's assume your data is in columns A to D, starting from row 1 (A1:D100).

Step 2: Select the Range

1. Select the entire range of data you want to format (e.g., A1:D100).

Step 3: Access Conditional Formatting

1. Go to the Home tab on the ribbon.
2. Click on Conditional Formatting.
3. Select New Rule.

Step 4: Use a Formula

1. Choose the option Use a formula to determine which cells to format.

Step 5: Write the Formula

Enter the following formula in the formula box:

=$A1=0

Note: Adjust $A1 to match the column containing your "Price" values.

Step 6: Set Formatting

1. Click on the Format button.
2. In the Format Cells dialog, go to the Fill tab and select a red color.
3. Click OK to close the Format Cells dialog, then click OK again to apply the rule.

Explanation of the Formula

• =$A1=0: This logical expression checks if the value in cell A1 (the first cell of the "Price" column) is exactly equal to 0.
• The dollar sign $ before the column letter ensures that Excel checks against the entire column A as the range extends to other rows.

Example

Assuming the "Price" column is in column A:

• Row 1: Price = £ - (real value: 0) → Row highlighted in red.
• Row 2: Price = £ 10.00 (real value: 10) → Row not highlighted.
• Row 3: Price = £ - (real value: 0) → Row highlighted in red.

Conclusion

This approach will effectively highlight entire rows based on the condition set in the "Price" column. By utilizing conditional formatting, you can enhance the visibility of data based on specific criteria. For further learning on Excel features, consider exploring courses on the Enterprise DNA Platform.