Dear Excel 2003, I need my columns back.
April 30, 2010 9:25 AM Subscribe
A few questions about Excel 2003.
1. There's a large spreadsheet, in Excel 2003 under Windows XP, that has over 256 rows, which evidently is the limit of the program. Do later versions of Excel support the ability to load more than 256 tables and if so, which versions, or what OS (Mac or PC)?
1a. Baring that ability, is there another way to get those last 20 columsn to load? I tried deleting some other columns, doing a save as and reloading the file, but those last columns still don't show up.
2. Within the spreadsheet described above, is it possibel to search for and isolate ROWS which do not have data in at least 25 of the 276 columns? If so, how?
Asuume I'm a total new Excel newb and speak slowly please!
1. There's a large spreadsheet, in Excel 2003 under Windows XP, that has over 256 rows, which evidently is the limit of the program. Do later versions of Excel support the ability to load more than 256 tables and if so, which versions, or what OS (Mac or PC)?
1a. Baring that ability, is there another way to get those last 20 columsn to load? I tried deleting some other columns, doing a save as and reloading the file, but those last columns still don't show up.
2. Within the spreadsheet described above, is it possibel to search for and isolate ROWS which do not have data in at least 25 of the 276 columns? If so, how?
Asuume I'm a total new Excel newb and speak slowly please!
Best answer: Excel 2007 for Windows will give you 16,384 columns (I assume you mean columns and not rows). If you don't want to shell out for it right now there is a 2010 Beta out now that is good for use until October.
posted by clarkie666 at 9:31 AM on April 30, 2010
posted by clarkie666 at 9:31 AM on April 30, 2010
1. Microsoft says: The Excel 2007 "Big Grid" increases the maximum number of rows per worksheet from 65,536 to over 1 million, and the number of columns from 256 (IV) to 16,384 (XFD).
posted by oxit at 9:36 AM on April 30, 2010
posted by oxit at 9:36 AM on April 30, 2010
Can you explain what you mean by "isolate rows"? Do you want to move them a new worksheet (tab)? Do you want to move them to a different place in the same worksheet?
posted by desjardins at 9:37 AM on April 30, 2010
posted by desjardins at 9:37 AM on April 30, 2010
Try the excel count blank function
You could do this for rows / columns and then use either:
- conditional formating to highlight rows/columns
or
- auto filter and create a custom filter for all rows/colunms >= 25
posted by MechEng at 9:41 AM on April 30, 2010
You could do this for rows / columns and then use either:
- conditional formating to highlight rows/columns
or
- auto filter and create a custom filter for all rows/colunms >= 25
posted by MechEng at 9:41 AM on April 30, 2010
Regarding number 2. The COUNTBLANK function allows you to count blank cells. A way to isolate or filter (though someone may pipe up with a more elegant answer) would be
1. Create a new column at the start of your sheet (Column A)/ Right click the A header and choose Insert.
2. In this place the following formula =COUNTBLANK(X:Y) where X is the start of your column range (e.g. B2) and Y is the end of your range (e.g. GG2). This will count the number of blank cells in the row in which it is placed. Fill down this formula to all rows. It should update the range to match the row automatically.
3. You can then apply a filter on the A column to only show rows that have a higher number of blanks than 25. So use the Sort and Filter functionality and set it to cells that equal at least 25.
Feel free to memail me for an example.
posted by clarkie666 at 9:45 AM on April 30, 2010
1. Create a new column at the start of your sheet (Column A)/ Right click the A header and choose Insert.
2. In this place the following formula =COUNTBLANK(X:Y) where X is the start of your column range (e.g. B2) and Y is the end of your range (e.g. GG2). This will count the number of blank cells in the row in which it is placed. Fill down this formula to all rows. It should update the range to match the row automatically.
3. You can then apply a filter on the A column to only show rows that have a higher number of blanks than 25. So use the Sort and Filter functionality and set it to cells that equal at least 25.
Feel free to memail me for an example.
posted by clarkie666 at 9:45 AM on April 30, 2010
Best answer: In a new column
use this formula
=COUNTBLANK(range)
where range would be (a1:zz1) [or click and drag to define the range]
Click the bottom right corner of the box and drag this down the other cells of column or use copy paste function of formula.
This will explain conditional formating for entire row
This will explain how to add an autofilter
posted by MechEng at 9:48 AM on April 30, 2010
use this formula
=COUNTBLANK(range)
where range would be (a1:zz1) [or click and drag to define the range]
Click the bottom right corner of the box and drag this down the other cells of column or use copy paste function of formula.
This will explain conditional formating for entire row
This will explain how to add an autofilter
posted by MechEng at 9:48 AM on April 30, 2010
Have you tried OpenOffice? According to their website, the latest version of OpenOffice Calc (3.x) supports up to 1,024 columns. OpenOffice is free and does pretty much everything that MS Office 2003 does.
The only drawback to using it would be if you are collaborating with someone else who is also using Excel 2003 then they will not be able to open a file with more than 256 columns.
posted by jonnyploy at 9:59 AM on April 30, 2010
The only drawback to using it would be if you are collaborating with someone else who is also using Excel 2003 then they will not be able to open a file with more than 256 columns.
posted by jonnyploy at 9:59 AM on April 30, 2010
Seconding OpenOffice. It supports more than 256 columns, it should feel very familiar to an Excel 2003 user, has great compatibility with XLS files, and is free of charge.
The main difference you'll see when using OpenOffice as opposed to Excel is that function parameters need to be separated with a semicolon instead of a comma. ie:
Excel: =CONCATENATE("String One", "String Two")
OpenOffice: =CONCATENATE("String One"; "String Two")
No idea why that particular design decision was made. But when you open/save Excel files it's smart enough to convert the notation, so formulas you type in OpenOffice will work in Excel and vice-versa.
posted by Vorteks at 10:07 AM on April 30, 2010
The main difference you'll see when using OpenOffice as opposed to Excel is that function parameters need to be separated with a semicolon instead of a comma. ie:
Excel: =CONCATENATE("String One", "String Two")
OpenOffice: =CONCATENATE("String One"; "String Two")
No idea why that particular design decision was made. But when you open/save Excel files it's smart enough to convert the notation, so formulas you type in OpenOffice will work in Excel and vice-versa.
posted by Vorteks at 10:07 AM on April 30, 2010
Excel 2003 has a limit of 256 columns (the vertical rows, the ones named with letters) and just over 60,000 rows (the horizontal rows, the ones named with numbers). All versions of 2007 have a higher limit of columns, like others said. I don't think it is possible to make a file using 2003 with more than 256 columns.
If you can use someone's 2007 to mess with the file but want to do most of the work in 2003, here is what I suggest:
If there are less than 256 rows in the file, Transpose the data. This means you rotate the data so that the rows become the columns and the columns become the rows. Now you should be able to see all of the data. You do this by selecting all of the data, copying it and then going to Edit - Paste Special and then checking the Transpose box. You may want to paste the data in another location to avoid having leftovers of your old data.
If there are too many rows to do this, you'll need to separate the data into two sheets within the same file. To keep everything aligned, each row should have a unique identifier. Hopefully you already have something like an account number or employee name. If you don't, make a new column and name it "ID". Then type 1 for the first row, two for the second row and then highlight them both. Now double click the black square in the lower right corner of the box you highlighted (your cursor should turn into a black plus sign when you are hovered over the correct box). This should auto fill the whole column with unique numbers.
Now, add a sheet (Insert - Worksheet) or use a blank one. Copy the column with the unique identifier to the new sheet. Cut and Paste enough columns from the old sheet to the new sheet to keep the old sheet under 256. Now you can use some of the formulas above on both sheets and add them together to get your count of blanks.
posted by soelo at 10:18 AM on April 30, 2010
If you can use someone's 2007 to mess with the file but want to do most of the work in 2003, here is what I suggest:
If there are less than 256 rows in the file, Transpose the data. This means you rotate the data so that the rows become the columns and the columns become the rows. Now you should be able to see all of the data. You do this by selecting all of the data, copying it and then going to Edit - Paste Special and then checking the Transpose box. You may want to paste the data in another location to avoid having leftovers of your old data.
If there are too many rows to do this, you'll need to separate the data into two sheets within the same file. To keep everything aligned, each row should have a unique identifier. Hopefully you already have something like an account number or employee name. If you don't, make a new column and name it "ID". Then type 1 for the first row, two for the second row and then highlight them both. Now double click the black square in the lower right corner of the box you highlighted (your cursor should turn into a black plus sign when you are hovered over the correct box). This should auto fill the whole column with unique numbers.
Now, add a sheet (Insert - Worksheet) or use a blank one. Copy the column with the unique identifier to the new sheet. Cut and Paste enough columns from the old sheet to the new sheet to keep the old sheet under 256. Now you can use some of the formulas above on both sheets and add them together to get your count of blanks.
posted by soelo at 10:18 AM on April 30, 2010
« Older Is it common for motor vehicle laws to have... | Now that Lala.com is shutting down, what are some... Newer »
This thread is closed to new comments.
I'm unsure about columns, but Excel 2003 only supports about 66,000 rows. I was dealing with a data set with about twice that. My IT guys upgraded me, and 2007 had no problem loading the 124,000 rows in my data (with about 35 columns, for a total of 34MB file)
posted by kimdog at 9:30 AM on April 30, 2010