The comments here cover most of the options, all with their limitations.
If the need is significant enough, and the specificity of the margins is important (eg you want precisely x pixels top and bottom), and you don't want to do it manually, you could write some vba code.
The basic algorithm being:
For each row to be adjusted
apply autoheight to the row (this expands it vertically to fit snug)
check what height that set the row to
re-set the height to this number plus your desired additional margin
Next
This has its limitations, too, but may be a solution depending on your needs.
Clearly this example is just around top and bottom margins (which are the harder ones to contorl, since for horizontal you at least have indenting options)
Loading
ExcelTips (Menu Interface)
This Site
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
Author Bio
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Subscribe
Helpful Links
ExcelTips FAQ
ExcelTips Resources
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
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.)
- 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.
Figure 1. The Alignment tab of the Format Cells dialog box.
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:
[???,??0.00;-??,??0.00]
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.
Related Tips:
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Leave your own comment:
Comments for this tip:
Jordan 12 Aug 2016, 01:09
BOBNTAM92 22 Oct 2015, 16:29
It took me forever but I finally figured this out For Excel 2013.
If you want to increase the margin between the left or right edge of a cell without changing the preferred alignment of the cell content, do the following:
1. Click on the cell you want to modify
2. Click on the HOME tab
3. In the STYLES section of the HOME ribbon click on COMMA 2 and you will see the extra space appear before or after the text, depending on the cell's alignment.
4. If your numbers get reformatted after clicking on COMMA 2, right click on the cell, go to the NUMBER tab, then modify the number category accordingly to add/remove decimal places, symbols, etc.
Hope this helps!
If you want to increase the margin between the left or right edge of a cell without changing the preferred alignment of the cell content, do the following:
1. Click on the cell you want to modify
2. Click on the HOME tab
3. In the STYLES section of the HOME ribbon click on COMMA 2 and you will see the extra space appear before or after the text, depending on the cell's alignment.
4. If your numbers get reformatted after clicking on COMMA 2, right click on the cell, go to the NUMBER tab, then modify the number category accordingly to add/remove decimal places, symbols, etc.
Hope this helps!
Bozhidar Stoinev 02 Oct 2015, 04:07
There are numerous ways to do this; however Excel is not HTML nor CSS enabled so this is not achieved by the popular concepts...
First, and most obvious way is to simply put a column on the left/right or row above/below and set its size.
Second, you could adjust the cell indent; it is relative to horizontal alignment - i.e. if you content is on the right, the indent increases from the right. However, cell indents are meant just for horizontal alignment.
Another way would be to add spaces on the left/right or add new rows (Alt+Enter) on top/bottom. Then, you need adjust the horizontal/vertical alignment and voiala! The content is on the side you need, but it also have its padding.
However, messing around just with the cell alignment IS NOT the same as setting "paddings" as there is no definition of "padding box within the cell" - the cell itself is the box; So I don't see the solution provided as working one...
First, and most obvious way is to simply put a column on the left/right or row above/below and set its size.
Second, you could adjust the cell indent; it is relative to horizontal alignment - i.e. if you content is on the right, the indent increases from the right. However, cell indents are meant just for horizontal alignment.
Another way would be to add spaces on the left/right or add new rows (Alt+Enter) on top/bottom. Then, you need adjust the horizontal/vertical alignment and voiala! The content is on the side you need, but it also have its padding.
However, messing around just with the cell alignment IS NOT the same as setting "paddings" as there is no definition of "padding box within the cell" - the cell itself is the box; So I don't see the solution provided as working one...
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.
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.
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.
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.
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