by Allen Wyatt
(last updated March 14, 2020)
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:
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:
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.
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 Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but ...Discover More
If you've got a lot of worksheets in your workbook, you may want to display their tabs in to rows at the bottom of the ...Discover More
Need to create a large number of worksheets using specific names? If so, you'll love the ideas presented in this tip.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.