Before an error message whether customized or default can be displayed, you must restrict what goes into a cell in your worksheet, so that when it is violated, the error message will be displayed. You should also set a custom input message for the cell or column to tell he user the type of data or number of characters to enter in that cell. In this tutorial, I will explicitly explain step by step with screenshots, how you can set a custom error message, input message and error alert for a cell, group of cells or column in MS Excel.

Guides on how to set up a custom message for a cell in ms excel

For example, as an MS Excel spreadsheet manager, you might want to design a product stock system for one of your clients. The system will have a PRODUCT DESCRIPTION column in which the number of characters for the description of each product must not be more than 50 characters. You will need to add a custom error message for that column through the Data Validation feature in Microsoft Excel. You can also set up input message to tell the user the type of data he or she is required to enter in that cell or column.

This is part 4 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 3) at http://www.microsofttut.com/2017/04/how-to-create-dropdown-menu-list-for-a-worksheet.html, I explicitly explained how to create a drop down menu list in MS Excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series at http://www.microsofttut.com/p/microsoft.html.

To Set a Custom Error Message for a Column in your Excel Worksheet:

1. Prepare your worksheet like the one shown below.

The product stock system for the custom error message illustration

2. Highlight the entire column C by placing your cursor on the “C” label and clicking when you see the down pointing arrow.

3. Click the Data Validation drop down arrow located in the Data tools group under the Data tab. Then click Data Validation from the resulting dropdown menu option. This prompts the Data Validation dialogue box.

4. Under the Settings tab, click the Allow: drop down arrow and select Text Length from the resulting options. This activates the Source text box.

5. Select between from the Data drop down menu list.

6. Since the character should not be more than 50, type 0 in the Minimum text box and type 50 in the Maximum text box as shown below.

Specify the minimum and maximum allowed characters

You have restricted the number of characters that each cell under the PRODUCT DESCRIPTION column will accept. But if the specified number of characters are exceeded, MS Excel will pop up a default error message dialogue box that warns the user about wrong entry without telling the user how to correct the mistake.

So you need to set an input message that displays beside the selected cell and tells the user the type and number of characters accepted in that cell. But it is optional.
Then you need to customize the error message to advise the user on what to do.

To Set a Custom Input Message for a Cell or Column in Excel:

1. Highlight the desired cell or entire column and call up the Data Validation dialogue box using the above specified procedure.
Click http://www.microsofttut.com/2017/04/how-to-set-custom-error-input-message.html to study full tutorial with screen to screen images.