|
Home > Format > Conditional > Hide Questions
Hide or Show Excel Questions Automatically
|

|
If you're building a long Excel data entry form, hide some questions, until they're needed. Based on the first answer, show follow-up questions, or leave them hidden. This can make it easier for people to fill in the answers.
|
|
1) Hide Follow-Up Questions
In this short video, I show a data entry form, where only one question is showing. But there's also a hidden follow-up question, and it might appear, based on your answer.

Watch the video to see how that works, and there are written steps below the video.
2) Example: Hide Follow-Up Questions
For one of my clients, an insurance company, I built Excel data entry forms for insurance policy applications.
- When customers needed commercial property insurance, insurance agents filled in those Excel questionnaires, with the customer's details.
- There were lots of questions in each application form!
So, to make things easier for the agents and customers, I hid some questions:
- For multi-part questions, only the main questions were visible.
- The follow-up items were hidden at first - they appeared when needed
2.1) Main Questions Always Visible
To show you how these hidden questions work, I've built a sample data entry workbook, with two main questions, and their follow-up questions.

In this sample data entry form, the two main questions are always visible:
- In cell B2, the first question is, "Do you have any dependents?"
- In cell E2, there's a data validation drop-down list -- Yes or No
- In cell B6, the second question is, "What is your age?"
- In cell E6, there's a green box, where you can type your age
2.2) Follow-Up Questions - Show or Hide
Depending on your answers to the main questions, the follow-up questions are shown, or they stay hidden.
2.2.1) Question 1
For the "dependents" question in my sample data entry form, after you choose Yes or No, here's what happens:
- If you select No, nothing happens. The follow-up question remains hidden.

- If you select Yes, there are two changes on the sheet:
- In cell B4, a follow-up question appears -- List names and ages
- In cell E4, a green box appears, where you can type your answer

2.2.2) Question 2
For the "age" question, after you type your age in cell E6, here's what happens:
- If you type an invalid number, the follow-up question stays hidden.
- If you type a valid number:
- In cell B8, a follow-up question appears, based on your age group
- In cell E8, a green box appears, where you can choose Yes or No from the drop down list

3) Steps: Hide Follow-Up Questions
In the sections below, I show the steps for hiding some questions. If you'd like to follow along, download my sample data entry form, and go to the Start sheet.
- The Start sheet has a simple two-question data entry form
- All the question and answer cells are visible, all the time

After you complete the steps in the sections below:
- Show: The follow-up questions will only show when a specific answer is given to the main question.
- Hide: The rest of the time, the follow-up questions will be hidden.
3.1) Change Cell Formatting
The first step is to hide the follow-up questions, by formatting those cells, so they "blend in" on the worksheet.
- First, in my sample data entry form, go to the Start sheet.
- All the questions and answer cells are visible, all the time
- Select the question cells and answer cells, for the follow-up questions:
- On the Start sheet, select cells B4, E4, B8 and E8
- On the Excel Ribbon, go to the Home tab
- For font colour, and for fill colour, choose White
- From the Borders drop-down list, select "No Borders"
After this change, the follow-up cells will be "hidden", no matter what answer is given for the main question

4) Steps: Show Follow-Up Questions
Next, follow the steps below, to add conditional formatting to the "hidden" cells, for Question 1, and for Question 2.
This will make the follow-up questions visible, when needed.
4.1) Question 1 Follow-up
For Question 1, you'll add 2 conditional formatting rules. These rules will check cell E2, to see if it is "Yes"
A) To show the follow-up question text, follow the steps below:
- Select cell B4, and on the Ribbon, click the Home tab
- Click Conditional Formatting, and click New Rule
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- In the box for Format values where this formula is true:, enter:=E2="Yes"
- Note: The text match is not case-sensitive, in this formula
- Click the Format button
- On the Font tab, select Automatic as the colour
- Important: Do this even if Automatic is already selected
- Click OK twice, to close the dialog boxes.

