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).
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.
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.
Back to top
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...".
Back to top
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.)
Back to top
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).
Back to top
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.
Back to top
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.
Back to top
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.
Back to top