Bug 154449 - Calc should support hidden Named Ranges for better MSO compatibility
Summary: Calc should support hidden Named Ranges for better MSO compatibility
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) alpha0+
Hardware: All All
: medium enhancement
Assignee: Rafael Lima
Whiteboard: target:24.2.0 inReleaseNotes:24.2
Keywords: filter:xlsx
Depends on:
Blocks: ODF-spec Excel-Default-Names
  Show dependency treegraph
Reported: 2023-03-29 14:46 UTC by Rafael Lima
Modified: 2024-01-26 08:47 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

XLSX file with hidden named ranges (17.18 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2023-03-29 14:46 UTC, Rafael Lima

Note You need to log in before you can comment on or make changes to this bug.
Description Rafael Lima 2023-03-29 14:46:55 UTC
Created attachment 186290 [details]
XLSX file with hidden named ranges

Excel uses hidden named ranges to store info for some of its features (see f.i. the discussion in bug 38948 about how Excel saves its solver settings).

Users can also create hidden named ranges via VBA macros (but not via the UI), which is very useful for advanced users. See below how to create hidden named ranges in Excel:


However, in LO Calc, when we open a XLSX file with hidden named ranges, they are visible to the user. The reason is that we simply do not support hidden named ranges.

Open the attached XLSX file, which has 2 hidden named ranges (I also added 2 macros to hide/show them). If you open this file in Excel, the named ranges will be hidden, whereas in Calc you'll be able to see them in the UI.

This issue has become more important since bug 38948 has been fixed. Now Calc can stores solver settings to the file using named ranges (as in Excel), but we are unable to hide them to avoid showing users a long list of named ranges that should be hidden (which is what Excel does).

Also, when doing a round trip of hidden named ranges from Excel -> Calc -> Excel, the hidden named ranges will be visible in Excel at the end of the round trip, hence causing a compatibility problem.

I'm not sure how we should tackle this problem, but my initial suggestion would be to add the possibility of hiding a named ranges via the flag defined in XNamedRanges::addNewByName() method.


We could have a new HIDDEN flag in the NamedRangeFlag constant group.


The main issue is that ODF does not support the hidden property in <table:named-range> and <table:named-expression>.

Comment 1 Buovjaga 2023-04-11 11:26:04 UTC

Arch Linux 64-bit, X11
Version: (X86_64) / LibreOffice Community
Build ID: 8029a239cf294d06bb9f29aeb9b6897cb422a880
CPU threads: 8; OS: Linux 6.2; UI render: default; VCL: kf5 (cairo+xcb)
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Built on 11 April 2023
Comment 2 Rafael Lima 2023-08-14 00:19:53 UTC
Proposed WIP patch here:
Comment 3 Commit Notification 2023-10-01 11:02:30 UTC
Rafael Lima committed a patch related to this issue.
It has been pushed to "master":


tdf#154449 Add support for hidden named ranges

It will be available in 24.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.