This post is going to show you all the ways you can add indents to your Google Sheets.

Keeping your dataset organized and easy to read is very important.

In Google Sheets, there are many things you can do to increase the visual appeal of your datasets. This post will show you all about indentation.

Adding indents to cell contents is a proven way to make your spreadsheet easy on the eye.

When you pair indenting with other text formatting options such as bold fonts and colors, your spreadsheet will explode with visual appeal.

Unfortunately, Google Sheets does not have a dedicated tool for adding indents to cells. Indenting will require a bit of creativity.

In this post, you will learn various tools and means that you can use to add indenting to your cells for better organization of your dataset.

The indentation methods that will be discussed in the post are as follows.

• Adding indents with the Spacebar.
• Adding indents with a Custom Format.
• Adding indents with an Extra Column.
• Adding indents with the SUBSTITUTE Function.
• Adding indents with the TEXT function.
• Adding indents inside a Drawing Textbox.
• Adding indents with an Apostrophe.
• Adding indents with an Apps Script.
• Removing indents.

Get your copy of the example workbook to follow along.

Add Indents with the Spacebar

This method is both a simple and easy-to-use method.

All you have to do to use indentation in a cell is to manually create space characters using the spacebar on your keyboard.

In this example, four preceding space characters were used to create indents.

Already you can see how using this method won’t be sustainable for large projects.

Asides from the obvious drawback that you have to enter spaces into each cell manually, you also have to keep track of the number of space characters you use.

Fortunately, there are ways you can use the spacebar method to indent that will eliminate the monotony involved.

= "    " & A2

You can use this formula to apply indents without the monotony of having to do it one at a time. The formula uses the ampersand operator to bring together the space characters entered in double quotes and the contents in cell A2.

The formula returns the results of the operation in separate cells. Once you use this formula in one cell, you can copy it to other cells by dragging down the square on the bottom-right corner of the active cell.

= CONCAT ( "    ", A2 )

You can also use the CONCAT function to achieve the same result.

CONCAT only allows the concatenation of two values, whereas, there’s almost no limit to the number of values you can concatenate using the ampersand operator.

Otherwise, CONCAT and the ampersand operator do the same operations.

You can introduce the ARRAYFORMULA to the equations like this and avoid the need to copy and paste down the formula.

= ARRAYFORMULA ( CONCAT ( "    ", A2:A5 ) )

The ARRAYFORMULA forces functions that don’t take array arguments to return results in an array. This means you don’t have to use the copy down the formula.

Add Indents with a Custom Format

Google Sheets has many built-in number formats. The custom format feature allows you to create formatting syntax that suits your need.

You can use the Custom number format to add indents.

Follow these steps to create a custom indent formatting.

1. Go to the Format menu.
2. Select Number from the options.
3. Select Custom number format from the submenu options.

Note: select the cells you want to apply formatting before opening the Custom number format menu.

In the format input, press the spacebar four times, then enter the @ symbol.

The Sample section below the search bar will give you a preview letting you know how the formatting instruction you just entered will affect the display of the cell values.

The Custom number format is mainly used for formatting numbers, but using the @ symbol will allow you to pass formatting instructions for text values as well.

When you’re done, click on the Apply button and the formatting instruction will insert an indent to each of the selected cells.

The Custom number format method eliminates the redundant space characters in your data from the first method. The spaces only appear in the formatting but the actual value remains unchanged.

If you want other cells to apply the custom formatting to new entries, you can simply apply the custom formatting. And when you add new entries to the list, they will carry the formatting.

Another way you can make new entries carry the custom formatting is to apply the custom format from the list of recent formats from the highlighted section in the image.

There, you will find the list of recently used custom formats.

When you add new items to the list, simply selected them, go to the recent formats list and select the format you want to apply.

Add Indents with an Extra Column

You can use an extra column to create a visual illusion of an indent in your spreadsheet.

This method is very effective, and you don’t have to use any formulas or create a custom format to maintain the structure of your indent.

