Excessive number of rows and columns in a new Calc worksheet

[bvlenci]

When I open a new worksheet in Calc, it always has over 100,000 rows and an excessive number of columns as well. I can’t even read the row numbers, or select a range of rows. Before I begin to enter data, I have to pare it down to a reasonable size.

I can’t find any way to totally eliminate these excess cells. I tried to select all the columns to the right of the last one I want, right click, and choose eliminate selected columns, but nothing happens. So I tried just hiding them, but this leaves the remaining columns squished into the original space. For the rows, it’s even more difficult, but I managed to do it. Then I define a default row height and cell width, and finally I have something workable, although the original several million cells still exist of course.

Then when I finally get something that looks reasonable, I start to enter numbers and find that the font is minuscule, even if I choose the largest font available. It’s as though Calc is shrinking the numbers to a size that would fit into the orginal 2-million cell worksheet.

Isn’t there some way to open a new worksheet with a predefined number of rows and cells? I’m tired of all this fooling around before I can begin to do a simple calculation or create a simple graph.

[Lupp]

(Also see amendment by editing on 2020-08-28.)

Presently (2016-07-31) every Calc sheet has 1024 (2^10) columns and 1048576 (2^20) rows. There is an enhancement request to allow for more columns. Work is on the way.
You needn’t be afraid, however, that an empty sheet opened will eat up your RAM or will need most of your HD when saved. The colums and rows only “exist” in a very faint way as long as they do not contain used cells. It’s mainly the grid just generated for the screen, and not consisting of real cell, what you sees.
No need to force Calc to support less columns and/or cells in a sheet.
If you want to only see a limited number of all those columns and rows, you can hide the other ones, of course. I never could observe something “squished” on such an occasion. But: For what reason do you hate unused columns and/or rows so much? They are completely harmless.
Procedure to hide all column and rows starting with a certain cell to the right and to the bottom:
0. Start with a virgin sheet.

1. Select the respective cell.
2. Hit Shift+Ctrl+CRight.
3. Hit Shift+Ctrl+CDown.
4. Rightclick on one of the highlighted column labels and select ‘Hide’.
5. Rightclick on one of the highlighted row labels and select ‘Hide’.
6. Hit Ctrl+Pos1.
   Done!
   (Better omit this.)

===Edit 2020-08-28 about 16:30 UTC===
Due to a recent post to this thread I announced a solution by user code which I now provide. I still would stress my above advice to better omit the usage. No actual use in it.
See attachment.
ask74371hideUnusedColumnsRowsOfSheets_1.ods

[NikGervae]

“There is an enhancement request to allow for more columns.”

I don’t want more columns or rows, I want less! Specifically, I want exactly as many columns and rows as my data demand. Google sheets does the sensible thing. (Apple’s Numbers does even better, allowing multiple small sheets on a single canvas, but that’s a whole other beast, and Numbers sucks in so many other ways I’m trying desperately to find a decent alternative.)

“But: For what reason do you hate unused columns and/or rows so much? They are completely harmless.”

They are not harmless. They obfuscate my document’s scope and make it difficult to navigate. I would like my document to contain and display only data that I create, so that the scroll bars accurately reflect the size of my sheet, and so that I can scroll to either end without sailing past my data into an unwanted desert of empty cells.

I shouldn’t have to jump through bizarre hoops to get a spreadsheet to behave and present itself in a sensible manner.

[Lupp]

When I answered the question, I couldn’t know what you want. The OriginalQuestioner, however, asked for “…any way to totally eliminate these excess cells…”, and there was none. There still is none.
It’s very doubtable if the OQ (at 2016-07-31 !) had a clear idea of what spreadsheets are. There are many who tend to mix-up the view they get with the “real thing”.
I don’t use lots of different brands of spreadsheet software, but knowing a bit about their internal working, I would doubt a claim that any of them actually “eliminates excess cells” from the sheet objects. They may have (whether by default or based on a setting) a way to exclude unused “extra ranges” from the view.
My above answer tells exactly how to get such a clipped view in LibreOffice Calc.
Most of your comment is your opinion, and clearly subject to argument.
Plrease feel free to create a related enhancement request at bugs.documentfoundation.org , This Q&A site is not the appropriate place.

[Lupp]

