People with bosses usually either want to do 1 of 2 things to him (or her, but for the purposes of this article, let’s stick with him): impress him or annoy him without being obviously annoying. Here’s how to pimp your Excel worksheet to either impress him or annoy him, whichever it is you feel like doing. Who knows, you may even be able to use it at work.
To activate Data Validation, go to the ‘Data’ menu on the Menu bar and select ‘Validation…’.
Basically, Data Validation allows you to set what you want the value in the selected cell to be. It could be a number, text, or a whole number. By default, it’s set to ‘Any Value’, allowing the user to input anything that they want in the cell. Here’s the breakdown of the options:
Whole Number / Decimal
Selecting the ‘Whole Number’ option restricts the user to only entering a full number into the cell and not letters or fractions like 1.244. If the user attempts to put in anything other than a full number, an error message will appear, thus annoying him to bits, as such.
Likewise, the ‘Decimal’ option restricts the user to only entering numbers into the cell. This gives the user more flexibility, as they can input both whole numbers and fractions. Anything else will result in an error message.
These options can only be set to allow a specific range of numbers, like ‘between’, ‘greater than’, ‘equal to’, etc. This is great if you’re one of those people who create quizzes on Excel in their free time, but if you want to allow any number, you could always just set it to a minimum of 0 and a maximum of 1,000,000,000.
List
The ‘List’ option is far more interesting and has the potential to totally impress your boss instead of annoy the hell out of him when he tries to enter something and Excel doesn’t let him. Activating the ‘List’ function creates a dropdown box where the values are made up of things that you specify. This has the potential to impress anyone with anything less than an intermediate level knowledge of Excel functions, i.e. anyone above the age of 40.
To input the list of what you want the dropdown box to say, click the ‘Source:’ box and select the cells you are using as a reference.
The drawback of this function is that the values that you specify have to be kept somewhere in the same worksheet. You can’t even create a separate worksheet for the data because it has to be in the same one.
For this option, there’s a box called ‘In-cell dropdown’. Un-checking that box will get rid of the dropdown list but still restrict the values entered into the cell to whatever you want it to be. If your box tries to put in something other than what you choose, an error message will appear and annoy the hell out of him.
Date / Time
The ‘Date’ and ‘Time’ options are exactly like ‘Whole Number’ and ‘Decimal’ in that the user can only enter dates in a mm/dd/yyyy format and times in 00:00 format. Anything else will create an error message or appear as a date or time.
Text Length
The ‘Text Length’ option allows you to set how long you want the entry to be. For example, if you’re asking your boss to fill in a report on your performance and you want him to write ‘Awesome’, you could set it to 8 characters and drop psychological hints by using the word ‘Awesome’ frequently around him. “Isn’t this pen awesome?” “How awesome was that Tiger Woods thing?”
Custom
Of course, a much easier way would be to set it so that he can only type ‘Awesome’ and nothing else. But seriously, you can customize the cell according to whatever formula you want using the ‘Custom’ option.
That’s that. On the next episode of Pimp Your Worksheet, we work on other visual effects, like creating scroll buttons. Who knows, we may even make it to pimping charts one day. Excel Pimp Out!
