So if the libreOffice developers ever decide to implement this feature the world will be a much happier place :-).
I ditched MS Office on my Mac in favor of libreOffice for stability and speed but I confess I do miss this one feature.
3 Likes
This is a very old unsolved thread, so obviously it remains relevant. I just want to second sethsn’s explanation of why this feature is so useful. Particularly important is the fact that it allows the user to automatically copy the formulas each time you, say, add a row of data. By creating a table in Excel, the bottom range of the data set is defined so that by simply pressing “Tab” at the last cell, another row can be added that retains all formatting AND formulas from the above row.
There are many other threads that have been marked as “solved” that are essentially asking how to do this, but one gets the feeling that other commenters and moderators do not quite understand the question (e.g. here Automatic formula when add row?).
Thanks again to sethsn. I would upvote you if I had any karma … but alas, I’m a newbie to the forum.
1 Like
You Suck at Excel with Joel Spolsky - YouTube at minute 37 explains this REALLY good. this is a killer feature, and libreoffice should really try to get this done.
2 Likes
It already works, they are called database ranges. I opened one of my old spreadsheets that already had a table in it and all of the normal table functionality that excel has works in Calc. In the data dropdown, choose “Define range”, and then select the area for the table and name it.
@Testestestest -
That feature in LibreOffice Calc is not Dynamic, it only creates a data range of what you already have… its nothing like Tables in Excel at all, the same feature as this in Excel will be to select the area and give it a name… that is neither dynamic… I just tried it in LibreOffice Calc 6.4…
To everyone saying that the “Create Datarange” in LibreOffice do the same as “Tables” in Excel, it do not, It is not dynamic, it do not dynamically extend the data range with new row (as a table in i.e. Base do…
The “Data - Define Range” in Calc only define a static range, as if selecting a Range with Cells in Excel and giving it a name…
Yes, you can.
Insert->Object->Chart
There is a difference between the words create and insert. The question was regarding creation of a table, not insertion of a table.
Perhaps is this what you are looking for:
https://wiki.documentfoundation.org/ReleaseNotes/5.1#Table_structured_references
It already works, they are called database ranges. I opened one of my old spreadsheets that already had a table in it and all of the normal table functionality that excel has works in Calc. In the data dropdown, choose “Define range”, and then select the area for the table and name it.
That feature in LibreOffice Calc is not Dynamic, it only creates a data range of what you already have… its nothing like Tables in Excel at all, the same feature as this in Excel will be to select the area and give it a name… that is neither dynamic…
I just tried it in LibreOffice Calc 6.4…
1 Like
A database range is dynamic. If you insert a new row when the cell cursor is inside the database range, then the size of the database range is extended and all references to the database range in formulas will adapt to the new size of the database range. To be clear, to extend the size of a database range do not simply write new data at the end, but set cursor at the last cell, insert a row below and then enter the new data.
However, array functions are currently not dynamic. You need to adapt their output size manually, when the source in their parameters change.
It seems that Calc separates the complex assortment of features included in Excel’s “Insert Table” formatting, named ranges, and filtering / sorting. AutoFormat / Themes both are LibreOffice Calc’s equivalent of Excel’s “Insert Table” formatting. The Calc AutoFilter feature provides the data sorting and filtering features of Excel’s (inserted) Tables. Named ranges group the data together into a unit.
1 Like
Please do not confuse “named ranges” with “database ranges”. Their use cases are very different. “named ranges” are managed in menu Sheet > Named Ranges and Expressions, whereas “database ranges” are managed in menu Data.
Indeed, when an Excel Table is extended, Excel generates a new row, uses banding color for clarity, copies formulae present in the existing row, moves cell focus to the new row and 1st column, etc… It is a real time saver.
But, there is another significant advantage to Excel Tables not yet mentioned here: Associated Charts Are Extended Automatically
- Assume you are in Excel and you’ve created a Table with 1 row of headers and 5 rows of data/formulae/etc…
- Assume you’ve created a Chart (e.g. line graph) that includes all 6 rows (header + data).
- When you extend the Excel Table, Excel creates and inserts a new empty row automatically. When you the user enter the data/formulae/etc… the row is now complete. The associated Chart automatically adds that new complete row into the Chart. This is incredibly helpful and saves time.
Use-Case: Financial Data Entry
- Your row might include a date, a cost basis, a current market value.
- You have 5 months of data, from January 1, 2020 to May 1, 2020. Your Chart has 5 data points.
- When you extend the Excel Table and add a 6th set of data, your Chart automatically extends to include the 6th set of data (e.g. June 1, 2020). Normally, you would have to edit the chart and the chart’s data ranges. That takes time and multiple clicks.
In summary, I would be happy to see LibreOffice add Excel Tables as a feature.
3 Likes
Thanks for the new information. But since it doesn’t answer the question, please re-post it as a comment.
To do that, hover on “more” (just above these comments), and press “repost as comment under question”. Thanks.
I cant understand why something like this has not been implemented yet.
2 Likes
LibreOffice has a database component which is by far more powerful than any fake tables on a calculator’s grid.
Base does not replicate the functions of Excel Tables for the average user.
Tables allow me to define a dynamic range, sort, filter, and have all of the functions of a spreadsheet at my fingertips without having to learn how to write SQL queries.
Base, when I can get it to work at all, is overkill for the tasks I would use an Excel Table for.
2 Likes
To be done once:
File>New>Database…
Type: Spreadsheet
Specify the spreadsheet and check “register database”
Save the database document. Nothing has been converted or imported. All data are still in the spreadsheet.
Call the query designer or query wizard to create meaningful queries from the spreadsheet tables.
Save the database, close the database, forget the database.
To be done whenever you need sorted and/or filtered data in Calc or in Writer
Hit Ctrl+Shift+F4 for the data source window.
Drag a query or table icon from the left pane into the document.
2 Likes
Relax, we just want the software to make life easier. If copying a feature from excel is the solution then it’s very welcome.