B) To show the follow-up answer cell, follow the steps below:
- Select cell E4, and on the Ribbon, click the Home tab
- Note: To allow for a long text entry, E4:H4 are merged cells
- Click Conditional Formatting, and click New Rule
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- In the box for Format values where this formula is true:, enter: =$E$2="Yes"
- Note: The text match is not case-sensitive, in this formula
- I used an absolute reference ($E$2), because E4:H4 are merged cells
- Click the Format button
- Select the outline border, light green fill and Automatic as the font colour
- Important: Choose Automatic, even if Automatic is already selected
- Click OK twice, to close the dialog boxes.
The text box now appears if Yes is selected.
4.2) Question 2 Follow-up
For Question 2, you'll add 2 conditional formatting rules. These rules will check cell E6, to see if an age number has been entered
A) To show the follow-up question text, follow the steps below:
- Select cell B8, and on the Ribbon, click the Home tab
- Click Conditional Formatting, and click New Rule
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- In the box for Format values where this formula is true:, enter: =$E6<>0
- Note: The text match is not case-sensitive, in this formula
- Click the Format button
- On the Font tab, select Automatic as the colour
- Important: Do this even if Automatic is already selected
- Click OK twice, to close the dialog boxes.
B) To show the follow-up answer cell, follow the steps below:
- Select cell E8, and on the Ribbon, click the Home tab
- Click Conditional Formatting, and click New Rule
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
- In the box for Format values where this formula is true:, enter: =$E6<>0
- Note: The text match is not case-sensitive, in this formula
- Click the Format button
- Select the outline border, light green fill and Automatic as the font colour
- Important: Choose Automatic, even if Automatic is already selected
- Click OK twice, to close the dialog boxes.
5) Test the Hidden Questions
To test the hidden questions:
- In cell E2, select Yes from the drop down list
- The follow-up question and answer cell should appear in row 4.
- In cell E6, type a number between 1 and 120
- The follow-up question and answer cell should appear in row 4.
6) Age Question Lookup Table
In cell B8, the follow-up question depends on the age that's entered in cell E6.
6.1) Lookup Table
On the AdminLists sheet, there's a 2-column lookup table, named AgeQs.
- The minimum age for each question is in column D
- There is a question for each age group in column E
For example, anyone aged 40 to 59 would see the question:
- "Are you saving for retirement?"

6.2) Lookup Formula
On the data entry form, in cell B8, there's a lookup formula:
=IFERROR(VLOOKUP(E6,AgeQs,2,TRUE),"")
- The VLOOKUP function gets the age from cell E6
- In the AgeQs table, it looks in the left-most column, to find the closest age (exact age or below)
- Then, it returns the question from that row, in the AgeQs table, column 2

7) Get the Excel File ✅
Get Monthly Excel Tips!
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
8) Video Transcript
This is the full transcript of the Hide Follow-Up Questions video, shown above.
'--------------------------------
I'm creating a workbook that has a lot of questions for people to answer.
And sometimes, if you say yes or no, you have to fill in a subsequent question or two.
So here's a little example.
The first question is, do you have any dependents?
And if the person says yes, we would like them to fill in the names and ages of those dependents.
To keep the form streamlined and easy for people to follow, I set it up so that:
- if you select no, the subsequent question doesn't appear.
- If it's empty, no question.
- But if you select Yes, that second question appears.
And to do that I use conditional formatting.
So here's another sheet where I've just got my list set up, and that's just a little data validation list.
I've typed the question here, the subsequent question, and this is just a little merged cell where someone could type the names and ages.
So to create my conditional formatting:
- I'm going to change all of this to white font and white fill.
- I'll also get rid of the borders, so no border.
So right now, nothing is visible.
If we click on that cell, we can see the question in the formula bar.
To create the conditional formatting:
- On the Home tab of the ribbon, I'll click Conditional Formatting and New Rule.
- I'm going to use a formula and you can see that our Yes is in cell E2.
- So our formula will just check that cell.
- Equals E2 equals Yes.
- So if e two is yes, then we want that label to show up, so we'll change the font color to automatic.
And now you can just see the preview here.
- So now it's yes, so that shows up.
- If I change it to no, it disappears again.
The other item is this merged cell, so we'll set conditional formatting:
- a new rule, and again, we'll use a formula and equals
- I'm going to add a dollar in front of the e this time because it's a merged cell and it goes across a few columns.
- So E2 is equal to yes.
- And for this cell that they're going to fill in, we'll format it with an outline border and a light blue color just to show them they should fill that in.
Note: I forgot to format the font colour as Automatic!
So there's the blue box that they should fill in.
If they select no, nothing appears.
More Tutorials
Conditional Formatting Intro
Conditional Formatting Examples
Color Scales | Data Bars | Icon Sets
Fix Conditional Formatting Extra Rules
Conditional Formatting Multiple Cells
Conditional Formatting - Currency
Conditional Formatting Documentation