MULTI IN ONE: How to add an error message to an Excel Spreadsheet

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the previous part, you saw how to add drop down lists to your Excel spreadsheets. In this part, we'll display an error message for our users. If you haven't already done so, you need to do the previous tutorial first.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Data Validation - restricting what data can go in a cell</h3>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You can also restrict what goes in to a cell on your spreadsheet, and display an error message for your users. We'll do this with our Comments column. If users enter too much text, we'll let them know by displaying a suitable error box. Try the following:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Highlight the E column on your spreadsheet (the Comments column)</li>
<li>From the <b>Data Tools</b> panel, click <b>Data Validation</b> to bring up the dialogue box again</li>
<li>From the <b>Allow</b> list, select Text length:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Select Text Length" border="1" height="155" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText.gif" width="163" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you select Text Length from the list, you'll see three new areas appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="164" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText2.gif" width="271" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
What we're trying to do is to restrict the amount of text a user can input into any one cell on the Comments column. We'll restrict the text to between 0 and 25 characters.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The first of the new areas (Data) is exactly what we want - <b>Between</b>. For the minimum textbox, just type a 0 (zero) in there. For the maximum box, type 25. Your dialogue box should then look like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="164" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText3.gif" width="269" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To add an error message, click the<b> Error Alert</b> tab at the top of the Data Validation dialogue box:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Error Alert Tab" border="1" height="250" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText4.gif" width="386" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Make sure there is a tick in the box for "Show error alert after invalid data is entered".</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You have three different Styles to choose from for your error message. Click the drop down list to see them:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The error styles" border="1" height="125" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText5.gif" width="159" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the <b>Title</b> textbox, type some text for the title of your error message.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Type a Title for your error" border="1" height="149" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText6.gif" width="359" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now click inside the error message field and type some text for the main body of your error message. This will tell the user what he or she did wrong:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Type the error message that the user will see" border="1" height="147" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText7.gif" width="359" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click OK on the Data Validation dialogue box when you're done.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To test out your new error message, click inside any cell in your Comments Column. Type a message longer than 25 characters. Press the enter key on your keyboard and you should see your error message appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Our Error Message" height="114" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidTextError.gif" width="428" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
As you can see, the user is prompted to Retry or Cancel. But our title (Too many characters) is at the top, our Stop symbol is to the left, and our Error message is displaying nicely!</div>
<h3 style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Hiding Spreadsheet Data in Excel 2007 to 2013</h3>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The data that went in to our lists doesn't need to be on show for all to see. You can hide this text quite easily.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Highlight the columns with your data in it (F, G and H for us)</li>
<li>Click on the <b>Home</b> tab from the top of Excel</li>
<li>Locate the <b>Cells</b> panel</li>
<li>On the Cells panel, click on <b>Format</b>. You'll see the following menu:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Hide and Unhide in Excel 2007" height="438" src="http://www.homeandlearn.co.uk/excel2007/images/2HideCells.gif" width="258" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Move your mouse down to <b>Hide & Unhide</b> and you'll see a Sub Menu appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Hide Columns in Excel 2007" height="138" src="http://www.homeandlearn.co.uk/excel2007/images/2HideCells2.gif" width="145" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click on Hide Columns from the Sub menu. Excel will hide the columns you selected:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The columns have been hidden" border="1" height="261" src="http://www.homeandlearn.co.uk/excel2007/images/2HideCells3.gif" width="379" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the spreadsheet above, the columns F to H are no longer visible.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To get them back again, highlight the columns E and I. From the same sub menu, click <b>Unhide Columns</b>.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next part, we'll explore Web Integration in Excel.</div>
</div>

How to add an error message to an Excel Spreadsheet

In the previous part, you saw how to add drop down lists to your Excel spreadsheets. In this part, we'll display an error message for ...