36

I don't want to stretch all rows and columns to make them more readable than they are by default...

4
  • 1
    By "stretching" do you mean increasing the width of the column based on its content? Increasing the height of a row based on its content? Something else? Mar 6, 2016 at 5:25
  • I had to vote down, because the body is just a statement of what you don't want to do. Dec 15, 2019 at 19:43
  • @ctrl-alt-delor no OP is trying to ask how to apply a minimum padding to cells separately from manually resizing the rows or columns, which is possible in other programs like Excel
    – Andy
    May 22, 2020 at 4:45

16 Answers 16

17

I found a solution, which was actually quite obvious - select all cells and then stretch. It is not exactly cell padding though...

3
  • What do you mean by stretch all cells? Feb 28, 2019 at 21:24
  • 1
    @MonoThreaded, you select all cells by CTRL/CMD+A or select specific rows and cols via clicking on its numbers or letters while holding CTRL/CMD key. If you've selected at least two rows and/or two columns then you can drag a single border between them - all widths/heights will change.
    – ivkremer
    Jul 2, 2019 at 10:33
  • This must be from an old version; if I do this, it sets the size of all cells to be equal - to the size of the cell that I dragged Jul 2, 2020 at 15:51
8

It would appear that at this time (April 2014) there is no way to add horizontal cell spacing.

2
  • 12
    Actually you can add a very small amount of padding if you add thick borders (3px) and make them white. Sep 28, 2018 at 3:04
  • Hmm, this is good! You can actually add the thick borders, select all and double click to auto-size, and then reduce the borders again, for a tiny bit of padding!
    – Killroy
    Oct 26, 2021 at 7:57
6

As there is currently no way to add either vertical or horizontal padding, you need to use a workaround to be able to add cell padding. Since these are workarounds, I will add the cons to using them below.

For horizontal padding:

Thanks to Pitter Pat (source) for this method. Essentially you need to change the format of the cell and add spaces to the format. This will ensure the content of the cell is not changed, but the way it looks (in terms of padding) is changed.

  1. Go to Format > Number > More formats > Custom number format...

  2. Type in the following and apply:

" $"#,##0.00_ ;" -$"#,##0.00_ ;" $"0.00_ ; @ ;

This should give you five spaces of padding to the left and right of positive and negative numbers, zeros, and text.

Cons:

  • This could likely mess with custom formats you are using, if you are using anything other then positive or negative numbers, zeros, or text
  • This does not add true padding to the right side of a cell. In order to achieve "true" padding, the only way I see is to make a new column to the right of the cell you want to pad and try to make it blend in.

For vertical padding:

This is mostly manual.

  • You need to select all the cells (click the top left box, on top of "1" and on the left of "A", or control/command + A). Select the vertical align and set it to center. Then, change the height of all the rows. NOTE: this will get rid any custom vertical align/height settings you had for any cells or rows).

  • Ensure that the text wrap setting is set to anything other than "wrap text" (i.e. "clip" or "overflow").

  • If necessary, manually wrap text in cells and change their heights.

As you can see from these steps, this mainly has to do with playing around with the rows and heights and whatnot. The cons with this is that it isn't a good, "definite" way of adding vertical padding—if your content changes, you will manually need to change the padding again. It also is quite cumbersome if you rows aren't similar to each other.

1
  • 3
    The above number format gives an error in the latest Google Sheets.
    – Ben Davis
    Nov 7, 2018 at 18:27
5
  • select the rows right click on the row header 'Resize row...'
  • find 'align' icon in tool bar and center the text vertically.
2

My Workaround: Add a skinny column to the side you want padding on, and then make the border between the skinny column and the column you are working with white. Then when you print it looks like everything is moved in however much you want. Honestly, cell padding is such a standard feature in spreadsheets. It's pretty ridiculous that Google Sheets doesn't have this function.

1

You might want to try copying the entire spreadsheet and pasting it into a new file. The padding will be recovered, then you can resize the column width to fit the data.

1

This solution works:

  1. Mark the cells you want the padding (horizontally left).

  2. Go to "Custom Number Format":

enter image description here

  1. Enter " "@ where the whitespace represents the distance you want to have. The more whitespaces the more distance to the left. The @ sign is a placeholder for the cell's text.

See also Format numbers in a spreadsheet

enter image description here

1
  • which should be the right answer !!! Jun 1, 2021 at 14:35
1

Working solution as of 2023.

Select all headers before resizing

  • One common mistake is that people select all cells, and not the actual headers. You can tell if the headers are selected if they become inverted.
  • To select all headers – select the cell one column (or row) away from your existing data, and press CTRL/CMD + A.

Example of all headers being selected

enter image description here

Example of no headers being selected

enter image description here

Stretch the headers

Stretch the headers of the columns or rows. Stretching a single header cell, will make the rest of the column/rows stretched also.

enter image description here

1

The padding of individual cells can be set in pixels via the Sheets API to produce results like any of:

varying padding in cells

For example, using a batchUpdate request to set the padding to 0 pixels for cell A1 on the first sheet would use a request like:

{
  "requests": [
    {
      "updateCells": {
        "rows": [
          {
            "values": [
              {
                "userEnteredFormat": {
                  "padding": {
                    "bottom": 0,
                    "left": 0,
                    "right": 0,
                    "top": 0
                  }
                }
              }
            ]
          }
        ],
        "start": {
          "columnIndex": 0,
          "rowIndex": 0,
          "sheetId": 0
        },
        "fields": "userEnteredFormat.padding"
      }
    }
  ]
}

As a one-off, you can execute this with the "Try It!" functionality of the Google API Explorer to set the padding in an arbitrary cell, then copy the format of that cell elsewhere as needed.

0

You can put extra space at the top and bottom of cells using soft returns; either by Option+Enter on a Mac or Alt+Enter on a PC.

0

User Skeleton Bow's formula is outdated for the current version of Google Sheets. In my case, I only need to add one space of padding to text, in which case you can use "@ " to do just that. Adding more spaces before/after the @ will add them around your text.

0

This isn't perfect and as others say, shouldn't be required, but if you add a space or - character in an unused column (usually far right, off-screen) and set the font size to be larger than your biggest font in the cells you are using, you'll get some padding. Note this only works on single line height content; if you have content wrapping over multiple lines, these will display with normal padding.

0

Work around for non-numeric cells:

  1. Select the cells or column that the non-numeric data is in
  2. On the home ribbon, click the More Formats dropdown
  3. Select the "Accounting" numeric format

This will insert a small amount of padding on the left side of each cell.

Note: Does not work for numeric data (for obvious reasons).

-1

Try changing the font size in all the cells to a smaller font, then bumping the font size back up again.

-1

There is an add-on that is called Text Adder. I used that to solve my problem. It allows adding text in front or after the words. I typed spaces and it worked giving me the horizontal padding I was looking for since last year. Thank you to the author of that add-on.

-2

Right click on column. Select row height and custom.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.