Let’s see how to record Macros to get increase and decrease Indent in Google Sheets. It, the Macro based indentation, has some pros and cons. I’ll come to that.
Indentation is a formatting style in word processing apps including Google Docs. Some of the Spreadsheet apps included this text formatting.
We normally use Indent in Excel to increase the distance of text from its left side border. Of course, as an alternative, we can tap the space bar before entering the text in a cell.
But it will make issues in lots of formulas based on the functions like Vlookup, Sumif, Match, Query, etc. as the formulas won’t be able to identify/match the indented text based on criteria.
Must Read: Google Sheets Function Guide.
Indentation (text) doesn’t add any white space and won’t make any impact on formulas. The length of the indented text will be the same before and after formatting it.
In my Excel days, indentation was one of my favorite text formatting tools. I didn’t find the Indent useful to apply to numbers.
I used to single indent text columns as it, in my personal opinion, looks neat in printouts.
Unfortunately, Google Sheets doesn’t have this feature so far. So I am using Macros to get Increase and Decrease Indent in Google Sheets.
I wish Google will introduce the Excel similar Increase and Decrease Indent buttons to Google Sheets.
Increase Indent (example) in Google Sheets:
Decrease Indent (example) in Google Sheets:
Pros and Cons of the Macro Based Indentation in Google Sheets
With Macro we can record Indent UI interactions in Google Sheets. Once recorded we can run the recorded Indentation Macro using the Tools menu or using the assigned shortcut that starts with Ctrl+Alt+Shift.
The Macro in Google Sheets has its plus and minuses that are also applicable to our Macro based Indent in Google Sheets.
Here are the pros and cons of using a macro-based Increase decrease Indent in Google Sheets.
Pros
- Easy to create and apply custom Indentation in Google Sheets.
- It doesn’t add white spaces. You can test an Indent applied text (later) with the LEN or LEFT functions.
- It’s easy to apply Increase as well as Decrease Indent from the Tools menu > Macros.
- You can also use keyboard shortcuts.
- It’s possible to format a single cell, a range, or even two or more distant ranges with an Increase/Decrease Indent.
Cons
- Slow processing compared to normal menu/shortcuts.
- Only available within the created Spreadsheet (workaround below).
Among the above cons, the last point is the major issue in using the increase/decrease Indent in Google Sheets. But normally we apply formatting to new files, right? So, I guess, it might not be an issue for many.
Workaround to Get the Recorded Macros in All the New Files in Google Sheets
Straightaway to the steps.
Set the indentation macro(s) in a new Google Sheets file and name it (name the file) as ‘Draft’.
Next time when you want to create a new file in Google Sheets open the file ‘Draft’ and go to the menu File > Make a copy.
In that copy, add a new tab and then delete the existing tabs. The Indent macros will be available in that copied file. Use it as your new file.
How to Get Increase Indent in Google Sheets
As I have already mentioned, we are going to use the Macro feature to get Indent in Google Sheets.
Let’s start recording the required Macros. I am recording 4 macros. Each macro will increase the indentation by one space each. The same we can use to decrease the indentation.
I’ll only explain the first Macro. You may follow the same steps to create other Macros. Here we go!
- Click on any blank cell or a cell containing any text.
- Navigate to Record macro (Tools > Macros > Record macro).
- Just enable the ‘Relative reference’ (do not Save or Cancel).
- Navigate to the menu command called Custom number formats (Format menu > Number > More formats > Custom number formats).
- Click on the blank field there, then tap spacebar one time and type
@.
- Click on the ‘Apply’ button to save the new custom number format for Indent in Google Sheets.
- The Macro is recording the UI, right? Click ‘Save’ on the Macro tiny window.
- Name the Macro as ‘Indent 1’ and specify the number 1 to use the shortcut Ctrl+Alt+Shift+1.
- Again ‘Save’.
We have created/recorded a Macro and shortcut to indent texts and numbers in Google Sheets.
We need to create three more Macros to get more indentation. I’ll come to that. Let’s first test the just created Indent.
Testing Macro and Assigned Shortcut
In any cell type any text for example ‘Info Inspired’ in cell B2. Then go the Tools menu and select Macros > Indent 1.
You are running the Macro for the first time. So you must authorize Sheets to run the Macro that you have just recorded.
Follow the onscreen instructions.
- A script attached to this document needs your permission to run – Continue.
- Choose an account – Select your account.
- Wants to access your Google Account – Allow.
That’s it. The indentation will be applied to the text ‘Info Inspired’ in cell B2.
Adding More Indent in Google Sheets
Just follow the above steps (except the authorization steps under the ‘Testing Macro and Assigned Shortcut’). Some changes are also required in the steps. What are they?
Macro names should be Indent 2, Indent 3 and Indent 4 and use the numbers 2, 3, and 4 for the shortcuts respectively (see the third screenshot above).
Also instead of @(a single white space and @) use the custom number format as follows.
1. Indent 2: @ (two white spaces then @).
2. Indent 3: @ (three white spaces then @).
3. Indent 4: @ (four white spaces then @).
Testing Multiple Indent Shortcuts and Macros in Google Sheets
We have already created the Indent Macro menu items and shortcut keys. To format text as per the indent do as follows.
Select the cell(s) for example A4:A6 and go to the menu Tools > Macros and click on the required Indent menu command for example Indent 4.
How to Indent Texts in Two Ranges at a time That Are Not Adjoining?
For example, I am applying Indent in Google Sheets to the range A4:A6 and A8:A10.
First I should click on cell A4, press the Shift key and click on cell A6. Then press the Ctrl key, hold it and click on cell A8 and drag to A10.
Press Ctrl+Alt+Shift+4 or select Indent 4 from the Tools (menu).
The above are the steps to get an Indent format in Google Sheets.
How to Get Decrease Indent in Google Sheets
To remove Indent formatting, select the cells and click Format > Number > Automatic.
What about decreasing Indent in Google Sheets?
To Decrease Indent there are no special steps to follow. In the just above example, I have used Indent 4 to add 4 white space equal length of spaces to the left (prefix) of texts.
To Decrease Indent, select the above cells (Indent 4 applied cells) and select a lower Indent like Indent 1, Indent 2 or Indent 3.
Test Indent Formatted Text with Len or Left
Type “Test” in cell A1 (without double quotes) and use the following formula in cell B1.
=len(A1)This LEN will return 4 which is the length (number of characters) of the text/string. Now apply Indent 2 (Ctrl+Alt+Shift+2) to cell A1.
The LEN result will be the same # 4 not # 6. What does that mean?
The above Indent workaround in Google Sheets is only formatting the text similar to the Increase Indent in Excel, not any physical space (character) added.
Number Formatting? Things to Remeber
Actually, the Increase/decrease Indent in Google Sheets are for text formatting. It will work with numbers too, but not recommended. Why?
Using the ISNUMBER function test an Indent applied cell which contains a number. The formula would return FALSE.
The reason the Indent changes the number formatting to text.
I have formatted cell A8:A10 using Indent 3. The cells were containing the numbers 50, 100, and 150 respectively.
The following SUM would return the result as 0.
=sum(A8:A10)How do I correct the formula then?
=sum(ArrayFormula(A8:A10*1))That’s all. Enjoy!
You May Like: Google Sheets: Macro Based Random Name Picker.
