Find Max Length of a Column

Finding the max lengh of values in a column is helpful when creating databases. This preview from our Excel Tips & Tricks course will help you do just that.

Question
What is the largest “Product Name” length? (a: 127)
Steps:

1. Create a new row in “Orders” sheet above all the row headers by pressing (Ctrl, Shift and +) together and selecting Entire row from the insert dialog box
2. In cell Q1, right above the “Product Name” column, write the formula, =MAX(LEN(Q3:Q9995)) and hit enter

Note:

• If you get #VALUE! Error then tweak the formula to, {=MAX(LEN(Q3:Q9995))}. The curly braces ensures that the formula has run on a set of values and not just a single value at a time.
• To select a range of values, hold (Ctrl, Shift and Downward arrow) together

Question
What is the maximum length of all the columns in “Orders” table
Steps:

1. Copy paste the formula from previous question in all the empty columns of the new row
2. Select the cell range from A1 to U2 and copy it pressing (Ctrl and C)
3. Create a new sheet, name it “Max Length”
4. In cell A1, right click and select Paste > Paste Special
5. From “Paste Special” dialog box, select Values under Paste and check Transpose box and hit OK
6. Now that we have the maximum length data stored in a separate worksheet, go back to “Orders” sheet, Select entire Row 1(which we created to calculate Max Length) and hit “Delete” in the Tools bar under Home tab.

Note: You may cut and paste the Length column to the right side of column header names to make the data in the new “Max Length” sheet more presentable.

Advance Tip:
Q: Why are we even interested in Maximum lengths of values?
A: Knowledge of maximum length of characters helps in creating databases without any chance of missing out on characters if a fixed length is to be specified for a variable. Eg: varchar in SQL.