Microsoft Excel has been referred to as "the best application ever written for Windows." You may or may not agree with that statement, but you can't deny that Excel is one of the oldest Windows products and has undergone many re- incarnations and face-lifts over the years. Cosmetically, the current version-Excel 2007-barely even resembles the original version. However, many of Excel's key elements have remained intact over the years, with significant enhancements, of course.
This chapter presents a concise overview of the features available in the more recent versions of Excel, with specific emphasis on Excel 2007. It sets the stage for the subsequent chapters and provides an overview for those who may have let their Excel skill get rusty.
Note | If you're an old hand at Excel, you may want to read only the section on the Excel user interface and ignore or briefly skim the rest of the chapter. |
The History of Excel
You probably weren't expecting a history lesson when you bought this book, but you may find this information interesting. At the very least, this section provides fodder for the next office trivia match.
Spreadsheets comprise a huge business, but most of us tend to take this software for granted. In the pre-spreadsheet days, people relied on clumsy mainframes or calculators and spent hours doing what now takes minutes.
It Started with VisiCalc
Dan Bricklin and Bob Frankston conjured up VisiCalc, the world's first electronic spread- sheet, back in the late 1970s when personal computers were unheard of in the office environment. They wrote VisiCalc for the Apple II computer, an interesting machine that seems like a toy by today's standards. VisiCalc caught on quickly, and many forward-looking companies purchased the Apple II for the sole purpose of developing their budgets with VisiCalc. Consequently, VisiCalc is often credited for much of Apple II's initial success.
Then Came Lotus
When the IBM PC arrived on the scene in 1982, thus legitimizing personal computers, VisiCorp wasted no time porting VisiCalc to this new hardware environment. Envious of VisiCalc's success, a small group of computer enthusiasts at a start-up company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathon Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. Released in January 1983, Lotus Development Corporation's 1-2-3 proved an instant success. Despite its $495 price tag (yes, people really paid that much for a single program), it quickly outsold VisiCalc and rocketed to the top of the sales charts, where it remained for many years. Lotus 1-2-3 was, perhaps, the most popular application ever.
Microsoft Enters the Picture
Most people don't realize that Microsoft's experience with spreadsheets extends back to the early 1980s. In 1982, Microsoft released its first spreadsheet-MultiPlan. Designed for computers running the CP/M operating system, the product was subsequently ported to several other platforms, including Apple II, Apple III, XENIX, and MS-DOS. MultiPlan essentially ignored existing software user-interface standards. Difficult to learn and use, it never earned much of a following in the United States. Not surprisingly, Lotus 1-2-3 pretty much left MultiPlan in the dust.
Excel partly evolved from MultiPlan, first surfacing in 1985 on the Macintosh. Like all Mac applications, Excel was a graphics-based program (unlike the character-based MultiPlan). In November 1987, Microsoft released the first version of Excel for Windows (labeled Excel 2 to correspond with the Macintosh version). Excel didn't catch on right away, but as Windows gained popularity, so did Excel. Lotus eventually released a Windows version of 1-2-3, and Excel had additional competition from Quattro Pro-originally a DOS program developed by Borland International, then sold to Novell, and then sold again to Corel (its current owner).
Excel Versions
Excel 2007 is actually Excel 12 in disguise. You may think that this name represents the twelfth version of Excel. Think again. Microsoft may be a successful company, but its version-naming techniques can prove quite confusing. As you'll see, Excel 2007 actually represents the tenth Windows version of Excel. In the following sections, I briefly describe the major Windows versions of Excel.
EXCEL 2
The original version of Excel for Windows, Excel 2 first appeared in late 1987. It was labeled Version 2 to correspond to the Macintosh version (the original Excel). Because Windows wasn't in widespread use at the time, this version included a runtime version of Windows-a special version with just enough features to run Excel and nothing else. This version appears quite crude by today's standards.
The original Excel 2 for Windows. Excel has come a long way since its original version. (Photo courtesy of Microsoft Corporation)
EXCEL 3
At the end of 1990, Microsoft released Excel 3 for Windows. This version offered a significant improvement in both appearance and features. It included toolbars, drawing capabilities, worksheet outlining, add-in support, 3-D charts, workgroup editing, and lots more.
EXCEL 4
Excel 4 hit the streets in the spring of 1992. This version made quite an impact on the marketplace as Windows increased in popularity. It boasted lots of new features and usability enhancements that made it easier for beginners to get up to speed quickly.
EXCEL 5
In early 1994, Excel 5 appeared on the scene. This version introduced tons of new features, including multisheet workbooks and the new Visual Basic for Applications (VBA) macro language. Like its predecessor, Excel 5 took top honors in just about every spreadsheet comparison published in the trade magazines.
EXCEL 95
Excel 95 (also known as Excel 7) shipped in the summer of 1995. On the surface, it resembled Excel 5 (this version included only a few major new features). However, Excel 95 proved to be significant because it presented the first version to use more advanced 32-bit code. Excel 95 and Excel 5 use the same file format.
EXCEL 97
Excel 97 (also known as Excel 8) probably offered the most significant upgrade ever. The toolbars and menus took on a great new look, online help moved a dramatic step forward, and the number of rows available in a worksheet quadrupled. And if you're a macro developer, you may have noticed that Excel's programming environment (VBA) moved up several notches on the scale. Excel 97 also introduced a new file format.
EXCEL 2000
Excel 2000 (also known as Excel 9) was released in June of 1999. Excel 2000 offered several minor enhancements, but the most significant advancement was the ability to use HTML as an alternative file format. Excel 2000 still supported the standard binary file format, of course, which is compatible with Excel 97.
EXCEL 2002
Excel 2002 (also known as Excel 10) was released in June of 2001 and is part of Microsoft Office XP. This version offered several new features, most of which are fairly minor and were designed to appeal to novice users. Perhaps the most significant new feature was the capability to save your work when Excel crashes and also recover corrupt workbook files that you may have abandoned long ago. Excel 2002 also added background formula error checking and a new formula-debugging tool.
EXCEL 2003
Excel 2003 (also known as Excel 11) was released in the fall of 2003. This version had very few new features. Perhaps the most significant new feature was the ability to import and export XML files and map the data to specific cells in a worksheet. It also introduced the concept of the List, a specially designated range of cells. Both of these features would prove to be precursors to future enhancements.
EXCEL 2007
Excel 2007 (also known as Excel 12) was released in early 2007. Its official name is Microsoft Office Excel 2007. This latest Excel release represents the most significant change since Excel 97, including a change to Excel's default file format. The new format is XML based although a binary format is still available. Another major change is the Ribbon, a new type of user interface that replaces the Excel menu and toolbar system. In addition to these two major changes, Microsoft has enhanced the List concept introduced in Excel 2003 (a List is now known as a Table), improved the look of charts, significantly increased the number of rows and columns, and added some new worksheet functions. For more, see the sidebar, "What's New in Excel 2007?".
Note | XML (extensible Markup Language) stores data in a structured text format. The new file formats are actually compressed folders that contain several different XML files. The default format's file extension is .xlsx. There's also a macro-enabled format with the extension .xlsm, a new binary format with the extension .xlsb, and all the legacy formats that you're used to. |
Microsoft Office Excel 2007 Change formula recalculation, iteration, or precision
To use formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) efficiently, there are three important considerations that you need to understand:
Calculation is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. To avoid unnecessary calculations, Microsoft Office Excel automatically recalculates formulas only when the cells that the formula depends on have changed. This is the default behavior when you first open a workbook and when you are editing a workbook. However, you can control when and how Excel recalculates formulas.
Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. Excel cannot automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula. This is called a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate. Circular references can iterate indefinitely. However, you can control the the maximum number of iterations and the amount of acceptable change.
Precision is a measure of the degree of accuracy for a calculation. Excel stores and calculates with 15 significant digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.
What do you want to do?
- Change when a worksheet or workbook recalculates
- Recalculate a worksheet or workbook manually by using keyboard shortcuts
- Change the number of times Excel iterates a formula
- Change the precision of calculations in a workbook
- Change the number of processors used to calculate formulas
- Learn about calculating workbooks that were created in an earlier version of Excel
Change when a worksheet or workbook recalculates
As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.- Click the Microsoft Office Button
, click Excel Options, and then click the Formulas category.
- Do one of the following:
- To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.
- Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.
- To recalculate all dependent formulas—except data tables—every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic except for data tables.
- Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic Except for Data Tables.
- To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by clicking Calculate Now under Calculation Options in the Calculation group on the Formulas tab), in the Calculation options section, under Workbook Calculation, click Manual.
- Note When you click Manual, Excel automatically selects the Recalculate workbook before saving check box. If saving a workbook takes a long time, clearing Recalculate workbook before saving may improve the save time.
- Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Manual.
- To manually recalculate all open worksheets, including data tables, and update all open chart sheets, on the Formulas tab, in the Calculation group, click the Calculate Now button.
- To manually recalculate the active worksheet and any charts and chart sheets linked to this worksheet, on the Formulas tab, in the Calculation group, click the Calculate Sheet button.
Changing any of the options affects all open workbooks.
Note If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.Recalculate a worksheet or workbook manually by using keyboard shortcuts
| To | Press |
|---|---|
Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation. | F9 |
| Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet. | SHIFT+F9 |
| Recalculate all formulas in all open workbooks, regardless of whether they have changed since last time or not. | CTRL+ALT+F9 |
Recheck dependent formulas, and then recalculate all formulas in all open workbooks, regardless of whether they have changed since last time or not. | CTRL+SHIFT+ALT+F9 |
Change the number of times Excel iterates a formula
- Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
- In the Calculation options section, select the Enable iterative calculation check box.
- To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet.
- To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet.
Change the precision of calculations in a workbook
Before you change the precision of calculations, keep in mind the following important points:
By default, Excel calculates stored, not displayed, values The displayed and printed value depends on how you choose to format and display the stored value. For example, a cell that displays a date as "6/22/2008" also contains a serial number that is the stored value for the date in the cell. You can change the display of the date to another format (for example, to "22-Jun-2008"), but changing the display of a value on a worksheet does not change the stored value.
Use caution when changing the precision of calculations When a formula performs calculations, Excel usually uses the values stored in cells referenced by the formula. For example, if two cells each contain the value 10.005 and the cells are formatted to display values in currency format, the value $10.01 is displayed in each cell. If you add the two cells together, the result is $20.01 because Excel adds the stored values 10.005 and 10.005, not the displayed values.
When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. If you later choose to calculate with full precision, the original underlying values cannot be restored.
- Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
- In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.
Change the number of processors used to calculate formulas
A computer can have more than one processor (it contains multiple physical processors) or can be hyperthreaded (it contains multiple logical processors). On these computers, you can improve or control the time it takes to recalculate workbooks that contain many formulas by setting the number of processors to use for recalculation. In many cases, portions of a recalculation workload can be performed simultaneously. Splitting this workload across multiple processors can reduce the overall time it takes complete the recalculation.
- Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
- To enable or disable the use of multiple processors during calculation, in the Formulas section, select or clear the Enable multi-threaded calculation check box.
- Note This check box is enabled by default and all processors are used during calculation. The number of processors on your computer is automatically detected and displayed next to the Use all processors on this computer option.
- Optionally, if you select Enable multi-threaded calculation, you can control the number of processors to use on your computer. For example, you might want to limit the number of processors used during recalculation if you have other programs running on your computer that require dedicated processing time.
How to control the number of processors
- Under Number of calculation threads, click Manual. Enter the number of processes to use.
Note The maximum number is 1024.
Learn about calculating workbooks that were created in an earlier version of Excel
To ensure that older workbooks are calculated correctly, Excel behaves differently when you first open an old workbook than when you open a workbook created in the current version.When you open a workbook created in the current version, Excel recalculates only the formulas that depend on cells that have changed.
When you use open a workbook that was created in a earlier version of Excel, all of the formulas in the workbook — those that depend on cells that have changed and those that do not — are recalculated. This ensures that the workbook is fully optimized for the current Excel version.