Chapter 7. The Big Grid
In this chapter
Other Limits in Excel 2007 125
Working with Excel 2003 Named Ranges in Excel 2007 127
Tips for Navigating the Big Grid 127
The biggest fundamental change in Excel 2007 is the increased grid size. Microsoft is fond of calling this “the big grid.” I would say that “big” is an understatement. When I heard rumors in 2004 that Microsoft would add rows to Excel, I thought perhaps they would double the 65,536 rows and Excel would have 130K rows. I even mentioned to Excel project manager Dave Gainer that if they didn’t increase to at least 130K rows, it would be disappointing. For columns, I thought Microsoft would increase it to 1,024 columns, enough for three years of workday dates.
Excel 2007 Grid Limits
The new grid in Excel offers 1,048,576 rows—that is, 2^20 rows—an increase from 65,536 rows in Excel 2003. It offers 16,384 columns—that is, 2^14 columns—an increase from 256 columns in Excel 2003. Overall, the new grid provides for 17.1 billion cells on each worksheet.
You will now be able to analyze more complex datasets. For example, if you regularly analyze 2,000 items, you can analyze 2.5 years of monthly data in one Excel worksheet. In Excel 2007, you can analyze 10 years of weekly data or 43 years of monthly data. Columnwise, previous versions of Excel could handle only nine months of daily data going across the worksheet. Excel 2007 can handle 45 years of daily dates or 63 years of weekdays.
It is interesting to compare the size increase in the history of spreadsheets. You will see that the size increase is unprecedented. Here is a brief history of spreadsheets:
- In October 1979, VisiCalc debuted, with 255 rows and 63 columns.
- In 1983, Lotus 1-2-3 debuted, with 8,192 rows and 256 columns. The 2 million cells per worksheet in this version was a 13,000% increase over VisiCalc.
- In 1987, early versions of Excel offered 16,384 rows by 255 columns. This 4 million cells was double the amount offered in Lotus 1-2-3 release 2.2.
- In Excel 97, Microsoft increased Excel to offer 65,536 rows by 255 columns. This 16.7 million cells per spreadsheet was quadruple the previous limit.
- In Excel 2007, the new grid size is 1,048,576 rows by 16,384 columns. This is 17.1 billion cells, which is a 102,300% increase over the previous limit.
The chart in Figure 7.1 shows the number of cells available in a single worksheet for various spreadsheet versions. Note that the y-axis is plotted using a logarithmic scale, which means that each gridline is 100 times larger than the previous gridline.
Figure 7.1. Seventeen billion cells on a single worksheet is a bit more than “big.”
Who Can Use 17 Billion Cells?
It is unlikely that many people will need 17 billion cells. In fact, most computers sold today do not have enough memory to fill the 17 billion cells on a single worksheet, let alone go to Sheet2. Another important question is, “Who needs more than 256 columns or more than 65,536 rows?” The answer: A lot of people.
In previous versions of Excel, there has been no good way to organize a year’s worth of daily dates going across a spreadsheet. In Excel 2007 you can now have labels in Column A and stretch 43+ years of daily dates across a column.
Small businesses that use QuickBooks often curse that software’s desire to put blank columns between columns when exporting to Excel. With versions of Excel prior to Excel 2007, this imposed a limit of less than five years of weekly data across the columns. Even with QuickBooks wasting half the columns, with Excel 2007 you can now fit 157 years of weekly QuickBooks data across the columns. (If you have more than 157 years of QuickBooks data, you can probably archive some of that by now.)
The limit of 65,536 rows has been a problem for a while. I suspect that the 1.1 million row limit will continue to be a problem. One of my clients already has a dataset with 655,000 rows for his chain of retail stores. As that chain continues to open stores, I can foresee running out of rows in Excel 2007 in the next few years.
Many people resort to using Access only when their datasets have more than 65,536 rows. With Excel 2007, you’ll be able to quickly summarize far more datasets, and casual use of Access should decrease.
Why Are There Only 65,536 Rows in My Excel 2007 Spreadsheet?
When I initially installed Excel 2007, I loaded the largest Excel workbook I had. I pressed End+Down Arrow with anticipation. I was perplexed to see that there are only 65,536 rows in the spreadsheet, as shown in Figure 7.2.
Figure 7.2. What is all the hype? There are still only 65,536 rows.
Notice that the title bar for the workbook says that the file is 7-Ending.xls (Compatibility Mode). The key here is that the file is in Compatibility mode. In this mode, you cannot add features that were not available in Excel 2003. Compatibility mode is the default when you open a file created in Excel 97–2003.
To leave Compatibility mode, you click the Windows icon and then choose Convert, as shown in Figure 7.3.
Figure 7.3. You choose Convert to leave Compatibility mode.
Caution
When you upgrade a file, Excel warns you that the original file will be replaced by the upgraded version, as shown in Figure 7.4. This behavior is a little annoying. In the process of saving the file as an XLSM file, Excel actually deletes the old XLS version of the file. To keep a version of the XLS file, either backup the file or use the following method described.
Figure 7.4. Converting actually deletes the original version of the file.
After you convert a file, the file still only shows 65,536 rows. You have to close the file and reopen it to access all the rows.
You might prefer this method instead of using the Convert option:
- Open the Excel 2003 workbook.
- Click the Office icon and then select Save As.
- From the Save As Type dropdown, choose Excel Macro-Enabled Workbook (*.xlsm).
- Choose Save.
- Close the workbook.
- Reopen the workbook.
When you follow these steps, the XLS version of the workbook remains on your hard drive.
With the bigger grid, it is far more likely that you will encounter larger files, formulas, and pivot tables. With a 102,300% increase in the file, many of the old limits in Excel 2003 no longer make sense. Because of the bigger grid, Microsoft provided relaxed limits in many areas. Limits are discussed in the next section.
There is also an unusual quirk with the big grid. Previously, columns were labeled from A to IV. Now, columns are labeled from A to XFD. This means that a lot of three-letter words are now valid column names. In previous versions of Excel, range names such as ROI2007 or TAX2004 would have been legal names. Now that these are actual cell addresses, those names can no longer be used in Excel 2007. The workaround is discussed in the section “Working with Excel 2003 Named Ranges in Excel 2007,” later in this chapter.
Other Limits in Excel 2007
In addition to the grid size, a number of other aspects of Excel 2007 have new limits. Table 7.1 illustrates these new limits.
Table 7.1. Excel 2007 Limits
As you can see in Table 7.1, Excel 2007 has some excellent improvements. It also has some improvements that allow people to build worse spreadsheets.
Many people try to rely on nested IF functions when they should instead learn about VLOOKUP. Increasing from 7 to 64 nested functions allows people to put off learning about VLOOKUP for even longer. (If you’ve been avoiding VLOOKUP, read about it in Chapter 24, “Powerful Functions.”)
With versions of Excel prior to Excel 2007, any pivot table that relied on daily dates almost always had to be built with the dates going down the side instead of across the rows. This was annoying, especially if you planned on rolling the dates up to months or quarters that would eventually fit in the 256 columns.
The number of Excel formats was a problem that was rarely encountered but that caused horrible frustration when it was hit. Now, the limit will be hit much less frequently.
Even with these new limits, some areas could still be improved. For example, there is still a limit of eight levels of indentation in outlining. However, for the most part, the new limits are incredible and allow much larger analyses to happen in Excel instead of elsewhere.
Working with Excel 2003 Named Ranges in Excel 2007
The new columns in Excel go out to column XFD.
Many range names were valid in prior versions of Excel but are not valid in Excel 2007 because they are the same as Excel 2007 cell addresses. For example, TAX2006 might have been a range name in Excel 2003. This name is invalid in Excel 2007 because there is a cell called TAX2006.
Excel’s solution is to prefix invalid range names with an underscore character when you upgrade the workbook. Excel gives you the warning “One or more names in this workbook conflict with a valid cell reference in Excel 2007.” After you acknowledge the first message, Excel proceeds to tell you the complete list of names that it is changing, one at a time. You can either click OK to each name or skip the list by selecting OK to All.
However, despite Excel’s solution, invalid range names can cause problems. If you have VBA macros that refer to the range name, you have to manually fix the VBA code. Furthermore, if you used the name in a text argument in a worksheet function, you have to manually update those formulas to use the new name.
This feature will not affect many people, but for those it does affect, it will be very annoying.
Tips for Navigating the Big Grid
The navigation tips described in the following sections are not new to Excel 2007. However, with 16 billion cells, there is a better chance that you don’t want to be scrolling around with the Page Up and Page Down keys.
Using Shortcut Keys to Move Around
A variety of shortcuts enable you to quickly move around a worksheet:
Using the End Key to Navigate
The End key is one of the six keys above the arrow keys on a standard keyboard. When you press the End key, an indicator lights up in the status bar of the Excel window. When Excel is in End mode, you can press an arrow key or the Home key. Pressing an arrow key takes you to the edge of a contiguous range of cells. Pressing Home while in End mode will take you to the last used cell in the worksheet.
In Figure 7.5, pressing End and then the Down Arrow key causes the cell pointer to jump from D36 to D46. When the cell pointer is on the edge of a range, pressing End+Down Arrow again causes Excel to jump over a range of blank cells and land on the starting edge of the next range. For example, pressing End+Down Arrow from D46 causes the cell pointer to jump to D58.
Figure 7.5. End+arrow key will cause Excel to jump over a range of blank cells or a contiguous range of cells.
If you press the End key to move right or down from the last cell that contains data, the cell pointer jumps to the last row or column in the spreadsheet. In a blank worksheet, you can press End+Down Arrow and End+Right Arrow to move to XFD1048576.
Pressing End and then Home causes the cell pointer to move to the last active cell in the worksheet.
Using the Current Range to Navigate
If your data has many blank cells, using Ctrl+arrow keys or Ctrl+End key will lead to frustration.
You can press Ctrl+* to select the current region. A current region starts from the current nonblank cell and extends out in all directions until Excel encounters a completely blank row, a completely blank column, or the edge of the spreadsheet.
Then you can press Ctrl+. (that is, Ctrl plus the period key) to move the active cell to each corner of the selection. From the top-left cell of a region, you can press Ctrl+* and then press Ctrl+. twice to go to the last used cell in the current region.
Using Go To to Navigate
You can press the F5 key to display the Go To dialog. Then you can type a cell address and press OK to quickly jump to that cell.
You can also use the Name box the same way you use the Go To dialog. The Name box is the drop-down area immediately to the left of the formula bar. You click in the Name box, type a valid cell address, and press Enter. Excel then jumps to that cell.