Bug 88793 - EDITING, subtotals don't apply formatting
Summary: EDITING, subtotals don't apply formatting
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Whiteboard: target:5.3.0
Keywords: bibisected, bisected, regression
: 92233 (view as bug list)
Depends on:
Blocks: Function-Subtotal
  Show dependency treegraph
Reported: 2015-01-25 20:26 UTC by Robert Gonzalez MX
Modified: 2022-07-09 03:35 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

Test file (71.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-01-25 20:26 UTC, Robert Gonzalez MX
Screenshot (133.48 KB, image/png)
2015-01-25 21:34 UTC, m_a_riosv
Screenshot (65.74 KB, image/png)
2016-06-09 16:30 UTC, Robert Gonzalez MX
Subtotals screenshot (106.12 KB, image/png)
2016-08-11 21:03 UTC, Robert Gonzalez MX
Subtotals screenshot (138.80 KB, image/png)
2016-08-23 20:46 UTC, Robert Gonzalez MX
screenshot_Linux (179.65 KB, image/png)
2017-04-27 08:22 UTC, Arul Michael
selecting the option group by group (237.24 KB, image/png)
2017-07-25 16:46 UTC, Xavier Van Wijmeersch
without selecting group by group (180.08 KB, image/png)
2017-07-25 16:50 UTC, Xavier Van Wijmeersch
Test file with previous versions (49.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-04-29 17:03 UTC, Robert Gonzalez MX
Test file created with LO 6.1 apha1 (19.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-04-29 17:04 UTC, Robert Gonzalez MX
Descriptions, screenshots and steps (627.04 KB, application/vnd.oasis.opendocument.text)
2019-09-24 18:21 UTC, Robert Gonzalez MX

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Gonzalez MX 2015-01-25 20:26:37 UTC
Created attachment 112795 [details]
Test file

In LO on Windows 8.1 when applying subtotals it doesn't perform the include formatting option.

Steps to reproduce:

Open the test file
Go to cell A1
Select form the menu Data
Select Option Subtotals
In the first group select Group by: Group
Select in the calculate subtotals for: the months 
use the function sum
Select tab options
Select include formats
apply with OK

The result of the subtotals doesn't have the formatting
Comment 1 m_a_riosv 2015-01-25 21:34:50 UTC Comment hidden (obsolete)
Comment 2 m_a_riosv 2015-01-25 21:47:47 UTC
Sorry @Robert too quick.

I see now.

Version: Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
Version: Build ID: 1fc1d0d7d38bb16832d0d78d78682ed2d5f8716f
 TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2015-01-08_03:10:08
Version: Build ID: db4be3fee9369d8a0bb2df07c25564f19eb564ed
 TinderBox: Win-x86@51-TDF, Branch:libreoffice-4-3, Time: 2015-01-23_21:42:55

Crash as already reported with:
Version: Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7
Version: Build ID: cd2434655c94814598daa8105cab0af314a6adf7
 TinderBox: Win-x86@42, Branch:libreoffice-4-4, Time: 2015-01-23_02:40:36
Version: Build ID: e3c0025461bdf55e62a308a76c3aa0a35109f076
 TinderBox: Win-x86@39, Branch:master, Time: 2015-01-24_02:19:31

Last working for me:
Version (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24)
Comment 3 Cor Nouws 2015-02-03 10:05:42 UTC Comment hidden (obsolete)
Comment 4 Matthew Francis 2015-02-12 04:47:25 UTC
The subtotals lose their formatting as of the below commit.

Adding Cc: to markus.mohrhard@googlemail.com; Could you take a look at this? Thanks

 57efd69c22e2c6f5cb4d057345644b6e07a62d48 is the first bad commit
commit 57efd69c22e2c6f5cb4d057345644b6e07a62d48
Author: Markus Mohrhard <markus.mohrhard@googlemail.com>
Date:   Fri May 24 22:41:44 2013 +0200

    remove inherited number formats, related fdo#60215
    Change-Id: I23d5e1b3baeb1499ada1fba1665027bdbe3fbb87
Comment 5 Robert Gonzalez MX 2015-05-24 00:21:47 UTC

Reproducible with Version:
Build ID: 0a16c3dda4150008d9be6f24cbd15ac198d116d3
Locale: es-MX (es_MX) on windows 8

I think the option of formatting should be independent from the pre-sorting.
The option of include formatting is only selectable when the pre-sorting option is enabled.
Comment 6 m_a_riosv 2015-06-21 22:18:36 UTC
*** Bug 92233 has been marked as a duplicate of this bug. ***
Comment 7 Robinson Tryon (qubit) 2015-12-13 11:12:16 UTC Comment hidden (obsolete)
Comment 8 Robert Gonzalez MX 2016-06-09 16:29:45 UTC Comment hidden (obsolete)
Comment 9 Robert Gonzalez MX 2016-06-09 16:30:56 UTC Comment hidden (obsolete)
Comment 10 Commit Notification 2016-07-29 23:31:00 UTC
Arul Michael committed a patch related to this issue.
It has been pushed to "master":


tdf#88793 EDITING, subtotals don't apply formatting

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://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.
Comment 11 Robert Gonzalez MX 2016-08-11 21:02:16 UTC

I just tested it with Version:
Build ID: f471c55378290fb8dc3571c74cc6abe90d81c46c
CPU Threads: 8; OS Version: Windows 6.2; UI Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-08-11_08:44:29
Locale: es-MX (es_MX); Calc: group
On Windows 10

And is not fixed yet. I add a screenshot of the result.
Comment 12 Robert Gonzalez MX 2016-08-11 21:03:15 UTC
Created attachment 126764 [details]
Subtotals screenshot
Comment 13 Robert Gonzalez MX 2016-08-23 20:45:51 UTC Comment hidden (obsolete)
Comment 14 Robert Gonzalez MX 2016-08-23 20:46:30 UTC
Created attachment 126993 [details]
Subtotals screenshot
Comment 15 m_a_riosv 2016-08-23 21:37:02 UTC
Looks as if Subtotal follow the format only with a standard format.

i.e. with the sample file, selecting Spanish (Mexico) as language for locale setting (Menu/Tools/Options/Language settings/Language/)
seems to work fine.
or selecting a % format without decimals, results in a % format with two decimals.
Comment 16 Xisco Faulí 2016-09-10 22:37:12 UTC Comment hidden (obsolete)
Comment 17 Xisco Faulí 2016-09-26 15:13:39 UTC Comment hidden (obsolete)
Comment 18 Robert Gonzalez MX 2016-11-13 17:55:40 UTC

I have tested this bug with Calc Version:
Build ID: d54a8868f08a7b39642414cf2c8ef2f228f780cf
CPU Threads: 8; OS Version: Windows 6.2; UI Render: default; 
Locale: es-MX (es_MX); Calc: group
on Windows 10 and XP SP3

And still reproducible.

I have changed the locale settings to English (USA) and the default language for documents too and still reproduces.

Also tested inserting the subtotal function and in this way the formatting is applied.

Steps to test:

insert a whole row at row 5
select the whole new row 5 
clear direct formatting to ensure there is no formatting
go to D5
insert function with the fx button in the formula toolbar o with the menu insert function or ctrl F2
seek for the subtotal function in the wizard
click next and in the function field type 9 (sum)
in the range field type D2:D4 then OK
The result displays formatted

Also tested with Version:
Build ID: 4ea62fe8a1194bca3bee5048832456c3559dcb8e
CPU Threads: 8; OS Version: Windows 6.2; UI Render: default; Layout Engine: new; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-11-05_23:04:01
Locale: es-MX (es_MX); Calc: group


Build ID: f20df23ad9380d19dfb64b51d943109bd3895efe
CPU Threads: 8; OS Version: Windows 6.2; UI Render: default; Layout Engine: new; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-11-12_23:23:49
Locale: es-MX (es_MX); Calc: group
Comment 19 Arul Michael 2017-04-27 08:22:53 UTC
Created attachment 132884 [details]

I could not recreate the issue in my linux machine with the master build. Please see the screenshot attached following the steps given in comment 1.
Comment 20 Xavier Van Wijmeersch 2017-07-25 16:46:48 UTC
Created attachment 134844 [details]
selecting the option group by group

this screenshot shows zero's in column B where there need the groupsname
Comment 21 Xavier Van Wijmeersch 2017-07-25 16:50:11 UTC
Created attachment 134845 [details]
without selecting group by group

Did two test one following the description, that one give the zero's in column B
second test i did not select group by group and the group name is given in column B
Comment 22 Robert Gonzalez MX 2018-04-29 16:55:29 UTC

Tested with Version: (x64)
Build ID: cb47f0d320994e001bc38dc2ee9b7d957b15e6ab
CPU threads: 8; OS: Windows 10.0; UI render: GL; 
Locale: es-MX (es_MX); Calc: CL

I have made two tests, using Data - Subtotals, which is the case for this bug, and another test using the function subtotals.

What I find out using Data - Subtotals is that in the new rows inserted by the grouping, the cell style used for this is "Result" and is used in all the cells with the subtotal results.
But if I apply manually the cell style "Result2" to one of the subtotals results, the correct formatting is applied.

These styles are not present in a new spreadsheet, so I guess that they are created when the Data - Subtotals procedure is executed.

Maybe in this procedure, the style applied to the grouping is "Result" and the style to apply to the subtotal results should be "Result2".

Actually, creating a new spreadsheet in 6.1, and typing all the test data again, and performing the Data - Subtotals with the menu, it doesn't create the "Result" and "Result2" styles. Looking for them in the styles navigator window, they are not present nor hidden.

When selecting the cells of the typed data, the "default" style is highlighted in the styles window,  but when a cell in the subtotals row is selected or clicked, nothing is highlighted in the styles window, and there are no "hidden" styles.

Also, the subtotals results don't apply the same formatting from the data used.
Comment 23 Robert Gonzalez MX 2018-04-29 17:03:31 UTC
Created attachment 141764 [details]
Test file with previous versions
Comment 24 Robert Gonzalez MX 2018-04-29 17:04:14 UTC
Created attachment 141765 [details]
Test file created with LO 6.1 apha1
Comment 25 Robert Gonzalez MX 2018-04-29 17:17:13 UTC
When opening the file created with LO 6.1 apha1 in LO x64 all the cells including the ones created by the Data - Subtotals have the default style and the subtotals results have the correct style, but they don't have the bold italics highlight.

Redoing the Data - Subtotals procedure, displays the message "Delete data? yes o no". Answering yes refresh the subtotals losing the formatting. But the bold italics highlight is not made, and the "Result" and Result2" styles are not created either.
Comment 26 QA Administrators 2019-09-02 09:20:23 UTC Comment hidden (obsolete)
Comment 27 Robert Gonzalez MX 2019-09-13 02:39:50 UTC
I have tested this bug with Version:
Build ID: b79626edf0065ac373bd1df5c28bd630b4424273
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5; 
Locale: es-MX (es_MX.UTF-8); UI-Language: en-US
Calc: threaded

In the first test file I sent "Bug subtotals test1.ods", still the subtotals doesn't display the format correctly. It has the "Result" cell style, that has the bold and italics font display, but in the numbers it has the standard format.

In the second test file "2018 04 29 Test subtotals.ods", the subtotals display correctly, with bold, italics and currency. But reviewing the numbers category, in the "Result" cell style, it has the standard format as well. So I think the currency format is in some way inherited from the format of the data cells.
In this file the subtotals seems to work as expected.

In the third test file "2018 04 29 New subtotals test.ods", that was created new with version 6.1, the subtotals are displayed with the currency format, but not the bold and italic format. And also the "Result" and "Result2" cell styles were not created.

Creating a new calc file with LO doesn't create this cell styles also.

Also I tested with the UI in Spanish and English, with the same results, because there were some problems with the cell styles when the UI changed in English/Spanish.

Maybe if the "Result" and "Result2" cell styles where reincorporated, to the default initial styles when a new spreadsheet is created, the issue will resolve or reduce its presence. As I see it in the 2nd and 3rd test file.

In the 1st test file maybe I messed up with the "Result" style doing my testings.

I Hope this is helpful.
Comment 28 Timur 2019-09-13 13:49:36 UTC
Robert, thank you for following your bug report. 
But please make it simple for testers and explain clearly: what is experienced and expected result. 
You say "The result of the subtotals doesn't have the formatting" but I see formatting on screenshots. 
Please write on screenshots and maybe make some manually as you expect (in other color to be seen). 
Please then test with new profile (you may rename "user" folder down in %AppData%\LibreOffice\).
Comment 29 Robert Gonzalez MX 2019-09-24 18:19:58 UTC
Hi there.

I have made a writer file with screenshots and descriptions. I made new tests with LO and found that maybe if the “Result” and “Result2” cell styles where reincorporated, to the default initial styles when a new spreadsheet is created, the issue will resolve or reduce its presence.

Descriptions and test steps are in the writer file.
Comment 30 Robert Gonzalez MX 2019-09-24 18:21:38 UTC
Created attachment 154452 [details]
Descriptions, screenshots and steps
Comment 31 Brian McKee 2020-07-08 15:28:38 UTC
I can confirm this issue is present in 6.0.6

If you create the missing Result style BEFORE you insert subtotals, it works.

Here's a different simple way to demonstrate.

Step One
Create any table of random stuff.  e.g.  

| Title | Head 1 | Head 2 |
| A     |  1     |  2     |
| A     |  1     |  2     |
| B     |  1     |  2     |
| B     |  1     |  2     |
| C     |  1     |  2     |
| C     |  1     |  2     |

Step Two
Format → Spreadsheet Theme    
Pick any of them, like say Autumn Leaves

Step Three
Data → Subtotals
Group By Title,  Sum Head 1 and Head 2

Observe the formatting on the 'Result' lines - it will have a grey background.

Now, open a new spreadsheet and do Step One, then Step *Three*, then Step *Two*

See how the Result lines don't have any formatting this time?

Alternatively, you can create a new style called 'Result', and as long as you do that BEFORE you insert subtotals, it works as expected....

As a workaround, I created a blank spreadsheet, and in that spreadsheet created a style called 'Result'  that had the formatting I wanted.

I then saved that spreadsheet as an .odt, and chose that spreadsheet as my default template.  (see https://documentation.libreoffice.org/assets/Uploads/Documentation/en/GS5.2/HTML/GS5203-StylesAndTemplates.html#__RefHeading__7025_1080284074)
Comment 32 QA Administrators 2022-07-09 03:35:17 UTC
Dear Robert Gonzalez MX,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword

Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team