If you are creating a worksheet for someone else to fill out, why not provide dropdowns where the person can choose the valid values from a dropdown list? It is easy to do. It makes life easier for the person filling out the form. It makes sure the data that comes back to you is correct. Episode 378 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
So you have a great idea, but when you have to create a worksheet, they're trying to send out to someone else to fill out.
I used to have to send a worksheet out to a salesforce of 50 sales reps, and if I sent this to 50 sales reps, they would find at least eight different ways to spell the word “Northeast”.
That drove me crazy, when I got all those spreadsheets back, and I had to try and compile them to have all of the same values, basically spelled a different way.
And we can overcome that, plus make the spreadsheet easier for the person to use, using something called Data Validation.
And what it does is, it basically adds a drop-down right to the cell.
So if this is a cell where I want them to enter a district value, I can select that cell and then go to Data Validation.
Now it turns out that every single cell in Excel is set up with a validation rule that currently says “We're going to allow any value.” But you can change that, and say that the values are going to come from a list.
Once we have a list, a new box pops up where we can show this source.
Click in the source box, and then, off on the right-hand side of your spreadsheet where no one will see, have a list of the valid values, select those and then click OK.
Now, as soon as I go in and select that cell, it gives me a drop-down with all the valid values.
Hopefully the people that you're sending the spreadsheet to will take advantage of the drop-down and choose from a list.
Now if they're stubborn, and they decide to try and enter something on their own, like N.E.
Excel won't let them at enter that value.
Hey, there you have it.
You can use Data Validation to add a drop-down list right to the worksheet, to make the worksheets easier to use, and also ensure that the values that you get back are consistent from worksheet to worksheet.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
So you have a great idea, but when you have to create a worksheet, they're trying to send out to someone else to fill out.
I used to have to send a worksheet out to a salesforce of 50 sales reps, and if I sent this to 50 sales reps, they would find at least eight different ways to spell the word “Northeast”.
That drove me crazy, when I got all those spreadsheets back, and I had to try and compile them to have all of the same values, basically spelled a different way.
And we can overcome that, plus make the spreadsheet easier for the person to use, using something called Data Validation.
And what it does is, it basically adds a drop-down right to the cell.
So if this is a cell where I want them to enter a district value, I can select that cell and then go to Data Validation.
Now it turns out that every single cell in Excel is set up with a validation rule that currently says “We're going to allow any value.” But you can change that, and say that the values are going to come from a list.
Once we have a list, a new box pops up where we can show this source.
Click in the source box, and then, off on the right-hand side of your spreadsheet where no one will see, have a list of the valid values, select those and then click OK.
Now, as soon as I go in and select that cell, it gives me a drop-down with all the valid values.
Hopefully the people that you're sending the spreadsheet to will take advantage of the drop-down and choose from a list.
Now if they're stubborn, and they decide to try and enter something on their own, like N.E.
Excel won't let them at enter that value.
Hey, there you have it.
You can use Data Validation to add a drop-down list right to the worksheet, to make the worksheets easier to use, and also ensure that the values that you get back are consistent from worksheet to worksheet.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
