How to create custom Input & Error Messages In Excel - gambillweating

In Excel, custom input or error messages explain the rules for for each one field to forbid data-entry errors. Think, for instance, that information technology's your job to create, maintain, and assign exploiter IDs and passwords for your companionship employees. You create a spreadsheet to execute this task. So you find transferred. Indeed now you have to delegate this task to someone else. The best way to insure the job is through with right is to create input and misplay messages.

For the User IDs, first create an Computer error Alert that says, "No duplicates allowed." Then create an Input Message that, when displayed, explains what kinda data can be entered. And last, a backup Error Message for tower E that says: "Only 7 digits allowed."

Create an Error Alert in Excel

1. Enter the next column headers connected columns A through G: Gross revenue Staff, User IDs, Passwords, Valid Dates, User IDs, Passwords, Valid Dates (again).

2. Set the width of column A to 24. Enter a dozen drug user names in column A (from A2 through A13 is decent).

3. Change the newspaper column widths of B through G to 12: Home > Data formatting > Column Width > 12, then click Okay.

4. Highlight B2:B13.

5. From the DATA yellow journalism, dog theData Validation button, then select Information Validation from the drop-pour down leaning.

6. When the Data Validation panel appears, select the Settings tab, then click the diminutive arrow beside the Allow field loge, and choose Custom from the drop-down list.

7. When the Recipe flying field box appears, insert the shadowing formula: =COUNTIF($B:$B,B2)=1.

8. Click the Vogue field box arrow and choose Stop, Warning, or Information for your Error Subject matter icon.

9. Enter a Style in the Title field box (something that describes what your message is about).

10. Adjacent, select the Error Watchful tab, and enter a custom message in the Error Message field box: No matching Drug user IDs allowed. Detent Hunky-dory.

11. Next, enter some random User ID numbers in chromatography column A. Repeat combined of those numbers and line what happens: The Error Message boxwood appears and displays your message.

01 create a custom user input error message based on a formula — Create a custom, drug user input Error Substance based along a formula.

Input Message (with Error Message backup)

1. Highlight E2:E13.

2. From the Information tab, select Data Validation> Information Validation.

3. When the Data Proof duologue corner appears, select the Input Message tab.

4. Enter a Title in the Title field box (something that describes what your message is about): User IDs

5. And then enter the substance abuser content in the Stimulant Message loge: Enter a 7-finger's breadth, numeric Exploiter ID.

Then click OK.

6. Poster that when you click anyplace in column E, the Input Message pops au courant the block out with instructions regarding this field.

7. You can also add an Error Message in case the users enter more Oregon fewer than seven digits.

8. Highlight column E, then pick out the DATA tab> Data Validation> Data Proof and choose the Settings tab.

9. Click the petite arrow beside the Allow field box, and select Text Length from the drop-knock down list.

10. Insert Equal To in the Data field box, then go in 7 in the Length flying field boxful. Sink in OK.

11. Next, choice the Error Alert tab, choose the Style field box and select Stop, Admonitory, or Info for your Erroneous belief Subject matter icon.

12. Enter a Title in the Title field loge: User IDs

13. And last, enter a custom message in the Error Message field boxwood: Solely 7 digits allowed. Cluck OK.

14. Enter some random, seven-fingerbreadth User ID numbers in chromatography column E. Go in one with more or fewer than seven numbers and note what happens: The Error Content box appears and displays your message: Only 7 digits allowed.

02 create a custom input message with an error alert backup — Create a custom-made Stimulus Message with an Error Alert backup.

Create an Misplay Alert for passwords

For the Passwords, first create an Erroneous belief Alert that says: No duplicates allowed. Then create an Stimulation Message that, when displayed, explains what benign of data fire beryllium entered. And last, a backup Error Message for column E that says: Only 7 digits allowed.

Error Subject matter

1. Highlight C2:C13.

2. From the DATA tab, select Data Establishment> Data Validation.

3. When the Data Validation dialog box appears, select the Settings tab, past click the tiny arrow beside the Allow field box, and select Custom from the drop-down list.

4. When the Convention airfield box appears, enter the following: =Mathematical(C2,LOWER(C2)). Click OK.

5. Following, select the Error Alert tab, and in the Flair field box, choose Stop consonant, Warning, or Information for your Error Subject matter icon.

6. Enter a Title in the Rubric field boxful: Small letter.

7. So, record a custom message in the Misplay Subject matter field box: Only lowercase allowed for all passwords. Then chatter OK.

8. Next, enrol some unselected passwords in column C. Hear to enter an uppercase letter and so take down what happens: The Mistake Alert box seat appears and displays your error message: Only small allowed for all passwords.

03 create an error message based on the lowercase formula — Create an Error Message supported the lowercase formula.

Input Message

1. Play up F2:F13.

2. From the Information tab, select Information Validation> Data Validation> Input Message pill.

3. Put down a Claim in the Title field box: Passwords

4. Recruit the user content in the Input Message box: Enter a small letter password between 8 and 10 characters. Click OK.

5. Click anywhere in column F, and posting the Input Subject matter pops dormie on the screen with instructions regarding this field.

04 create a custom input message for passwords — Make over a customised Input Message for passwords.

Error Message

1. Highlight D2:D13.

2. From the DATA tab, select Data Validation> Data Validation> Settings tab, and then select Date from the drop-down in the Allow theater of operations box.

3. In the Data field box, select Between.

4. Enroll 7/1/17 in the Start Go steady field box and 9/30/17 in the End Date field of operations box. Click OK.

5. Next, select the Error Alert tablet, and in the Vogue subject field loge, opt Stop, Warning, operating theater Information for your Error Message icon.

6. Enter a Title in the Championship field box: Date.

7. Then, enter a custom message in the Error Subject matter field box: Must enter a valid 3rd quarter date: between 7/1/17 and 9/30/17. Then click Satisfactory.

8. Next, enter some random Dates in column D that do not fit these parameters and mark what happens: The Wrongdoing Alert message box appears and displays your error message: Must enter a valid 3rd quarter appointment: between 7/1/17 and 9/30/17.

05 create an error message based on a range of dates — Produce an Misplay Message based along a range of dates.

Input Message

1. Spotlight G2:G13.

2. From the Information tab, prize Data Validation> Data Validation> Input Message.

3. Enter a Form of address in the Championship field boxful: Valid Dates.

4. Embark the exploiter message in the Input Message box: Enter a valid 3rd quarter escort: betwixt 7/1/17 and 9/30/17. Then tick OK.

5. Click anywhere in editorial G, and notice the Input signal Message pops au fait the screen with book of instructions regarding this champaign.

06 create a custom input message for valid dates — Create a custom Stimulant Message for valid dates.

Note that there are eight Allow fields, eight Data sensible operators, and hundreds of functions and formulas that you tooshie use to customise your spreadsheets for user restrictions and data stimulant. Play about with information technology and come across what happens. Luckiness!