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.

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.

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.

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.