When we work in Excel, the cell content does not always fit the cell size. In such situations, some options available to us include text wrapping, changing the row height and column width, etc.
But there are times when we need to shrink the text and get it t fit into a cell without affecting the layout of our Excel sheet. One scenario would be where you need to print your data and don’t want to increase the column width.
In this article, I will show you simple methods that we can use to get the text to fit in cells in Excel (shrink-to-fit text in Excel).
Using the Format Cells Option
In Excel, we can quickly and automatically reduce the text to fit into a cell. However, most Excel users do not use the shrink text to fit in a cell feature.
The main reason is that when a text is lengthy, sometimes, after shrinking, the text becomes so small and difficult to read.
However, when we have restrictions on changing the layout (for example, we are not permitted to change the column width or row height), this is the best option for us.
Below I have a table that shows answers to three questions, and the text does not fit the cell completely.
I want to shrink the text to fit in cells for row 1. I can do this by following the below steps.
- Select the cells that we want to shrink text to fit in cells. So, I select cells B1 to D1.
- Go to the “Home” tab.
- Click on the “Alignment Settings” Expansion icon.
Then, Excel will open the “Format Cells” dialog box and I am in the “Alignment” tab of the “Format Cells” dialog box.
- Go to the “Text control” group and check “Shrink to fit”.
- Click the “OK” button.
As soon as I click the “OK” button, Excel shrinks the selected text to fit the cell.
Now, I can see the full questions inside each cell without adjusting row height or column width.
We can also open the “Format Cells” dialog box using the following two ways.
- Right-click on the selected cells and select “Format Cells” from the Context Menu.
- Use the Excel keyboard shortcut “Control + 1”. To use this, hold down the “CTRL” key and press the “1” key.
Also read: Make all Cells the Same Size in Excel
Using the Excel Keyboard Shortcut
Some Excel users prefer to use keyboard shortcuts. There is an Excel shortcut to shrink text to fit in a cell in Excel.
Below I have a table that shows answers to three questions, and the text does not fit the cell completely.
I want to shrink the text to fit in cells for row 1. After selecting the cells that I want to apply shrink to fit, I can use the below shortcut.
ALT + H + F + A + (ALT +K) + Enter
I have to use the above shortcut as follows.
- Select the cells that I want to apply “Shrink to fit”. So, in this case, I am selecting cells B1 to D1.
- Press the below keys one after another.
ALT + H + F + A
When I press the above keys one after another, I will get the “Format Cells” dialog box. The active tab of the “Format Cells” dialog box is the “Alignment” tab.
- Then I have to press the ALT key and the letter “K” together. In other words, I have to hold down the ALT key and press “K”.
When I use these keys, the “Shrink to fit” of the “Text Control” group will be checked.
- Finally, I have to press the ‘Enter’ key.
As soon as I press the “Enter” key, Excel shrinks the selected text to fit the cell.
Now, I can see the full questions inside each cell without adjusting row height or column width.
Also read: Autofit Column Width in Excel (Shortcut)
Using VBA Code to Fit Text in Cells in Excel
Sometimes we like to do our Excel work with just a click of a button.
If we want to shrink text to fit in a cell in Excel with just a click of a button, we have to enter a VBA code.
Below I have a table that shows answers to three questions, and the text does not fit the cell completely.
I want to shrink text to fit in cells for row 1 with just a click of a button.
The below VBA code helps me to shrink text to fit in a cell in Excel.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Shrink_to_fit()
Selection.ShrinkToFit = True
End Sub
To apply the above VBA code, I have to do the following steps.
- Press ALT + F11 keys to open the VBA Editor. If the function keys are locked, I have to press the Fn key as well. I have to press ALT + F11 keys together.
- Expand the “Insert” menu of the VBA Editor dialog box and click “Module”.
- Copy and paste the above VBA code into the Module.
- Click the Save button.
- Click “No” in the pop-up message.
- Save the workbook as an “Excel Macro-Enabled Workbook (*.xlsm).
- Go to the “Insert” tab of the Excel file. Expand the “Illustrations”. Expand “Shapes”. Click on the rectangle shape and draw a rectangle.
- Type “Shrink-to-Text” inside the rectangle.
- Select the Shape and right-click on it. Then, select “Assign Macro…” from the context Menu.
- Select the Macro name from the “Assign Macro” dialog box and click the “OK” button.
Now I can shrink text to fit in the selected cells by just clicking the “Shrink-to-Fit” button. So, I am selecting cells B1 to D1 and clicking the “Shrink-to-Text” button.
As soon as I click the “Shrink-to-Text” button, Excel will shrink the text to fit in the selected cells.
Now, you have learned three methods to shrink text to fit in a cell in Excel. After we shrink the text, always remember to check the readability of the text.
Other Excel articles you may also like:
I have never had the idea to shrink text, i am glad upon practicing this lesson I am able to do it on my own. Thanks so kindly.
Dominic Cooper