Finding the Widest Cell Contents

Written by Allen Wyatt (last updated March 14, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


8

Mary has several spreadsheets with between 10,000-80,000 rows each. Occasionally a column is wider than it should be, probably due to content making it wide. So, Mary wonders if there is an easy way to find the widest cell without scrolling through 10,000-80,000 rows to find it.

There are several ways to go about this, but for the purposes of this tip I'll only describe three of them. The first is an easy way if you can utilize a helper column in your worksheet. Let's say that the column you want to check is A. You could put this formula in the first cell of an unused column:

=LEN(TRIM(A1))

Copy the formula down as many cells as necessary, and then use the filtering capabilities of Excel to show the largest values in that column. This will give you those cells you desire.

The second formula-based way is to use a formula like these:

=MIN(IF(LEN($A:$A)=MAX(LEN($A:$A)),ROW($A:$A),1048577))
=CELL("address",INDEX($A:$A,MATCH(MAX(LEN($A:$A)),LEN($A:$A),0)))

Both of these should be entered as array formulas by pressing Ctrl+Shift+Enter. The first formula will return the row number of the cell in column A that has the longest length. The second formula returns the actual address of the cell with the longest length.

The third approach is good if you need to figure out the long lengths quite regularly. It involves using a macro to derive the necessary information:

Sub FindWidestCells()
    Dim Ad(10) As String
    Dim Le(10) As Integer
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer
    Dim lCols As Long
    Dim lRows As Long
    Dim Rng As Range
    Dim c As Range
    Dim sTemp As String

    lCols = ActiveCell.Column
    lRows = Cells(Rows.Count, lCols).End(xlUp).Row
    Set Rng = Range(Cells(1, lCols), Cells(lRows, lCols))

    For Each c In Rng
        ' Find shortest length in the group
        K = 1
        For J = 2 To 10
            If Le(J) < Le(K) Then K = J
        Next J
        If Len(c.Text) > Le(K) Then
            Le(K) = Len(c.Text)
            Ad(K) = c.Address
        End If
    Next c

    ' Sort the cells
    For J = 1 To 9
        L = J
        For K = J + 1 To 10
            If Le(K) > Le(L) Then L = K
        Next K
        If L <> J Then
            sTemp = Ad(L)
            Ad(L) = Ad(J)
            Ad(J) = sTemp
            K = Le(L)
            Le(L) = Le(J)
            Le(J) = K
        End If
    Next J

    sTemp = "Longest cells:" & vbCr
    For J = 1 To 10
        If Le(J) > 0 Then
            sTemp = sTemp & "    " & Ad(J) & " (" & Le(J) & ")" & vbCr
        End If
    Next J

    MsgBox sTemp
End Sub

All you need to do is to select a cell in the column you want to check and then run the macro. What is returned is a list of the 10 widest cells in the column, in descending order based on length.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13747) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Turning Off Hyphenation for Individual Words

Word does a semi-decent job when it comes to automatically hyphenating your documents. It even lets you exclude certain ...

Discover More

Precision and Number Display

Do you need to work with very precise numbers in your worksheets? If you do, then it is imperative you understand ...

Discover More

Adding an Ellipsis to the Beginning of Some Paragraphs

The Find and Replace feature of Word is very powerful. You can even use it to add a unique character to the beginning of ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Picking Worksheets Quickly

If your workbook contains a multitude of worksheets, the worksheet tabs at the bottom of the program window start to lose ...

Discover More

Conditionally Setting the Color of Worksheet Tabs

If you want to set the color of a worksheet tab based on some conditions within the worksheet, you'll need to resort to ...

Discover More

Jumping to Alphabetic Worksheets

Got a workbook with a lot of worksheets in it? Here are some handy ways to jump to the worksheet you want, alphabetically.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 7?

2020-03-17 11:37:45

J. Woolley

@Willy Vanhaelen
This simple fix added to the top of your macro is better than the one I posted yesterday because it allows for a discontinuous Selection and chooses the column containing ActiveCell:
Intersect(Selection, Columns(ActiveCell.Column)).Select


2020-03-16 10:49:30

J. Woolley

@Willy Vanhaelen
Your macro is excellent, but it fails if Selection has more than one column. A simple fix is to add at the top:
Selection.Columns(1).Select
But it would be better to analyze each column.
Your formatted image is a great idea. Too bad it does not permit copy/paste.


2020-03-16 06:25:04

Willy Vanhaelen

Here is the right screen shot:
(see Figure 1 below)

Figure 1. 


2020-03-16 06:19:50

Willy Vanhaelen

@John Tomp
You are right, the number of characters doesn’t necessarily correspond with the space needed to fill a cell. Besides the macro doesn’t do what is asked for namely simply finding the cell taking the widest cell contents. Here is a relatively simple macro that just does what is asked for:

