Excel 2007 Tutorial

This tutorial demonstrates the use of EXCEL to solve Laplace's equation.

Figure 1   Excel Home tool bar

Excel 2007 has a number of tool bars, Home, Insert, Page Layout, Formulas, Data, Review, View, and Acrobat.

The "HOME" tool bar shown above contains buttons to control many basic tasks.

Numerical solution of Laplaces equation is based on the fact that the potential at a point is the average of the surrounding potentials. Figure 2 contains a portion of the excel sheet used to calculate the potentials. Cell G13 is selected. The cell value is the average of the values of four neighbors. The formula;

was entered into cell G13 as shown in Figure 2. This formula requires the cell value to be the average of four cell neighbors.

Figure 2   The value of cell G13 is the average of 4 of its neighbor cells.

To set up excel for the calculation,

  • Select a cell and enter the formula to calculate its value as the average of its neighbors, as shown in Figure 2. Let excel know it is a formula by including the equal sign.
  • Position the cursor on the lower right corner of the cell containing the formula so that the cursor appears as a black cross.
  • Drag the black cross to cover the area where values of the potential are to be calculated. Notice that as the formula is copied to another cell, the formula changes so appropriate neighbor cells are referenced.
  • Enter the boundary conditions in the approprate cells.
  • The calculation contains a "circular reference" . Usually this indicates an error, but here it is essential. To allow circular references and enable iterations,

    1. Click on the excel button on the upper left .
    2. At the bottom right of the form select "excel options" > then select "formulas"
    3. On the "Calculations options" form select "Enable iterative calculation"
    4. Increase the Maximum Iterations (Here we used 5000).
    5. Reduce the Maximum Change (Here we used 0.0001). Iterations will stop when the maximum iteration is reached or the change is less than the maximum change.

  • Format the cell values using the buttons on the Home tool bar. Here we aligned to the center of cells and formatted the cell to a number with one decimal point.

Excel calculates the values when the file is saved.

Color cells based on value

To achieve the cell color based on value as shown in Figure 3, use;

Home > styles > Conditional formatting > Color scales
Pick the color scale of your choice. Here we chose "more rules" and picked a 3 color scale with blue for mimimum, white for midpoint and red for maximum.

Figure 3   Excel solution to Laplace's equation. Boundary conditions, V = 0 at y = 0 and y= 20, V = 10 at x = 10 and x = -10.

Excel file