SHEETS Function (LibreOffice Calc)

SHEETS returns the number of sheets in the reference supplied to it. With no argument, it counts all sheets in the current spreadsheet document. With a reference on only one sheet, it returns 1, even if the document holds dozens of other tabs. With a 3D reference spanning multiple sheets, it counts the sheets in that interval, inclusive of both endpoints. Hidden sheets are included, so SHEETS() can return a larger number than the tabs currently visible along the bottom of the Calc window. LibreOffice Calc Help — Spreadsheet Functions, SHEETS; OASIS OpenDocument 1.4 OpenFormula, §6.13.32, consulted 2026-10-13.

Common confusion: SHEETS() counts worksheets; SHEETS(A1:C20) returns 1 because the reference is still on a single sheet. It does not count the rows, columns, populated cells, visible cells, or number of spreadsheet files. For dimensions within a sheet, use ROWS or COLUMNS.

=SHEETS([Reference])

Square brackets denote an optional argument in the Help syntax; do not type them. There are no other documented arguments or defaults. The official Help examples use =SHEETS(Sheet1.A1:Sheet3.G12) to count three sheets in a continuous run. The OpenFormula specification requires a reference, not a text string representing a sheet name; the related SHEET("Name") function has different accepted argument types. LibreOffice Help — SHEETS; OpenFormula §6.13.32, consulted 2026-10-13.

These examples use Calc A1 reference notation: a dot between the sheet name and cell (Archive.A1) and single quotes around a name containing spaces ('Sales 2026'.A1). English function names and semicolon argument separators were used in the tested Calc installation. Your displayed names, separators or reference style can vary with locale/options; in particular do not assume Excel’s ! separator is valid in Calc’s default A1 syntax. Calc Help — Referencing a Cell in Another Sheet, consulted 2026-10-13.

Enter this in any empty cell:

=SHEETS()

In the four-tab example below, the result is 4. The result is the same when the formula is placed on the last tab, because no-argument SHEETS() always counts the document, not the current tab’s position. Both placements were native-tested on October 13, 2026. For the position of a particular tab, use SHEET instead.

Create a new Calc document. Rename its first tab Overview, and add Sales 2026, HiddenData, and Archive in that order. Put 123 in A1 of each sheet so the ranges are easy to locate, then hide HiddenData (do not delete it). The physical sheet order is:

On Overview, enter the following formulas in otherwise empty cells. All of these exact example formulas were native-tested in LibreOffice Calc 25.2.3.2 on October 13, 2026.

The contents of those cells do not determine the number of sheets. Even if all referenced cells are empty, the referenced sheet span remains the same. This follows from the reference-counting meaning in the Help and OpenFormula; the separate experiment here used 123 in each sheet as a convenient visible fixture. OpenFormula §6.13.32, consulted 2026-10-13.

The argument Overview.A1:Archive.D5 identifies a 3D rectangular reference from cell A1 on Overview through cell D5 on Archive across consecutive sheets. The count is determined by the sheet endpoints and their current order. Changing A1 to A500 does not create more sheets. On a local rectangular range such as A1:Z500, SHEETS still returns 1. On a range that spans four tabs, it returns 4 even if the middle sheet is hidden. LibreOffice Help — SHEETS; OpenFormula §6.13.32, consulted 2026-10-13.

Advanced scenario: identify a template’s sheet count

Suppose a workbook template is intended to contain four tabs. You can place this formula on its Overview tab:

=IF(SHEETS()=4;"Expected sheet count";"Check workbook tabs")

Expected output in the four-tab fixture: Expected sheet count. If a fifth tab is inserted, the logical evaluation changes to Check workbook tabs. The native mutation experiment separately verified SHEETS() going from 4 to 5 when NewTab was inserted. The exact IF-wrapped formula is a documented derivation, not an independently executed assertion in the test workbook. Use IF for conditional messages.

