Highlight Correct Answer with Conditional Formatting

Time to walk through another exciting Excel adventure 🙂 Today, we’re zooming in on an interesting question I recently received: “How can I highlight correct answers on an Excel worksheet for my granddaughter’s multiplication tables?”

Sounds like a marvelously practical application of Excel, doesn’t it? Well, let’s dive in and find out how we can make studying easier and more interactive.

Video

Exercises

We’ll walk through three exercises designed to walk through the procedure step-by-step.

Exercise 1: The Basics

Imagine you want to set up a series of multiplication problems for a student – let’s say 1×1, 2×1, and so on like this:

The student would then enter their answers in column C.

Now, wouldn’t it be wonderful if Excel could automatically indicate if the answers were correct by highlighting them? One option is to use the built-in conditional formatting rules.

For example, we could select C6 and apply a Conditional Formatting rule like Conditional Formatting > Highlight Cell Rules > Equal To and then enter 1:

Select Green and when the student enters the correct answer (1 in this case) bam:

And we could set up a separate conditional formatting rule like this for each individual answer cell.

#TheCatch

However, there’s a slight snag. This method can be incredibly time-consuming as we would have to manually set up a separate rule for each cell. We’re all about efficiency here at Excel University, so let’s streamline this process.

Exercise 2: Optimizing the Process

What’s the secret to making this process less tedious? First, we need to separate the components of the equation. So, instead of the problem being written in a single cell as above, we want to use multiple columns like this:

That way, our formulas can reference the individual components.

Now, the trick to setting up one conditional formatting rule that will work for the entire answers range is to use a Conditional Formatting Formula. A Conditional Formatting Formula is like an ordinary formula that we write into cells, except it is written in a Conditional Formatting dialog. For this to work, our formula will return TRUE when the answer is correct, and FALSE when it is incorrect. When the result is TRUE, our desired conditional format will be applied. When the result is FALSE, the formatting will not be applied.

If we were to write the formula in a cell first, just to get it working, we would write it in column G like this:

=F6 = (B6 * D6)

This formula compares the answer entered by the student in F6 to the computed answer (B6*D6).

And when the answer in F6 is correct, it returns TRUE:

And when the answer in F6 is incorrect, it returns FALSE:

Once we have the basic formula working, we can either type it or copy/paste for our Conditional Formatting Formula. To apply our Conditional Formatting rule to the entire answers range, we select the entire answers range F6:F15 and go to Home > Conditional Formatting > New Rule.

In the resulting New Formatting Rule dialog, we pick Use a formula to determine which cells to format, type or paste our formula, and pick our desired Format:

Note: if you are applying this technique in your own workbook, be sure to double-check that the cell references point to the desired cells and are of the correct type (absolute/relative/mixed).

Now, the student can enter their answers. Correct answers will be formatted in green:

Now, what if we wanted to incorporate other types of equations, such as addition and subtraction. Well, that leads us to our next exercise.

Exercise 3: Expanding to Other Math Operations

Now that we’ve mastered multiplication tables, we can apply this same process to a wider range of arithmetic problems involving addition, subtraction, and division. For example, we’d like our student to complete these math problems and enter the answers in F:

This is where we’ll make use of the robust SWITCH function. With this trick up our sleeves, we can return correct answers regardless of whether the operations involve addition, subtraction, multiplication, or division.

SWITCH

The SWITCH function in Excel evaluates an expression and compares it to a list of values in order. When it finds an exact match, it returns the corresponding result associated with that value. If there is no match, it can return an optional default value.

Here is the syntax for the SWITCH function:

SWITCH(expression, value1, result1, [value2, result2], ..., [default])

• expression: The value or expression that SWITCH will compare against the list of values.
• value1, value2, …: The values that SWITCH will compare with the expression.
• result1, result2, …: The results that SWITCH returns if expression matches the corresponding value1, value2, … etc.
• default: [Optional] The value that SWITCH will return if there is no match found. If default is omitted and no match is found, #N/A error is returned.

