Analysis Tools

Excel is certainly no slouch when it comes to analysis. After all, that's what most people use a spreadsheet for. Most analysis tasks can be handled with formulas, but Excel offers many other options.

Outlines

A worksheet outline is often an excellent way to work with hierarchical data such as budgets . Excel can create an outline (horizontal, vertical, or both) automatically, or you can do so manually. After the outline is created, you can collapse or expand it to display various levels of detail.

Analysis ToolPak

In previous versions of Excel, the Analysis ToolPak add-in provided additional special-purpose analysis tools and worksheet functions, primarily statistical in nature. In Excel 2007, these features are built in. These tools make Excel suitable for casual statistical analysis.

Pivot tables

Pivot tables are among Excel's most powerful tools. A pivot table is capable of summarizing data in a handy table, and this table can be arranged in many ways. In addition, a pivot table can be manipulated entirely by VBA. Data for a pivot table comes from a worksheet database or an external database and is stored in a special cache, which enables Excel to recalculate rapidly after a pivot table is altered . Figure 2-26 shows a pivot table.

image from book
Figure 2-26: Excel's pivot table feature has many applications.

CROSS-REFERENCE

See Chapter 17 for information about manipulating pivot tables with VBA.

Solver

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.

XML features

One of the few new features introduced in Excel 2003 dealt with XML files. (XML is an accepted standard that enables exchange of data between different applications.) You can import data from an XML file and then map the data to specific worksheet cells.

Add-Ins

An add-in is a program that's attached to an application to give it additional functionality. To attach an Excel add-in, use the Add-Ins tab in the Excel Options dialog box.

In addition to the add-ins that ship with Excel, you can download additional add-ins from Microsoft's Web site (http://www.office.microsoft.com), and you can purchase or download many third-party add-ins from online services. You can use the coupon in the back of the book to acquire a free copy of the Power Utility Pak add-in. And, as I detail in Chapter 21, it's very easy to create your own add-ins.

Macros and Programming

Excel has two built-in macro programming languages: XLM and VBA. The original XLM macro language is obsolete, and it has been replaced by VBA. Excel 2003 can still execute most XLM macros, and you can even create new ones. However, you cannot record XLM macros. You'll want to use VBA to develop new macros.

CROSS-REFERENCE

Part III of this book is devoted to the VBA language.

File Format

A key consideration is file compatibility. Excel 97 through Excel 2003 all use the same file format, so file compatibility is not a problem for these four versions. Excel 2007, however, uses a new file format. Fortunately, Microsoft has made a "compatibility pack" available for earlier versions of Excel. This compatibility pack enables older versions of Excel to read and write the new XLSX file format.

It's important to understand the difference between file compatibility and feature compatibility. For example, even though the compatibility pack enables Excel 2003 to open files created by Excel 2007, it cannot handle features that were introduced in later versions.

CROSS-REFERENCE

Refer to Chapter 4 for more information about Excel's file format, and read Chapter 26 for more information about compatibility issues for developers.