Spreadsheet Tools

Initial Publication Date: October 14, 2010
While the data management and graphing features of spreadsheet programs are well-known, powerful programs like Microsoft Excel include a large number of additional tools that can be used to great effect in a course.

The details of how to use the following list of tools in Microsoft Excel are described in the links below. An earlier version of many of these instructions appears in Cahill and Kosicki (2000).

Screen shot of graphs

Jump to: Specialized functions | Fill data series | Generating tables of data | Statistical tools | Numerically solving simultaneous equations |Optimization | Matrix algebra operations

Instructions are given below for Office 2007 and earlier versions. Unfortunately, the various versions of Excel have slight differences in how some commands are found and executed; however, the instructions below should guide the user through most versions. More instructions on how to use Excel can be found on the What is Excel page of Mathematics and Statistical Models. This site contains examples and links to understanding the basics of Excel. Instructions for several Excel features are embedded in assignments on Spreadsheets Across the Curriculum examples.

Specialized functions

Excel provides a wide variety of built-in formulas for completing a number of computations. Aside from a wide array of standard statistical, financial, and mathematical functions, Excel has a number of logical operators, database management tools, and formulas for making time calculations.
Back to top

Fill

Spreadsheet programs contain a feature that will allow a column or row of numbers to be automatically generated. In Excel, this command can be used to create data with linear or growth characteristics or may be used to estimate the trend series of existing data.

Back to top

Data tables

Data tables in Excel may be used to automatically create an array of data from three-variable equations. The table is set up so that values for two independent variables are entered on the edges, and the middle of the table contains the data for the dependent variable. This is useful for creating diagrams, including three-dimensional charts which can be easily rotated to different orientations with the mouse.
Back to top

Statistical tools

Excel has a number of built-in statistical tools that are useful in a variety of situations. An option in scatter plot charts of data allows a regression (trend) line to be plotted and the equation and R-squared value to be displayed. Basic ordinary least squares linear regressions are straightforward to compute, as are a variety of hypothesis test statistics and analyses of distribution functions.
Back to top

Solving simultaneous equations

Many users have been frustrated by the "cannot resolve circular references" error message Excel returns when two formulas refer to each other. However, an option in Excel allows the user to resolve these circular references by solving the equations through a numerical method. This allows a fairly large system of equations to be solved numerically.
Back to top

Optimization

The Solver Add-in for Excel allows Excel to find an optimal solution for a system of formulas programmed in a spreadsheet. Specifically, Solver can be directed to find the minimum value, maximum value, or any specific value for a formula cell by changing the value(s) in other specified cells(s). This tool also allows for constraints to be set, including those that specify algebraic relationships, integer values, positive or negative values, etc. Goal Seek is a simpler tool that will find a particular value for a cell by changing one other cell's value without any constraints.
Back to top

Matrix Algebra

Excel can manipulate data as vectors or matrices and can compute some basic matrix operations. This is sometimes useful when presenting more advanced topics in mathematics or statistics.
Back to top