Download it now!
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:
: 139291 (view as bug list)
Depends on:
Blocks: Calc-Enhancements
  Show dependency treegraph
 
Reported: 2020-05-06 15:20 UTC by Pedro
Modified: 2021-01-08 19:04 UTC (History)
10 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.
Comment 18 m.a.riosv 2020-12-29 14:01:28 UTC
*** Bug 139291 has been marked as a duplicate of this bug. ***
Comment 19 Thierry 2020-12-29 20:05:42 UTC
I filed a duplicate of this request (sorry !), explaining again some of the good reasons to add this feature.

As I mentioned in my request:
This Excel feature is really a key one for a spreadsheet management tool such as... Excel... or Calc ! We are using those tools to manipulate data, organized in tables. That's the core feature of those tools...

Before Excel introduced the "format as table" feature, we had to do all the stuff "by hand". AND TO MAINTAIN IT when the table content was changing. Very uncomfortable and time consuming...

Today, with this feature, it is very simple to:
- organize dynamic data in table (named range, easier references in external vlookup,...)
- create a footer with smart formulas (sum, subtotal,...), that remains a footer, whatever you add/remove in the table and that don't get sorted in the table content flow...
- add new lines in the range (simply press tab key at the end of the table)
- get formulas automatically created in the columns (limit risks of mistake)
- use explicit column titles as names in the formulas (simplify writing and maintenance)
- get a consistent auto formatting (such as alternate colors for rows,...) whatever sort/insert/delete action you can do on the table itself
- ...


And I fully agree with @Pedro : no, this is not a bug of Calc loosing formatting, but a request for enhancement.
=> we are looking for introducing this notion of "table"

I really think we need this feature in Calc, to be able to continue using LibreOffice as a standalone alternative to Microsoft Office in a consistent and effective way...

I also realize it may not be easy to develop, but, at least, let's track this need and look at it...

Thanks !
Comment 20 jocn 2021-01-08 19:04:17 UTC
Tables are ubiquitous in any Excel spreadsheet I make. I'm very happy to see everything that's been implemented so far. It makes feature parity seem very possible.

Here are the gaps I consider most important:

- The @ operator to specify a structured reference to a cell in the current row. Calc's syntax to accomplish the same thing is much more verbose, implicit, and, as far as I know, undocumented.

- Calculated columns: some way to ensure that every cell of a field inside a database range uses the same formula, that changing the height of the range will also copy the formula, that changing the formula in one cell will change it for the whole column.

- The ability to rename a database range, updating the name in any references elsewhere in the workbook.

- Formula bar autocomplete for structured reference syntax, especially when selecting a range of cells.