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:
- 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
- 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:
- Copy paste the formula from previous question in all the empty columns of the new row
- Select the cell range from A1 to U2 and copy it pressing (Ctrl and C)
- Create a new sheet, name it “Max Length”
- In cell A1, right click and select Paste > Paste Special
- From “Paste Special” dialog box, select Values under Paste and check Transpose box and hit OK
- 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.