The Workings of Workbooks

The core document of Excel is a workbook. Everything that you do in Excel takes place in a workbook.

Beginning with Excel 2007, workbook "files" are actually compressed folders. You may be familiar with compressed folders if you've ever opened a file with a .zip extension. Inside the compressed folders are a number of files that hold all the information about your workbook, including charts, macros, formatting, and the data in its cells.

An Excel workbook can hold any number of sheets (limited only by memory). The four types of sheets are

• Worksheets

• Chart sheets

• MS Excel 4.0 macro sheets (obsolete, but still supported)

• MS Excel 5.0 dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you want (each in its own window), but only one workbook is the active workbook at any given time. Similarly, only one sheet in a workbook is the active sheet. To activate a different sheet, click its corresponding tab at the bottom of the window, or press Ctrl+PgUp (for the previous sheet) or Ctrl+PgDn (for the next sheet). To change a sheet's name, double-click its Sheet tab and enter the new text for the name. Right-clicking a tab brings up a shortcut menu with some additional sheet-manipulation options.

You can also hide the window that contains a workbook by using the View Window Hide command. A hidden workbook window remains open but not visible. Use the View Window Unhide command to make the window visible again. A single workbook can display in multiple windows (choose View Window New Window). Each window can display a different sheet or a different area of the same sheet.

Worksheets

The most common type of sheet is a worksheet-which you normally think of when you think of a spreadsheet. Every Excel 2007 worksheet has 16,384 columns and 1,048,576 rows. After years of requests from users, Microsoft finally increased the number of rows and columns in a worksheet.

How Big Is a Worksheet?

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 × 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using a 1024 × 768 video mode with the default row heights and column widths, you can see 15 columns and 25 rows (or 375 cells) at a time-which is about .000002 percent of the entire worksheet. In other words, more than 45 million screens of information reside within a single worksheet.

If you entered a single digit into each cell at the relatively rapid clip of one cell per second, it would take you over 500 years, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 36 million sheets of paper-a stack about 12,000 feet high (that's ten Empire State Buildings stacked on top of each other).

Note 

Versions prior to Excel 2007 support only 256 columns and 65,536 rows. If you open such a file, Excel 2007 enters compatibility mode to work with the smaller worksheet grid. In order to work with the larger grid, you must save the file in one of the Excel 2007 formats. Then close the workbook and reopen it.

Having access to more cells isn't the real value of using multiple worksheets in a workbook. Rather, multiple worksheets are valuable because they enable you to organize your work better. Back in the old days, when a spreadsheet file consisted of a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now, you can store information on any number of worksheets and still access it instantly.

You have complete control over the column widths and row heights, and you can even hide rows and columns (as well as entire worksheets). You can display the contents of a cell vertically (or at an angle) and even wrap around to occupy multiple lines.

Note 

By default, every new workbook starts out with three worksheets. You can easily add a new sheet when necessary, so you really don't need to start with three sheets. You may want to change this default to a single sheet. To change this option, use the Office Excel Options command, click the Popular tab, and change the setting for the option labeled Include This Many Sheets.

Chart Sheets

A chart sheet holds a single chart. Many users ignore chart sheets, preferring to use embedded charts, which are stored on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to locate a particular chart, and they prove especially useful for presentations. I discuss embedded charts (or floating charts on a worksheet) later in this chapter.

Macro Sheets and Dialog Sheets

An Excel 4.0 macro sheet is a worksheet that has some different defaults. Its purpose is to hold XLM macros. XLM is the macro system used in Excel version 4.0 and previous versions. This macro system was replaced by VBA in Excel 5.0 and is not discussed in this book.

An Excel 5.0 dialog sheet is a drawing grid that can hold text and controls. In Excel 5.0 and Excel 95, they were used to make custom dialog boxes. UserForms were introduced in Excel 97 to replace these sheets.

What's New in Excel 2007?

Here's a quick and dirty overview of the new features in Excel 2007:

• A new tab/Ribbon user interface

• New XML file formats

• Worksheet tables

• Significantly larger worksheet grid (1,048,576 rows x 16,384 columns)

• Ability to use more memory

• Unlimited conditional formats per cell

• 100 levels of undo

• Maximum formula length increased to 8,000 characters

• Support for 64 levels of nesting in a formula

• Formula autocomplete

• Better-looking charts

• Workbook themes

• Skins

• Page Layout view

• New conditional formatting options

• Less confusing Excel Options dialog box

• New collaboration features (requires SharePoint)

• SmartArt and Improved WordArt

• Compatibility checker

• Easier pivot tables

• Twelve new worksheet functions, plus integration of the Analysis ToolPak functions

• PDF output (via a downloadable add-in)

• Resizable formula bar

• Many new templates

• More control over the status bar