Excel 2007 sports a massive grid. Take a tour of cell XFD1084576 to get a sense of just how big the new grid really is. Also: how to unlock the extra rows when you open your legacy workbooks in Excel. Episode 401 shows you how.
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
It's a 2007 Tuesday, we’re going to talk about some of the great new features in Excel 2007.
And we now know that January 30th 2007 is the retail date, that's the date that the box will be available on your local retailer shelves.
The most amazing new thing about the new Excel is the grid size.
If you're using the current Excel and you're regularly importing lots of data, you will probably run into the limit of 65536, that's the number of rows in the current version of Excel, going back to Excel 97.
We're also limited to 256 columns, column IV, if you're trying to put daily dates going across your worksheet, you can't fit an entire year’s worth of daily dates in the old Excel.
The new Excel blows through those limits, the new row size, 1.1 million rows, the new column size goes out to column XFD, that's over 16000 columns!
If you wanted to put weekdays going across, you can now fit 46 years worth of weekdays.
Let me illustrate this, you can see this little diagram here, this yellow box is the Excel that we're living in right now, this green area is the new Excel!
There's over 17 billion cells on a worksheet, and that's just Sheet1!
To give you an idea, we could put the name of every living person on Earth, and only fill up <50% of the worksheet.
Or, to give you another idea, we could put 1% in every Excel cell on Sheet1, and not quite have enough for Bill Gates’ net worth!
Anyway, one frustrating thing, if I choose the worksheet and hit End, down, End, right, you'll see that we get out to that last cell, that cell XFD1048576, fantastic thing.
But the very first day that I got the new Excel, I immediately went out, I opened a file, and I did basically the same thing, I went End, down, End, down, and I only got to row 65536.
I was so upset!
Everyone had told me about the new 1.1 million rows, and here I only have 65000 rows.
What's going on?
If you look up in the title bar, you'll see that we're in something called Compatibility Mode.
Compatibility Mode says “Hey, we're going to be able to save this, that way you can go back to an old version of Excel, so we're not going to let you get to more than 65000 rows.” Two ways to solve the problem, and I hate the first way.
If you use File, Convert, Excel will delete your original file and save the file in the new Excel 2007 format, it really hacks me off that they delete my original file.
Instead, I prefer to use Save As, save as either a Macro-Enabled Workbook or a Binary Workbook, or an Excel workbook.
I'm going to go with Macro-Enabled, save it “Tips and Tricks”, you'll see that we have a new file extension, xlsm stands for macro.
We also have xlsx or xlsb, three different file formats in Excel 2007, click Save.
Now to still get beyond 65536, you have to do File, Close, and then reopen the file that you just saved.
And now, when you hit End, down, you will go to 1.1 million rows.
There you have it, 1.1 million rows by 16000 columns in the new Excel, it's fantastic!
However, when you open up an old legacy workbook in Excel, you're only going to get to 65000 until you either Convert the file, or Save As and then Close and reopen it.
Hey, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
It's a 2007 Tuesday, we’re going to talk about some of the great new features in Excel 2007.
And we now know that January 30th 2007 is the retail date, that's the date that the box will be available on your local retailer shelves.
The most amazing new thing about the new Excel is the grid size.
If you're using the current Excel and you're regularly importing lots of data, you will probably run into the limit of 65536, that's the number of rows in the current version of Excel, going back to Excel 97.
We're also limited to 256 columns, column IV, if you're trying to put daily dates going across your worksheet, you can't fit an entire year’s worth of daily dates in the old Excel.
The new Excel blows through those limits, the new row size, 1.1 million rows, the new column size goes out to column XFD, that's over 16000 columns!
If you wanted to put weekdays going across, you can now fit 46 years worth of weekdays.
Let me illustrate this, you can see this little diagram here, this yellow box is the Excel that we're living in right now, this green area is the new Excel!
There's over 17 billion cells on a worksheet, and that's just Sheet1!
To give you an idea, we could put the name of every living person on Earth, and only fill up <50% of the worksheet.
Or, to give you another idea, we could put 1% in every Excel cell on Sheet1, and not quite have enough for Bill Gates’ net worth!
Anyway, one frustrating thing, if I choose the worksheet and hit End, down, End, right, you'll see that we get out to that last cell, that cell XFD1048576, fantastic thing.
But the very first day that I got the new Excel, I immediately went out, I opened a file, and I did basically the same thing, I went End, down, End, down, and I only got to row 65536.
I was so upset!
Everyone had told me about the new 1.1 million rows, and here I only have 65000 rows.
What's going on?
If you look up in the title bar, you'll see that we're in something called Compatibility Mode.
Compatibility Mode says “Hey, we're going to be able to save this, that way you can go back to an old version of Excel, so we're not going to let you get to more than 65000 rows.” Two ways to solve the problem, and I hate the first way.
If you use File, Convert, Excel will delete your original file and save the file in the new Excel 2007 format, it really hacks me off that they delete my original file.
Instead, I prefer to use Save As, save as either a Macro-Enabled Workbook or a Binary Workbook, or an Excel workbook.
I'm going to go with Macro-Enabled, save it “Tips and Tricks”, you'll see that we have a new file extension, xlsm stands for macro.
We also have xlsx or xlsb, three different file formats in Excel 2007, click Save.
Now to still get beyond 65536, you have to do File, Close, and then reopen the file that you just saved.
And now, when you hit End, down, you will go to 1.1 million rows.
There you have it, 1.1 million rows by 16000 columns in the new Excel, it's fantastic!
However, when you open up an old legacy workbook in Excel, you're only going to get to 65000 until you either Convert the file, or Save As and then Close and reopen it.
Hey, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
