How to Limit Cell Size

One way to control the size of a cell is to use Excel's Data Validation.

A cell is a rectangular area that allows you to interact visually with the Excel program when entering data. A single cell in Excel is able to hold a large quantity of text or numbers. You can use a combination of Excel formulas and macros to accomplish this, though this process requires additional steps and can be more complicated to create, test and maintain. Instead, Data Validation actually limits the size of the cell by limiting the number of characters allowed in a cell.

Instructions

1. Click inside the cell, choose "Data" on the Menu Bar (in Excel 97 or 2000-2003), then choose "Validation." When using Excel 2007 and higher, click the "Data" tab on the Ribbon Bar, then in the "Data Tools" Group, click "Data Validation."

2. Click the drop-down option "Allow" on the Validation Criteria settings section. Select "Text Length" from the drop-down menu. The check box labeled "Ignore Blanks," by default, is checked.

3. Click "less than or equal to" from the Data drop-down option to select how you will restrict the quantity of text in your cell. Type the number of characters you want to restrict the cell to in the "Maximum" drop-down option.

4. Click the small icon on the right side of the "Maximum" drop-down options and drag your mouse across a group of cells in order to include the settings in all of those cells. Click the icon again, then return to the Validation dialog box.

5. Test your settings by typing a number of characters that exceed the number you selected in your Validation settings. If you set the "less than or equal to" option as 7, type 8 characters in the cell and the default error message will display.

6. Set an optional Input or Error Alert message. Open the Validation dialog box and set additional options using the "Input Message" tab or the "Error Alert" tab. Selecting the cell will display the Input Message. The Error Alert will display if the number of characters typed exceeds the Validation settings. Both of these settings are optional.

Tips & Warnings

• Data Validation is also used for providing useful information when data is entered and also as a feedback tool.

• If you choose to set your length for 10 characters, but then choose "less than" from the options, you cannot actually type in 10 characters, only 9 or less. For this reason, it is important to test your Validation.

References

• Microsoft: Office -- Apply Validation To Cells
• Microsoft: Office -- Designate Valid Cell Entries