Graphs, Equations and Tables in Microsoft Excel
We can represent functions in three ways*: graphs, tables (or list) and equations. It is often better to view simultaneously the representations of a function to be able to understand its behavior, but it is quite hard – or expensive – to find a software with such functionality. However, with some knowledge on formulas in MS Excel, it can actually be done.
In this tutorial, we are going to represent functions of the form y = mx + b by letting the user enter and change the values of m and b, then generate the equation, the table, and the graph at the same time. Although we are going to use Microsoft Excel 2007 in this tutorial, but this can be done using lower versions. The expected output of this tutorial can be downloaded here.
The prerequisite of this tutorial is basic knowledge of Microsoft Excel, but I designed this tutorial to be stand-alone, so inexperienced Excel users could follow it step by step.
The first thing that we are going to do is to place the letters m in A2 and b in A3. A2 and A3 are examples of cell addresses.
As you can observe, a worksheet is composed of columns labeled with letters and rows labeled with numbers. The intersection of a column and a row is called a cell and its location is determined by its cell address which is its corresponding column letter and row number.
Setting up for user input boxes
- To place the instruction, click cell A1, then type Type m and b in the colored boxes: Note that when you click a cell, the cell pointer appears. The cell pointer is the rectangular box where your text appears when you type in the worksheet.
- Type m in cell A2 and type b in cell A3.
- Place the cell pointer in B2, and choose the color you want from the theme color box(see Figure below). Using the Theme color box, change also the color of B3. You may want to change the font of the text you have written should you want.
Generating the Equation
We want the equation y = mx + b to appear when the user put the value of m in B2 and the value of b in B3. To do this, we will use the concatenate command. The concatenate command joins series of strings (characters, words or numbers). In our case, we will join the string “y =”, the content of B2 (which is our m), the string “x +” and the content of B3. We will put our equation in B7 so that we can put our graph on the right side of our worksheet. The logic is that we want the equation to appear like y = content of B2 x + content of B3, where the content of B2 is the number typed by the user in cell B2 and the content of B3 is the number typed by the user in B3. Notice that the different colors indicate the string groups.
- Type 3 in B2 and 5 in B3. This will enable us to see later if our concatenate command will work.
- Type Equation in A6.
- Place the cell pointer in A7. Type =CONCATENATE(“y =”, B2,”x + “, B3). Then press the ENTER key on your keyboard.
Notes on the concatenate command:
- Words concatenated are separated by commas.
- Texts are enclosed by quotations(such as “y =”)
- Values of cells are distinguished by their address. For example, B2 means that it will display the content of cell B2 which is 3.
- If you want to edit the equation, you can double click the cell or press the F2 function key on your keyboard.
Generating the Table
In this section, we will display the values of the x– from -5 to 5 from B10 all the way to L10 and compute for its corresponding y-values. from B11 to L11.
- Type Table in cell A9 and type x in cell A10 and y in cell A11.
- Next, we adjust the width of the cells containing the table. Highight A10 – B10. In the Home tab of the Excel toolbar, click Format from the Cells block, then click Column width.
- In the column width dialog box, type 3, then press the OK button.
- Type the -5 in B10, -4 in C10, and so on all the way to 5 in L10. Your table should look like the table below.
Finding the values of y
Next, we want to get the corresponding values of y in the equation y = mx + b. To do this, we multiply the value of m (located in B2) to the value of x (located in B10) then add it to the value in b (located in B3). Our expression should be, B2*B10 + B3. In Microsoft Excel, formulas always start with an equal sign. So we will type type =B2*B10 + B3.
- To compute for the value of y in B11, click cell B11, then type =B2*B10 + B3 then press the ENTER key.
- We can type one-by-one the other corresponding values of y but we can do better than that. We will just copy the values of cell B11 to C11 all the way through cell L11 to get the other y-values. But before that, we must modify the formula written in B11. We should edit that formula in B11 and change it to =$B$2*B10+$B$3. Notice that we add dollar signs in B2 and B3. For the explanation of this see ** below.
- To Edit the formula in B10, double click cell B11 (or click cell B10, then press the F2 function key on your keyboard), then change the formula to =$B$2*B10+$B$3
- Next we copy the formula in B11 and paste it from C11 all the way to L11. To do this, place the cell pointer in B11, then click the Copy icon from the toolbar (or press CTRL C).
- Highlight C11 to L11, then click the Paste icon from the toolbar (or press CTRL V)
- If you have done the procedures correctly, and have not changed the values of x and y you inputted earlier, your table should look like the diagram below.
Q1: Change the values of m and b in cells B2 and B3. What do you observe?
Constructing the Graph
- To create the graph, highlight the table from B10 to L11 as shown below
- In the Charts section of the Insert tab of the Excel, click Scatter and choose the graph on the second graph in the first row. A graph should appear on your worksheet.
- Try to change the value of m and b several times. What do you observe about the graph?
Formatting the Graph
If you can observe, the axes of the graph automatically adjust to the value of m and b. Entering the bigger values of m also increases the range of the y-axes. To remedy this problem, we must set the axes to constant range regardless of the values of m and b. We will set the range of the y-axis from – 20 to 20, and the range of the x-axis from -5 to 5.
Setting the Axis Range
- To set the domain and range of the graph of the y-axes, click the y-axis and be sure that a rectangular box appears as shown below.
- Right-click the rectangular box surrounding the y-axis, then click Format Axis from the pop-up menu.
- In the Axis Options, select all the Fixed option button, then copy the numbers shown below.
- Click the Close button.
- Now, adjust the Axis options of the x-axis by right-clicking the x-axis and choosing Format Axis. Copy the numbers as shown below.
- Click the Close button.
- Change the values of m and b. What do you observe about the equation, the table and the graph?
Q2: How does the value of m and b affect the graph?
*Actually, functions can also be represented verbally, so that makes the number of representations 4.
** When you copy formulas in Excel, the locations of cells also adjusts. For example, if I copy the formula in B11, which is =B2*B10 + B3, to C11 – that is one cell to the right – all the cell addresses in the formula will also adjust one cell to the right. If you do that, the formula in C11 will be =C2*C10+C3 where C2 is now our m and C3 our bwhich is different from their original locations. The dollar sign tells Microsoft Excel to points to a constant location – in our case, B2 and B3.