The Excel Addict - Help with Excel 2013, 2010,
2007, 2003

February 16, 2017
Greetings from The Excel Addict

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.

Francis Hayes -

So I went out that evening after the storm had subsided and spend a couple of hours clearing out my driveway.

Francis Hayes -

The next morning (Wednesday) we awoke to yet another big snowstorm. By that afternoon when the snow stopped, another 30-35 cm had fallen.

Francis Hayes -

 After another couple of hours my driveway was clear once again.

Francis Hayes -

oday'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
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]

If you missed my last newsletter, you can click here to view it online.

Quote of the Day

"Do something for somebody everyday
for which you do not get paid."

-- Albert Schweitzer --
If you have a favourite quote, send it to me and I may post it in my newsletter.

Excel in Minutes with

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.

[Click for Practice File]
Ensure Correct Length for Data Entry in Microsoft Excel 2007 2010 2013 2016 365

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.

Data Validation Text Length Error in Microsoft Excel 2007 2010 2013 2016 365

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.

If you've found this tip helpful, please share it.

Click here to get my tips every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630