One way to anonymise live data for test use

Take your table/spreadsheet with your live data and

  • sort ONLY the field holding the surname into A –> Z order
  • sort ONLY the field holding the first name into Z –> A order
  • split any email address field at the @ sign, and trash the part before the @
  • transpose the first address field by 73%.
  • sort the field holding the SUBURB field into A –> Z order

Your data is now (mostly*) anonymous, but with real, ordinary field contents.

* If you can use any data record to identify a living individual after that then the file had far too few records in it for testing purposes anyway, and you should have made the data up in the first place.

An example
Anonymised data

I strung a number of Excel functions together, to transpose the Address1 field.  I’ve highlighted those in blue.
=PROPER(CONCATENATE(RIGHT(D2,(LEN(D2)-(ROUND((LEN(D2))*73%,0)))),LEFT(D2,ROUND((LEN(D2))*73%,0))))

(the steps, and the comments around, how to anonymise data was shamelessly stolen from Tim Trent’s blog article Is it Legal to Test on Live Data?)