Sub Widest()
Dim cell As Range, R As Long
Dim X As Long, Y As Long, Z As Long
Application.ScreenUpdating = False
X = 0
Y = Selection.Row
Z = Selection.Cells(1).ColumnWidth
For Each cell In Selection
If Len(cell) > 0 Then
cell.Columns.AutoFit
If cell.ColumnWidth > X Then
X = cell.ColumnWidth
R = Y
End If
End If
Y = Y + 1
Next
ActiveSheet.Cells(R, Selection.Column).Select
Selection.Cells(1).ColumnWidth = Z
Application.ScreenUpdating = True
End Sub

Here is a screen shot that shows the right indentation but don't copy it.

Sub Widest()
Dim cell As Range, R As Long
Dim X As Long, Y As Long, Z As Long
Application.ScreenUpdating = False
X = 0
Y = Selection.Row
Z = Selection.Cells(1).ColumnWidth
For Each cell In Selection
If Len(cell) > 0 Then
cell.Columns.AutoFit
If cell.ColumnWidth > X Then
X = cell.ColumnWidth
R = Y
End If
End If
Y = Y + 1
Next
ActiveSheet.Cells(R, Selection.Column).Select
Selection.Cells(1).ColumnWidth = Z
Application.ScreenUpdating = True
End Sub


2020-03-15 11:55:10

J. Woolley

The macro I posted March 14 works better when this one line
Cells(tmpRow, tmpCol).PasteSpecial Paste:=xlPasteValues
is followed by these two additional lines
Cells(tmpRow, tmpCol).PasteSpecial Paste:=xlPasteFormats
Cells(tmpRow, tmpCol).PasteSpecial Paste:=xlPasteColumnWidths
which account for different formats including wrap text.


2020-03-14 19:05:03

Brian

The solution depends on what is meant by "widest". Looks like you've interpreted that to mean "most characters/digits", but that doesn't necessarily mean "widest". For example, the string "WWWWWWW" (7 characters) is much "wider" in most proportional fonts than ".........." (10 characters), even though the W string has fewer characters. If Mary had wanted to find the cell with the actual "widest width", can that be done?


2020-03-14 16:54:23

J. Woolley

Here is a modified version of the macro using each cell's actual width, not its character count. (Apologies for poor VBA code format when posting comments here.)

Sub FindWidestCells()
Dim Ad(1 To 10) As String
Dim Le(1 To 10) As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim tmpCol As Long
Dim tmpRow As Long
Dim lCols As Long
Dim lRows As Long
Dim Rng As Range
Dim Ac As Range
Dim c As Range
Dim sTemp As String
Application.ScreenUpdating = False
With ActiveSheet
tmpCol = .UsedRange.Column + .UsedRange.Columns.Count
tmpRow = .UsedRange.Row + .UsedRange.Rows.Count
End With
lCols = ActiveCell.Column
lRows = Cells(Rows.Count, lCols).End(xlUp).Row
Set Rng = Range(Cells(1, lCols), Cells(lRows, lCols))
Set Ac = ActiveCell
' Find shortest length in the group
For Each c In Rng
If Not IsEmpty(c.Value) Then
K = 1
For J = 2 To 10
If Le(J) <= Le(K) Then K = J
Next J
c.Copy
Cells(tmpRow, tmpCol).PasteSpecial Paste:=xlPasteValues
With Columns(tmpCol)
.AutoFit
If .Width > Le(K) Then
Le(K) = .Width
Ad(K) = c.Address
End If
.Delete
End With
End If
Next c
' Sort the cells
For J = 1 To 9
L = J
For K = J + 1 To 10
If Le(K) > Le(L) Then L = K
Next K
If L <> J Then
sTemp = Ad(L)
Ad(L) = Ad(J)
Ad(J) = sTemp
K = Le(L)
Le(L) = Le(J)
Le(J) = K
End If
Next J
' Report results
sTemp = "Widest cells (points):" & vbNewLine
If Le(1) = 0 Then
sTemp = sTemp & "this column is empty" & vbNewLine
Else
For J = 1 To 10
If Le(J) > 0 Then
sTemp = sTemp & " " & Ad(J) & " (" & Le(J) & ")" & vbNewLine
End If
Next J
Set Ac = Range(Ad(1))
End If
Application.ScreenUpdating = True
Ac.Activate
Ac.Show
MsgBox sTemp
End Sub


2020-03-14 05:57:04

John Tomp

Hello
Your methods donet actually calculate the width of the cells, they calculate the number of characters. Now a cell with 10 'i"s is much narrower than one with 10 "m"s (to take extreme values). And if you want to optimise cell width you need actual width not number of characters. Is there a way to do that?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.