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).
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.
To access functions in Excel, simply click on the fx button next to the formula bar at the top of the screen as shown on the right (click to enlarge). A search window is then opened to find the function the user desires; searches can be conducted in plain English or for a particular function (e.g. "standard deviation" or "STDEV"). Once the function is selected, a series of windows brings the user through a step-by-step process to set up the function correctly.
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.
To use this function, click on the starting cell or select the column or row in which the data are to appear. For example, if a series of numbers reflecting a certain pattern or growth rate are to be created, insert a starting value, then select that starting value cell and the cells in which the created data is to appear. If Fill is to be used to estimate a trend, select the data to be analyzed. (Hint - keep a clean copy of the original data, because Excel will copy over the original data with the estimated trend.) To implement the command, in Excel 2007 and later, click on the Fill icon near the right side of the Home tab in the Edit block (see top image on the right). In earlier versions of Excel, select Fill... from the Edit menu. Select "Series...".
A wizard then takes the user through the steps of creating a series. (See image, and click to enlarge) If you want to fill a particular block of cells, select the cells before clicking on the Fill button.
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.
This command is accessed in Excel 2007 by selecting the "Data" tab, then clicking on the "What-If Analysis" icon in the "Data Tools" group and choosing "Data table" as in the image to the right. This "data table" is different from the standard "Table" under the "Insert" tab, which is useful for filtering numbers. In earlier versions of Excel, data tables are inserted under "Data" menu.
Excel's data table command can generate the underlying data for a three dimensional plot based on a formula. To use the data table command, it is necessary to manually form a table of cells where the top row is the range of x values over which the 3D function is to be evaluated, and the left hand column represents the range of y values. The cells in the middle of the table will contain the value assigned to each combination of x and y (i.e. the z axis).
The Data/Table command requires that somewhere on the worksheet there must be starting values for x and y. Using references to the cells containing the starting values, type the function into the corner of the table. Then, the entire table of cells must be selected. Implementing the Data/Table command spreads the formula throughout the empty cells of the selected table by systematically replacing the starting value of x by each of the x values residing in the first row of the matrix. Similarly, Excel systematically replaces the starting y value with each of the y values residing in the first column of the table. On the helper pop-up window, the x values in this case are the "row input" and the y values are the "column input." To implement the Data Table command, type the cell addresses associated with the starting values for the row and column inputs into the Data/Table window.
Unfortunately, the Chart Wizard cannot plot this block of data unless the upper left corner cell is deleted. To make this deletion in a way that will not inhibit additional simulations, copy the data and paste it to another location on the worksheet using the Paste Special/Paste Link command. The Paste Link option appears under Paste Special in the Home tab (or in the under Paste Special... in the Edit menu of earlier versions of Excel). The Paste Link command will create a new table in which each cell contains a formula that sets the cell equal to its counterpart cell in the original table. Delete the cell in the upper left corner of this duplicate table, and then select the entire table to plot it.
Once the data are selected, use the Surface Chart option of Excel's Chart Wizard (use the Insert/Chart command for earlier versions of Excel) to construct a plot. Selecting the first option in the Chart sub-type gives a multi-color, three-dimensional look at the utility function. Different colors or shadings denote combinations of x and y falling into certain value ranges. To view the graph at different angles, click and drag on a corner of the graph or adjust the settings in the Chart/3-D View window. (Once the Chart Wizard is completed and the 3-D surface chart appears, it is necessary to make a minor adjustment to the way in which the x-axis is constructed. Select (click on) the x-axis and then choose the Format/Selected Axis command. Look under the Scale tab of the Format Axis window and remove the check mark from the "Value (Z) axis crosses between categories" option.)
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.
The simplest regression can be completed with the Fill command. The fitted values for a simple one variable (trend) regression is produced by selecting the data, accessing the Fill command (as described above) and checking the trend option. See the image on the right (click to enlarge).
On a chart, a one independent variable regression line may be plotted by clicking on the Trendline icon in the Layout tab (See image on left; click to enlarge). When "More Trendline Options..." is selected, a window opens with a number of options for type of regression. A check box is also provided to make the regression equation and R-squared value on the chart. (In earlier versions of Excel, regression lines are added as Insert/Trendline.)
A standard regression analysis is completed from the Analysis section of the Data tab when "Data Analysis" and "Regression" is selected. (See image on right; click to enlarge.) A wizard instructs the user to input the dependent variable data, the block of independent variable data, etc. The independent variable data must be in a single contiguous block of rows or columns. While this will report standard test statistics, only simple regression results may be produced.
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.
A system of simultaneous equations may be numerically solved in Excel by entering the right-hand side of formulas into cells assigned to the left-hand variables. For example, to program the equation y=2x, the user assigns a cell to the variable y, and types "=2*[x variable cell address]". Then an equation for x is similarly entered in the cell designated for the xvariable referring to the y variable cell.
Because the formulas refer to each other this generates a "circular reference" error in Excel. To resolve this error and allow Excel to solve the system numerically, click on the Office button, and select Excel Options. Choose Formulas, and then check "Enable iterative calculation". A maximum number of iterations may be entered along with the required precision level ("maximum change"). See image to the right.
When setting up a model, be sure the system has only one solution; the numerical method used by Excel will find the solution that is closest to the starting value. Occasionally for complex systems, the algorithm for Excel will fail to find the solution if the starting value is too far from the final answer, so it is good practice to enter the approximate solution into one of the cells before entering the formula. Likewise, if a comparative static analysis is conducted, care must be taken not to change parameter values too much.
There are a number of other methods to solve systems of equations in Excel. For example, the right side of an equation may be entered into one cell, the left side in another, and a third cell used to calculate the difference in values between the two. Or, two equations written in terms of the same left-hand variable may be entered into separate cells with a third cell to calculate the difference in values (and additional cells to house values for the variables). In either of these cases, Goal Seek or the Solver (see below) may be used to set the difference cell equal to zero thus solving the system.
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.
Solver is a powerful tool to find a constrained optimal solution. When setting up a system to use Solver, it is important that cells be set aside to contain parameter values and for for the objective function formulas refer to these cells. Once the system is set up, it is easiest to click on the objective cell, and then select Solver from the Data tab on Excel 2007 as on the image to the right (click to enlarge). (In earlier versions of Excel, the Solver is an Add-In that appears under the Tools menu once it is installed through "Tools/Add Ins...") A pop-up window then allows a desired optimum to be selected or value to be entered. Other spaces are provided to specify the cells to be changed to achieve the optimum and to set constraints. Constraints may be equality, inequality, integer, or binary.
Like Solver, Goal Seek can be used to obtain particular values of a formula cell by changing another cell that includes relevant data. In Excel 2007, from the "Data" tab, click "Data Tools", the "What-If Analysis" icon, and then "Goal Seek" as in the image on the right (click to enlarge). (In earlier versions of Excel, Goal Seek is under the Tools menu.) The pop-up window allows the user to input the objective cell address (which must contain a formula), the target value, and another cell address with data that can be changed to impact the objective. Goal seek does not allow optimal solutions or constraints.
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.
Excel can manipulate data as vectors or matrices. This is sometimes useful when presenting more advanced topics in mathematics or statistics. Matrix functions native to Excel are matrix multiplication (MMULT), inverting a matrix (MINVERSE) and finding the determinant of a matrix (MDETERM). The fx function wizard can guide the user through setting up each operation, but creating the final matrix result is not intuitive. To accomplish this, enter the matrix operation in the top left-hand corner of the block of cells that is to contain the matrix operation result. Then, select all the cells in this block. Press F2, then Ctrl-Shift-Enter to fill the matrix with the result.
Show Excel instructions
Hide
