Using EXCEL Spreadsheets to Evaluate the Fourier Series of a Function

The goal of this tutorial is to create an EXCEL spreadsheet that calculates the first few terms in the Fourier series expansion of a given function.

The Fourier Series expansion of a function f(x) has the form

where

In this tutorial we will consider the following function:

and its odd extension on [-1, 1]. Using the properties of even and odd functions, one finds

and a₀ = 0. Thus, the expansion reduces to a sine series

where L = 1.

Before setting up a spreadsheet to compute the terms of this Fourier series, one needs to evaluate the coefficients b_n of the series,

A few integrations per parts yield

The value of sin(nπ/2) depends on the value of n, in particular

sin(nπ/2) = 0 for even values of n
sin(nπ/2) = (-1)ⁿ for odd values of n

Hence, the Fourier Series expansion of f can be written as

Basic Steps to Set Up the Spreadsheet

These are the basic steps to set up an EXCEL spreadsheet that allows you to calculate the first few terms in the Fourier series derived above. Note that this is a half-range expansion, thus the terms of the series will be calculated at specific points in the interval [0, 1].

1. How to Tabulate the Spatial Interval
2. How to Tabulate the Indices of the Fourier Series
3. How to Compute the Terms of the Fourier Series
4. How to Compare the Function to its Fourier Series Expansion

Step 1: How to Tabulate the Spatial Interval

The first step is to tabulate the sample points in the spatial interval. These will be the points at which the terms of the Fourier Series of f(x) will be evaluated.

Subdivide the interval [0, 1] into N+1 equally-spaced sample points a distance Δx apart. In a blank EXCEL spreadsheet, enter the sample points along a row, as shown below. For this tutorial, let Δx = 0.1 and N = 10. Note that the value of Δx is shown in cell E1.

Each sample point is calculated as

for n = 1, ... N and x₀ = 0.

For example, in the spreadsheet below the value of the first sample point x₁ = x₀ + 0.1 is shown in cell E3: it is computed by adding the fixed space increment (cell E1) to the value contained in the cell to the immediate left of E3 (cell D3). The expression displayed in the formula box shows the specific EXCEL formula used to compute the value in cell E3. The same formula can be copied and pasted in the cells along row 3 to calculate the desired sample points. Note that the value of the space increment Δx should be always read from cell E1, therefore in the formula the reference to cell E1 should not change when the formula is copied and pasted in other cells of row 3. This is achieved by preceding both the column and row labels in the cell address by a dollar sign, that is, $E$1 instead of E1.

Step 2: How to Tabulate the Indices of the Fourier Series

The second step is to tabulate the index k in the Fourier Series.

The first nonzero term in the series corresponds to k = 0. In your EXCEL spreadsheet enter 0 in a column different from those used to store the spatial points, for example choose column A, cell A7, as shown in the image below. The other indices will be entered in the rows below cell A7. Note that you do not need to enter the value of each index by hand. Simply add 1 to the previous index. For example, the value in cell A8 is obtained by adding 1 to the value in cell A7, as displayed in the formula box. Then, the same formula used in cell A8 can be copied and pasted into as many cells (in column A) as the terms you wish to compute.

Step 3: How to Compute the Terms of the Fourier Series

The k^th term of the Fourier Series for the function considered in this tutorial has the form

By systematically changing the value of the index k, one can compute as many terms as desired at a given point x. This tutorial will show you how to sep up a spreadsheet to perform these calculations effortlessly.

First code an EXCEL formula to compute the coefficients of the Fourier series. The k^th coefficient has the form

In coding a formula to compute b_k, keep in mind that the desired value of k must be read from the corresponding cell in column A.

In the image below, column B is used to store the values of the coefficients b_k. Specifically, the value of the first coefficient for k = 0 is shown in cell B7. The formula used in cell B7 is shown in the formula box. The other coefficients for k > 0 will be computed by simply copying and pasting the formula used in cell B7 into the cells immediately below cell B7.

Note that EXCEL has a built-in function, PI(), that returns the constant π accurate to 15 digits. Also, note how in the formula used in cell B7 the cell containing the value of k is referenced by using a dollar sign in its address (i.e., $A7 instead of A7). This is simply to indicate that, in case this formula is pasted into a column other than column B, the value of k must still be read from a cell in column A.

Next compute the terms of the Fourier series at each sample point x_n. Begin by coding a formula to compute the expression

where the value of the corresponding coefficient b_k is read from column B. The image below shows the formula used to compute the first term of the series at x₀ = 0. The result is stored in cell D7. The formula is obtained using

• the value of the index k stored in cell A7;
• the value of the sample point x₀ stored in cell D3;
• the value of the corresponding Fourier coefficient stored in cell B7;
• the number π returned by the EXCEL function PI().

Note that again dollar signs are used to reference some cells. Even though it is not necessary to use dollar signs in order to code the formula that computes just one term in the expansion of f, it will become extremely useful as you attempt to fill in the rest of the spreadsheet, as explained next.