Follow these steps to use insert indentation using an extra column.

1. Select the data you want to add indentation and move it to a separate column

When you want to relocate a column to another, place the cursor on the edge of the selected column until the cursor changes to a hand icon.

Click and drag when you get the hand icon to grab the selected column, then move it to your desired location.

2. Use Ctrl + X to cut the headers of the list and paste them into the column on the left in column A

3. Reduce the width of column A.

As you resize the width of column A, the list header spills over to the next cell. The empty cells in column A serve as the indent so that the items can stand marked from the list header.

By turning off the gridlines for the sheet, you get to truly appreciate the result of this method.

You can turn off the gridline for a sheet by going to the View menu and selecting the Show option and then choosing Gridlines in the submenu.

Add Indents with the SUBSTITUTE Function

You can add indentation to a cell using the SUBSTITUTE function.

= SUBSTITUTE ( text_to_search, search_for, replace_with, [occurrence_number] )

The SUBSTITUTE function searches for specified text within a cell and replaces it with another text just like the Find and replace feature. It can also be used to replace all of the text inside a cell.

The SUBSTITUTE function uses four arguments to carry out its operation

• text_to_search is the text to search in.
• search_for is the text that you want to find in the text_to_search argument. You should know that search_for will match parts of a word and also whole words. That means if you have an in search_for, it will match an and hand.
• replace_with is the character that will be substituted for search_for.
• occurrence_number is an optional argument that allows you to specify which occurrence of the search_for should be replaced. That is, if you search for l in Hello, and enter 2 in occurrence_number, it will cause the function to only replace the second occurrence of l in the string.

= CHAR ( table_number )

The CHAR function is used to return a character from the Unicode table. You just insert a number into the table_number argument and the function will return the corresponding character from the Unicode table.

Together, you can use the SUBSTITUTE and CHAR functions to add indents to a cell that uses line breaks.

= SUBSTITUTE ( A2, CHAR ( 10 ), CHAR ( 10 ) & "    " )

CHAR(10) returns a line break character. By using the CHAR(10) function in the search_for and replace_with arguments, the SUBSTITUTE function searches for a line-break and replaces it with a line-break and 4 space characters. The 4 space characters are inserted by the ampersand operator.

You will notice that the first item on the list isn’t aligned with the rest. That is because there’s no line break before the first item.

To align it with the rest, simply insert a line break before the first item.

To insert a line break, place the cursor before the first item and press Ctrl + Enter.

Add Indents with the TEXT function

The TEXT function is one of the tools useful for adding formatting to cell contents.

= TEXT ( number, format )

With the TEXT function, you only need to identify the cell you want to format using the number argument and pass formatting instructions with the format argument.

= TEXT ( A2,"    @" )

This syntax uses the four spaces and the @ symbol to add indents to the cells like in the Custom number format method.

Add Indents Inside a Drawing Textbox

You can also add indents to your data if they’re in a textbox.

In Google Sheets, you can use Drawing to create and insert various shapes and images, including a textbox.

Open the Drawing tool.

1. Click on the Insert menu.
2. Select Drawing from the options.

When the Drawing canvas opens, use the textbox icon to create a text box.

Enter your data and use the small square box on the edges to resize the textbox.

Follow these steps to insert an indent to the content of the textbox

1. Select the content to which you want to add an indent.
2. Go to the ruler at the top of the canvas and place your mouse on the Left indent arrow. It is the downward-facing arrow on the ruler.

3. Click and drag the Left indent arrow from left to right.

There are also indent commands available in the formatting menu. Use these to indent a preset amount or remove any indentation.

Click on Save and close to insert the image into your spreadsheet.

A drawing is treated as an object in the spreadsheet. It will not exactly fit into cells, rather, it will float above the sheet. Nevertheless, you can resize and adjust according to provide as close a semblance as possible to it being in a cell.

Add Indents with an Apostrophe

You can use this method to add indents to your cell contents.

