Data Analysis Using Microsoft Excel

Data Analysis Using Microsoft Excel - Chemical Kinetics

Data Analysis Using Microsoft Excel - Chemical Kinetics

Peter Keusch, University of Regensburg

German version

"Microsoft Excel's capabilities for analyzing data are limited only by your creativity".
William W. Dorner

Determination of the Rate Constant

The Alkaline Hydrolysis of Esters can be monitored by measuring the change in conductance of the reaction mixture with time. The following example shows the analysis of data, which were logged by a data acquisition system (e.g. Cassy or Chembox / Chemex).

Excel File - Alkaline Hydrolysis of Ethyl Propionate (Download)

Plot of conductivity k versus time t

Table 1: Highlighting of the data pairs

The first step in creating a graph using MS Excel is entering the experimental data into a worksheet. The data should be in adjacent columns with the x data in the left column.

The pairs of measured data are highlighted. This can be done with the mouse cursor or via the keybord (Table 1 ).

br>

Then 'Chart' under the 'Insert' menu is chosen. The Chart Wizard takes you through four steps before it draws the graph. The selection of the 'Chart type' - click on XY(Scatter) - and the selection of the appropriate 'Chart sub-type' e.g. 'data points connected by smoothed lines without markers' are the most important steps (Fig. 1). In step 4 of the wizard, click 'As new sheet', and then click 'Finish'.

Figure 1: Selection of the chart type and the chart sub-type

Double click inside the colored graph area permits to remove the grey background. Double-clicking on any element (label, data line, gridline, axis, etc.) will open up a dialog box enabling the user to make extensive modifications in color, layout and more - e.g. double-click on the axis activates the 'Format Axis' dialog box allowing to refine the format of each axis individually ( Fig. 2).

Figure 2: Line chart plot of conductivity versus time t
Hydrolysis of ethyl propionate at 48.3 °C (1) 52.1 °C (2) und 58.4 °C (3)

Application of the second order kinetics

Conversion of the measured data

The alkaline ester hydrolysis is a reaction second order.
The value for k_¥ (e.g. 1,18) is found in the table. Into the first cell (e.g. F4), which is to obtain the value for y = ln (0,5 * (k₀ - k_¥) / (k - k_¥) + 0,5) * 50 according to equation (18) Kinetic equations (Download), e.g. the following formula is entered

=LN(0,5*(B$4-1,18)/(B4-1,18)+0,5)*50.
The formula is displayed in the formula bar (Table 2) when that cell is highlighted, and the result of the formula is displayed in the actual cell. The bottom-right corner of the active cell is drag down to tabulate the rest of the values.

Table 2: Conversion of the experimental data and highlighting of columns
(Every tenth pair of measured values is taken into account)

Creating a second-order plot

Nonadjacent columns are highlighted using the following procedure (Table 2): Ar first the x-values (first column) are highlighted. Then, while holding down the control key (CTRL) on the keyboard, the block containing the y-values is selected. The columns of data highlighted must have exactly the same rows for each column.
Under the menu point 'Insert ® Chart' Excel is instructed to generate a chart from the highlighted spreadsheet cells. In the present case it is suitable to choose the first 'Chart sub-type' e.g. 'scatter without line' (Fig. 3 and 4).

Figure 3: Selection of the chart type and the chart sub-type

plot

Figure 4: Second order kinetics plot according to equation (18) Kinetische Gleichungen (Download)

Graphical determination of the rate constant k

As shown in the scattergram (Fig. 4) the linear portion of the graph is apparent after a induction phase. The appropriate worksheet data (see Trend 1, 2 and 3 in Table 3) are highlighted and copied.

Table 3: Highlighting the data pairs of the linear portion

Next the chart sheet is activated. In the 'Paste Special' dialog (Fig. 5) under the 'Edit' menu is made sure that the cells are added as 'New series' and 'Values (Y) in Columns' are selected. Then the check box next to the statement 'Categories (X Labels) in First Column' is marked.

Figure 5: 'Paste Special' dialog box
Click OK to add the new series of data. On the chart the data series will appear in a different color with different markings as data points (Fig. 6) ¹.

To get to the linear regression menu right click on the highlighted data points on the chart. At the 'Add Trendline' dialog box select 'Type' tab and the 'Linear' option (see image below). Then select OK. A best fit straight line will be drawn through the appropriate data points. If you want to edit the trendline, you can doubleclick it to change its weight, color etc.

By right clicking on the trendline 'Format Trendline' and the 'Options' tab is selected (Fig. 6). To extend the trend line beyond the first and last points of the graph you must enter values in the forecast forward and backward boxes. At the bottom, check 'Display equation on chart'. Select OK. An equation in the form y = mx + b is displayed on the chart. You can drag the text object to any place on the chart so that you can better view it: m is the slope and b is the y-intercept. The slope of the line corresponds to the value of the rate constant k.

Figure 6: Linear regression adding a trendline straight line equation

The Bromination of reactive Aromatics is followed by measuring the change in absorbance with time.

Excel File - Bromination of Acetanilide (Download)

Suppose the reaction obeys a pseudo first order rate law. On that condition the pseudo first order rate constant can be evaluated from a plot of absorbance versus time. According to equation (7) Kinetic equations (Download) Excel is used to fit a exponential curve to highlighted data points (Fig. 7). From the Chart menu choose 'Add Trendline'. The type should be 'exponential'. Under 'Options' tab select 'Display equation on chart', then click OK. The exponential trendline fits data to the expression y = A e ^{- kx} where k' is the pseudo order rate constant (Fig. 7).

Figure 7: Bromination of acetanilide at different reaction temperatures
Plot of absorbance vs time Evaluation of rate constant k'

¹ A multiple series XY Chart with different x-axis data for each data can be generated in the same manner.