By the way: With “google-sheets” or “apple numbers”
How do you extend the used range if needed?
“…allowing multiple small sheets on a single canvas,”
Do you actually think this is a relevant feature for serious applications?

IMO it only would make sense if the “canvas” was what LibO has as the DrawPage of a document. In fact you can insert sheets into LibO text documents (e.g.) as OLE objects to get a view of the kind you report as a feature of “apple numbers”.
A feature allowing for getting the full Calc UI for such objects, and to enable references accross such objects might be very valuable.

[NikGervae]

Thank you for the link to report a bug! This topic was all I had found on the matter so I commented here.

[NikGervae]

By the way: With “google-sheets” or “apple numbers”
How do you extend the used range if needed?

You just add rows and columns of cells. In Google Sheets and Apple Numbers, there are menu commands to do that, and a button at the bottom to add as many rows at once as you want. In Apple Numbers, you can also drag a special handle at the right or bottom and it adds news cells.

[NikGervae]

“…allowing multiple small sheets on a single canvas,”
Do you actually think this is a relevant feature for serious applications?

I think it’s relevant for practical, usable documents containing mutliple small sheets, to display those sheets at the same time without having to switch tabs. But as I indicated, nobody else does that.

"IMO it only would make sense if the “canvas” was what LibO has as the DrawPage of a document. In fact you can insert sheets into LibO text documents (e.g.) as OLE objects to get a view of the kind you report as a feature of “apple numbers”. "

I did not know that, I will check it out.

“A feature allowing for getting the full Calc UI for such objects, and to enable references accross such objects might be very valuable.”

Yes, it might.

[mark_t]

Try setting menu “View”, “Page Break Preview” instead of “Normal”. Then you should only see the range of used cells on the sheet that contain data. Then set “View”, “Zoom”, 100%.

Another way to tell if the used range of the sheet is too large is if you can select the dark region of the scroll bar and drag it to the end of the scroll bar. It should move the display area only to the end of the used range of the sheet. Don’t use the arrow at the end of the scroll bar as this will add rows or columns to the display as it scrolls.

[Lupp]

Sorry! This is one of the few cases where I would explicitly object. There isn’t a thing as a “too large …used range of the sheet”. Firstly this topic is about unused ranges, secondly the “too” doesn’t apply. Millions of users never mind the dedault size of sheets, There is no “too” so far.
Using ‘View’ > ‘Page Break’ while working on the sheets is a bad habit from the Excel world. Reduce obscurity! Emphasise functionality! It’s about Calculation software. Text-tables for dead data!

[mark_t]

Just trying to provide bvlenci a simple method to see the cells that are used rather than the cells available. He seemed to imply that there was too large a range, so I wanted him to see what is used rather than what is available.

[Lupp]

Sorry again. I should have made my statement in another place (or omit it). It was not primariliy a comment on your answer, but a kind of general pleading against the WYSIWYG thinking with respect to spreadsheets.

[NikGervae]

Page Break Preview litters my spreadsheet with outlines and “Page N” covering my data. Not a solution.

As for WYSIWYG, I am certainly seeing what I’m getting, but what I’m getting is something I explicitly don’t want—unused cells.

[Lupp]

I am certainly seeing what I’m getting, but what I’m getting is something I explicitly don’t want—unused cells.

No. The “WhatYouGet” was always related to printouts, and originally only a silly advertisment concerning MS Word. They still stick to it by not supporting style families and many very clever features of LibreOffice.

What you get printed using Calc, however, is what you selected ande defined as PrintRanges - or, by default, the used area - just as you like it…

[jonnie45]

I agree entirely with the OP’s original comment or thinking behind it.

I am in the process of designing a template, made the mistake of highlighting an entire row a certain colour and then continued with merging cells and adding rows to make a header for a costs spreadsheet for a self-building project

The intended spread will have less than ten columns.

Because I made a mistake and did an operation that affects a whole row I now have a neat “active” table that is ten columns wide with a near infinite blue row sticking out the side. It is near impossible to scroll to the right of the table because it contains so many colums and so I am now looking around for a “select all cells to the right of this cell” type function so that I can effectively “set colour on all cells to the right of this cell” and undo my original mistake of colouring an entire row.

