Adjusting Cell Margins for More White Space
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Adjusting Cell Margins for More White Space.
Serena asked if there is a way to adjust the text margins with a cell so that there is more white space within a cell. The answer is that there is no intrinsic Excel command to add more space, as you can do in Word tables or with the CELLPADDING attribute in an HTML table. However, there is a way you can accomplish the desired goal, although it is somewhat more manual in nature. Follow these general steps:
- Select the cell you want to adjust.
- Click the Cells option on the Format menu. Excel displays the Format Cells dialog box.
- Make sure the Alignment tab is displayed. (See Figure 1.)
Figure 1. The Alignment tab of the Format Cells dialog box.
- Using the Horizontal drop-down list, choose Center.
- Using the Vertical drop-down list, choose Center.
- Click on OK.
- Adjust your column width to leave the desired white space on both sides of the cell contents.
- Adjust your row height to leave the desired white space above and below the cell contents.
The drawback to this approach, of course, is that it affects the width of not just one cell, but of the entire column and row. In addition, your numbers will not line up properly—on the decimal point—if you choose to center horizontally. The way around this last issue is to still center the contents horizontally, but use a custom number format for the cells, as follows:
The question marks in the format forces Excel to leave space as if there were a digit, even if there isn't.
You cannot change the vertical white space without adjusting row height. There is, however, one thing you can try if you want to change the horizontal white space: Adjust the indent value used in a cell. All you need to do is display the Format Cells dialog box, and then set the indent on the Alignment tab. If you are using Excel 97 or 2000 you can only adjust the left indent. If you are using a later version of Excel you can adjust either the left or right indent (check out the Horizontal drop-down list), but not both.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2762) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Adjusting Cell Margins for More White Space.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Comments for this tip:
Mike 08 Jul 2015, 10:28
If you just want to add space to increase readability, select the whole sheet by clicking the the box at the top left of the sheet.
Adjust the height of any row and it will automatically adjust the height of all rows.
Do the same with columns.
Tim 18 Jun 2015, 12:05
I know this is an old post, but while searching for a way to add cell padding I found no good answers. In playing around with custom formats I discovered a very simple solution.
Select the 'Number' tab from 'Format Cells', select 'Custom' then enter a \ followed by the number of spaces you desire then an @ followed again by \ and number of spaces. ie. "\ @\ " provides a 2 space left and right margin.
Loki 08 Jun 2015, 16:32
Quote: "You cannot change the vertical white space without adjusting row height."
Well, that's plain wrong. First set your cells to "wrap text". Put a forced new line (hold Alt and press Enter)to start the text and finish the text with a forced new line. Then when you automatically size the row heights to fit the content - you will have a line of white space at the top and bottom of the cell.
In Excel 2010 - use Distributed Indent (in Format Cells - Horizontal Alignment) to put white space on both sides of the text although this will also cause the text to be aligned on both sides.
It's not that hard.
Dan Hettler 18 Aug 2014, 16:18
Would it be possible to, I don't know, NOT have a bunch of unnecessarily added and no-reason-for white space in the cells? Perhaps, maybe, just maybe, have ONLY the actual text that is typed in?
awyatt 14 Aug 2014, 16:18
Rebecca: Why would you think there should be a box for padding? What gave you that impression?
Rebecca 14 Aug 2014, 12:39
With respect to the steps above (copied below) and the dialog above, I still don't see a box for padding.
4. Using the Horizontal drop-down list, choose Center.
5. Using the Vertical drop-down list, choose Center.
6. Click on OK.
7. Adjust your column width to leave the desired white space on both sides of the cell contents.
8. Adjust your row height to leave the desired white space above and below the cell contents.
nobody 19 Jun 2014, 19:27
Yes, there certainly should be separate settings in the alignment tab of cell formatting for left, right, top and bottom indents. No excuse to be required to choose left or right and if center is chosen, it will not allow you to enter a value for indent. Most spreadsheets I create have at least one remarks or description block which requires word wrap, and has multiple lines. The text is placed far to close to border lines or the other depending on which side you set the indent.
Mike 22 May 2014, 10:21
Again, Micros**t doesn't provide enough flexibility for USERS to choose how their sheets are formatted. You cannot indent text from the right without changing justification (i.e. left-justified text with a right indent) and you cannot have different values for left and right indents. (There are also no top and bottom indents.) But you DO get to have "unknown problem and needs to close" messages, so I suppose all is not lost. :p
chinna 23 Apr 2014, 02:03
how to set cell margins in excel 2007
Leave your own comment: