Skip to main content
Chemistry LibreTexts

Excel Part 3—Constructing a Titration Curve

  • Page ID
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)

    I. Introduction

    A titration curve is a graph of how some quantity of a solution (the dependent variable, y) changes with the addition of known amounts of a titrant of known concentration (the independent variable, x).  With acid-base titrations, the dependent variable is the pH of the solution; and the titration curve answers the question “How does the pH of the solution change with the addition of the titrant?”   The neutralization of a strong base with a strong acid comes to equilibrium quickly, so this titration can be done with a sigmoidal titration curve.

    The construction of an acid-base titration curve involves thinking through how the pH is determined during each different phase of the process:

    • Before the addition of any titrant
    • Pre-equivalence phase
    • Equivalence point
    • Post-equivalence phase.

    As you think through how the pH is determined at these different times in the titration, you will see how to construct the functions needed to do the calculations for each phase.  After that, we will use the pH and volume of titrant information to construct the titration curve.

    II. The Problem

    Let’s say that you want to titrate a 50.00 mL aliquot of 0.1000 M NaOH with a 0.1000 M solution of HCl and then construct the resulting titration curve.  Using an Excel spreadsheet, calculate the pH of the solution after the addition of 0.00, 10.00, 25.00, 40.00, 45.00, 49.00, 49.50, 50.00, 50.50, 51.00, 55.00, 60.00, 75.00, and 90.00 mL of titrant; and then prepare a titration curve from the data.

    III. Setting Up the Spreadsheet

    A. Data and Functions

    1. Reference Info

    What do we know in the beginning that will be relevant in an acid-base titration?  The volume and the concentration of the solution being titrated, the concentration of the analyte, and the ion-product constant for water (Kw) are four pieces of information that I would use.

    Enter the following labels: VNaOH, mL in cell A3, [NaOH] in cell A4, mmol NaOH in cell A5, [HCl] in cell A6, and Kw in cell D3.  You can type the value for four of these in the cell to the right of the label: 50.00 in cell B3, 0.1000 in cell B4, 0.1000 in cell B6, and 1.00E-14 in cell D3. The mmol NaOH (which will be the same at each point in the titration) must nevertheless be calculated, so enter the function needed for this value in cell B5.

    2. The Independent Variable (x)

    The independent variable is the variable that we change by design: in this case, that’s the increasing volumes of titrant added at different points in the titration.

    Enter the label VHCl, mL into cell A8, and then enter the volumes of titrant added into cells A9-A22. Enter the label mmol HCl into cell B8, enter the function needed to calculate this into cell B9, and then drag it down through cell B22. (Remember to format the cells for the correct number of digits past the decimal.)

    3. The Functions that Reveal the Dependent Variable (y)

    The dependent variable of interest is the pH of the solution, but the calculation of pH involves different preliminary calculations during the different phases of a titration.  Think about the things we need to know for each phase:

    • Identify What’s in the Flask.
    • Understand the Neutralization Reaction.
    • Calculate the pH Based on What Remains after the Neutralization.

    Enter the following labels: mmol NaOH in cell C8, mmol HClexcess in D8, Vtotal, mL in cell E8, [OH-1] in cell F8, [H3O+] in cell G8, pOH in cell H8, and pH in cell I8.

    1. Before the Addition of any Titrant

      Before the addition of any titrant, what’s in the flask, and how is it related to the pH of the solution?



    2. Pre-Equivalence Phase

      If you were designing a titration experiment, you would want to have several data points in the pre-equivalence phase and in the post-equivalence phase.  In addition, you would want to space some of them closer together just before and just after the equivalence point.  In this titration, 50.00 mL of titrant is required to reach the endpoint.  How did I know that?


      Remember the BCA (Before Change After) chart?  We will use what we learned there to construct the functions that will ultimately calculate the pH.  Notice that as the titration proceeds, the mmol OH-1 will decrease because of neutralization of the NaOH and dilution of the original solution.


      Why is it that the functions you wrote for the initial points are the only ones you need for the pre-equivalence phase? Drag them down through the rows that correspond to the pre-equivalence phase (but no farther).  Note that there’s nothing in columns C and G at this point.

    3. Equivalence Point

      At the equivalence point, all of the analyte has reacted with the titrant; none of the reactants remain.  (We have already talked about the volume of titrant required to reach the equivalence point.)  At this point, what determines the pH?


    4. Post-Equivalence Phase

      What’s going on in the solution once you get past the equivalence point?  What determines the pH?



    B. Spreadsheet Documentation and Details

    Enter the label Spreadsheet Documentation into cell A24, and then complete this section as before.  Remember to include borders, row/column headings, and a title.

    IV. Generating the Titration Curve

    The generation of the titration curve is a feature of Excel.  Create the graph y vs. x to see what this relationship looks like, and insert this graph into your spreadsheet.

    This page titled Excel Part 3—Constructing a Titration Curve is shared under a CC BY-NC-SA 4.0 license and was authored, remixed, and/or curated by Contributor via source content that was edited to the style and standards of the LibreTexts platform.