Customization in Excel

This section describes two features that enable you to customize Excel-macros and add-ins.

Macros

Excel's VBA programming language is a powerful tool that can make Excel perform otherwise impossible feats. You can classify the procedures that you create with VBA into two general types:

Macros that automate various aspects of Excel
Macros that serve as custom functions that you can use in worksheet formulas

Cross Ref

Part VI of this book describes how to use and create custom worksheet functions using VBA.

Add-in Programs

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.

Cross Ref

Chapter 23 describes how to create your own add-ins that contain custom worksheet functions.

Internet Features

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 hyperlinks (including e-mail addresses) directly into cells .

Caution

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 considered an export-only format.

You can also create Web queries to bring in data stored in a corporate intranet or on the Internet.

Analysis Tools

Excel is certainly no slouch when it comes to analysis. After all, most people use a spreadsheet for analysis. Many analytical tasks can be handled with formulas, but Excel offers many other options, which I discuss in the following sections.

Database Access

Over the years , most spreadsheets have enabled users to work with simple flat database tables (even the original version of 1-2-3 contained this feature). Excel's database features fall into two main categories:

Worksheet databases: 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 names ) and 16,384 fields (one per column).
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 cell pointer within a worksheet database and choose the Data Sort & Filter Sort command, Excel allows you to select the sort keys by choosing field names from a drop-down list.

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.

image from book
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.

Cross Ref

Chapter 9 provides additional details regarding worksheet lists and databases.

Excel can automatically insert (or remove) subtotal formulas in a table that is set up as a database. It also creates an outline from the data so that you can view only the subtotals or any level of detail that you desire .

Outlines

A worksheet outline often serves as an excellent way to work with hierarchical data, such as budgets . Excel can create an outline automatically by examining the formulas in your worksheet (use the Data Outline Subtotal command). After you've created an outline, you can collapse or expand the outline to display various levels of details. Figure 1-12 shows an example of a worksheet outline.

image from book
Figure 1-12: Excel can automatically insert subtotal formulas and create outlines.

Scenario Management

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.

Pivot Tables

One of Excel's most powerful tools is the pivot table, 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 rapidly after a pivot table is altered .

Cross Ref

Chapter 18 contains additional information about pivot tables.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

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 charts have improved significantly.

Auditing Capabilities

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 Formula Auditing group .

Cross Ref

Refer to Chapter 21 for more information about Excel's auditing features.

Solver Add-in

For specialized linear and nonlinear problems, Excel's Solver add-in calculates solutions to what-if scenarios based on adjustable cells, constraint cells , and, optionally , cells that must be maximized or minimized.