## Graphs, Equations and Tables in Microsoft Excel

**Introduction**

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

**and**

*m***, 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.**

*b*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.

**Preliminaries**

The first thing that we are going to do is to place the letters ** m** in

**A2**and

**in**

*b***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

**in**

*m***B2**and the value of

**in**

*b***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

**), the string “x +” and the content of**

*m***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

**(located in**

*m***B2)**to the value of

**(located in**

*x***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**cell*y*in B11*,*click**B11, then type =****B2*B10 + B3**then press the ENTER key.- We can type one-by-one the other corresponding values of
but we can do better than that. We will just copy the values of cell*y***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. We should edit that formula in*B11***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 thefrom the toolbar (or press**Paste**icon*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**Inser**t 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
and*m*. What do you observe about the graph?*b several times*

**Formatting the Graph**

If you can observe, the axes of the graph automatically adjust to the value of ** m** and

**. Entering the bigger values of m also increases the range of the**

*b**y*-axes. To remedy this problem, we must set the axes to constant range regardless of the values of

**and**

*m***. We will set the range of the**

*b**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
**Clos****e**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
and*m*. What do you observe about the equation, the table and the graph?*b*

**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

**which is different from their original locations. The dollar sign tells Microsoft Excel to points to a constant location – in our case,**

*b***B2**and

**B3**.