Today’s topic is input messages.
Many spreadsheets are made not for personal use, but for other people to use. Maybe your boss asked you to make a sheet for him/her to use, or maybe you are in charge of creating a spreadsheet for the whole office to use. Whatever the case, input messages can be very helpful in adding usability and “idiot-proofing” to your spreadsheets.
The case for idiot-proofing
This post is actually very closely related to the Dropdown Lists post from a few months ago. Like the dropdown lists, input messages are accessed via the “Data Validation” menu. Below you will find an example you can work.
The setup for the example is a matrix of monthly plant revenues. There is a box that allows the user to do a “quick lookup” of the revenues generated by a single plant in a single month. The only way the lookup works is if the user correctly enters a plant number (between 1-10) and correctly enters a month or the word “Total.” The example walks you through how to create an input message to alert the user to these things.
- Download the example spreadsheet here
- Navigate to cell D3
- Go to the Data menu. Then in the “Data Tools” section click on “Data Validation.” Keyboard shortcut: Alt→A→V→V. (For a Mac it is just called “Validate” instead of “Data Validation”)
- The data validation box should pop up. Go to the “Input Message” tab and click the box that says “Show input message when cell is selected”
- Where it says “Title” type in “Plant Number”
- For the actual input message type “Please insert a number between 1-10”
- Click OK
- Repeat the process for Cell D4 but change the message to say something along the lines of “Please type a month or the word ‘Total’” Examples are shown below.
One last tip about input messages is that they can be moved. If you click and then drag an input message to another area in the spreadsheet every input message will pop up in that location until you close the file, then they will revert to showing up directly next to the cell you select.
Loading ...