Not able to sort, 2 million + rows

[Guest]

My excel 07 database is saying that I have 26,401,166 rows. I have 700 rows
and 19 columns of data.

I cannot sort my columns due to the oversize of my file. Can anyone help!?

 

[Guest]

Update:
Excel will not allow me to sort or to select and delete rows as it says
close other apps, etc. (nothing else is open).

Help!

 

[Guest]

A couple of things to try.

Can you copy the sheet into a new workbook? That may recover it for you.

If the sheet copy doesn't work, not sure if this will work either, but you
can try:

Open a new, empty workbook (this is probably the part that might not work
either).
Choose cell A1 in the empty book, type = and then go to the hosed book and
click cell A1 on the sheet there, hit [enter].

Back in the new workbook edit the formula to remove the $ in it (changing
!$A$1 to simply !A1). In the Name Box (the box above the column A & B
letters that should now say A1) type in A1:S700 and press [Enter] then on
the Home tab, in the Editing group choose Click Fill and first choose Right
then click it again and choose Down.

With the entire area still selected, use Edit | Copy followed by Paste |
Paste Values to turn the links into actual values.

Problem with this is that you lose any formulas and formatting.

Good luck, hope it helps some.

 

[Guest]

Thank you,

Copying didn't work. Initially I couldn't get A1 to unhide. I ran a
corrupted file repair and it reappeared. I followed your instructions and
retrieved the database. When I went to format the table it asked if I wanted
to format rows 1 thru 10 million or so. I chose 1 thru 800.

Now, can someone please tell me how to change the zeros in the blank cells
back to blank cells?

A couple of things to try.

Can you copy the sheet into a new workbook? That may recover it for you.

If the sheet copy doesn't work, not sure if this will work either, but you
can try:

Open a new, empty workbook (this is probably the part that might not work
either).
Choose cell A1 in the empty book, type = and then go to the hosed book and
click cell A1 on the sheet there, hit [enter].

Back in the new workbook edit the formula to remove the $ in it (changing
!$A$1 to simply !A1). In the Name Box (the box above the column A & B
letters that should now say A1) type in A1:S700 and press [Enter] then on
the Home tab, in the Editing group choose Click Fill and first choose Right
then click it again and choose Down.

With the entire area still selected, use Edit | Copy followed by Paste |
Paste Values to turn the links into actual values.

Problem with this is that you lose any formulas and formatting.

Good luck, hope it helps some.

My excel 07 database is saying that I have 26,401,166 rows. I have 700 rows
and 19 columns of data.

I cannot sort my columns due to the oversize of my file. Can anyone help!?

 

[Guest]

Glad you got your database back!

Can't help too far with the hide zero values. In 2003 it was under:
Tools | Options | [View] tab and check/clear "Zero Values" option in the
Window area.

I don't have it memorized for 2007, but to get to the equivalent of Tools |
Options in 2007, click on the big round Office button at upper left. Near
the lower right corner of the window that opens is a button [Options], click
it and hunt around a bit for the specific group and setting?

If you haven't posted back that you have found it within a couple or 3
hours, I'll post more details as I'll be at a machine with 2007 on it by then.

Thank you,

Copying didn't work. Initially I couldn't get A1 to unhide. I ran a
corrupted file repair and it reappeared. I followed your instructions and
retrieved the database. When I went to format the table it asked if I wanted
to format rows 1 thru 10 million or so. I chose 1 thru 800.

Now, can someone please tell me how to change the zeros in the blank cells
back to blank cells?

A couple of things to try.

Can you copy the sheet into a new workbook? That may recover it for you.

If the sheet copy doesn't work, not sure if this will work either, but you
can try:

Open a new, empty workbook (this is probably the part that might not work
either).
Choose cell A1 in the empty book, type = and then go to the hosed book and
click cell A1 on the sheet there, hit [enter].

Back in the new workbook edit the formula to remove the $ in it (changing
!$A$1 to simply !A1). In the Name Box (the box above the column A & B
letters that should now say A1) type in A1:S700 and press [Enter] then on
the Home tab, in the Editing group choose Click Fill and first choose Right
then click it again and choose Down.

