OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to limit number of rows and columns?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
sheets
General User
General User


Joined: 19 Mar 2010
Posts: 6

PostPosted: Fri Mar 19, 2010 5:33 pm    Post subject: How to limit number of rows and columns? Reply with quote

Great product, thanks to all involved!

I've searched the built-in Help and this site, and can't find an answer.

I would like to set up a spreadsheet, with, say, only columns A - H. I can see how to *hide* columns, but not how to eliminate all beyond a certain number. The max is, what 2^16? Every time I hide some, more just show, and it scrolls almost infinitely.

I'd like to do this for rows, but it's not so critical, since the max isn't so large.

This should make the spreadsheets less bulky, save disk space, and perhaps open faster. Certainly, easier to read.

TIA for any help.

Edit: Can I set the default to, say, 20 columns instead of 65,000+ ? How?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Mar 19, 2010 11:51 pm    Post subject: Reply with quote

A spreadsheet is not the type of application most users expect.
Database tables have the columns you defined for each table and the count of rows you fill with data.
Using a spreadsheet as if it were a database is very common (and quite often legitimate) but you have to deal with severe draw backs and risks.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sheets
General User
General User


Joined: 19 Mar 2010
Posts: 6

PostPosted: Sat Mar 20, 2010 5:13 pm    Post subject: Reply with quote

Villeroy wrote:
A spreadsheet is not the type of application most users expect.
Database tables have the columns you defined for each table and the count of rows you fill with data.
Using a spreadsheet as if it were a database is very common (and quite often legitimate) but you have to deal with severe draw backs and risks.

Perhaps my terminology is incorrect. Let us not worry about what to call it.

Let's say I have various data in Columns A and B. Perhaps I would like to sum each column at the bottom row. Or find the mean. And perhaps each row in A is added to B to produce Column C. And perhaps I would like to have the sum, median, etc. of Column C. And perhaps perform other operations - multiply by a certain percentage, etc.

OO Calc seems extremely well suited to this. Why would I not use it? What else should I be using? I've been using it this way for several years.

It doesn't matter to me whether you call that a spreadsheet, a database, whatever. It works. I'd just like to know how to limit the number of rows and columns to what I actually need. Is this not possible?

Thank you for your time.
Back to top
View user's profile Send private message
pkon
OOo Enthusiast
OOo Enthusiast


Joined: 26 Feb 2010
Posts: 116

PostPosted: Sun Mar 21, 2010 2:35 am    Post subject: Reply with quote

Well, this is a property of Calc, as of today, that you always have the maximum numbers of columns and rows. So you cannot limit this in other way as hiding and maybe protecting the sheet with unprotected area you want the user to use. This property is to my knowledge compiled into the application.

I think this was what Villeroy tried to say to you. To some extend I agree with Villeroy that users some times are using a mathematical tools as a type writer and expecting database functionality at the same time, while it actually is a big calculator only with advanced functions and formatting. So it is a matter of using the right tools for the issue you have to solve.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Mar 21, 2010 3:40 am    Post subject: Reply with quote

By mere intention there is no concept of a "table" in spreadsheets. You have freely availlable matrices of cells and each cell is supposed to take any value or formula.
Of course you can organize data in a rectangle of cells and treat that rectangle as a database-like table with a first row of column labels. But a spreadsheet allows completely different concepts as well as multiple tables side by side, below each other or in any arrangement of rows and columns.
Sometimes the result of a mathematical operation is an array of variable size.
To me your question indicates that you want a more structured and less error-prone way to organize data. In this case I use to recommend a database where you define the columnar structure of tables, valid entries and relations between tables before you start filling them with data.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Sun Mar 21, 2010 6:28 am    Post subject: Re: How to limit number of rows and columns? Reply with quote

sheets wrote:
Great product, thanks to all involved!

I've searched the built-in Help and this site, and can't find an answer.

I would like to set up a spreadsheet, with, say, only columns A - H. I can see how to *hide* columns, but not how to eliminate all beyond a certain number. The max is, what 2^16? Every time I hide some, more just show, and it scrolls almost infinitely.

I'd like to do this for rows, but it's not so critical, since the max isn't so large.

This should make the spreadsheets less bulky, save disk space, and perhaps open faster. Certainly, easier to read.

TIA for any help.

Edit: Can I set the default to, say, 20 columns instead of 65,000+ ? How?


