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.
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.
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.
- In the Text Control section, add a check mark to Shrink to Fit, then click OK.
Note: If you turn on the Wrap Text feature, the Shrink to Fit option is disabled.
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.
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.
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.
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.
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.