Too many rows displaying on spreadsheet


H

HelloFromMass

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have a spreadsheet that has roughly 1,000 rows by 15 columns. HOWEVER, rather than the spreadsheet displaying a few more hundred below that, it is displaying 10,000's more rows. It is a bother rather than a real problem, but i can't use the scroll bar on the right. Any way to reduce the number of empty rows that are displaying? Thanks
 
Ad

Advertisements

C

CyberTaz

The number of rows is fixed @ 1,048,576 but you can select & hide the ones
you don't want to have displayed using the Format> Row> Hide command.

The unanswered question, though, is "Why can't you use the Vertical Scroll
Bar?" There's nothing about the number of displayed rows that disables it in
any way. It's operation is geared to the number of rows actually used in the
sheet.
 
H

HelloFromMass

Thanks Bob. Yes, that is exactly the issue I have, the vertical scroll bar. It works on the other sheets in the workbook, but, of course it doesn't, on the largest one. I have tried to see if there is data in a cell out in the hinterlands that is causing this, but doesn't look that way.
 
C

CyberTaz

I'm afraid I can't do much to help if you don't fully describe the problem
you're having. Vague references such as "... it doesn't [work]" & "... can't
use" don't tell us anything. *Why* can't you use it? Exactly what is the
problem & how is it manifesting itself differently than what you expect?

Regards |:>)
Bob Jones
[MVP] Office:Mac
 
J

John McGhie

Being a Word MVP, this is an irritating habit of Excel that annoys the hell
out of me, too. The problem appears worse in Excel 2008 because it provides
the new "Big Grid" that goes down a million rows.

That's a highly-prized advance if you want to use a million rows, but it
makes navigating a bit more difficult :) Doubly so because we have no VBA.
Normally you use VBA to go to the end and search "up" using "Find" to get
the last row containing data, but we can't do that because Excel 2008 has no
"Search Backwards" command.

Here's how to overcome it.

You need to know three things:

1) Excel considers "Formatting" to be "content". For example, if you type
a date in a cell, Excel formats the cell as a date. If you then delete the
date, Excel still considers that cell has content, because it has a date
format. To really "empty" that cell, you need to use Edit>Clear>All...

2) Excel maintains a "Last used cell" range in each worksheet. But it does
not reset this range UNTIL YOU SAVE.

3) Excel uses a "sparse matrix" file format to hold down the file size.
That means that while it always "knows about" the big grid of 1,048,576 rows
by 16,384 columns, those rows and columns do not actually "exist" in the
file unless they contain content (either data, or formatting, or both).

To Find the Last Cell in a Sheet:

Click in Cell A1, then use Control + End. The selected cell will be the one
Excel "thinks" is the lowest one that actually contains anything (even if
you can't see anything there...)

To Find the Last Cell that contains Data

1) Save the Workbook

2) Use Command + End

The selected cell will be the last one that actually contains data.

If you know the last data is in Row 1000:

1) Use Control + G to open the GoTo window.

2) Enter location A1000

3) Now scroll down and identify the last data row by eye.

4) Click in column A of the row below that.

5) Hit Command + Shift + End to select from there to the bottom.

6) Use Edit>Delete>Entire Row to delete the empty rows.

7) Save to reset the "Last used cell" to the area that contains data.

Hope this helps

Thanks Bob. Yes, that is exactly the issue I have, the vertical scroll bar.
It works on the other sheets in the workbook, but, of course it doesn't, on
the largest one. I have tried to see if there is data in a cell out in the
hinterlands that is causing this, but doesn't look that way.
--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]
 
Ad

Advertisements

B

blockparty

Thanks a lot John, fixed the same problem I was having where suddenly my scroll bar was scaled for over a million rows when I was only using about 300. Thanks again!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top