The simple answer is that you cannot. A spreadsheet does not allow it. What you see is what you get. The alternatives for easy reading are a) format using lines, colours etc. or use Villroy's suggestion and move to a data base.

Quote:
Let's say I have various data in Columns A and B. Perhaps I would like to sum each column at the bottom row. Or find the mean. And perhaps each row in A is added to B to produce Column C. And perhaps I would like to have the sum, median, etc. of Column C. And perhaps perform other operations - multiply by a certain percentage, etc.

OO Calc seems extremely well suited to this. Why would I not use it? What else should I be using? I've been using it this way for several years.


Certainly you can do it and it's fairly useful for doing this. In fact I think that this is what a spreadsheet (computer) was designed for.. You just cannot limit the number of columns and rows that appear.


Quote:
It doesn't matter to me whether you call that a spreadsheet, a database, whatever. It works. I'd just like to know how to limit the number of rows and columns to what I actually need. Is this not possible?


No it is not possible.

You can get the result you want with a data base ( See BASE) with a bit more work but with better data integrity.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
sheets
General User
General User


Joined: 19 Mar 2010
Posts: 6

PostPosted: Sun Mar 21, 2010 11:28 pm    Post subject: Reply with quote

Wow. Thank you all for your time, but the more I read, the more confused I'm getting. Please keep in mind that I'm a user, not a programmer or technical writer.

The first confusing thing is that several posters have said not to use Calc for spreadsheets, but to use Base. Yet when I open a new OO Calc and click "save" or "save as" (in Win), the default "type" is "ODF Spreadsheet (.ods)". If Calc should not be used for spreadsheets, why does it default to that description of the file type to save as?

Villeroy wrote:
By mere intention there is no concept of a "table" in spreadsheets.

Nowhere did I use the word "table" in my posts.
Villeroy wrote:
You have freely availlable matrices of cells and each cell is supposed to take any value or formula.

Which suits my needs perfectly, as described above.
Villeroy wrote:

Of course you can organize data in a rectangle of cells and treat that rectangle as a database-like table with a first row of column labels. But a spreadsheet allows completely different concepts as well as multiple tables side by side, below each other or in any arrangement of rows and columns.

I don't have a need for that; I can use new columns to create any new set of data and perform any operations on them. Also, please see above, in that Calc calls *itself* a spreadsheet document by default.
Villeroy wrote:
Sometimes the result of a mathematical operation is an array of variable size.
Not for my purposes. I gave a generic example in OP, so here is a specific example:

Column A = test scores of my students, #1-20, at beginning of class year, in rows 1-20.
Column B = test scores of same students at end of class year, numbered the same.
Column C = B minus A, i. e., improvement (or loss, if a negative number) for each numbered student.

Row 21A = mean of Column A.
Row 21B = mean of Column B.
Row 21C = mean of Column C = mean improvement during the year. (Let's skip standard deviations and root mean squares for simplicity, OK?)

This is an a array of fixed size for a given number of students. More students can be added or eliminated, if they leave during the year, but for any number of students, n, the array size is fixed. This is all I need to do (not that exact thing, but that type of data entering and manipulation).
Villeroy wrote:

To me your question indicates that you want a more structured and less error-prone way to organize data.

Please forgive me, but I don't see that I am needing any more structure than what is already present. I don't see any errors, or room for errors, or any mechanism for errors. As mentioned, it has worked perfectly so far.
Villeroy wrote:

In this case I use to recommend a database where you define the columnar structure of tables, valid entries and relations between tables before you start filling them with data.

Calc lets me label each row and column. I enter valid entries myself and need no preconditions (bounds-checking, etc.) I can define the relations before, during, or after entering data. (for example, the relation C = B - A). It will instantly update and perform that operation. Again, while I appreciate your helpfulness, I'm not sure what a database would provide that this simple tool does not.

Also, please forgive me if my terninology is incorrect, but as a layman, I think of a "database" as a means of *storing* data, not for performing operations among them. My hard-copy telephone book for my hometown is a database. Your forum server has a database of registered users. When I log in, my username is searched, found in the database, and then (hopefully a hashed version of) my password is compared to the (hopefully hashed) version stored in your db. If they match, a "true" is returned, and I am logged in; if not, an error is returned. This db adds and deletes users over time, but no operations are performed combining one user or set of users with another -- multiplying those with initial "A" by those with initial "B", etc., unlike my spreadsheet example.

If this is a misconception on my part, please forgive me. But this is why spreadsheet seemed to solve my needs perfectly and database seemed unnecessary.
pkon wrote:
...it actually is a big calculator only with advanced functions and formatting...

As said, that's good enough for me! Smile

jrkrideau answered my question the most directly:
sheets wrote:

Edit: Can I set the default to, say, 20 columns instead of 65,000+ ? How?

jrkrideau wrote:

The simple answer is that you cannot. A spreadsheet does not allow it. What you see is what you get. The alternatives for easy reading are a) format using lines, colours etc. or use Villroy's suggestion and move to a data base.

