excel Analysis Tools

Excel is certainly no slouch when it comes to analysis. After all, that’s what most people use a
spreadsheet for. You can handle most analysis tasks with formulas, but Excel offers many other
options:
h 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 you create the outline, you can collapse or expand it to
display various levels of detail.
h 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. Beginning with Excel 2007, these features are built in. These tools make Excel
suitable for casual statistical analysis.
h Pivot tables: Pivot tables are among Excel’s most powerful tools. A pivot table is capable
of summarizing data in a handy table, and you can arrange this table in many ways. In
addition, you can manipulate a pivot table 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.
h 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.