This check verifies only the count, not the right names, correct order, formulas or permissions. A workbook with a missing sheet and a different extra sheet can still return 4. For template audits, combine the count with checks based on expected names or reference availability. Do not treat the result as a cryptographic or structural integrity check.

Advanced scenario: count a bounded phase of a project

If tabs are ordered from Overview through Archive, use:

=SHEETS(Overview.A1:Archive.D5)

The four-tab fixture produced 4. In a separate native tab-moving experiment, moving Archive from the last position to the second changed the same formula’s result from 5 to 2, while SHEETS() stayed 5. The range includes the sheets currently between those two endpoints. This distinction matters for repeatable monthly workbooks: a range that once covered January–December might cover a different number of tabs after users rearrange them. For fixed reporting periods, establish clear template rules about tab order.

Use with SHEET to explain the difference

In the fixture:

=SHEET("Archive")    → 4
=SHEETS()             → 4
=SHEETS(Archive.A1)   → 1

The matching first two values are coincidental: the target tab is the last in the initial four-sheet file. The third value is 1 because it is a single-sheet reference. SHEET asks which tab; SHEETS asks how many. Those three formulas are independently native-tested in the saved workbook, using a four-tab fixture with HiddenData hidden.

The 504 observations came from native Calc execution, not a generic spreadsheet approximation. LibreOffice Help — Error Codes identifies 504 as a parameter-list error (consulted 2026-10-13). Exact error formatting, argument coercion and permissive multi-range behaviors can change across releases, application preferences and import routes.

Hidden-sheet caveat: Hidden tabs are included in both SHEETS() and SHEETS(Overview.A1:Archive.D5) when they fall inside the selected range. Both the OpenFormula specification and the native fixture agree. This does not mean SHEETS can reveal hidden sheets’ contents or their names. It returns a count.

The standard function is named SHEETS in LibreOffice Help and OpenFormula §6.13.32. It is not an alias for singular SHEET; their outputs and argument contracts differ. No independently verified alternate LibreOffice function name was found to justify a duplicate page. The function is defined by OpenFormula 1.4 for ODF compatibility, with a single optional reference argument and a numeric result at least 1. LibreOffice Help — SHEETS; OASIS OpenFormula §6.13.32, consulted 2026-10-13.

Direct execution scope: LibreOffice Calc 25.2.3.2 on Linux, with formulas entered natively using UNO (not imported from an Excel workbook); ODS file saved/reopened and validated. The latest English Help page consulted on October 13, 2026 identified itself as LibreOffice 26.8 Help; that is documentation context only, not a test in 26.8. No tests were run in Windows, macOS, Microsoft Excel, Google Sheets, non-English locales, or older LibreOffice versions. No claim about their exact error messages or text coercion is warranted.

• SHEET — numerical position of the specified tab or current sheet.
• ROWS and COLUMNS — number of rows/columns in a referenced range.
• ROW and COLUMN — ordinal position in the grid, not number of worksheets.
• AREAS — how many disjoint reference areas are present, not worksheet count.
• INDIRECT — construct and resolve a reference from text.
• IF — turn a count into a simple quality-control message.

1. Meaning, optional argument/default, sample 3D range: LibreOffice Calc Help — Spreadsheet Functions (SHEETS), consulted 2026-10-13.
2. Required result, included hidden sheets, reference syntax and external-link restriction: OASIS OpenDocument 1.4 — OpenFormula §6.13.32 SHEETS, consulted 2026-10-13.
3. Calc sheet reference notation and quotes: LibreOffice Help — Referencing a Cell in Another Sheet, consulted 2026-10-13.
4. Numeric error codes: LibreOffice Help — Error Codes, consulted 2026-10-13.
5. Native evidence: saved dated test records and supporting ODS/script artifacts. Combined across SHEET and SHEETS, 25/25 numeric fixture assertions passed, with 7 separately classified diagnostics. No GUI clicks or Excel execution are claimed.