The Incredible Shrinking Excel Font

Well, maybe it’s not incredible, but after all these years of using Excel, I stumbled upon its shrinking font feature. Did you know about it? If not, keep reading to see how it works.

And if you’ve ever had a worksheet where changing the font size had no effect, this might solve the mystery.

Report Headings

Picture, if you will, a report with headings of various lengths, like this example with month headings, shown below. The columns are wide enough to fit the numbers, but some columns are wider, to fit the column heading.

ShrinkFont01

You can waste a lot of time, fiddling with the column widths, so the heading text appears. For single word headings like these, the Wrap Text settings isn’t any help. You can leave some columns wider, or adjust the heading font size, to fit more text in the cells.

Shrink the Font

And here’s where today’s discovery of the font shrinking feature comes to the rescue. Instead of adjusting the font size manually, you can get Excel to make the adjustment for you.

  • In this example, we’ll select the heading cells – A1:H1
  • Then, on the Ribbon’s Home tab, click the Dialog Launcher for the Alignment group.

ShrinkFont02

  • In the Text Control section, add a check mark to Shrink to Fit, then click OK.

ShrinkFont03

Note: If you turn on the Wrap Text feature, the Shrink to Fit option is disabled.

ShrinkFont08

Font Size Changes Automatically

With the Shrink to Fit setting turned on, some of the heading cells, like January,  change to a smaller font size, so the full text fits in the cell.

ShrinkFont06

In this case, the headings look odd, in all different sizes, so it might be better to turn off the Shrink to Fit setting, and just go with a smaller size font.

The Shrink to Fit would work better for stand-alone headings, where there are no adjacent cells to compare.

Adjust the Column Width

Later, if you change the column width, the font size for the heading cells will magically readjust, to fit the new width.

The maximum font size will be the selected font size for the cell. So, if the font is set to 11 pt, and you make the column really wide, the font will stay at 11 pt.

ShrinkFont07

Confusing Font Sizes

It’s interesting that the font size shown in the Ribbon doesn’t change, even if the heading changes to a tiny font size. In the screen shot below, the February and March headings both show as 11 pt, but they’re different sizes on the worksheet.

ShrinkFont04

When the font size is reduced, selecting a larger font size on the Ribbon doesn’t have any effect. For example, if you choose a 36 pt font for the February heading, it stays the same tiny size. The larger font does affect the row height though, even if the displayed font doesn’t change.

ShrinkFont05

So, if can’t figure out why a cell’s font size won’t change, check that Shrink to Fit setting. And if a co-worker has a workbook with this problem, you can be the office hero by solving it.

_______________

You may also like...

10 Responses

  1. Ah, I’ve seen a colleague caught out with this before and have idly wondered before why there was no “grow to fit” option too.

  2. Bob Ryan says:

    Debra – I have been aware of this feature for some time – but not aware of the “Custom Lists” option you mentioned in last week’s PivotTable blog! Thank you for that one. I most recently used this “Shrink to Fit” feature just a couple of weeks ago when I imported long lengths of text into Column A but did not want the text to appear longer than the width of Column A. I had formulas in columns to the right that extracted certain parts of that text using MID, etc.

  3. Dave says:

    This can be useful if you have a date in a cell and want it formatted to look like:

    10/05/2011
    Wednesday

    You can use a custom number format of:
    mm/dd/yyyy#dddd
    where # = alt-0010
    hit and hold the alt key while typing 0010 on the numeric keypad.

    Then make sure you have wrap text turned on, then turn on shrink to fit.

    You’ll probably have to adjust the rowheight manually, though.

  4. @Jennifer Deacon Nice Idea I believe this can be suggested for the next updgrade to Microsoft!

  5. Alan Murray says:

    You have to love these little gems in Excel. Hidden away until the moment they happily frustrate a user.

    Nice post Debra. I love these little tips.

  6. Jon Wittwer says:

    Wow … I’m extremely embarrassed to say that this is the first time I noticed that little box … this is going to be awesome for date fields. I’d really like to slam my head against my computer right now for not noticing this years ago.

  7. Thanks for the Shrink to Fit tips, and glad to hear that I’m not the only one who had never noticed this setting!

  8. Gregory says:

    Wow, that’s pretty amazing. I’ve never used that feature. Who new?

  9. PAM says:

    I stumbled across this trying to find how to fit everything on a spreadsheet. I have tried to minimize the font and also changing the shrink to fit and I printed on landscape.My problem is the spreadsheet prints in 2 sections and the 2nd section that doesn’t fit prints after the first I guess. I’d hate to tape it to the 1st section. I guess its too wide, but I tried everything to get it on one paper. The number of papers is ot the problem its the width thats the problem.

  10. Jennifer Boer says:

    I am inserting Doulos IPA symbols into my excel spreadsheet cells. When i do, the font shrinks (in the ribbon too) from 11 to 9.5. Having to highlight text and increase the font size every time this happens is tedious. Can any of you advise me how to maintain the font size in the destination cell after inserting a symbol please?

Leave a Reply to Bob Ryan Cancel reply

Your email address will not be published. Required fields are marked *