With the entire area still selected, use Edit | Copy followed by Paste |
Paste Values to turn the links into actual values.

Problem with this is that you lose any formulas and formatting.

Good luck, hope it helps some.

My excel 07 database is saying that I have 26,401,166 rows. I have 700 rows
and 19 columns of data.

I cannot sort my columns due to the oversize of my file. Can anyone help!?

 

[Guest]

A couple of future resources:
Interactive help tool (internet connection required - won't give your exact
answer
http://www.microsoft.com/downloads/...BD-2758-47B3-9F90-93788112B985&displaylang=en

Downloadable workbook: may be of more assistance
http://office.microsoft.com/search/redir.aspx?AssetID=AM101938681033&CTT=5&Origin=HA100625841033

Thank you,

Copying didn't work. Initially I couldn't get A1 to unhide. I ran a
corrupted file repair and it reappeared. I followed your instructions and
retrieved the database. When I went to format the table it asked if I wanted
to format rows 1 thru 10 million or so. I chose 1 thru 800.

Now, can someone please tell me how to change the zeros in the blank cells
back to blank cells?

A couple of things to try.

Can you copy the sheet into a new workbook? That may recover it for you.

If the sheet copy doesn't work, not sure if this will work either, but you
can try:

Open a new, empty workbook (this is probably the part that might not work
either).
Choose cell A1 in the empty book, type = and then go to the hosed book and
click cell A1 on the sheet there, hit [enter].

Back in the new workbook edit the formula to remove the $ in it (changing
!$A$1 to simply !A1). In the Name Box (the box above the column A & B
letters that should now say A1) type in A1:S700 and press [Enter] then on
the Home tab, in the Editing group choose Click Fill and first choose Right
then click it again and choose Down.

With the entire area still selected, use Edit | Copy followed by Paste |
Paste Values to turn the links into actual values.

Problem with this is that you lose any formulas and formatting.

Good luck, hope it helps some.

My excel 07 database is saying that I have 26,401,166 rows. I have 700 rows
and 19 columns of data.

I cannot sort my columns due to the oversize of my file. Can anyone help!?

 

[Guest]

Found it!
'Office Button' | Options button, in left pane, choose [Advanced] then
scroll down to find the "Display options for this worksheet" (alt+S should
get you there) and in that bunch you'll find the "Show a zero in cells that
have zero value". Clear the checkbox next to that.

A couple of future resources:
Interactive help tool (internet connection required - won't give your exact
answer)
http://www.microsoft.com/downloads/...BD-2758-47B3-9F90-93788112B985&displaylang=en

Downloadable workbook: may be of more assistance.
http://office.microsoft.com/search/redir.aspx?AssetID=AM101938681033&CTT=5&Origin=HA100625841033

Thank you,

Copying didn't work. Initially I couldn't get A1 to unhide. I ran a
corrupted file repair and it reappeared. I followed your instructions and
retrieved the database. When I went to format the table it asked if I wanted
to format rows 1 thru 10 million or so. I chose 1 thru 800.

Now, can someone please tell me how to change the zeros in the blank cells
back to blank cells?

A couple of things to try.

Can you copy the sheet into a new workbook? That may recover it for you.

If the sheet copy doesn't work, not sure if this will work either, but you
can try:

Open a new, empty workbook (this is probably the part that might not work
either).
Choose cell A1 in the empty book, type = and then go to the hosed book and
click cell A1 on the sheet there, hit [enter].

Back in the new workbook edit the formula to remove the $ in it (changing
!$A$1 to simply !A1). In the Name Box (the box above the column A & B
letters that should now say A1) type in A1:S700 and press [Enter] then on
the Home tab, in the Editing group choose Click Fill and first choose Right
then click it again and choose Down.

With the entire area still selected, use Edit | Copy followed by Paste |
Paste Values to turn the links into actual values.

Problem with this is that you lose any formulas and formatting.

Good luck, hope it helps some.

:

My excel 07 database is saying that I have 26,401,166 rows. I have 700 rows
and 19 columns of data.

I cannot sort my columns due to the oversize of my file. Can anyone help!?