Average column width, instead of maximum.

e561414

New Member
Joined
Mar 12, 2014
Messages
35
Hello, forum.

I have a list of descriptions, they live in a column. Some of them are very short, others are really long, but most of them are somewhere in between. If I double click the column header's right edge, it automatically adjust the width to display the longest description according to the view, I know that.

Question: Is there a way to make excel detect the most efficient width?

What is efficient? probably a width that lets me see most descriptions. I guess that would be the average width.

Does this make sense?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

ShawnPCooke

Board Regular
Joined
May 14, 2007
Messages
151
There's nothing built into Excel like that, unfortunately. Perhaps if you were to wrap text in the description column, then you could maintain a more moderate width while still seeing the full description?
 

e561414

New Member
Joined
Mar 12, 2014
Messages
35
Thank you for replying. What do you mean by "wrap"? is it the text adjustment function?

To clarify, I don't really care about seeing the longest descriptions. I like my descriptions with a constant row height, and if I use the text adjustment, some cells become half a screen in height and then I can't scroll properly because Excel has issues with that too, since it scrolls by row.

There's probably a macro for anything, but I haven't found one for either problem (Yup I can't code, yet).
 

ShawnPCooke

Board Regular
Joined
May 14, 2007
Messages
151
I'm not sure what version of Excel you have, but in 2007-2010 at least, you can find on the Home tab, in the Alignment pane, an option called "Wrap Text." This causes a long line of text to break at the end of one line and continue onto the next, while remaining in the same cell. The height of the rows is adjusted so that the entire description can be seen. But as you stated, you prefer a constant row height, so that won't work for you.

A macro could certainly adjust column widths for you. The most straightforward approach is to set a maximum width, so that no column will autofit to wider than that. You can use the code below; just adjust the value of varMaxColumnWidth to what you want your maximum width to be.

Code:
Public Sub AdjustColumnWidths()

    'This subroutine autofits the current workbook, then
    'adjusts long columns to a maximum value.
    
    'Declarations
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim varMaxColumnWidth As Variant
    Dim intColumnCount As Integer
    Dim j As Integer
    
    'Error handling
    On Error GoTo Abort
    
    'Initialization
    Set ws = ActiveCell.Parent
    Set rng = ws.UsedRange
    intColumnCount = rng.Columns.Count
    
    
    varMaxColumnWidth = 30      'Change this value to suit yourself
    
    'Autofit columns
    rng.EntireColumn.AutoFit
    
    'Loop through each column
    For j = 1 To intColumnCount
    
        'Check to see if the width is greater than the maximum
        If rng.Columns(j).ColumnWidth > varMaxColumnWidth Then
            
            'If so, adjust it to the maximum
            rng.Columns(j).ColumnWidth = varMaxColumnWidth
            
        End If
    
    Next j

Abort:

    'Destroy object variables
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing

End Sub
 

e561414

New Member
Joined
Mar 12, 2014
Messages
35

ADVERTISEMENT

Thank you very much for the effort, this information is very useful, but it doesn't do what I'm looking for.

Here's what I'd like the macro to do:

Based on a selection:
1. Measure the width of each cell in the column
2. Calculate the standard deviation of those widths
3. Use the result as the new width for the whole column

It would be useful to apply a quick format to my reports.
 

ShawnPCooke

Board Regular
Joined
May 14, 2007
Messages
151
The standard deviation measures variation from the average. So for example, if you have cells of width 3, 4, 4, 4, and 5, the standard deviation would be 0.707107, which is not going to be wide enough for anything.

Do you mean that you want the column width to be one standard deviation above the average? Citing the same example, the average width is 4, so one standard deviation above the average would be 4.707107.

If so, accomplishing this would be a fairly extensive operation. When you say that you want to measure the width of each cell in the column, what I assume you mean is that you want to measure the value of the column width if the column were autofitted to that cell. To do that, it would require the following

a) Create a new worksheet.
b) Copy-transpose the first 65,536 non-blank values in your source column into the first row of the worksheet.
c) Autofit those columns
d) Capture the column widths
e) Repeat as needed until all values have been captured.
f) Delete the new worksheet
g) Perform the average and std. dev. calculations.
h) Fit the source column.

Depending on the amount of data you have, this will be a time-intensive operation.
 

e561414

New Member
Joined
Mar 12, 2014
Messages
35
Depending on the amount of data you have, this will be a time-intensive operation.

If so, then it's pointless, it'd be best to keep the column with a fixed width. All right, man, it's been very educational. Thank you very much.

Have a nice one.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,290
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top