Ask Your Question

Excessive number of rows and columns in a new Calc worksheet

asked 2016-07-31 21:13:36 +0200

bvlenci gravatar image

updated 2016-07-31 21:23:26 +0200

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-07-31 22:07:19 +0200

Lupp gravatar image

updated 2016-07-31 22:09:53 +0200

Presently 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.
(Better omit this.)

edit flag offensive delete link more

answered 2019-03-30 12:12:45 +0200

jonnie45 gravatar image

updated 2019-03-30 12:22:28 +0200

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 ... (more)

edit flag offensive delete link more

answered 2016-08-01 00:23:34 +0200

mark_t gravatar image

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.

edit flag offensive delete link more


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!

Lupp gravatar imageLupp ( 2016-08-01 10:45:04 +0200 )edit

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.

mark_t gravatar imagemark_t ( 2016-08-01 18:45:15 +0200 )edit

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.

Lupp gravatar imageLupp ( 2016-08-01 19:30:26 +0200 )edit
Login/Signup to Answer

Question Tools


Asked: 2016-07-31 21:13:36 +0200

Seen: 4,248 times

Last updated: Mar 30