Excel accounting format makes underlines just short of the width of the column. Can LO do that?

I have seen some solutions to get the - for a zero but none mention the length of the underline.

You ask for a feature of another application, which seems not to fulfill your requirements assuming users of LibreOffice know that feature and its shortcomings. That’s likely not the case with long-term (and hence experienced) users of LibreOffice. Please try to explain what you want to achieve, how your raw data look like and how you want 'em to look like. That’s a more promising approach to get a sound answer.

Excel 2010 Accounting format uses character U+002d which is just the - key in the numeric keypad or the dash (under the underscore) on the top row. The formatting is included in LO Calc at the bottom of the Number list,

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

You could replace the - with an em dash — U+2014 in the above format

Cheers, Al

The formatting is included in LO Calc at the bottom of the Number list

Certainly not. It may have been added to the default template you use, but it is not a default format of any locale data’s number formats, specifically not en-NZ. Also, it being listed under Number instead of Currency is because of the use of the simple "$" string instead of using a proper currency modifier, which for the NZD $ case would be [$$-1409] so the format code should be

_-[$$-1409]* #,##0.00_-;-[$$-1409]* #,##0.00_-;_-[$$-1409]* "-"??_-;_-@_-

@erAck Thank you for the correction. I must have copied it from Excel. I see now that it does say user-defined.

I would like to make the underscore make a line that is just short of the edges of the column rather than just under the number or description. Is there a way to do that? I’m an accountant so that type of underscore is handy for financial statements. I’ve used Excel but am new to LO Calc.

Did you try the formatting code that erAck supplied? Copy the code and then right click on a cell(s), select Format Cells > Numbers > User-defined, paste the code into the field Format Code, change the currency codes to your own currency, e.g. for USA change 1409 to 409. Click OK.

Click on the cell with your new format, then in the sidebar select Styles (F11), at the top right click on the icon with the tool tip New style from selection, in the dialog that opens type Accounting and OK out. You can now apply the style to any cells by selecting them and double-clicking Accounting in the sidebar

To single underline press Ctrl+U or click the underline icon. To double underline click on the arrow beside the sidebar underline icon and select the double underline ( or add the double underline to the toolbar icon by right-clicking, selecting customize, and ticking the double underline icon). Cheers, Al

I just tested the format code, and can see that not all $ signs are well aligned. Don’t know why.

I hesitate to suggest this as it might be frowned on, just add a ? at the beginning so that positive numbers have a space before them. The negatives values won’t stand out so much.

?_-[$$-1409]* #,##0.00_-;-[$$-1409]* #,##0.00_-;_-[$$-1409]* "-"??_-;_-@_-

change the currency codes to your own currency, e.g. for USA change 1409 to 409

That additional space for positive values adds even more oddity…

These placeholder space formats work best with monospaced fonts, otherwise with proportional fonts the different character widths get in the way.

I hesitated to mention it as the offset for negative numbers helps emphasise the difference, this is rather the point of this formatting, to help someone looking over the figures see differences. I personally wouldn’t have the space in front but some people prefer form over function.

As a simple comment: There is only a small difference in width between space and dash (U+002d) for most proportional fonts, especially the ones generally used in spreadsheets, so the difference is almost imperceptible.

Digging a little deeper following my previous comment, and using @erAck format code:

I get differences at zoom values of 100%, and many other % values; but not in the preview, nor in zoom values of 180%, 200%, 250%, 350%, 400%. Maybe operating system, or screen adapter?

image description
image description