How to Calculate Derivative in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In our daily lives, we often need to calculate derivatives with respect to a variable. Generally, a derivative is defined by the rate of increase or decrease of a function with respect to a variable. In Excel, there is no built-in function to calculate the derivative. However, we can estimate the value of derivatives by following some quick and efficient approaches in Excel. In this article, we will learn two simple methods to calculate derivative in Excel. So, let’s start this article and explore these methods.

Table of Contents hide

Download Practice Workbook

2 Simple Methods to Calculate Derivative in Excel

1. Using Mathematical Formula

2. Utilizing Finite Difference Method

Practice Section

Conclusion

Download Practice Workbook

Calculating Derivative.xlsx

2 Simple Methods to Calculate Derivative in Excel

In this section of the article, we will learn two simple methods to calculate derivative in Excel. Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

1. Using Mathematical Formula

Using the mathematical formula is one of the easiest ways to calculate derivative in Excel. We have some specific mathematical formulas to calculate the derivatives. For example,

the derivative of x,

d/dx(xⁿ) = n*x^n-1

d/dx(sinx) = cosx

d/dx(cosx) = -sinx etc.

Let’s say, we have the following dataset, where we have sin(2x^2) as the input of f(x) Value. According to the mathematical formula, the derivative of this function is 4x*cos(2x^2), which is denoted by f'(x). Now, let’s follow the steps mentioned below to calculate the value of f'(x) in Excel.

Steps:

Firstly, apply the formula given below in cell C9.

=SIN(2*B9^2)

Here, cell B9 indicates the first cell of the x Value column, and the SIN function will return the f(x) Value in cell C9.

Then, press ENTER.

Consequently, you will have the f(x) Value for x = 0, in cell C9 as shown in the following image.

Afterward, use the AutoFill option to get the remaining f(x) Values.

After that, use the following formula in cell D9.

=4*B9*COS(2*B9^2)

Here, cell B9 refers to the first cell of the x Value column, and the COS function will return the f'(x) Value.

Now, hit ENTER.

As a result, you will have the f'(x) Value for x = 0, in cell D9.

Finally, apply the AutoFill feature to get the rest of the f'(x) Values as demonstrated in the following picture.

2. Utilizing Finite Difference Method

Utilizing the Finite Difference Method is another smart way to calculate derivative in Excel. First, let’s familiarize you with the Finite Difference Method.

In the Finite Difference Method, we will use an h Value. The h Value is a very small value that is almost equal to 0. In this example, we will use 0.00001 as our h Value. The formula to calculate the derivative in the Finite Difference Method is given below.

d/dx(x) = {f(x+h)-f(x)}/h

Let’s say we have the following dataset with a polynomial 2x^3+x^2−4x that is denoted by f(x). Our goal is to calculate the f'(x) Value of this polynomial. Now, let’s use the instructions outlined in the following section to calculate the derivative in Excel.

Steps:

Firstly, insert the h Value as shown in the following image.

After that, use the following formula in cell D7.

=B7+C7

Here, cell B7 indicates the first cell of the x Value column, and cell C7 represents the first cell of the h Value column.

Then, press ENTER.

Afterward, use the AutoFill option to get the (x+h) Values for all x Values as shown in the image below.

Now, apply the following formula in cell E7.

=(2*B7^3)+(B7^2)-(4*B7)

Subsequently, hit ENTER.

As a result, the f(x) Value will be available in cell E7.

Then, use the AutoFill feature to obtain the remaining f(x) Values.

After that, use the formula given below in cell F7.

=(2*D7^3)+(D7^2)-(4*D7)

Here, cell D7 indicates the first cell of the (x+h) Value column.

Then, press ENTER.

Subsequently, you will have the f(x+h) Value for x = 1 in cell F7.

Then, apply the AutoFill option to get the remaining f(x+h) Values.

Following that, enter the following formula in cell G7.

=(F7-E7)/C7

Here, cell F7 refers to the first cell of the f(x+h) Value column, cell E7 indicates the first cell of the f(x) Value column, and cell C7 represents the h Value.

Then, hit ENTER.

Consequently, you will get the f'(x) Value for x = 1 in cell G7.

Lastly, use the AutoFill feature to get the rest of the f'(x) Values as demonstrated in the following image.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to calculate derivative in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.