Скачать или смотреть Mastering Conditional Formatting in Excel with Multiple Conditions

Discover how to apply `conditional formatting` based on multiple conditions in Excel, specifically for TICKER and STS values across different worksheets.
---
This video is based on the question https://stackoverflow.com/q/66734322/ asked by the user 'Ryan Bennett' ( https://stackoverflow.com/u/15440112/ ) and on the answer https://stackoverflow.com/a/66734921/ provided by the user 'Ron Rosenfeld' ( https://stackoverflow.com/u/2872922/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: conditional formatting with multiple conditions

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering Conditional Formatting in Excel with Multiple Conditions

Conditional formatting can be a powerful tool in Excel when you want to visually highlight important data. However, it can be tricky to set up—especially when you need to consider multiple conditions across different worksheets. In this post, we'll investigate a common issue faced by many Excel users and how to resolve it effectively.

The Problem: Conditional Formatting with Multiple Conditions

Imagine you have two worksheets:

Worksheet 1 contains columns labeled STS and TICKER.

Worksheet 2 has a column labeled TICKER that you're trying to format based on specific criteria from Worksheet 1.

You want to apply conditional formatting to Worksheet 2 so that any TICKER that appears in Worksheet 1 with an STS value of Y is highlighted. Here are the conditions you are working with:

If the TICKER is found in Worksheet 1 and its corresponding STS is Y, the formatting should apply.

If the TICKER is present but with an STS of N, the formatting should not apply.

If the TICKER is not found in Worksheet 1 at all, no formatting should apply.

You initially attempted to use a COUNTIF formula, but this didn't effectively account for the STS condition. Instead, you'll want to use a COUNTIFS function that allows for multiple criteria.

The Solution: Using the COUNTIFS Function

Step-by-Step Guide

Open Excel: Begin by opening your Excel workbook containing the two worksheets.

Select Worksheet 2: Navigate to Worksheet 2 where you want to apply the conditional formatting.

Conditional Formatting Setup:

Go to the Home tab on the Ribbon.

Click on Conditional Formatting in the Styles group.

Choose New Rule from the dropdown.

Use a Formula to Determine Which Cells to Format:

In the New Formatting Rule dialog, select the option for Use a formula to determine which cells to format.

Enter the COUNTIFS Formula:

Here’s the formula you'll want to enter into the provided field:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Formula:

Sheet1!$B:$B references the TICKER column in Worksheet 1.

A2 refers to the TICKER in Worksheet 2 that you want to evaluate.

Sheet1!$A:$A references the STS column in Worksheet 1, and "Y" specifies the condition for marking as valid.

Format Your Cells: Choose the formatting style (like font color, fill color etc.) that you wish to apply when the conditions are met.

Apply and Review: After setting the formatting, click OK and observe your sheet. The cells in Worksheet 2 should now be highlighted based on the criteria set in Worksheet 1.

Important Notes

The COUNTIFS function allows you to count the number of entries meeting all specified criteria. It is ideal for your scenario where you need to evaluate both TICKER and STS values.

Remember, in Excel, FALSE is equivalent to 0, so there's no need for explicit comparison in the formula.

Conclusion

Conditional formatting, when utilized correctly, can significantly enhance your data analysis and presentation. By using the COUNTIFS function, you can easily apply rules based on multiple conditions spread across different worksheets. Now, whenever a TICKER from Worksheet 1 has an STS of Y, it will stand out in Worksheet 2, helping you draw insights effortlessly.

With practice, such techniques will become second nature. Happy Excelling!