For an efficient use of the spreadsheet, it is essential to store information in an orderly manner. The sample points are stored in adjacent columns along one row, the indices of the series are entered in subsequent rows in one column: the same structure should be used to compute the terms of the Fourier Series. Thus, if the first term of the expansion at x₀ = 0 is stored in cell D7, then it is convenient to store the second term computed at the same point x₀ in the cell immediately below D7, or cell D8, consistently with the way the values of the index k are tabulated. Similarly, the value of the first term of the expansion computed at the next sample point x₁ should be stored in the cell to the right of cell D7, or cell E7, following the tabulation of the sample points. This way of storing information in columns and rows will allow you to organize your computation in a structured manner.

When copying and pasting the formula used in cell D7 into other cells, keep in mind that EXCEL will automatically update the cell addresses in the formula based on the position of the new cell in which the formula is pasted relative to the original cell. So, if the formula in cell D7 is copied and pasted, say, into cell D8, that is, into a cell in the same column of but one row below the original cell, all cell addresses in the formula will be updated to reflect the change of row, with the exception of those containg dollar signs. Any row or column label preceded by a dollar sign will not be changed when the formula is pasted into a new cell.

For example, the formula in cell D7 contains references to cells A7 and D3, which store the values of the index and sample point, respectively. If no dollar signs were used and the formula were copied and pasted, say, into cell E8, that is, into a cell one column to the right and one row below, EXCEL would increase all row and column labels by one, that is, any reference to A7 would change to B8, and any reference to D3 would change to E4, and the result of the calculation would be incorrect. To perform the correct computation in cell E8, the formula should read the new index from cell A8 and the new spatial point from cell E3. Thus, any reference to A7 should change to A8, and any reference to D3 should change to E3. In other words, the column label A in the address referencing the cell containing k should be fixed, and so should be the row label 3 in the address referencing the cell containing x. Therefore, a dollar sign must be added in front of the column label A and the row label 3 in the addresses referencing cells A7 and D3, respectively. Thus, $A7 should be used instead of A7, and D$3 instead of D3. A similar reasoning should be applied to reference the cell containing the Fourier coefficient, B7.

Keep in mind that the Edit|Fill|Fill Down and Edit|Fill|Fill Right features will allow you to fill in cells quickly and thus calculate as many modes as you like effortlessly.

Step 5: How to Compare the Function to its Fourier Series Expansion

Once you have calculated all the desired terms of the Fourier series at each sample point, you need to find the (truncated) series expansion of the function at each of those points. This corresponds to adding up all the calculated terms at each point. The summation can be performed easily in EXCEL by using the build-in function SUM().

The image below shows the values of the Fourier series S_N(x) at the sample points, truncated at N = 24. In particular, cell D32 contains the value of the expansion at x = 0 obtained by adding all the values in column D, from cell D7 to cell D30. Note that the formula used in cell D32 is displayed in the formula box. The values of the expansion are the other sample points are stored in the adjacent cells in row 32, and are obtained by copying and pasting the formula used in cell D32 into the other cells.

Next tabulate the values of the function f at the sample points. The values of the function can be stored, for example, in row 4 and aligned with the values of the points at which they are calculated, as shown in the image below.

For an easy comparison, plot the function and its Fourier series expansion on the same graph.

Begin by plotting the function whose values are stored in row 4:

• Select with the mouse all cells in row 3, from D3 to N3, by left-clicking and scrolling along the row.
• Press the CTRL key and hold it.
• Select with the mouse all cells in row 4, from D4 to N4, by left-clicking and scrolling along the row. Release the CTRL key.
• Start the Chart Wizard by clicking on the corresponding icon on the toolbar.
• Select the "XY (Scatter)" chart type and a chart sub-type of your choice.
• Select "Series in Rows" and press "Next".
• Enter a chart title and axis labels, if desired.
• Place chart "as object in Sheet 1" if you want your plot to be displayed in the same worksheet as your data.
• Click "Finish" to creat the plot of u(x) at t = 0.05.
• Right-click with the mouse on any data point on the chart and select "Source Data" from the menu.
• Select the Series tab.
• Type a name for the data series in the chart, for example "Fourier Series Solution". This is the description that will appear on the chart legend for this data series.

Then add the plot of the Fourier series calculated in row 32:

• Right-click with the mouse on any data point on the chart showing the graph of f(x) and select "Source Data" from the menu.
• Select the Series tab.
• Click "Add" series to add a new curve to the same plot.
• Type a name for the new curve, for example "Fourier Series S_N(x)".
• Left-click in the dialog box for X Values and select all the cells corresponding to the sample points in row 3.
• Delete any text present in the dialog box for Y Values and select with the mouse (righ-click and hold) all cells in row 32 corresponding to the values of the Fourier series.
• Click OK to add a second curve to your plot.

The resulting plot will look like the image below. Note the excellent match between the function and its Fourier series.