libreoffice-calc

Force LibreOffice to keep a specified number of decimals (not just hide them)


I want LibreOffice Calc to round all numbers to only n decimals.

When I change this option in Format->Cells, it only hides the extra digits. (Which becomes obvious by double-clicking the cells.)


Solution

  • Tools > Options > LibreOffice Calc > Calculate > Precision as shown forces calculations to use the displayed precision instead of the underlying value. This is rarely used (I had not heard of it before now) and may be confusing for whoever uses your spreadsheet. Normally, Format > Cells affects how values are displayed but not the actual calculations.

    For specific cells, use ROUND() as mentioned by @tohuwawohu, or a similar function such as TRUNC(). These formulas can easily be filled down to cover long lists of values.

    =ROUND(A1;2)
    =ROUND(A1;2) + ROUND(B1;2)
    

    To truncate all decimals after two places, go to Edit > Find and Replace.

    However, this won't prevent someone from entering values with more digits in the future.

    To force all entered numbers to be limited to a certain number of decimals, run a macro whenever the sheet gets updated. Write the macro to search for any numbers with many decimal places and round them. Such a brute force method is likely to make the spreadsheet difficult to use however.

    Related post answered by a dev: https://ask.libreoffice.org/t/why-doesnt-calc-limit-decimals-when-set-to/77411/2