But it will be best to put it to use if you want to indent numeric values.

The spreadsheet will not let you add spaces in front of numbers, but with the apostrophe operator, you can easily do this.

In the cell in which you want to add the indent, insert the apostrophe operator, and press the Space key four times. Press Enter afterward.

The apostrophe will convert the number to text so it can add the spaces in front.

To copy the formatting to the other cells, simply drag down the cell.

Add Indents with an Apps Script

Numerous functions are provided in Google Sheets.

These functions can handle almost all types of operations. Nevertheless, there are one or two occasions where an operation requires something more tailor-made.

The apps script allows you to create custom functions that suit whatever special requirement is needed by your project.

You can use the apps scripts to create a function that automates repetitive tasks.

To access the apps script code editor window, go to the Extensions menu and click on Apps script.

function changeIndent() {
  var selectedRange = SpreadsheetApp.getActive().getActiveRange();
  var selectedValues = selectedRange.getValues();
  var selectedColumns = selectedRange.getWidth();
  var selectedRows = selectedRange.getHeight();

  for (i = 0; i < selectedRows; i++) {
    for (j = 0; j < selectedColumns; j++) {
      if (selectedValues[i][j].substring(0, 4) === "    ") {
        selectedRange.getCell(i + 1, j + 1).setValue(selectedValues[i][j].substring(4));
      } else {
        selectedRange.getCell(i + 1, j + 1).setValue("    " + selectedValues[i][j]);
      };
    };
  };
};

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Indents')
    .addItem('Add/Remove indents', 'changeIndent')
    .addToUi();
};

Copy and paste the above code into the code editor. Hit Save, then Run. Grant all the necessary permissions and refresh your spreadsheet window.

After the spreadsheet window refreshes, the code will create a new menu item will appear named Indents.

In the Indents submenu, you will find the Add/Remove indents command.

The Add/Remove indents command will add indents to the selected range or remove them if they already exist.

How to Remove Indents

Now that you’ve learned how to add indents, you can go on to see how you can remove indents from your spreadsheet.

These methods that will be discussed assume that indents have been applied using 4 space characters.

Remove Indents with Find and Replace

If you have manually inserted indents in your cells with 4 space characters then you can use the Find and replace feature to reverse the operation.

Go to the Edit menu option and select Find and replace to open the Find and replace dialogue box.

You can also use the keyboard shortcut Ctrl + H.

The indents in column A were added manually. Follow these steps to use Find and replace to remove them.

1. In the Find box, press the space bar four times.
2. Leave the Replace with box empty.
3. Change the Search parameter to This sheet.

When you’re done, click on Replace all. It will remove all the spaces in the column and replace it with nothing since a replacement wasn’t specified.

You can also follow these steps to remove indentation added using the Apostrophe method.

Remove Indent with the SUBSTITUTE Function

You can use the SUBSTITUTE function also remove indents.

= SUBSTITUTE ( B2, "    ", "" )

Using the above formula will allow you to remove indents from any data which contains leading space characters.

The function will search for 4 spaces and it will replace them with nothing.

Remove Indents with Paint Format

The Paint format tool allows you to copy formatting from one cell to another. You can use it to remove any indents based on format.

1. Convert the cell value into Plain text using the step described previously.

2. Select the cell already formatted as Plain text and click on the Paint format tool. The Paint format tool copies the formatting in the selected cell.

3. To paste the selected formatting, select and drag across the cells where you want to paste the formatting.

Conclusions

Adding indentation in your Google Sheets documents is sometimes required, but unfortunately, there is no built-in option available.

Thankfully indents can be achieved with several methods.

These methods discussed will serve your indentation needs in any situation.

For small datasets of no more than a couple of rows, you can use the Spacebar or Apostrophe method to add indents.

If indention is for many rows, you can use formats, apps script, or any of the functions described.

You can also easily remove the indents with the methods used in applying them.

Do you know of other methods for applying indentation? Let me know down in the comments section!