In this tutorial, I am going to share with you step-by-step procedures to make first derivative graph on Excel. Also, throughout this tutorial, you will learn some valuable functions and techniques that will be helpful in other Excel-related tasks. In the last section, we will see how to find the slope of a given dataset.
How to Make First Derivative Graph on Excel: Easy Steps
We have taken a concise dataset for this tutorial to explain the steps clearly. This dataset has 5 columns and 6 rows. The main inputs for this dataset are the Price and the Demand columns. Here, the Price will be in Dollars and the Demand will be in the number of units.
Step 1: Inserting Input Data
In this first step, we will insert the necessary data to calculate the first derivative and generate the graph in Excel. Let us see how to do this.
- First, go to cell B5 and insert the Price data as in the image below in cells B5 to B10.
- Then, format the cells in column B as Accounting.
- Similarly, insert the Demand data in cells C5 to C10.
Step 2: Creating Variations Columns
In order to calculate the first derivative, we need to find the variation in the Price and Demand data. For this, we will use some basic formulas. Follow the steps below.
- To begin with, go to cell D5 and type 0.
- Next, type in the following formula in cell D6:
- Now, press the Enter key and copy the formula to the cells below.
- As a result, this will give the Price Variation.
- Similarly, insert the below formula in cell E6:
- Then, press Enter and copy this formula to the below cells.
Step 3: Finding First Derivative
Once we have calculated the variations, we can proceed to find the first derivative using another simple formula. Let us see how we can do this.
- To begin this step, type 0 in cell F5.
- Then, insert the following formula in cell F6:
- Now, press Enter and copy this formula to the cells below using the Fill Handle.
Step 4: Generating First Derivative Graph
Now, that we have all the required data, we can proceed to generate a graph. In Excel, there are many options to create a graph, We will the Scatter plot to clearly visualize the curve.
- First, select the cells from B5 to B10 and F5 to F10 holding the Ctrl key.
- Then go to the Insert tab and from the Scatter drop-down, select Scatter with Smooth Lines and Markers.
- Consequently, this will generate the derivative graph reflecting the change in Demand with respect to Price.
Derivative Function in Excel to Find Slope
The SLOPE function in Excel returns the slope of a regression line based on some y and x values. This slope is actually the measure of the steepness of the data variation. In Mathematics, we use the formula as rise over run which is the change in y values divided by the change in x values.
- First of all, navigate to cell C10 and type in the following formula:
- Finally, press the Enter key and you will get the slope for the input data.
Things to Remember
- If there is only one set of points, the SLOPE function will return #DIV/0!
- If the number of y and x values are not equal, the formula will return #N/A.
- To copy a formula to other cells, you can double-click on the Fill Handle instead of dragging.
Download Practice Workbook
I hope that you were able to apply the steps that I showed in this tutorial on how to make first derivative graph on Excel. Although we have worked with a small dataset, you can follow these steps to generate graphs from more large datasets. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.