Of course someone will helpfully offer a solution I am sure but if it was possible to limit a spreadsheet to a sensible number of columns and rows then people would not search for functionality to undo an change that affected zillions of cells because like me they made a stupid mistake and selected an entire row instead of individual cells.

We have a situation here where people can affect more cells than they might have intended.
They may go on to do more work before realising their mistake.

Like me they may end up with a N x M table which they do not want to throw away yet needing to undo a change on all cells to the right of that table or all cells underneath that table because they were offered functionality that can affect zillions of cells in one easy operation.

I think it would be so much easier if people could confine their work area to a more sensible range so that
they can undo mistakes more easily without having to know an incantation that allows them to selectively
undo an operation on the near infinite unwanted “tail” ( right or beneath) of their intended table.

I have zero evidence but I would still place a bet that more users use tables that are less than 100 columns wide than users that use tables that are more than 100 columns wide. It seems perfectly sensible to me to offer functionality that reduces the table width to a sensible range.

I think some people in this forum have offered very confident assertions “there is no such thing as too large” I think the person who said that may not be thinking about usability in the context of different user levels of expertise. I rarely use spreadsheets but I am highly technical and have 30 years IT software writing experience.

I just want something that works easily.

I have a table of ten columns

I am offered functionality that can affect zillions of columns

Like anyone else I can get myself in a mess and create something that I do not wish to throw away and start
again but where it seems rather painful to selectively undo my mistake on such wide spreadsheets.

I could undo the damage the “long way around” without increasing my expertise or consulting gurus if I could simply lose the X thousand columns to the right of the area I am actually interested in.

Good usability is not about channeling everyone to the path of guru user - its often about giving occasional users an obvious and intuitive path to undoing their mistakes, its also about giving them the ability to restrict ranges so that they can do themselves less damage. It would just be easier for me all around if I could throw away the zillions of columns I do not want and repair my table in a simple fashion.

People simply do not always wish or indeed have time these days to invest in software, its about making things as easy as they can be - I probably first used a spreadsheet before some people on this thread were born but I have never become an expert ( never had the time or need ) yet I can usually do what I want intuitively.

I see the excessively wide spread sheet as an untied shoe lace that can trip people up, just stands out for me as a pitfall waiting for the casual user.

Right time to start again - it will probably be quicker and less frustrating just to rebuild my table manually from the start and this time remembering to select only cell ranges not rows all the way through.

Update - simple intuitive fix was to copy past the cells I actually wanted to a new spreadsheet - easier to select what I wanted to keep rather than trying to select and undo a change on the rest which is excessive as the OP stated. Fine - fixed but I lost 30 mins messing around - it would just have been so much easier if I could have removed the excessive columns.

My reaction and length of posting is because I object to the over confident assertions made by others - usability is rarely served by 1+1=2 arguments.

[NikGervae]

A solution to this problem would be to create a spreadsheet with a reasonable number of cells, only scroll to the edges of those cells, without automatically adding more, and allow a way for the use to add more as needed. That last step is already done! All you have to do is not automatically create hundreds of thousands of cells the user doesn’t want.

[Lupp]

The current standard still is 2^10 (1024) Columns, 2^20 (1048576) rows each. This results in 2^30 (1073741824) logical (addressable) cells. The currently (7.0.0) experimental enhancement is 2^14 columns of 2^24 rows each, and thus 2^38 (about 2.7E11) cells. Of course, such a huge number of cells isn’t actually “created”. Even if the “creation” of a cell would only need 1 µs (mikrosecond = 1E-6 s) the process would eat about 76 h (>3 days) otherwise, and no ordinary man’s RAM could keep all these cells.
We are talking about views.
Created in a more substantial sense are cells if you insert something in or assign something to - or if you access one by user code explicitly.
The view of a sheet is created with much smaller numbers of columns and rows - depending on the zoom-factor, the window settings, and the screen-size. We are therefore talking about navigation and hiding.

[Lupp]

Everything you may want can be implemented by user code without annoying other users (like myself) wanting it as it currently is. If I find the time I will give an example.

[Lupp]

Found the time. See attachment to my edited answer.

[mariosv]

Instead opening the file, link to it with a matrix formula with the size you like, so you don’t touch the original file and you have the data you want to.