When you were in high school, you may have used your graphing calculator to plot math functions. It was magical typing in the equation and having the calculator instantly plotting the graph of complex functions.

But, Excel and a graphing calculator differ in how they produce graphs of functions defined by formulas. Excel can create these graphs but it’s not as straightforward as using a graphing calculator.

In previous blog posts I showed you how you can leverage Excel's Dynamic Arrays to simplify the process of creating 2D cartesian plots, 2D parametric equations and 2D polar functions.

The drawback with the process used in the blogs above is that every time you want to plot a new function you have to go to the cell holding the formula of the function and modify it (remember because we're using Dynamic Arrays--that spills--it is only one formula that we need to edit).

Wouldn't it be nice, instead, to input your function inside a cell and have Excel read that string of characters and plot the function--similar to a graphing calculator? Well that's what I'm going to do here.

Before we start, however, I need to give credits where credits are due. This methodology uses an Excel function called EVALUATE(). I learned about how to use the EVALUATE() function to evaluate (obviously) formulas from Craig Hatmaker. You can read about it here. I highly recommend following the work of Craig on LinkedIn. You'll learn a ton of concepts about the proper application of 5g modelling in business.

I also need to mention that with EVALUATE() being an Excel 4.0 Macro we need to save the file in the .xlsm format for it to work.

Ok. Let's get started.

We'll start off from this blog post and wrap the EVALUATE() function into a LAMBDA() function to simplify the process of graphing functions in EXCEL. A reminder. I'm graphing a function with a single independent variable x in a cartesian coordinate system defined by the following equation:

y = f(x) = (1 - 4x - x³/17) * sin(x²) in the interval [-15,15]

The setup was as per the below screenshot and the function to be graphed lived in cell C8.

Next, lets wrap the EVALUATE() function inside a LAMBDA() function that I'll call EvaluateFormula.

5. On the Formulas tab, in the Defined Names group, click Define Name.
6. In the New Name dialog box, in the Name box, type EvaluateFormula.
7. In the Refers to box, type the following formula:

=LAMBDA(formula,EVALUATE(formula))

as per the screenshot below:

8. Click the Ok button to close the New Name dialog box.

By now we have created a named formula EvaluateFormula in addition to the two already existing named ranges lstX and lstY as can be seen in the Name Manager dialog box below:

9. Enter the following formula in cell C8:

= IFERROR(EvaluateFormula(F4),NA())

Notice that I used the IFERROR function to force an #N/A where functions cannot be evaluated (i.e. division by zero, LN of numbers less than or equal to zero, square roots of negative numbers, etc.). Like that the Excel charts will show #N/A as an empty cells (gaps) instead of a zero.

10. Above the chart, select the text box holding the function definition and its interval. Next, in the Formula Bar, type the equal sign "=" followed by a mouse click on cell F5. Press the Enter key on the keyboard. Like this we made the chart title dynamic.
11. Re-format the font and color of the textbox.

The worksheet should look like this:

12. If you want to plot the curve of another equation, say

y = f(x) = abs(sin(x^x) / 2^((x^x - pi() / 2) / pi())) in the interval [0,3]

then type the following text in cell F2

abs(sin(x^x) / 2^((x^x - pi() / 2) / pi()))

13. Type 0 in cell C2 and 3 in cell C3. Adjust the boundaries of the horizontal scale and the chart should look like this:

One last thing before closing. We used "x" as the independent variable. This has an advantage. All the Maths and Trig functions in Excel don't have x in their name except the EXP() function. Hence, If you want to plot a function which includes exponentials, you have to type it using capital letters as in EXP. Otherwise Excel will substitute the x in exp with the reference in cell F3 and the model won't work.

One more thing :). This model may not work on your computer depending on the Macro settings on your system. Enable Excel 4.0 macros when VBA macros are enabled should be checked in your settings.

1. The first thing to do is to define the function to be plotted. To do that enter "f(x) =" in cell E2 and type the function (1 - 4 * x - x^3 / 17) * sin(x^2) in cell F2. Remember this is a string expression without the = sign at the beginning. Always use a small cap "x" to represent the independent variable (bare with me, more about this later) and use Excel principles and concepts for writing formulas.

2. Next, I need to tell Excel where the independent x values live in the worksheet. For that enter "x =" in cell E3 and the following formula in cell F3:

= CELL("address",B8) & "#"

The # symbol at the end of the formula is telling EXCEL that we are referencing a Dynamic Array.

3. Next, I need to substitute the "x" in the function definition (cell F2) by the reference in cell F3. For that enter "f(x) =" in cell E4 and the following formula in cell F4:

= SUBSTITUTE(F2,"x",F3)

4. Finally, I need to make the chart title dynamic for that I'll add the function definition and the boundaries of the interval into a single text with a forced return (CHAR(13)). For that, enter "Chart title" in cell E5 and the following formula in cell F5:

= E2 & " " & F2 & CHAR(13) & C2 & " ≤ x ≤ " & C3.

The worksheet should look like this:

Views > Excel charts > Maths in excel - graphing calculator in excel

CREATE AWSOME CHARTS

Thank you for reading, and I hope you found this useful. If you have any questions, find me on X and ask me anything.