how to create "Format As Table" in LibreOffice calc

English
, ,
#1

I can’t seem to find this functionality in Calc.

Format as Table (CRTL+T) in Excel allows you to automatically update all the formulas in the rows below the row you are editing.
I have worksheets with 1000 rows and I don’t want to pull down the formulas each time I make a change, I just want to edit the first row and have Calc automatically update the rest. The “Format as Table” function in Excel does that and has been around for years. Does Calc have a similar function? Or how do I accomplish the (simple) task above?

Nice video showing it in action:

Thanks in advance

#2

(I cannot tough out these videos, but:)
The table-in-a-sheet concept doesn’t exist in Calc, afaik.
You can define a named range, say ‘myTable_1’. If you there have formulas in every cell of the first row - just entered or edited probably: Enter the name of the range into the NameBox (leftmost in the formula bar) and press Ctrl+D. This will copy down to the rest of the range without needing further action on your behalf.
Of course, you can additionally automate the process and introduce more details to it (concerning headers e.g.) by creating user code. Little use probably.

#3

@Lupp - Little use? In what way? That is one of the most useful features of Excel in my work that I can update the formula in any row and it will update the whole table (and gives me the choice of not doing so). I cannot imagine that I am the only one using this feature.

#4

-0- Nobody should expect me to watch a clickery video for 25 min 35 s just to understand a question.
-1- My comment was concerning the expectable use of the user code.
-2- Filling needs parameters or has to replace them with defaults. How?
-3- I obviously use spreadsheets in a different way. I don’t miss the feature.
-4- Please, don’t expect everybody to know Excel (which one?).
-5- Feature requests are handled like bug reports: Address them to https://bugs.documentfoundation.org.

#5

I too have experienced this feature in MS Excel (some version with a ribbon) before I switched to LO Calc. I found it very useful as well. Sadly it does not seem to be implemented in LO Calc. Thanks for the link @Lupp. It seems that bug 66377 covers this feature request to some degree, but the only activity for the past years is closing duplicates (which might be regarded as an indication that the reporters of those duplicates also wanted some table support)…

#6

Apologies, it seems bug 132780 better covers this as a feature request. For reference.

#9

Hint: Like 90% of spreadsheet users you are using an arithmetic calculator as a poor database surrogate. If you were using some kind of true database you could apply functions to a whole set of (filtered and sorted) records.

#10

This office suite comes with a database component which is able to look up millions of row references within seconds without adjusting any formula ranges and without bothering about data types or missing values.

#7

You’re likely after Data > Select Range in LibreOffice.

Data > Define Range in LibreOffice seems the same as Home > Format as Table in Microsoft Office. Thats how I find a table when opening a Microsoft Office file anyway and it seems to work fine in both directions interchangeably.

Visual formatting is a separate step in LibreOffice.

IMPORTANT: Don’t confuse this with named ranges of cells. They are different.

(This is here partly in case I’m looking for it in the future.)

#8

No, it haven’t. You will need to learn a few things to get over this nuisance.
In general, working with Calc ranges requires more knowledge and care than Excel.
Use Database Ranges.

Forget. The formula itself, of course, won’t fill down the rows. Click on the cross in the lower right corner of the cell.

You still have to get used to inserting new rows/columns correctly, and not just adding them by filling data in new rows below or columns to the right. Feel the difference between the terms Insert and Add.

Smart tables appeared in Excel in 2007. This means that we are at the level of Excel 2003 in this sense. But there is one more nuisance. Excel 2003 knew how to work with dynamic ranges (using the OFFSET function), and Calc still does not know how, if we are talking about using pivot tables or expanding charts. Only absolute references to the source (a serious flaw). And don’t forget to correct them yourself! Which is why our member @Villeroy is so skeptical about newbies using spreadsheets: it’s too easy to get it wrong. For me, these bottlenecks are resolved by macros, and this is a very complex topic. Everything can be automated, however.

Of course, I was able to overcome all these difficulties, but it will take effort. Will you develop this topic further or keep using Excel? But if you come to us seriously and for a long time, then all problems can be solved. But not as easy as you imagine.

Perhaps I disappointed someone…

#11

Use database ranges that are linked to an actual database and set up all the formatting once, preferably with cell styles. The formatted range and all references to it will grow and shrink automatically with the linked record set.

#12

@Villeroy, could you show a small example of such a connection: Calc Database Range connected to Base DB.

#13

Open a blank sheet and the data source window.
Drag some table or quiery icon from the left pane into the sheet.
Bibliography.biblio will do.
You get a database range “Import1”. This is range A1:AE21 when I use my Bibliography.biblio table
menu:Data>Define…
Select “Import1” and click “Options”
Column headers is always checked since db-data are always imported with column labels.
You have to check “insert/delete cells” (this is always the right setting) and “keep formatting” (this means the formatting of the spreadsheet)
Add some simple formula aggregating a column, say =COUNTA(A2:A21) anywhere below.
Add some simple formula calculating a row value, say =COUNT(A2:AE2), in AF2 directly adjacent to the import range.
Add some record to the database.
Click anywhere in “Import¹” and call Data>Refresh.
The reference in your first formula will expand to =COUNTA(A2:A22)
The second formula will be filled down to the entire column until the end of the db-range.
Remove the dummy record from your database.
Click anywhere in “Import¹” and call Data>Refresh.
The reference in your first formula will shrink to =COUNTA(A2:A21)
The second formula will adjust to end of the db-range.

Any (conditional) formatting, charts etc. behaves in the same way. Refreshing the linked record set inserts and removes cells accoring to the modified size of the imported record set.

The only spreadsheet I use regularly consists of linked pivot tables and database ranges with GETPIVOTDATA and lots of aggregation formulas. I did not enter any value nor formula since months. I just open the document and click a button which starts a tiny little macro refreshing the db-ranges and pivot tables. This way I collect and aggregate several data sources with a single click calling 10 lines of Basic.
There is one little glitch: A column formula gets lost when the adjacent record set happens to be empty (no records). The formula range shirnks to null.

#14

Everything worked out, except for one thing. Replaces the totals row. The flag is set. Why?

Yes, this is important because Calc does not work with data models and relationships like Excel does. Working with a single range as a data source for a PivotTable is a serious limitation.

#15

There is a RFE in bugzilla PIVOT TABLE: add the option to link several tables as source on one pivot table.

and a lot of RFE, https://bugs.documentfoundation.org/buglist.cgi?bug_severity=enhancement&list_id=1365288&query_format=advanced&resolution=---&short_desc=pivot&short_desc_type=allwordssubstr, please add your comment where you like it be implemented.

#16

No, I want to use BASE DB where it complements Calc’s capabilities. I am mastering Base.

#17

It’s possible to import and use Table formulas in Calc Formula_engine, but you need to use xlsx format file to save. it

#18

I use it (table structured references) with database ranges. This is especially helpful when writing macros: indeed, there is no need to search out the range boundaries. I hope you understand me. In this case, it does not matter that structured references will then be replaced by absolute references.

Edit:
It is convenient and simple to write code. So why not use it? Few people use it. And you?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.