3

In Excel 2013, I seem to have had the default indent reference width (i.e. the width of an indent value of "1") for left-aligned text somehow increase.

When I use a previously created worksheet, I can successfully apply an indent value of "1" in a cell to get the desired indent size, i.e. an indent of a single character. Note how, in the screen shot below, the indented text is shifted to the right by one character relative to the unindented cell above it.

enter image description here

However, when I now create a new worksheet, an indent of "1" shifts the text to the right by three characters, as shown in the screen shot below. Larger indents are relative to this, e.g. an indent of "2" shifts the text to the right by six characters, etc. (not shown).

enter image description here

In both cases, you can see in the screen shots that the font in that cell (and, as far as I can tell, the font elsewhere in the worksheet) is "Courier New", font size "11". I've looked through the "Format Cells" window, the document "Options", and elsewhere, and I can't seem to find a way to control what an indent of "1" means.

Note that cell indents cannot have decimal values; they must be integers. So I can't solve this problem by, e.g., applying an indent of "0.333". But even if I could indent it fractionally, that would not satisfy me. I want to be able to control what my indents are, in part to make formatting between documents consistent, but also just more generally to feel like I'm formatting the document in a rational way.

So how can I exert control over the default indent reference width?

  • I believe that the amount of indentation is based on the size of the font used by the "Normal" style, and not size the font used in the cell in question. Is it possible that the "Normal" style in your second workbook is not that same size as that in first? – Blackwood Jan 23 '18 at 19:19
  • @Blackwood, the Normal styles in the two workbooks are indeed different, but not in a way that explains the indentation discrepancy. From what I can tell, the Normal style in the old worksheet resets the text to Calibri font with font size 11 while in the new worksheet it resets the text to Courier New, also size 11. If I dig into the Normal style "Modify" options I can't see any other real differences. Switching either between Courier New and Calibri also doesn't fix the indentation differences so it's not something inherently different about the two fonts. – Andrew Willems Jan 23 '18 at 19:43
  • While I can't explain how the amount of indentation is calculated, a space in Courier New 11 is wider than a space in Calibri 11, so that could account for the indentation being greater when the normal font is Courier New. Can you reproduce the difference by creating two new workbooks, one with each of the two fonts as its normal font? – Blackwood Jan 23 '18 at 19:48
  • @Blackwood: Whoa. That seems to work. My current default Normal style is Courier New font and that produces what I'll call a wide indent. If I modify the Normal style to be Calibri, then the indent is what I'll call narrow. Moreoever, even if I now manually switch the font in that cell, or indeed in the whole worksheet, to Courier New, the narrow indent is maintained. Thus the indent size does indeed seem to be determined by the font choice in the Normal style. However, it's not simply the size of the space character, because the space character in Calibri is even smaller than in Courier New. – Andrew Willems Jan 23 '18 at 20:06
  • So, I think you've solved my problem, but I must confess that I still don't understand what's going on. Can you think of some thing in Calibri that is bigger/larger than the corresponding thing in Courier New if it's not the space character? Could it have something to do with the size of the tab character, which one cannot, of course, type into an Excel cell manually? Any further thoughts? In any case, if you write up your solution as an answer, I'll be more than happy to accept it. – Andrew Willems Jan 23 '18 at 20:09
1

The amount of the indent is affected by the size of the font used by the "Normal" style. If you change the normal style from using Calibri 11 to Courier New 11, the amount of the indent will increase, even in cells formatted with some other font or size.

So you can (somewhat) control the amount of indentation by choosing a different font for the normal style, and you can at least keep it consistent by using the same font for the normal style in different workbooks.

This is presumably related to the fact that spaces are wider in fixed fonts like Courier New than proportional fonts like Calibri. However, I don't know what the calculation is. My initial guess, that an indentation of 1 was the width of two spaces (or one decimal digit) turns out to be incorrect.

  • How did you know about this connection between the Normal style and the indent size? I've tried Googling this several different ways and I don't seem to find anything at all about that relationship. But, in any case, thanks very much, as I don't think I would have figured this out on my own without a lot of blood, sweat and tears. – Andrew Willems Jan 23 '18 at 20:26
  • @AndrewWillems I have come across other odd things that are affected by the size of the normal style's font, such as the default width of columns. When I saw your question, I guessed that it might be the culprit and verified that it did indeed have an effect. – Blackwood Jan 23 '18 at 20:30
  • Ahh. In fact, if you look at my two screen shots, even though I have manually changed the font to Courier New in both of them, the column widths (which are the defaults in both cases) are different in the two images. Your comment explains why. Again, thanks for your insight and experience. – Andrew Willems Jan 23 '18 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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