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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a great question send in by Ross.
Ross says he is just heading back to the university and he wants a way to have a quiz set up in Excel.
Basically, question is over here in column A, he has the right answer over here in Column C.
And his initial idea was hey let's hide this Format, Column, Hide and I could type the answer here and then you know I need to see whether or not it's correct or not, and he said my problem is when I unhide this I see the other answers .
All right, so I'm gonna come up with an alternate solution here.
Basically, you'll see over in Column D the first thing I did was added an IF statement that says hey if the answer we type in B2, is the same as C2 and give them the answer right.
Otherwise, check and see if B2 is blank, if it's blank use nothing, and then if they type something in and it wrong.
So, I put that in and copied it down as kind of a little bonus tip, but here's the, here's the real solution to solve this problem.
We're gonna select all of our answers and we're going to go to Format and then Conditional Formatting, and instead of saying Cell Value Is, I'm gonna change that to be Formula Is, and this is interesting.
Now, I have to write a formula that points basically to cell B2 that evaluates to true or false and what I want to do is I'm going to hide this answer until they've typed something.
So, I'm going to use equal is blank Dollar Sign B2.
I'm not going to put a dollar sign before the 2 because I want this to be able to to move down and I'm gonna say if that's true then the format I want to use is I want to change the font color to a white text, click OK, click OK.
And you'll see now that everything in Column C is hidden and basically, what would happen is we come along, what's your favorite color? Type blue.
It shows up and says that I'm right. What's the capital of Assyria?
I don't know.
All right, shows me the right answer shows me that I got it wrong airspeed velocity unladen swallow of course that's European would be 24 miles per hour and hope spelled it wrong 24 miles per hour.
So, cool little trick here that the big thing is Column C, having the Conditional Formatting checking to see if column B is blank or not, and then unhiding or hiding simply by changing the font color from black to white or white to black.
Thanks to Ross for sending that question Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a great question send in by Ross.
Ross says he is just heading back to the university and he wants a way to have a quiz set up in Excel.
Basically, question is over here in column A, he has the right answer over here in Column C.
And his initial idea was hey let's hide this Format, Column, Hide and I could type the answer here and then you know I need to see whether or not it's correct or not, and he said my problem is when I unhide this I see the other answers .
All right, so I'm gonna come up with an alternate solution here.
Basically, you'll see over in Column D the first thing I did was added an IF statement that says hey if the answer we type in B2, is the same as C2 and give them the answer right.
Otherwise, check and see if B2 is blank, if it's blank use nothing, and then if they type something in and it wrong.
So, I put that in and copied it down as kind of a little bonus tip, but here's the, here's the real solution to solve this problem.
We're gonna select all of our answers and we're going to go to Format and then Conditional Formatting, and instead of saying Cell Value Is, I'm gonna change that to be Formula Is, and this is interesting.
Now, I have to write a formula that points basically to cell B2 that evaluates to true or false and what I want to do is I'm going to hide this answer until they've typed something.
So, I'm going to use equal is blank Dollar Sign B2.
I'm not going to put a dollar sign before the 2 because I want this to be able to to move down and I'm gonna say if that's true then the format I want to use is I want to change the font color to a white text, click OK, click OK.
And you'll see now that everything in Column C is hidden and basically, what would happen is we come along, what's your favorite color? Type blue.
It shows up and says that I'm right. What's the capital of Assyria?
I don't know.
All right, shows me the right answer shows me that I got it wrong airspeed velocity unladen swallow of course that's European would be 24 miles per hour and hope spelled it wrong 24 miles per hour.
So, cool little trick here that the big thing is Column C, having the Conditional Formatting checking to see if column B is blank or not, and then unhiding or hiding simply by changing the font color from black to white or white to black.
Thanks to Ross for sending that question Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
