[OT] Limitations on Excel

Fusion_power · 2003-08-22, 21:25

Ok so this is an odd place to ask this question but I figure somebody who hangs out here might just know.

I have a couple of serious issues with Excel. The first and most important is the limitation of 65,536 rows of data. I routinely manipulate data that might go as high as a million rows. Is there any add in or other program that would allow me to manipulate a million rows?

The second is the number of digits displayed. Someone here recently shared a method of increasing the digits so I have a possible fix for this.

The third is the internal memory usage. I have several simple bot routines that harvest data. In the process, they open several thousand files from a server, gather the data, and close the file. After processing several hundred files, the processor slows down response time to a crawl and a memory check shows that almost all the available memory is allocated. Are there some good ways to check the memory usage and/or to free up the memory that is currently allocated but not used?

Fusion

sdbardwick · 2003-08-22, 21:41

Corel Quattro Pro 10 will allow exactly one million rows , but I don't know about the other issues (it came free with my Dell, but I don't use it).

Edit:
Just checked memory usage - with one column of integers (1 to 1,000,000), it's using 120MB of RAM, but doesn't seem horribly slow on a P4 2.6

Wacky · 2003-08-22, 21:59

I must ask "Why do you think that you should use a spread sheet program to process this volume of data?".

I would suggest that you consider some program that processes records from the file(s) and generates an appropriate summary.

There are many tools available for this purpose.

Perhaps you can describe just what it is that you are trying to accomplish.

Fusion_power · 2003-08-22, 22:31

Wackerbarth,

Excel is a common program among the several dozen people who receive the file. Otherwise I might use something more database oriented like Filemaker Pro. I can work around the limitations with little problem, but would rather be able to work without those limitations.

I also believe in getting the most possible bang for the buck on a program. Excel delivers some excellent tools for manipulating data in arcane ways. There are things it can do that most database programs can't.

The other factor to consider is that I am distributing the file to a group of relatively unsophisticated users. It has to be uncomplicated on the surface but capable of some real acrobatics by clicking just a few buttons.

The users include tech engineers, sales/marketing types, customer service, etc.

Wacky · 2003-08-23, 01:01

I understand many of the advantages of Excel. However, having used it extensively for decades, I also recognize many of its limitations.

It sounds to me as if you are trying to distribute a rather excessive number of individual records. Does each recipient really need each of the detail records? I was suggesting that you might be able to summarize or partition them so that it is not necessary to have so many records to deal with at a time.

Xyzzy · 2003-08-23, 05:34

Quote:

Originally Posted by Wackerbarth

...having used it extensively for decades...

http://www.wikipedia.org/wiki/Microsoft_Excel

I remember first using it back around 91-92 on my 286 IBM PS/1... I think I was running Windows 3.0 at that time...

I honestly didn't know it was that old... Someday I'm going to rule at Trivial Pursuit!

Wacky · 2003-08-23, 10:57

Quote:

Originally Posted by Xyzzy

I honestly didn't know it was that old...

Quote:

It was originally developed for the Mac in 1985

I was an "early adopter". The Microsoft programs were some of the best programs available on the Mac.

-- Until they quit writing them as Mac products and started copying their inferior Windows version.

2003-08-22, 21:25	#1
Fusion_power Aug 2003 Snicker, AL 7×137 Posts	[OT] Limitations on Excel Ok so this is an odd place to ask this question but I figure somebody who hangs out here might just know. I have a couple of serious issues with Excel. The first and most important is the limitation of 65,536 rows of data. I routinely manipulate data that might go as high as a million rows. Is there any add in or other program that would allow me to manipulate a million rows? The second is the number of digits displayed. Someone here recently shared a method of increasing the digits so I have a possible fix for this. The third is the internal memory usage. I have several simple bot routines that harvest data. In the process, they open several thousand files from a server, gather the data, and close the file. After processing several hundred files, the processor slows down response time to a crawl and a memory check shows that almost all the available memory is allocated. Are there some good ways to check the memory usage and/or to free up the memory that is currently allocated but not used? Fusion

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
The Limitations of the Cranial GPU	Flatlander	Science & Technology	3	2013-06-13 13:34
maximum theoretical speed of LL test w/o bandwidth limitations?	ixfd64	Hardware	30	2012-03-05 06:16
Help on a macro in excel	Bundu	Programming	6	2007-10-26 13:29

2003-08-22, 21:41	#2
sdbardwick Aug 2002 North San Diego County 5·137 Posts	Corel Quattro Pro 10 will allow exactly one million rows , but I don't know about the other issues (it came free with my Dell, but I don't use it). Edit: Just checked memory usage - with one column of integers (1 to 1,000,000), it's using 120MB of RAM, but doesn't seem horribly slow on a P4 2.6

2003-08-22, 21:59	#3
Wacky Jun 2003 The Texas Hill Country 3²·11² Posts	I must ask "Why do you think that you should use a spread sheet program to process this volume of data?". I would suggest that you consider some program that processes records from the file(s) and generates an appropriate summary. There are many tools available for this purpose. Perhaps you can describe just what it is that you are trying to accomplish.

2003-08-22, 22:31	#4
Fusion_power Aug 2003 Snicker, AL 7·137 Posts	Wackerbarth, Excel is a common program among the several dozen people who receive the file. Otherwise I might use something more database oriented like Filemaker Pro. I can work around the limitations with little problem, but would rather be able to work without those limitations. I also believe in getting the most possible bang for the buck on a program. Excel delivers some excellent tools for manipulating data in arcane ways. There are things it can do that most database programs can't. The other factor to consider is that I am distributing the file to a group of relatively unsophisticated users. It has to be uncomplicated on the surface but capable of some real acrobatics by clicking just a few buttons. The users include tech engineers, sales/marketing types, customer service, etc.

2003-08-23, 01:01	#5
Wacky Jun 2003 The Texas Hill Country 3²·11² Posts	I understand many of the advantages of Excel. However, having used it extensively for decades, I also recognize many of its limitations. It sounds to me as if you are trying to distribute a rather excessive number of individual records. Does each recipient really need each of the detail records? I was suggesting that you might be able to summarize or partition them so that it is not necessary to have so many records to deal with at a time.