To view ALL formula on Spreadsheet


New to LibreOffice, so please forgive the simple question !!

It’s possible in Excel to view all formula on a sheet by doing :-

    1. In an empty cell, type = (equal sign).
    1. Click the Select All button (to the left of cell A1).
    1. Select the cell, and on the Formulas tab, in the Formula Auditing group, click Trace Precedents Button image twice.

I’ve managed to find the ‘Tools’ ‘Detective’ ‘Trace Precedents’, but can’t work out how to display all formula on the sheet. I’m needing this to ensure the formulae in an XLS file is going to be stored in an ODS correctly

Many thanks

Try setting the display formula option.

In the menu “Tools”, “Options…”, expand “LibreOffice Calc”, select “View” and then under the Display settings enable “Formulae”.

Edit: Adding macro which shows precedents of every cell in the range selected when the macro is executed. This is only a quick attempt and probably in need of some optimisation.

Do NOT select every cell of the worksheet as this will take a very long time as it includes all unused cells.

Sub TraceRangePrecedents()

	Dim oRange As Variant
	Dim oRangeAddress As Variant
	Dim oController As Variant
	Dim oDocument As Object
	Dim oDispatcher As Object
	Dim ic As Integer
	Dim ir As Long
	Dim sCell As String
	oRange = ThisComponent.CurrentSelection
	oRangeAddress = oRange.RangeAddress
	oController = ThisComponent.CurrentController
	oDocument   = oController.Frame
	oDispatcher = createUnoService("")
	For ic = oRangeAddress.StartColumn To oRangeAddress.EndColumn
		For ir = oRangeAddress.StartRow To oRangeAddress.EndRow, ir))
			oDispatcher.executeDispatch(oDocument, ".uno:ShowPrecedents", "", 0, Array())
		Next ir
	Next ic
End Sub

Many thanks for this, but I was hoping to have the equivalent of Excel where there are blue lines going to the precedents, but this will certainly help me

Use shortcut CTRL + F10 to toggle view of formulas

Doesn’t seem to work for me, selects File in menu and then I can navigate the menu with arrow keys. Same with pressing F10 or Alt key. Suspect this key combination might be used by system. Windows 8.1 with LibreOffice 64 bit.


I know this is not what you expect (display of blue lines auditing for all cells in one command) but a simple command might help you in diagnosing: ViewValue Highlighting (Ctrl+F8)

Text cells are formatted in black, number cells in blue, protected cells are shown with light grey background, no matter how their display is formatted, and formulas are formatted in green.



In Libre Calc 5.4 there is a command View | Show Formula you can click and that will show all formulas. They aren’t colored nicely, but they show up.