Bug 132780 - Feature Request: Add Calc Tables with functionality similar to Excel tables
Summary: Feature Request: Add Calc Tables with functionality similar to Excel tables
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Enhancements
  Show dependency treegraph
 
Reported: 2020-05-06 15:20 UTC by Pedro
Modified: 2020-09-22 23:09 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Gif depicting excel tables functionality (1.03 MB, image/gif)
2020-05-06 15:20 UTC, Pedro
Details
Explainer of tables in Excel 365 1 (73.40 KB, image/png)
2020-05-06 15:22 UTC, Pedro
Details
Explainer of tables in Excel 365 2 (108.48 KB, image/png)
2020-05-06 15:22 UTC, Pedro
Details
Explainer of tables in Excel 365 3 (72.75 KB, image/png)
2020-05-06 15:23 UTC, Pedro
Details
Explainer of tables in Excel 365 4 (100.03 KB, image/png)
2020-05-06 15:23 UTC, Pedro
Details
Explainer of tables in Excel 365 5 (41.30 KB, image/png)
2020-05-06 15:23 UTC, Pedro
Details
Explainer of tables in Excel 365 6 (50.44 KB, image/png)
2020-05-06 15:24 UTC, Pedro
Details
OnlyOffice Desktop Editors has same feature and has GPL3 license (46.10 KB, image/png)
2020-05-15 15:13 UTC, Pedro
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pedro 2020-05-06 15:20:56 UTC
Created attachment 160446 [details]
Gif depicting excel tables functionality

One extremely handy feature present in Excel that Calc lacks is the capacity to create tables inside Excel. These tables offer several functionalities besides what is available to the normal spreadsheet cells, and what can be achieved with just Sort and Filter of a selection of cells in Calc. This feature was previously known as an Excel list.

The purpose of Excel tables is to manage and analyze related data easily.
These tables have several elements:

Header row - All tables have filtering enabled in header row to sort or filter quickly.

Banded rows - Alternate shading to distinguish data.

Calculated column - insert a formula in one cell in a table column and you can create a column in which that formula is applied to all cells in the same table column.

Total row - Can add a total row to a table. Excel will provide an AutoSum drop-down liist to select from functions

Sizing handle - a sizing handle in the lower right corner that allows to drag and make the table the size I want (selecting which cells are part of it).

Steps to create a Table in Excel 365:
1 - Select range of interest in a sheet,
2 - Go to Home -> Format as Table or Insert -> Table,
3 - Pick a style,
4 - Select if you want headers in dialog, click OK.

Functionalities of tables: besides the previously mentioned and VERY important: 1 - When you add new values to the cells below the table, it automatically extends the table to include them.
2 - make a chart of a table, and any time the table is extended the chart auto-updates to include the new cells.
3 - If you paste that chart in a Powerpoint presentation, whenever you have the Excel spreadsheet opened side by side the chart in the Powerpoint file will also update if "Refresh Data" is selected.

These features are important for people that are dealing with constantly growing related data, and for creating charts that auto-update allowing a decree of automatization that is not possible in Calc. This is an extensive feature but it's a feature that is also present in WPS Office and Softmaker Office besides Microsoft Office.

I would like to request that a similar feature is created since it vastly improves the use cases of Calc.
Comment 1 Pedro 2020-05-06 15:22:28 UTC
Created attachment 160447 [details]
Explainer of tables in Excel 365 1
Comment 2 Pedro 2020-05-06 15:22:53 UTC
Created attachment 160448 [details]
Explainer of tables in Excel 365 2
Comment 3 Pedro 2020-05-06 15:23:09 UTC
Created attachment 160449 [details]
Explainer of tables in Excel 365 3
Comment 4 Pedro 2020-05-06 15:23:28 UTC
Created attachment 160450 [details]
Explainer of tables in Excel 365 4
Comment 5 Pedro 2020-05-06 15:23:45 UTC
Created attachment 160451 [details]
Explainer of tables in Excel 365 5
Comment 6 Pedro 2020-05-06 15:24:25 UTC
Created attachment 160452 [details]
Explainer of tables in Excel 365 6
Comment 7 Pedro 2020-05-15 15:13:19 UTC
Created attachment 160864 [details]
OnlyOffice Desktop Editors has same feature and has GPL3 license

Important thing to mention: OnlyOffice Desktop Editors has an initial implementation of this feature. It's licensed under GPL v3 so maybe its code for this feature could be used as a blueprint to introduce this feature in Calc?
Comment 9 Heiko Tietze 2020-08-03 09:23:43 UTC
Isn't it the same as Format > Autoformat Styles...? https://help.libreoffice.org/6.4/en-US/text/scalc/guide/autoformat.html 

(Also known as Table Style in Writer; see also bug 101802)
Comment 10 Pedro 2020-08-03 13:57:01 UTC
No,it's not the same as Autoformat Styles.
Tables in Excel have numerous specific functionalities attached to them that I clearly enumerated in the first post and in the attachments.

AutoFormat Styles is just an aesthetic change to a number of cells that you select. Furthermore, you can't select Autoformat Styles to less than a 3x3 cell selection (which is quite silly).
Comment 11 Heiko Tietze 2020-08-03 14:01:47 UTC
My take: simplicity first. If you want to make charts of growing sheets use a pivot table or special functions. We don't need to copycat all Excel inventions.
But let's see what Eike thinks.
Comment 12 Pedro 2020-08-03 14:50:13 UTC
That's just one of the functionalities. 
I mentioned plenty of others:

These tables have several elements:

Header row - All tables have filtering enabled in header row to sort or filter quickly.

Banded rows - Alternate shading to distinguish data.

Calculated column - insert a formula in one cell in a table column and you can create a column in which that formula is applied to all cells in the same table column.

Total row - Can add a total row to a table. Excel will provide an AutoSum drop-down liist to select from functions

Sizing handle - a sizing handle in the lower right corner that allows to drag and make the table the size I want (selecting which cells are part of it).~

These allow the quick automation and make data manipulation in tables really easy and simple. To me simplicity first means making work easier for end-users, not simplicity by decreasing work for developers and having a less feature complete piece of software.
Comment 13 QA Administrators 2020-08-04 04:19:45 UTC Comment hidden (obsolete)
Comment 14 Eike Rathke 2020-08-05 21:18:50 UTC
I agree, those Table features are still missing. There was a GSoC idea for the table styles but that never got picked up, https://wiki.documentfoundation.org/Development/GSoC/Ideas#Implement_table_styles
I just added this bug for reference there (or actually changed it from the old outdated and misleading one).
Comment 15 Heiko Tietze 2020-08-06 10:41:50 UTC
(In reply to Eike Rathke from comment #14)
> I agree, those Table features are still missing.

Removing UX for now. Please ping if you need input.
Comment 16 Roman Kuznetsov 2020-08-06 18:22:08 UTC
I think it's a dupe of bug 66377. 

Pedro, what do you think?
Comment 17 Pedro 2020-08-07 10:18:43 UTC
(In reply to Roman Kuznetsov from comment #16)
> I think it's a dupe of bug 66377. 
> 
> Pedro, what do you think?

That bug seems to be about Calc losing formatting of tables created in Excel. It is related but not the same thing, since I want to focus on including the functionality in Calc, not only just keeping the Excel formatting.