sheets wrote:

OO Calc seems extremely well suited to this. Why would I not use it? What else should I be using? I've been using it this way for several years.

jrkrideau wrote:

Certainly you can do it and it's fairly useful for doing this. In fact I think that this is what a spreadsheet (computer) was designed for.

Thank you, jrkrideau.
jrkrideau wrote:

You just cannot limit the number of columns and rows that appear.

*That* was the question -- the *only* question -- in the OP. I'm sorry that it diverged into other areas. As quoted from OP:
sheets wrote:

I'd just like to know how to limit the number of rows and columns to what I actually need. Is this not possible?

jrkrideau wrote:

No it is not possible.

Thank you, that was the question.

As for the rest of your reply,
jrkrideau wrote:

You can get the result you want with a data base ( See BASE) with a bit more work but with better data integrity.

I'd rather avoid the bit more work, and how does better integrity fit in? I've seen no corruption of data, provided I format properly (number of significant digits, currency, etc.) How would a db provide better integrity?

FWIW, I just opened "base" for the first time. Instead of a ready-to-fill-in form, I had to go through a wizard. After "finish", *still* no ready-to-fill form. As Villeroy said, I have to start defining my own properties, fields, etc. Why do this, when Calc has a ready-to-go form, where I immediatly start filling in data, then just add the functions (Sum, Median, Multiply, etc.)?

Calc works very well for my needs. The simple answer is that there is no way to limit the number of rows or columns. I don't see why this would be so hard -- in Writer, I have many paper sizes, fonts, etc. to choose from; why could not a menu be added to Calc for number of rows and columns? Sounds like an easy addition.

In the meantime, I'll use it as is -- happily -- and I'm sorry if my original question wasn't clear and therefore took the time of so many helpful responders. Thank you all for your time.
Back to top
View user's profile Send private message
pkon
OOo Enthusiast
OOo Enthusiast


Joined: 26 Feb 2010
Posts: 116

PostPosted: Mon Mar 22, 2010 12:24 am    Post subject: Reply with quote

I don't understand why you are getting confused.

Quote:
The first confusing thing is that several posters have said not to use Calc for spreadsheets, but to use Base. Yet when I open a new OO Calc and click "save" or "save as" (in Win), the default "type" is "ODF Spreadsheet (.ods)". If Calc should not be used for spreadsheets, why does it default to that description of the file type to save as?


A spreadsheet is a big calculator with formatting. You can only enter text and numbers and have them shown in different formatting. Calc is not doing any check of data entry and you can freely enter what ever you want. So calc is powerful in these areas and is not a database program. Why is it not a database program. Because it does not have any indices, data validation, type validation etc. So Calc being a spreadsheet and not a database is the reason why saving as a spreadsheet.

But coming back to your original proposal to make your own limits on columns and rows. Why?
Do you want to limit the area of errors. There is no validation of data in a spreadsheet (except what one can make in Data -> Validity) so everything is handled by hand any way. Is it just a matter of having a nice look?

Do you want to limit the error someone else could enter into the spreadsheet.
As said in my earlier reply you can enforce limit by formatting the area you would like to manipulate unprotected and then protect the sheet with the trade off it gives you.
But personally I don't get your point of the limitation you would like to enforce. I think that the path of this will move in direction to be only limited to your RAM.

Hope it explains the 'issue' in a better way for you.
Back to top
View user's profile Send private message
lacole
General User
General User


Joined: 22 Mar 2010
Posts: 26
Location: Mississauga, Ontario, Canada

PostPosted: Mon Mar 22, 2010 7:53 am    Post subject: Limit row and columns viewed. Why not use page view? Reply with quote

