Display how data was entered into a cell for audit trail purposes

It is often useful to display how data was entered into a cell as it might provide additional information, a sort of audit trail.  So if, in a cell, someone entered =(3*10)+(6*5)+7 the cell will only show 67 and you won’t be able to tell how it was made up unless you click on the cell. How it was made up may be useful, so in this example above perhaps it is 3 boxes of 10, 6 boxes of 5 and 7 singles.

Note that in the ideal world, if this was important to you, you would have put it into separate cells, but in most cases this is not done and there is lots of hidden information available in how the cell comes to its number.

A easy way to make it visible is to create an adjacent column and make use of the FORMULATEXT function in Excel (note not all versions of Excel have this function available).

YouTube Show audit trail of cell contents

Using FORMULATEXT to show the cells Audit Trail

As shown below, cell D6 has some useful information typed into it, as does the rest of the column. When we print it, we lose access to this information. However, note that column G shows the detail and most importantly without the user manually typing it in.

Display how data was entered into a cell

This is achieved with the FORMULATEXT function. As shown below, in G4 all we did was enter the formula =FORMULATEXT(D4). That’s all. Excel is now told to show you how the formula in D4 is made up and NOT show the result of the formula.

Display how data was entered into a cell

This way you have the benefit of the result being used within your Excel calculations but you also can show the thinking that went into getting the result (and sometimes this is more useful!)

Write paragraphs in Excel

Excel refuses to align a number left or centre

Audit a spreadsheet to find inconsistent formula