Balancing Chemical Equations With Spreadsheets

Balancing Chemical Equations with Spreadsheet Matrix Algebra

Given the products and reactants of a chemical reaction, we want to find the stoichiometrically balanced equation for the reaction. This can be done with the matrix functions of a spreadsheet program. I use the OpenOfficeCalc spreadsheet which is available for free on the Internet, alternatively Excel can be used if preferred.

Step 1: Set Up the Inputs

To begin with, we write out the reactant and product species formulae and apply algebraic coefficients to each: Reactants Products

eg. a H2O2 +b K2Cr2O7 + c H2SO4 → d CrO5 + e K2SO4 + f H2O

The next step is to set up a table in the spreadsheet as shown in Table 1. The reactants and products are entered in column A and their coefficients a,b,c, d,e, & f in column B. Column C is for the calculated results and can be left empty to begin with. Next is a table in block D3:K8 which will contain the input matrix, with the coefficient headings a-f at the top of the block. In column J are entered each of the atomic species in the reaction, ie H,K,O,Cr & S ( their order doesn't matter ) and x at the end. The table is filled with the numbers of each type of atom in the reactant and product compounds, reactants being positive and products negative. Thus in column D, hydrogen peroxide has 2 H and 2 O atoms; in column E potassium dichromate has 2 K, 7 O and 2 Cr; and in column F sulphuric acid has 2 H, 4 O and 1 S. For the products, column G chromium pentoxide has -5 O and -1 Cr, column H potassium sulphate has -2 K, -1 S and -4 O, and column I water has -2 H and -1 O. Enter the value 1 in the bottom right cell I8, and fill column K with zeros and 1 as shown.( these are used for the matrix calculations )

Step 2: Matrix Algebra

Having set up the Input Data, select an area below the entry table area for the result matrix as in Table 2.

A matrix formula is entered in cell D18 as follows : {=MINVERSE(D3:I8)}

This will produce a new block D18:I23 containing the inverse matrix coefficients. Copy the block K3:K8 to the block K18:K23 , then enter the matrix formula {=MMULT(D18:I23;K18:K23)} in cell D25. This will fill block D25:D30 with a list of values, which will probably not be integer values which are required for the balanced equation. To generate these, enter the formula =MIN(D25:D30) in cell E25, and then enter the formula

=D25/E$25 in cell F25 and copy F25 to the rest of the block F25:F30. This should create a set of integer coefficients for variables a-f. These results can be copied into column C alongside each of the reactant and product formulae for the final balanced equation.

Step 3: Results

4 H2O2 + K2Cr2O7 + H2SO4 → 2 CrO5 + K2SO4 + 5 H2O

For reactions with different numbers of reactants and products, it will be necessary to modify the formulae and block sizes in the spreadsheet as appropriate.

The Formula View.pdf file shows each cell's contents in formula format.