Protecting historical data in live Excel using a hash

From tekkies.co.uk
Jump to: navigation, search

This is a technique I use to spot accidental edits to historical data in a live financial spreadsheet.

For each row of data in the table I care about, I concatenate the text of each column, then calculate a checksum:

=CRC16TWICE(CONCATENATE(A304, B304, C304...K304))

When I want to protect a row, I copy the value of the hash into the column next to it. I then use conditional formatting to highlight any rows that the calculated hash doesn't match the stamped hash.

Use whatever hash function you like - I use CRC16TWICE() by nixda - see http://stackoverflow.com/questions/14717526/vba-hash-string, as the hash-crash percentage is acceptable for my data & it's fast.