Look Who it Was Random video

Excel - How to Set Up a Quiz in Excel using Conditional Formatting | Excel Tutorial - Episode 682

1,693 views · Published 19 February 2009 · 2:53 · Indexed 6 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Set Up a Quiz in Excel using Conditional Formatting.

Welcome back to the MrExcel netcast! In today's video, we have a great question sent in by Ross. He's heading back to university and wants to know how to set up a quiz in Excel. Ross's initial idea was to hide the answer column and type in the answer, but he ran into a problem when he un-hid the column and saw the other answers. So, I'm going to show you an alternate solution to this problem.

First, I added an IF statement in column D that checks if the answer in column B matches the correct answer in column C. If it does, it displays "Right". If not, it checks if column B is blank and displays nothing. And if the answer is wrong, it displays "Wrong". This is a bonus tip, but here's the real solution to Ross's problem.

We're going to use Conditional Formatting to hide the answers until the user types in their answer. Select all the answers, go to Format, and then Conditional Formatting. Instead of "Cell Value Is", change it to "Formula Is". Now, we need to write a formula that evaluates to true or false. We want to hide the answer until the user types something in, so we'll use the formula "=ISBLANK(B2)". Make sure to not use a dollar sign before the 2, so the formatting can be applied to all cells. Then, change the font color to white. Now, when the user types in their answer, the correct answer will appear.

Let's try it out! What's your favorite color? Type in "blue". The answer appears and says "Right". What's the capital of Assyria? I don't know. The correct answer appears and says "Wrong". And for a fun example, the airspeed velocity of an unladen swallow is 24 miles per hour. If you spelled it wrong, the correct answer will appear and say "Wrong". This is a cool little trick that Ross's question inspired me to share with you.

Thank you, Ross, for sending in your question and thank you for watching this netcast from MrExcel. Don't forget to like and subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Ross is heading back to university and was hoping to use Excel to quiz himself. In Episode 682, I will show you how to use conditional formatting to hide the correct answer until an answer is filled in.

Table of Contents:
(00:00) Introduction and question from Ross
(00:20) Initial solution and problem with hiding answers
(00:47) Alternate solution using IF statement
(01:10) Real solution using Conditional Formatting
(01:48) Demonstration of solution
(02:15) Explanation of key elements in solution
(02:30) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Answer validation in Excel
Blank cell check in Excel
Cell value comparison in Excel
Change font color in Excel
Conditional Formatting in Excel
Excel quiz setup
Formatting cells based on formula in Excel
Formula Is in Conditional Formatting
Hide and unhide columns in Excel
Hide columns in Excel
Quiz grading in Excel
Tips for Excel quizzes

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152217/

More from this channel