This section describes two features that enable you to customize Excel-macros and add-ins.
Excel's VBA programming language is a powerful tool that can make Excel perform
Macros that automate various aspects of Excel
Macros that serve as custom functions that you can use in worksheet formulas
Part VI of this book describes how to use and create custom worksheet functions using VBA.
An add-in is a program attached to Excel that gives it additional functionality. For example, you can store custom worksheet functions in an add-in. To attach an add-in, use the Add- Ins tab in the Excel Options dialog box.
Excel ships with quite a few add-ins, and you can purchase or download many third-party add-ins from online services. My Power Utility Pak is an example of an add-in.
Chapter 23 describes how to create your own add-ins that contain custom worksheet functions.
Excel includes a number of features that relate to the Internet. For example, you can save a worksheet or an entire workbook in HTML format, accessible in a Web browser. In addition, you can insert clickable
In Excel 2003, HTML was a round-trip file format. In other words, you could save a workbook in HTML format, reopen it in Excel, and nothing would be lost. That's no longer the case with Excel 2007. HTML is now
You can also create Web queries to bring in data stored in a corporate intranet or on the Internet.
The entire database is stored in a worksheet. In Excel, a worksheet database can have no more than 1,048,575 records (because the top row holds the field
External databases: The data is stored outside Excel, such as in an Access MDB file or in SQL Server.
Generally, when the cell pointer resides within a worksheet database, Excel recognizes it and displays the field names whenever possible. For example, if you move the
A particularly useful feature, filtering, enables you to display only the records that you want to see. When Filter mode is on, you can filter the data by selecting values from pulldown lists (which appear in place of the field names when you choose the Data Sort & Filter Filter command). Rows that don't meet the filter criteria are hidden. See Figure 1-11 for an example.
Figure 1-11: Excel's Filter feature makes it easy to view only the database records that meet your criteria.
If you convert a worksheet database into a table (by using Insert Tables Table), filtering is turned on automatically.
If you prefer, you can use the traditional spreadsheet database techniques that involve criteria ranges. To do so, choose the Data Sort & Filter Advanced command.
Chapter 9 provides additional details regarding worksheet lists and databases.
Excel can automatically insert (or remove)
A worksheet outline often serves as an
Figure 1-12: Excel can automatically insert subtotal formulas and create outlines.
Scenario management is storing input values that drive a model. For example, if you have a sales forecast, you may create scenarios such as best case, worst case, and most likely case.
Excel's Scenario Manager can handle only simple scenario-management tasks, but most users find it adequate. However, it is definitely easier than trying to keep track of different scenarios manually.
One of Excel's most powerful tools is the
which enables you to display summarized data in just about any way possible. Data for a pivot table comes from a worksheet database (or table) or an external database, and is stored in a special cache, which enables Excel to recalculate data
Chapter 18 contains additional information about pivot tables.
As a companion to a pivot table, Excel also supports the pivot chart feature. Pivot charts enable you to link a chart to a pivot table. In Excel 2007, pivot
Excel also offers useful auditing capabilities that help you identify errors or track the logic in an unfamiliar spreadsheet. To access this feature, choose commands in the Formulas
Refer to Chapter 21 for more information about Excel's auditing features.
For specialized linear and nonlinear problems, Excel's Solver add-in calculates solutions to what-if scenarios based on adjustable cells, constraint
Excel 2007 Bible
John Walkenbach's Favorite Excel 2007 Tips and Tricks (Mr. Spreadsheet's Bookshelf)
Learn Excel 2007 Essential Skills with The Smart Method: Courseware tutorial for self-instruction to beginner and intermediate level
Learn Excel 2007 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques