Changing the number of available columns in MS Excel

[Tracie 10]

Does anyone know if there is a way to increase the maximum number of columns in MS Excel? The maximum number allowed is 255 (or thereabouts) but I really need to increase that to 512. Any ideas on how to do this, or is it completely impossible?

 

Thanks in advance

 

Tracie

[Scutts 10]

I'm afaid it is not possible to do this

[JoeP 11]

Tracie,

 

Sorry, I don't think that's a goer. I think there's a limit on the maximum column count and you've hit it.

 

Can I ask what you're trying to do? Can you pivot your data though 90'?

 

Joe

[Ann* 10]

Originally posted by TracieJC

Does anyone know if there is a way to increase the maximum number of columns in MS Excel? The maximum number allowed is 255 (or thereabouts) but I really need to increase that to 512. Any ideas on how to do this, or is it completely impossible?

 

Thanks in advance

 

Tracie

Can I ask why you need 512?

 

The only suggestion I can make is to use two sheets in the same workbook. Apparently the maximum number of columns is actually 256, so you could have two sheets of 256 columns.

[Tracie 10]

Originally posted by JoePritchard

Can I ask what you're trying to do? Can you pivot your data though 90'?

 

I have some images that are comprised of 512 x 512 pixels. I was hoping to import them into Excel as an ASCII file and then do some analysis on various bits of the image.... however it's not really essential to import the whole cadboodle, I can just take line sections through the image anyway. I just thought that someone out there *might* know a funky way of modifying Excel but not to worry Thanks for all the answers though!

 

Tracie

[metalman 21]

I had a look at this site:

 

http://www.mrexcel.com/

 

and searching for 'maximum number columns' gave me this explanation:

 

"The 256-column limitation

Every Excel worksheet is limited to 256 columns. Despite what must amount to thousands of requests over the years, Microsoft refuses to increase the number of columns in a worksheet.

 

Beginners often discover this limitation when they want to set up a spreadsheet that contains data for each day in a year. If they store the data horizontally, they run out of column in mid-September.

 

So we're stuck with 256. Why such a weird number? Why not 250? Or 365? The number of rows and columns is a by-product of the binary number system. 256 is 2, raised to the eight power, which is the maximum value that can be stored using eight bits. The number of rows in a worksheet is 65,536, which is 2^16. Older versions of excel contained only 16,384 rows, which is 2^14 power.

 

The reason for the 256-column limitation is probably due to the fact that Excel is so old, and it contains lots of code that would be "broken" if the number of columns were increased.

 

Following are a few related facts:

 

Corel's Quattro Pro spreadsheet supports 18,278 columns and up to 1,000,000 rows.

The spreadsheet in Microsoft's Office Web Components supports 18,278 columns and 262,144 rows"

 

So that looks like the final word on the subject.

 

Only other suggestion: can you use Microsoft Access which apparently can handle larger amounts of data than Excel, but I don't know whether it will do what you want image-wise. Don't know much about it, but would Mathematica be an alternative too if you can get hold of it (and work out how to use it)?

[evildrneil 14]

You could try using openoffice? It comes with something called calc which operates as Excel. I've never actually used it so I can't claim that it doesn't suffer from the same limits but its a free download (from http://www.openoffice.org) so may be worth a try

[Tracie 10]

Thanks for finding that for me metalman - that was pretty much what I imagined the case might be (Microsoft! ). I tried Origin but it suffers from the same limitation. As for Mathematica... I haven't tried that yet but I bet there's probably access to it somewhere in the department - I'll give that a try too. I'm also just about to have a nosey at DrNeil's link, so perhaps that might come up with something. I'll let you know once I've downloaded it

 

Thanks again for all the responses

[Edd 10]

Originally posted by evildrneil

You could try using openoffice? It comes with something called calc which operates as Excel. I've never actually used it so I can't claim that it doesn't suffer from the same limits but its a free download (from http://www.openoffice.org) so may be worth a try

 

Alas, openoffice also appears to suffer from this limitation

[JoeP 11]

Hi Metalman, Tracie

 

Yep, when Excel first came out it was designed for use in systems where the limiting step was going to be memory and in environments where the limit was the sophistication of the users.

 

In some respects from an IT tool usage point of view, there is a subjective reason why 256 or thereabouts is a good column limit and that's that if you have a problem that relies on having more than 256 columns on one spreadsheet then perhaps you're using teh wrong tool. Spreadsheets get dragged in to all sorts of these 'solution spaces' where they have no right to be.

 

Now....having slept on this....Tracie, is your image data in the form of a set of ASCII strings?

 

If so, here is a cunning plan.

 

Load the string data on to Worksheet 1.

Now find a friendly VBA programmer to write a Macro that takes the strings, goes through them a letter at a time, and pops the extracted character from the first 255 in to a cell in a corresponding row on Worksheet 2. Then from the second 255, put the ASCII character in to a cell in a corresponding row on Worksheet 3. This splits your row of character data across two tabbed worksheets.

Repeat until the whole image is 'split'.

 

Now your processing of teh image data will have to work across two tabbed worksheets but that's not that difficult.

 

I think this will allow you to do what you need.

 

Do you know how much I usually charge for these pearls of wisdom?

 

Hope this helps,

 

Joe

[Edd 10]

Originally posted by JoePritchard

Now....having slept on this....Tracie, is your image data in the form of a set of ASCII strings?

 

If so, here is a cunning plan.

 

Load the string data on to Worksheet 1.

Now find a friendly VBA programmer to write a Macro that takes the strings, goes through them a letter at a time, and pops the extracted character from the first 255 in to a cell in a corresponding row on Worksheet 2. Then from the second 255, put the ASCII character in to a cell in a corresponding row on Worksheet 3. This splits your row of character data across two tabbed worksheets.

Repeat until the whole image is 'split'.

 

Now your processing of teh image data will have to work across two tabbed worksheets but that's not that difficult.

 

 

Seems wierd in this day and age to run up against a limit of 256 anywhere. Wonder if they'll ever sort it out. It surprised me that Open Office suffers the same problem - but i guess they want to retain their backwards compatibility with M$ too.

 

Good suggestion about splitting across two sheets Joe - not sure if Tracie knows a good VBA programmer, but she does know a friendly one

[JoeP 11]

You'd be surprised how often that 8 bit limit comes crashing in to stuff. Hell, I've just had to use DOS batch file programming on a state of the art GIS system, of all things. Bizarre.

 

The code is pretty straight forward - just set up three loops - one for the rows, and then two for the columns in each row. The first colun loop writes to Sheet 1, the second to Sheet 2. Saves having to faff about with counting what column you're in.

 

The processing of the images will depend upon what's being done. I envisage more problems there, actually.

 

Good Luck!!

 

Joe