How to create a pivot table based on data from a named range?

[JimDeLaHunt]

I want to create a Pivot Table in LO Calc. The data is on tab Sheet1. There is a named Range for the cells on Sheet1 which have the data. I want the pivot table to be on Sheet3, a different tab. When I select a cell in Sheet3 and select menu item Insert… Pivot Table…, a dialogue box “Select a Source” appears. This dialogue box has three radio button options:

1. Named range, with a text entry field. This is disabled.
2. Current selection
3. Data source registered in Calc.

It seems that the “Named range” option is what I want. Why is it disabled? How can I make it enabled, and use it as the source for my pivot table?

Alternatively, is there a way to select the named range in Sheet1, then move to Sheet3, insert the pivot table, and have it use the selection on Sheet1? That does not work for me. I suspect that when I move to Sheet3, Calc decides that my “current selection” is a single cell in Sheet3.

The Creating Pivot Tables section in LibreOffice 26.2 Help does not mention the “Named range” option in the Select Source dialogue.

Observed on:

Version: 26.2.4.2 (AARCH64)
Build ID: 0229ac93fcf0d7cbc6376066c6f35021cef002dc
CPU threads: 10; OS: macOS 14.8.7; UI render: Skia/Metal; VCL: osx
Locale: en-CA (en_CA.UTF-8); UI: en-US
Calc: threaded

[Villeroy]

1. You have to create a named range first.
2. A named range is basically a named formula expression. For a pivot table you need a named range that is referenced by a fully absolute range address, such as $Sheet1.$A$1:$F$999 with 5 $.

IMHO, the feature has been implemented wrongly. A pivot table should be based on a database range rather than a named range.

[JimDeLaHunt]

Thank you @Villeroy for your prompt answer!

At first I din’t understand what you mean by, “You have to create a named range first.” When I tried to create my pivot table, I had already created a named range for the cells on Sheet1 which have the data. The “Named range” option in the Select a source dialogue with disabled anyway.

But then I realised: Calc offers two “Ranges”. One works and one doesn’t.

1. In the menu item Sheet… Named Ranges and Expressions > Define… there is a way to create one kind of range.
2. In the menu item Data… Define Range… there is a way to create a different kind of range. I some other areas this is called a “Database range”.

When I created a range using Sheet… Named Ranges and Expressions > Define… , that range caused the Select a source dialogue from **Insert… Pivot Table…" to become active. I was able to select that range.

So, the simple answer is: create the range using Sheet… Named Ranges and Expressions > Define…, rather than using Data… Define Range….

I find it confusing that these two different things are both named “Range” in English. The help docs for each could reduce the confusion by clarifying that both kinds of ranges exist, and might be confused for one another.