Creating User Prompts and Help with Custom Formats

Excel - Formatting and Reports

Including text with numbers is a handy trick you can use to warn users of incorrect entries or to prompt them with a help message. Use the “text” part of custom formats to do this. (You can also do this with the Data Validation command, but using custom formats allows the option of displaying text in the cell with the results.) For example, if you want people to enter a Federal Tax ID or Social Security number in a cell you might format at it as,

“TIN “###-##-####;”Enter nine positive digits”;”Enter your tax ID”

If the user enters a positive nine digits, then it appears as

TIN 123-45-6790

If they enter a negative number, the message “Enter nine positive digits” appears. If a zero is entered as the default, then the prompt “Enter your tax ID” appears.

Share the power...