Create Secret Hidden Questions in Excel

Everyone loves a good mystery! And now you can create a few in your Excel workbooks. I’ve been updating some Excel files that are used for data entry. Some tabs have a long series of questions, and some questions have two or more subsequent questions.

To make it easier for users to work with the file, I use conditional formatting to hide the subsequent questions, and show them only if the applicable answer is selected in a drop down list.

Select Yes or No

For example, in this drop down a user selects Yes or No, in answer to the question, “Do you have any dependents?”

If Yes is selected, the subsequent heading and data entry box appear.

Add Conditional Formatting

The first step in hiding the subsequent question is to format those cells with white font and fill, and no border.

Now they’re not visible, not matter which option is selected in the drop down list.

To format the label:

1. Select cell B4, and on the Ribbon, click the Home tab
2. Click Conditional Formatting, and click New Rule
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. In the box for Format values where this formula is true:, enter: =E2=”Yes”
   
5. Click the Format button
6. On the Font tab, select Automatic as the colour
7. Click OK twice, to close the dialog boxes.

The label now appears if Yes is selected.

To format the text box:

1. Select cell E4, and on the Ribbon, click the Home tab
2. Click Conditional Formatting, and click New Rule
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. In the box for Format values where this formula is true:, enter: =$E2=”Yes”
   
5. Click the Format button
6. Select the border, fill and font that you want for the box.
7. Click OK twice, to close the dialog boxes.

The text box now appears if Yes is selected.

Watch the Steps in the Conditional Formatting Video

To see the steps, you can watch this short video.

_________________

More Conditional Formatting

For more examples, go to the Conditional Formatting Examples page on my Contextures website.

Create secret hidden questions in Excel