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. ***