excelcellexceptcolumn-width

Auto adjust column widths in Excel while excluding certain cells/rows

I have looked to find related questions multiple times, but I have never found a similar question. Everyone is always wanting to know how to adjust column widths, that's easy, I have much more nuanced question.

I love auto adjust, makes clean looking tables and makes sure everything is visible (aka.; ctrl-a, double click up top between columns), but if you have one cell with a long piece of text (like notes at the bottom of a table), it makes that column like 400 pixels wide to capture the entire text, when I just wanted it to update per my column heading or table data. I would like to be able to flag this text cell to be ignored by excel for auto adjustment.

If you are OK with it being Centered, then you can set that cells alignment (and 1 cell to the right's) to "Center Across Selection" and it will be ignored. But, like my example above, I often want this text justified left or right, not allowing C.A.S. to work.

Has anyone been able to accomplish this?

EDIT: After over a decade of searching, I've found the answer. It is to use Center Across Selection as described above, but to use Custom Number Formats to auto-pad the text with spaces for the desired justification. I put together an image below to help explain this graphically.

Copy-able Custom Number Formats here as well (They only need to applied to the left-most cell with the text, all affected cells need to be Center Across Selection'd):

For left justified (remove quotes, note the end space):

"General* ;General* ;General* ;General* "

For right justified (remove quotes):

"* General;* General;* General;* General" Graphical explanation of using Center Across Selection to ignore auto-column adjustments for cells

Solution

  • I was able to find a solution to this based on using Center Across Selection (TLDR: See the edit to the original post). The beauty of CAS is that for the purposes of any Auto-Adjustment of Column Widths, Excel ignores any text typed in a cell using that CAS text alignment as long as there is also at least 1 blank cell to the right of your CAS text that is also using CAS (so it can't work on just 1 cell). This only works for text you WANT centered though, for anything else, you need to modify this method.

    For right justify, enter [your text] into the left-most cell to be combined into the justified region, CAS the region (under Formatting, Alignment, Horizontal, Center Across Selection), select the cell with text and enter as a Custom Number Format (Formatting, Number, Custom):

    This uses the standard General formatting for each of the positive;negative;zero;text values but takes each one and repeats ("*") a character (" ") to pad it before the value to fill up the entire selection.

    For left justify, it's exactly the same as above, except the Custom Number Format is:

    General* ;General* ;General* ;General*

    Noting the final space after * .

    (See comment below regarding ###### errors: either increase column widths or add additional columns to the Center Across Selection until it appears properly)

    See an example of this in graphic/Excel formatting: enter image description here