Astra

How to Make a Titration Curve in Excel and Find Equivalence Point

Estimated reading: 3 minutes

In this tutorial, we will learn step-by-step how to draw a titration curve in Excel from the lab data, and in the later part of this tutorial, we will find the equivalence point on the titration curve in Excel.

Sample output of this tutorial

How to Make a Titration Curve in Excel

We will divide the steps into three sections:

  1. Make Titration Curve in Excel
  2. Draw First Derivative Titration Curve in Excel
  3. Find the Equivalence Point on the Titration Curve in Excel

Suppose you get the following data in the lab during acid base titration

How to Make a Titration Curve in Excel data

Section A. Make Titration curve in Excel

Step 01: Select the data range B2:C20

Step 02: Navigate to the Insert tab

Step 03: From the chart section, click on the Scatter Chart.

How to Make a Titration Curve in Excel step3

Your titration chart is ready.

How to Make a Titration Curve in Excel done

In this chart, Volume is plotted along X-axis, and PH is plotted along Y-axis

Section B: Draw First Derivative Titration Curve in Excel

Step 04: Create a new column named ‘’Average’’ volume and apply the following formula in the cell D2

=(B2+B3)/2

Step 05: AutoFill the remaining cell in column D.

Draw First Derivative Titration Curve in Excel

Step 06: Again, create a new column named “Derivative” and apply the following formula to cell E2. Then AutoFill

=(C3-C2)/(B3-B2)
How to Make a Titration Curve in Excel step 6

Note: We omitted the last data point as the last data point alone won’t give the correct average and derivative

Step 07: Right-click on the chat we created in Section A.

Step 08: A list of several options will appear. Click on the “Select Data” option.

Draw First Derivative Titration Curve in Excel step 08

Step 09: A small window named “Select Data Source” will pop up. Click on the “Add” button. 

Step 10: A small window named “Edit Series” will appear.

Step 11: Click on the field of the Series X values and select the data range from the Average Volume column, or apply the following formula.

=Sheet1!$D$2:$D$19
How to Make a Titration Curve in Excel

Step 10: Similarly, input data from the Derivative column in the field of the Series Y Values or apply the following formula, then click on the OK button.

Step 11: Again click on the OK button in the Select Data Source

The result is not looking good.

Step 12: Double-click on the blue curve. The Format Data Series tab will appear on the right side of the monitor.

Step 13: Click on the Series options and select the Secondary Axis

Draw First Derivative Titration Curve in Excel step 13

The result is

Draw First Derivative Titration Curve in Excel done

Section C: Find Equivalence Point on the Titration Curve in Excel

Step 14: Now keep the mouse pointer on the apex point of the derivative curve. You will see the maximum point. Or you can apply the following formula to get the maximum point from the Derivative data. The maximum point of derivative data represents the equivalence point.

=MAX(E2:E20)
Find Equivalence Point on the Titration Curve in Excel

Find the volume and PH value that correspond to the equivalence point.

Find Equivalence Point on the Titration Curve in Excel done

If you don’t get the exact maximum point derivative data based on the lab data, you may need to do linear interpolation. 

1 thought on “How to Make a Titration Curve in Excel and Find Equivalence Point”

Leave a Reply

Your email address will not be published. Required fields are marked *

Share this Doc
Jump to Topics
SOLVED EXCEL
Scroll to Top