+ Reply to Thread
Results 1 to 8 of 8

How to De-Sensitize Data

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,146

    How to De-Sensitize Data

    You stand a much better chance of getting your issue worked on if you can provide a sample workbook.

    Often workbooks contain sensitive or proprietary data. We certainly do not want to see this data, but there are tools you can use to obscure the data.

    Rule1: First of all, we probably do not need ALL of your records. Anywhere from several dozen to several hundred rows of data will do depending on the nature of the data.

    CTRL-H: In my case I work with wireless devices and the failure codes associated with returned models. The vendors would get really pissed off as in big-time litigation should this information become public.

    First, I filter down the list of 100 or so models to about 10 (See Rule 1). Then I use CTRL-H to replace Samsung Galaxy with Model 01, and iPhone 7S with Model 02 and so on.

    To further obfuscate the data, I will use Fail Code 01 through Fail Code 10 in order for the first 10 models (whatever they are) and then copy/past this block of error codes down for as much data as I have.

    This works if you have a fairly fixed set of values.

    Drag Down: Sometimes every record is unique. You can alias everything one at a time or you can type in Person 01 in the first row and drag the value down it will become Person 02, Person 03, etc.

    This works for Social Security Numbers XXX-XX-0001 or phone numbers XXX-XXX-0001 and just about anything else you can think of.

    RANDBETWEEN: Finally if the actual numbers are an issue, you can randomize them. Use RANDBETWEEN to assign values and then Copy the whole range and save as values only.

    I sometimes use RANDBETWEEN to assign failure codes.

    RANDBETWEEN can also be used with Dates. Base Date + RANDBETWEEN (0,180) will get a list of dates up to 180 days after the base date.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: How to De-Sensitize Data

    This should get 'sticky' status in each of the main help forums.

    I'd only add to the list one reminder: "Hiding" rows/columns/sheets with sensitive data is not the same as removing them.
    I can't believe it needs to be said, but I often find troves of client info on hidden sheets.
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,934

    Re: How to De-Sensitize Data

    It definitely needs to be said! Especially with the new GDPR rules in the EU: https://www.csoonline.com/article/32...and-facts.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    211

    Re: How to De-Sensitize Data

    Also if you're a windows user, you should think about the meta-data

    right click the file(windows file explorer)>Properties(option)>Details(tab)>Remove Properties and Personal Information(link)>OK(button)

    Name:  SNAG_Program-0001.jpg
Views: 419
Size:  60.1 KB
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

  5. #5
    Registered User
    Join Date
    07-13-2018
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    12

    Re: How to De-Sensitize Data

    These are some great tips, as I compile a file to post on my own thread, I will use these practices to make sure I de-sensitize the data. Thank you fellow members.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,250

    Re: How to De-Sensitize Data

    Quote Originally Posted by CAntosh View Post
    This should get 'sticky' status in each of the main help forums.
    I have made this a sticky.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    05-22-2021
    Location
    New York
    MS-Off Ver
    Mac
    Posts
    4

    Re: How to De-Sensitize Data

    Quote Originally Posted by Gregor y View Post
    Also if you're a windows user, you should think about the meta-data

    right click the file(windows file explorer)>Properties(option)>Details(tab)>Remove Properties and Personal Information(link)>OK(button)

    Attachment 578859
    This is really tough to do on a Mac because each time I have to do it in per workbook. You have any suggestions on workarounds?

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,226

    Re: How to De-Sensitize Data


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-30-2017, 08:28 PM
  2. Replies: 11
    Last Post: 12-23-2014, 01:01 PM
  3. Replies: 2
    Last Post: 04-19-2014, 10:06 PM
  4. Replies: 34
    Last Post: 01-21-2014, 03:07 PM
  5. [SOLVED] Copy Data from one sheet and paste the consolidated data into another (Data Range Varies)
    By kittu55 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-13-2013, 10:02 PM
  6. Replies: 4
    Last Post: 02-01-2013, 10:18 AM
  7. Use of Excel to pull data from database daily, posting new data below older data
    By Ishness in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2011, 10:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1