Google sheets is a web-based spreadsheet application and part of the Google Docs Editors Suite.
It has come into prominence mainly due to the fact that it is free, but also because it allows several users to share and simultaneously edit the same document over the internet.
Despite its popularity and rich collection of features, no application is perfect and Google sheets is no exception.
One area where Google sheets is sorely lacking is indentation.
Indentation is used to make information more readable or to help highlight a hierarchical schema in the data.
Microsoft Excel offers a very simple and convenient indent option under its Alignment tab, which allows the user to quickly and easily increase or decrease the indentation of any cell or cells.
In contrast, Google sheets offers no such option, and users are forced to use workarounds to perform such a simple task.
In this article, we’ll be taking a look at a couple of relatively simple ways to do this.
We’ll be using the following example sheet which contains a list of addresses.
The sheet has been sorted on the county column, and the requirement is to indent all county names except for the very first occurrence of each name.
The result should look like the following.
Leading Space Character
Possibly the simplest way of indenting is to use leading space characters in each cell.
However, this is a high maintenance solution, especially if there are a lot of cells, where it becomes very time consuming going through each cell.
One restriction with this method is that the cells need to be formatted as Plain text.
Cells which have any of the numeric formats, including Date and Time, have their content right aligned by default, as in the case of the zip column in our example sheet.
But even if you change the alignment to left aligned for a cell with numeric formatting, any leading white space will still be ignored.
In the case of the zip column in our example sheet, we would lose nothing if we converted the column to Plain text format.
Date and Time formats can also be converted to Plain text without being compromised.
In fact, numeric cells converted to Plain text will still work in calculations and formulas.
To get back to our original aim, we are going to indent the county column cells using the space character.
For the purposes of demonstration, since a space is a non-printing character, the underscore has been used instead, so that it is easier to discern.
But almost any character could be used. In the snapshot above, we see the contents of cells G2 to G4 contain the name Ada.
Our requirement stipulates that the first of the cells containing the county name Ada, should be left as is, and the other two cells, G3 and G4, be indented.
The same rule has been applied to cell G6 which is as it was, while cells G7 to G11 have been indented.
Our particular example sheet contains 500 rows, so it would take some time to go through and add spaces to each of the county names.
What makes this even worse, is that if we wanted to increase or decrease the indenting later on, we would again need to go through all the cells in the column, one by one.
Custom Number Format
Surely you say, there must be an easier way.
The previous solution is quick and easy as long as there are only a few cells that need to be indented.
Even so, there is indeed an easier way, and it involves using the Custom number format option.
We start by selecting the entire county column.
Next, we need to deselect the first occurrence of each name so that we are in keeping with our requirements.
To do this, hold down the Ctrl key on the keyboard, and left mouse click on each cell to be deselected.
We are now ready to apply our indentation.
The good thing about this method as opposed to the previous, is that we don’t need to go through, applying the indent one cell at a time.
From the Google sheets menu, select Format > Number > Custom number format.
This will open the Custom number formats dialog box.
We will be entering our custom format in the very first input field at the top of the dialog box.
Here again, the underscore character has been used instead of the non printing space character so that it is clearly visible.
However, using an underscore in the Custom number formats entry, will also produce a space in the resulting output.
Hence, you can use either spaces or underscores, whichever is more convenient.
In our example above, there are four underscores followed by the @ character.
The @ character is required and indicates where the actual contents of the cell will be placed, which in this case is after the spaces.
Unfortunately, control characters like tabs cannot be used, so if you need a large indentation, add more space (or underscore) characters.
When satisfied, click on the Apply button, and you should now see your cell contents indented.
As was stated earlier, the good news with this approach is that if you decide you want to change the size of the indentation, you just need to select the cells again, and repeat the process.
Conclusion
While there is no dedicated indentation option in Google sheets as there is in Microsoft Excel, with a little effort using the Custom number format option, it is possible to selectively indent cell content.
