February 16, 2017
Hi fellow Excel Addict,
Well after two days of snow shoveling I've just about had my fill of snow for this winter. Actually I didn't really shovel the snow...I used my snowblower. But it was still a lot of work.
On Tuesday we had a big snowstorm, maybe 25-30cm (10-12 in). For anyone who isn't familiar with snow storms (lucky you), 10-12 inches of snow may not seem like much but when it gets blown around by 100km winds, it creates huge snowdrifts. One snowdrift in my driveway was up to my shoulder.
So I went out that evening after the storm had subsided and spend a couple of hours clearing out my driveway.
The next morning (Wednesday) we awoke to yet another big snowstorm. By that afternoon when the snow stopped, another 30-35 cm had fallen.
After another couple of hours my driveway was clear once again.
Today's 'Excel in Minutes' tip shows you how to 'Ensure Data Entered Is The Correct Length'.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I helped you 'Take a Shortcut to Your XLSTART Folder'. You can read that tip here.
I hope you have a great weekend and keep on Excelling,
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Ensure Data Entered Is The Correct Length
To ensure that the correct number of characters are entered for data such as SKU #s, serial #s, invoice #s, etc… you can use Excel's Data Validation feature.
For example, if column B contains SKU numbers and they are required to be 10 characters in length, here's what to do...
1) Select the range of cells in column B;
2) From the Data tab, click Data Validation in the Data Tools group;
3) On the Data Validation dialog, slick the Settings tab and from the 'Allow:' dropdown, select 'Text Length';
4) In the Data dropdown, select 'Equal to';
5) In the Length field enter 10 (the required length of the input data);
6) Optionally, you can customize the error message that appears if someone inputs an invalid entry. Click the Error Alert tab, enter a title for the error dialog and a message to the user.
7) Click OK.
CAUTION: Although Data Validation is a very useful feature in Excel, it does have some limitations which you need to keep in mind.
• If a cell from outside of the Data Validation range is copied and pasted over a cell containing Data Validation, the validations rules will be overwritten and, subsequently, any value can be entered in that cell.
• Data Validation does not apply to the results of formulas.
• If the Stop option is not selected in the Style dropdown on the Error Alert tab, any value can be entered.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630