Скачать или смотреть Comparing Results with Conditional Formatting in Excel

Learn how to effectively use Excel's `conditional formatting` to compare predictive and actual data sets, highlighting performance differences.
---
This video is based on the question https://stackoverflow.com/q/63365771/ asked by the user 'Imran' ( https://stackoverflow.com/u/7114244/ ) and on the answer https://stackoverflow.com/a/63366321/ provided by the user 'teylyn' ( https://stackoverflow.com/u/447970/ ) 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: Excel Conditonal Format between two data sets

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.
---
Comparing Results with Conditional Formatting in Excel

In the world of education, tracking student performance is vital. Many teachers use Excel to compare predictive data against actual results, helping them identify students who are excelling or those who may need additional support. This guide addresses a common challenge: how to apply conditional formatting between two datasets in Excel that represent predictive and actual performance data.

The Problem: Comparing Two Data Sets

You have two data sets on separate Excel sheets:

Predictions: These are the anticipated scores for pupils in different subjects.

Results: These represent the actual scores obtained by the pupils in the same subjects.

Both datasets have the same structure, for example:

Pupil NameEnglishMathematicsScienceJohn Doe789Peter John899The goal is to visually compare the results using conditional formatting:

If a pupil's actual result is higher than the predictive data, the cell should turn green.

If it's lower, the cell should turn red.

If the results are the same, there should be no color change.

The Solution: Using INDEX and MATCH Functions

To compare the two datasets effectively, you can use Excel's INDEX and MATCH functions together. Here’s a breakdown of how to set this up:

Step 1: Set Up Your Sheets

Ensure your predictive scores are listed on one sheet (named "Predictions") and the actual results on another sheet (named "Results").

The two sheets should have the same structure for smooth comparisons.

Step 2: Use INDEX and MATCH to Retrieve Predictions

To pull the predicted score for a pupil based on their name and subject, you can use the following formula.

Formula Example:

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

$A2 is the pupil's name.

Predictions!$B$2:$D$5 is the range of predicted scores.

Adjust the ranges based on your dataset.

Step 3: Set Up Conditional Formatting Rules

Now that we can retrieve the predicted score, we need to create conditional formatting rules based on the results.

Select the Range: Click on any result cell, starting from B2.

Create an ‘If Greater’ Rule:

Go to Home > Conditional Formatting > New Rule.

Select Use a formula to determine which cells to format.

Enter the formula:

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

Set the format to green.

Create a ‘If Less’ Rule:

Repeat the process for the second rule, using:

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

Set the format to red.

Important Notes on Cell References

The $ signs in your formulas are crucial. They anchor specific references to prevent them from changing as the formula is applied to other cells.

If you’re using an older version of Excel that doesn’t support off-sheet conditional formatting, you’ll need to copy the predictions to the same sheet as the results.

Conclusion

By utilizing conditional formatting in Excel with the INDEX and MATCH functions, you can effortlessly visualize performance differences between predicted and actual student results. This not only saves time but also enhances your ability to support your students effectively. Happy Excel-ing!