With this basic understanding of SWITCH, let’s apply it to our situation.

Determine Correct Answer

So, in our case, our expression will be the operator in our math problem. If it is a multiplication operator, we’ll multiple the values to determine the correct answer. If it is an addition operator, we’ll add the values to determine the correct answer, and so on. The following formula will work to determine the correct answer:

=SWITCH(C6, "x",B6*D6, "+",B6+D6, "-",B6-D6, "/",B6/D6)

So, when the operator in C6 is “x” the function returns B6*D6. When the operator is “+” the function returns B6+D6. And so on. This way, regardless of the operator, the SWITCH function will determine the correct answer.

Comparison Formula

But, this formula simply computes the correct answer. Recall we need our conditional formatting formula to compare the correct answer to the student answer and return TRUE/FALSE accordingly. All we need to do tweak it by comparing the results of the SWITCH function to the answer, like this:

=SWITCH(C6,"x",B6*D6,"+",B6+D6,"-",B6-D6,"/",B6/D6)=F6

This function will return TRUE when the answer in F6 is equal to the result of the SWITCH function. With this basic function working, we can now use it in a Conditional Formatting Formula.

Conditional Formatting Formula

To set up the Conditional Formatting rule, Home > Conditional Formatting > New Rule. In the resulting New Formatting Rule dialog, we pick Use a formula to determine which cells to format, type or paste our formula, and pick our desired format.

Then as the student enters answers, conditional formatting provides immediate feedback:

And with this technique, you can create one conditional formatting rule so that your granddaughter receives immediate feedback when entering answers 🙂

Conclusion

There you have it! An efficient way to turn an Excel worksheet into a fantastic learning aid. This technique uses a Conditional Formatting Formula that returns TRUE/FALSE to highlight the correct answers. If you have any suggestions, improvements, or questions, please post a comment below!

Sample File

FAQ

Q: What is the purpose of using conditional formatting in Excel for multiplication tables?

A: Conditional formatting in Excel can be used to automatically highlight correct answers in multiplication tables. This feature provides immediate visual feedback, making it a useful tool for educational purposes like practicing multiplication.

Q: How do I set up basic conditional formatting for individual cells in Excel?

A: To set up basic conditional formatting, select the cell (e.g., C6), go to ‘Conditional Formatting > Highlight Cell Rules > Equal To’, and then enter the correct answer (e.g., 1). This will highlight the cell when the entered answer is correct.

Q: Why can setting up individual conditional formatting rules be time-consuming?

A: Setting up a separate conditional formatting rule for each answer cell can be time-consuming because it requires manually creating a rule for each cell, which is not efficient for larger data sets or more complex tables.

Q: What is a Conditional Formatting Formula and how is it used?

A: A Conditional Formatting Formula in Excel is similar to a regular formula but is used within the Conditional Formatting dialog. It returns TRUE when a condition (like a correct answer) is met, and FALSE otherwise. This formula is used to apply formatting rules based on dynamic conditions.

Q: How can I optimize the conditional formatting process for a range of cells?

A: To optimize the process, separate the components of the multiplication equation into different columns, and then use a Conditional Formatting Formula that applies to the entire range of answer cells. This allows one rule to dynamically evaluate each cell in the range.

Q: Can I use conditional formatting for different types of math operations like addition and subtraction?

A: Yes, you can expand the conditional formatting to other operations like addition, subtraction, and division using the SWITCH function in Excel. This function evaluates an operation and returns the correct result, which can be compared to the student’s answer.

Q: What is the SWITCH function in Excel, and how is it used in this context?

A: The SWITCH function evaluates an expression and matches it against a list of values, returning corresponding results. In the context of this tutorial, it’s used to determine the correct answer based on the math operation (e.g., multiplication, addition) indicated in a cell.

Q: How do I apply the SWITCH function in conditional formatting?

A: To apply the SWITCH function, create a formula that uses SWITCH to calculate the correct answer based on the operation. Then, compare this result to the student’s answer. Use this formula in the Conditional Formatting dialog to apply the rule to the desired range.