It you only want to see columns a-h, switch to page view, and adjust the page view to only show the columns you want to see.
lacole
Back to top
View user's profile Send private message
sheets
General User
General User


Joined: 19 Mar 2010
Posts: 6

PostPosted: Tue Mar 23, 2010 7:34 pm    Post subject: Reply with quote

pkon wrote:
I don't understand why you are getting confused.

I was confused because people kept telling me not to use a spreadsheet as a spreadsheet. But that's settled, at least in my mind, if not in theirs, thanks. Smile
pkon wrote:

A spreadsheet is a big calculator with formatting. You can only enter text and numbers and have them shown in different formatting. Calc is not doing any check of data entry and you can freely enter what ever you want.

That's all I ever wanted.

pkon wrote:

So calc is powerful in these areas and is not a database program. Why is it not a database program. Because it does not have any indices, data validation, type validation etc.

Don't need those, thanks.
pkon wrote:

But coming back to your original proposal to make your own limits on columns and rows. Why?

Just distracting. If someone sends you snailmail of a two-page letter and 65,533 blank pages, wouldn't you consider that a waste? Why show more rows and coumns than you are using? (rhetorical question -- reply not necessary.)
pkon wrote:

Do you want to limit the area of errors.

No, I take full responsibility for each entry. I just didn't like having an endless page that would scroll endlessly.
pkon wrote:

There is no validation of data in a spreadsheet (except what one can make in Data -> Validity) so everything is handled by hand any way. Is it just a matter of having a nice look?

Yes.
pkon wrote:

Do you want to limit the error someone else could enter into the spreadsheet.

No.
pkon wrote:

But personally I don't get your point of the limitation you would like to enforce.

See above. Thanks for your reply.
lacole wrote:

It you only want to see columns a-h, switch to page view, and adjust the page view to only show the columns you want to see.

I don't have "page view". Under the "view" menu, I have only "normal" and "page break preview". Could you please be more specific about the location of the menu item you are describing? It sounds like exactly what I came here to find out - not seeing them is as good as not having them. The empty space doesn't seem to take up any consequential disk space, RAM, etc. TIA.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 23, 2010 11:10 pm    Post subject: Reply with quote

You asked for a feature that is not in spreadsheets (by mere intention). The grid is not a "waste of space". It is simply availlable but not stored in the file.

Quote:
I was confused because people kept telling me not to use a spreadsheet as a spreadsheet.

The spreadsheets as we know them had been invented for purposes completely different from what they are used for nowadays.
Like most people you use it as a datbase. You may use a truck as a sports car but then you must not complain about the huge box in your back.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
lacole
General User
General User


Joined: 22 Mar 2010
Posts: 26
Location: Mississauga, Ontario, Canada

PostPosted: Wed Mar 24, 2010 5:37 am    Post subject: Reply with quote

Page break view is what I meant.

The size of your spreadsheet is determined by the number of sheets you have and the the amount of data you have you have in it. If you have empty sheets, delete them. Keep only the sheets that have data in them.

You can see what will be printed by selecting Page Preview. You can also select an area and print selected cells.
_________________
lacole
Mississauga, ON Canada
Oo 3.2.0 portable Using Windows Xp, Vista Home Premium
Oo 3.3 Using Vista Home Premium 64Bit
Back to top
View user's profile Send private message
Duffy
General User
General User


Joined: 22 Nov 2010
Posts: 5

PostPosted: Mon Nov 22, 2010 4:15 pm    Post subject: Reply with quote

In AppleWorks one can specify the number of rows and columns in any spreadsheet in the Format menu. The number of rows or columns can be increased or decreased at any time. That makes the spreadsheets look very neat and it is easy to fit them to a page. That functionality would be nice to have in OO.
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Tue Nov 23, 2010 9:44 pm    Post subject: Reply with quote

Wow, a product called "appleworks" still exists? Have not used that since my Apple IIe computer. I really liked the product.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
AaronC
General User
General User


Joined: 05 May 2010
Posts: 12

PostPosted: Wed May 04, 2011 7:34 pm    Post subject: Reply with quote

I just have to say I find this behaviour dreadful also.

As I struggle to understand everything Open Office does, moving to a "Database" is something totally non-trivial.

I want columns A-M thanks, nothing else, no scrolling, its horrid.

AC
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group