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.
Created attachment 160447 [details] Explainer of tables in Excel 365 1
Created attachment 160448 [details] Explainer of tables in Excel 365 2
Created attachment 160449 [details] Explainer of tables in Excel 365 3
Created attachment 160450 [details] Explainer of tables in Excel 365 4
Created attachment 160451 [details] Explainer of tables in Excel 365 5
Created attachment 160452 [details] Explainer of tables in Excel 365 6
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?
Partially implemented. https://wiki.documentfoundation.org/ReleaseNotes/5.0#Table_structured_references https://wiki.documentfoundation.org/ReleaseNotes/5.1#Table_structured_references
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)
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).
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.
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.
[Automated Action] NeedInfo-To-Unconfirmed
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).
(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.
I think it's a dupe of bug 66377. Pedro, what do you think?
(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.
*** Bug 139291 has been marked as a duplicate of this bug. ***
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 !
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.
This is a much needed feature in LibreOffice Calc. Whenever I teach LO Calc to Excel users, this is possibly the #1 feature that they miss. And I personally agree that this feature provides awesome UX and it should exist in LO Calc (alongside Table Slicers - see Bug 119807). The "Format As Table" feature in Excel is not only about formatting, but rather about providing functionality that makes it much simpler to work with tables, as pointed out in Comments #19 and #20.
It really is a functionality that is in high demand. Questions are frequently asked in social networks about how to implement this feature in Calc. I hope it can be implemented soon.
TBH I think this is a duplicate of bug 66377. Fixing it will also fix this one. The problem of having both tickets open (bug 66377 and bug 132780) is that it becomes harder to centralize the discussion. As of now, bug 66377 has 14 duplicates, so how about we mark this one as duplicate and centralize the discussion there?
(In reply to Rafael Lima from comment #23) > The problem of having both tickets open (bug 66377 and bug 132780) is that > it becomes harder to centralize the discussion. While I agree that the difference is not always clear in each dupe, I think that one report (or rather RFE) is about implementing the feature in Calc itself, and the other is about being able to import/export the cells/table attributes from/to xls(x) file format.
(In reply to ady from comment #24) > (In reply to Rafael Lima from comment #23) > > > The problem of having both tickets open (bug 66377 and bug 132780) is that > > it becomes harder to centralize the discussion. > > While I agree that the difference is not always clear in each dupe, I think > that one report (or rather RFE) is about implementing the feature in Calc > itself, and the other is about being able to import/export the cells/table > attributes from/to xls(x) file format. When I created this bug I didn't find that other open bug. In here I try to outline the features of tables required in such an implementation. If you mark as duplicate can this be transferred to the other bug?
(In reply to Rafael Lima from comment #23) > TBH I think this is a duplicate of bug 66377. Fixing it will also fix this > one. > > The problem of having both tickets open (bug 66377 and bug 132780) is that > it becomes harder to centralize the discussion. > > As of now, bug 66377 has 14 duplicates, so how about we mark this one as > duplicate and centralize the discussion there? Also as I replied in comment 17 about bug 66377: 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.
As I replied some years ago : against making this as duplicates of 66377.
Created attachment 194197 [details] Google Sheets: Auto-applied formatting (1. Gif)
Created attachment 194199 [details] Google Sheets: Group By View (2. Gif) When you are using tables, you’ll also have access to our new type of view, group by, where you can aggregate your data into groups based on a selected column. For instance, you can decide to group all data at the same priority level in one place, as shown below.
Comment on attachment 194197 [details] Google Sheets: Auto-applied formatting (1. Gif) With tables, you can simplify and accelerate spreadsheet building by bringing format and structure to unorganized ranges. By selecting your data range and going to Format > Convert to table, Sheets now does the heavy lifting to format and organize data with a polished design including column types, filters, color coding, dropdown menus and more.
Created attachment 194200 [details] Google Sheets: pre-built tables (3. Gif) We’re also introducing pre-built tables that you can populate with common data types for everyday tasks like project management, inventory management, event planning and more. Now with pre-built tables, you never have to build a spreadsheet from scratch again.
Recently, Google Sheets released an auto formatting feature which would compliment this feature request, it is a great improvement over Office 365 and LibreOffice auto format Announced: 14/05/24 (DD/MM/YY) Google Sheets New Feature: New ways to quickly format and organize data with tables: https://support.google.com/docs/thread/274482551?hl=en
*** Bug 162805 has been marked as a duplicate of this bug. ***
The feature corresponding to Excel's "table" is in LibreOffice the "database range". So it would be good to have a comparison, which features are missing in "database range" that are available in Excel or Google Sheets and the other way round, which features are available in "database range" but not in Excel or Google Sheets. I think, the Wiki would be a good place for such a comparison. This would help to limit this enhancement request to a reasonable size.
There's so many differences than it's not even funny. We can start with name: Database range is meaningless to the common user. One can't even fathom it is supposed to be a similar functionality (hence why it was placed in Data tab in Tabbed UI instead of Insert tab. In Excel, Table is in Insert tab). Scope there: 1 - Change name of "Define Range" uno command to Table. 2 - Move command from Data Tab to Insert tab in Tabbed UI. Moving on. When one defines a table in Excel, it provides a Table design with a Table Style. Database Range has nothing. It has a handle to drag the Table to the neighbor columns and rows. Database range has nothing visible. Scope: 1 - Implement a table style upon creation of a database range. 2 - Implement a handle to expand table size. When selecting the Range in Calc in a completely different command (hidden in a menu) it is possible to expand the range of the table. However, it does not propagate the table style. It fills the new cells with new values based on expanded table. When creating a Table in Excel, a new tab in the UI is created (Table Design) with multiple options to change the design of the table (Header Row, First Column, Last Column, Banded Rows, Total Row, Last Column, Banded Columns). Nothing in Database Range. Scope: Table Styles. Any type of table styles. AutoFormat table styles should be implemented to Selected Range. It should be possible to better configure AutoFormat styles. There's the option to export the table data to Sharepoint list. There's a Tools group with: 1 - Remove Duplicates, 2 - Insert Slicer, 3 - Convert to Range 4 - Summarize with PivotTable. There's nothing in the comparable function of Calc. Scope: Add all these. The scope is enormous: new UI, new functions. If no one wants
(In reply to Pedro from comment #35) > There's so many differences than it's not even funny. > We can start with name: Database range is meaningless to the common user. > One can't even fathom it is supposed to be a similar functionality (hence > why it was placed in Data tab in Tabbed UI instead of Insert tab. In Excel, > Table is in Insert tab). > Scope there: > 1 - Change name of "Define Range" uno command to Table. > 2 - Move command from Data Tab to Insert tab in Tabbed UI. > > Moving on. > When one defines a table in Excel, it provides a Table design with a Table > Style. Database Range has nothing. > > It has a handle to drag the Table to the neighbor columns and rows. Database > range has nothing visible. > > Scope: > 1 - Implement a table style upon creation of a database range. > 2 - Implement a handle to expand table size. > > When selecting the Range in Calc in a completely different command (hidden > in a menu) it is possible to expand the range of the table. However, it does > not propagate the table style. It fills the new cells with new values based > on expanded table. > > When creating a Table in Excel, a new tab in the UI is created (Table > Design) with multiple options to change the design of the table (Header Row, > First Column, Last Column, Banded Rows, Total Row, Last Column, Banded > Columns). Nothing in Database Range. > > Scope: > Table Styles. Any type of table styles. AutoFormat table styles should be > implemented to Selected Range. It should be possible to better configure > AutoFormat styles. > > There's the option to export the table data to Sharepoint list. > > There's a Tools group with: > 1 - Remove Duplicates, > 2 - Insert Slicer, > 3 - Convert to Range > 4 - Summarize with PivotTable. > > There's nothing in the comparable function of Calc. > > Scope: Add all these. > > The scope is enormous: new UI, new functions. If no one wants This is 100% correct, there is a considerable disparity between Excel and Calc here. As a user of this functionality in Excel I can contest that you would not switch to LibreOffice if this was important to you. Does anyone know if this is on a roadmap or if a tender for this functionality can be created?
(In reply to John Mills from comment #36) > (In reply to Pedro from comment #35) > > There's so many differences than it's not even funny. > > We can start with name: Database range is meaningless to the common user. > > One can't even fathom it is supposed to be a similar functionality (hence > > why it was placed in Data tab in Tabbed UI instead of Insert tab. In Excel, > > Table is in Insert tab). > > Scope there: > > 1 - Change name of "Define Range" uno command to Table. > > 2 - Move command from Data Tab to Insert tab in Tabbed UI. There is no "Table" inserted. Compare with what happens, when you use "Insert Table" in Writer or Impress. The term "Database Range" describes the purpose and the features better than the term "Table". The term "Table" means all of the cells. That name "Database Range" exists for more than 25 years. OpenOffice and LibreOffice users know it. Coming from Excel you simple need to learn, that it is "Database Range" in LibreOffice. > > > > Moving on. > > When one defines a table in Excel, it provides a Table design with a Table > > Style. Database Range has nothing. > > > > It has a handle to drag the Table to the neighbor columns and rows. Database > > range has nothing visible. > > > > Scope: > > 1 - Implement a table style upon creation of a database range. > > 2 - Implement a handle to expand table size. Yes, you need to style the cell range in a separate step. You can do it independent from defining a database range. Yes, changing the source area is currently not possible by dragging. You need to modify the source area in the "Define Range" dialog. You should write a feature request for each. > > > > When selecting the Range in Calc in a completely different command (hidden > > in a menu) it is possible to expand the range of the table. However, it does > > not propagate the table style. It fills the new cells with new values based > > on expanded table. > > > > When creating a Table in Excel, a new tab in the UI is created (Table > > Design) with multiple options to change the design of the table (Header Row, > > First Column, Last Column, Banded Rows, Total Row, Last Column, Banded > > Columns). Nothing in Database Range. > > > > Scope: > > Table Styles. Any type of table styles. AutoFormat table styles should be > > implemented to Selected Range. It should be possible to better configure > > AutoFormat styles. Yes, a binding of a table style to a Database Range does not exist. Currently the feature "Table style" lacks a lot. You will find several bug reports. Currently a "Table style" is not a style but more like a stencil. You need to apply it again after changes in the area size. > > > > There's the option to export the table data to Sharepoint list. Is write access to a Sharepoint possible, if you do not have a license for Microsoft Office? > > > > There's a Tools group with: > > 1 - Remove Duplicates, will come in LibreOffice 25.2 > > 2 - Insert Slicer, Not really needed, UI gimmick. > > 3 - Convert to Range Data > Define Range > Delete > > 4 - Summarize with PivotTable. Data > Select Range; Data > Pivot Table. [..] > This is 100% correct, there is a considerable disparity between Excel and > Calc here. Features available for Database Range in LibreOffice, I do not see in Excel: Can reflect a table or query of an external database. Can sort with different algorithm Can filter case sensitive Can filter using regular expressions Output filtered data to different sheet
*** Bug 120457 has been marked as a duplicate of this bug. ***