You may be working with lists in Excel. They may get a little wide and you might like to cut down the size of some of the columns. If you could just easily find where the longest values were so you could cut them down in size.
Here are some handy formulas to help you do just that.
The exercise and the result are in the Text Length file.
We have a column of company names as part of a list. For simplicity, we will just show this column.
It is wider than we would like. We want to cut down the width of our overall list.
What is the longest company name? You may recall the formula for the length of cell A2 is:
=LEN(A2)
You may also recall one of the skills we have taught is working with arrays, which allows you to do calculations on an entire range at once. We called gave this range a name ("company") and by using an array, we can quickly generate the maximum length for the whole list.
Our formula converted for an array is:
{=MAX(LEN(company))}
The parts are:
LEN(company)- to get the length of any cell in the range named "company"
MAX(LEN(company))- get the maximum length
{=MAX(LEN(company))}- save the formula as an array (by clicking Ctrl, Shift and Enter at the same time).
We played a trick to demonstrate an error. Usually if you try to save an array formula, but accidentally save it as a regular formula by clicking Enter only, you will get an error. That did not happen in the above screen shot. Instead we got a meaningless number. When we save it properly (which you can tell by the brackets around the formula), we get the correct value of 65 for the maximum length.
It is good to know the maximum length. But can we find the cell with the maximum length?
This formula is a little more involved, let's walk you through it:
{=MATCH(C3,LEN(company),FALSE)}
Now we know where to go to shorten the company name. But wouldn't it be nice to see it first before we go to that cell, especially if we have a long list? Here you go:
For the solution, we use the INDEX function, which consists of:
INDEX, as we covered in a past lesson, returns the corresponding value from that spot in the range. Note that this is a formula that by design looks at a range, so we do not need to save it as an array.
With that information, we can decide if we want to cut out the second part of the name (after "/") for example.
Play around with the file. Trim this one down. See what is the next longest. How much you can cut down the column width?
We consider this an advanced formula. Even if you forget it, you know where to go for the Excel Tips Index, where you can then link to this sheet and have the file handy to cut and paste the formula. We hope this helps you manage your lists.