How to Deidentify Data in Excel

Protecting sensitive information is more than just good practice, it's a critical part of handling data responsibly. Before you share a dataset or use it for analysis, you need to remove any personally identifiable information (PII). This article will show you several practical methods for de-identifying your data directly within Microsoft Excel.

What is Data De-identification?

Data de-identification, also known as data anonymization, is the process of removing or obscuring personal information from a dataset so that the people it describes cannot be identified. This ensures privacy and helps you comply with regulations like GDPR, HIPAA, or CCPA, which have strict rules about how personal data is handled.

Personally Identifiable Information (PII) is any data that could be used to pinpoint a specific person. Common examples include:

• Full Names
• Email Addresses
• Phone Numbers
• Social Security Numbers or National IDs
• Physical Addresses
• Credit Card Numbers
• Birth Dates

Removing this information allows you to share data for analysis, research, or reporting without compromising individual privacy.

Before You Begin: Always Work on a Copy

This is the golden rule of data cleaning. Before you make any changes, save a copy of your original spreadsheet. De-identification is a destructive process - once you remove the original data, it's gone for good. Always perform these operations on a duplicate file to ensure you have a pristine, untouched version of your original data safely stored away.

You can create a copy by going to File > Save As and giving the new file a different name, like "Dataset-Anonymized.xlsx".

Method 1: Deleting Columns with PII

The simplest and most direct way to de-identify data is to completely remove columns that contain PII. This method is best when the sensitive information is not relevant to your analysis.

For example, if you're analyzing sales trends by region, you probably don't need the customers' names or email addresses. You only need the city or state. In this case, you can safely delete the columns containing names and emails.

Step-by-Step Instructions:

1. Click on the letter at the top of the column you want to remove (e.g., Column A for names). This will select the entire column.
2. To select multiple columns at once, hold down the Ctrl key (or Cmd on Mac) and click on each column letter you want to delete.
3. Right-click on any of the selected column headers.
4. From the dropdown menu, click Delete.

The selected columns containing sensitive data will be permanently removed from this version of your spreadsheet.

Method 2: Masking or Obscuring Data with Formulas

Sometimes you need to retain a hint of the original data without revealing the full picture. Masking lets you replace parts of the text with generic characters like asterisks (*). This is useful for things like showing the last four digits of a phone number or the domain of an email address.

Instead of manually editing each cell, you can use Excel formulas to automate this process in a new column.

Example 1: Masking a Phone Number to Show Only the Last Four Digits

Let's say your phone numbers are in Column C. We'll create a new column (D) to house the masked versions.

1. In cell D2, type the following formula. This formula tells Excel to add a series of asterisks and then pull the last 4 characters from the original phone number in C2.

=REPT("*", LEN(C2)-4) & RIGHT(C2, 4)

1. Press Enter. You should see something like "******1234".
2. Click on cell D2 again. Grab the small square dot (the fill handle) in the bottom-right corner and drag it down to apply the formula to all other rows.
3. Once you're done, you can hide or delete the original Column C.

Example 2: Masking an Email Address

For email addresses, you might want to show the first letter and the domain but hide the rest. If emails are in Column E, we can use a combination of formulas.

1. In the adjacent cell (F2), enter this formula:

=LEFT(E2, 1) & "***" & MID(E2, FIND("@", E2), LEN(E2))

1. Press Enter. An email like john.doe@email.com will now appear as j**@email.com*.
2. Use the fill handle to apply this formula down the column.

After creating your masked columns, you can copy them and use Paste Special > Values to convert the formulas into static text. This prevents formula errors later on. After that, you can safely delete the original columns with the PII.

Method 3: Pseudonymization (Replacing with IDs)

Pseudonymization is a powerful technique where you replace PII with a consistent, non-identifiable key or code. For example, instead of using real names, you assign each person a unique ID like "User_001," "User_002," and so on.

This method allows you to track individual activity within the dataset (e.g., how many times "User_001" made a purchase) without knowing their real-world identity. A key benefit is that if you keep a separate — and highly secure — lookup table linking the real names to the user IDs, you can re-identify the data later if needed.

Step-by-Step Instructions:

1. Create a Lookup Table: Create a new sheet in your workbook or a completely separate file. In this new location, create two columns: one for the PII (e.g., "FullName") and one for the pseudonym (e.g., "UserID"). Populate this table with every unique name from your dataset and assign each a unique ID. This lookup table is extremely sensitive and must be stored securely.
2. Add a New Column to Your Main Sheet: Go back to your main data sheet and insert a new column where you will put the "UserID".
3. Use XLOOKUP to Pull in the IDs: In the first cell of your new "UserID" column, use the XLOOKUP or VLOOKUP formula to find the matching ID from your lookup table. If your names are in Column B and your lookup table is on a sheet named "Lookup," the formula would look like this:

=XLOOKUP(B2, Lookup!$A$2:$A$100, Lookup!$B$2:$B$100, "Not Found")

1. Apply the Formula: Press Enter and drag the fill handle down to populate the UserID for all rows.
2. Replace Formulas with Values: Select the entire "UserID" column, copy it, and then use Paste Special > Values to lock in the IDs.
3. Delete the Original PII: Now that you have the UserIDs, you can delete the original column that contained the full names.

Method 4: Generalization and Bucketing

Sometimes, individual data points aren't PII on their own but can be combined with other data to identify someone. This is called quasi-identifiable information. For instance, knowing someone's exact birth date, zip code, and gender could be enough to identify them in a specific area.

Generalization helps prevent this by making the data less specific. Instead of using an exact birth date, you can use an age range. Instead of a 5-digit zip code, you can use the city or state.

Example: Bucketing Ages into Ranges

If you have a column with specific ages (e.g., Column F) and want to group them into ranges like "20-29" or "30-39," you can use a nested IF formula.

1. In a new column (G), enter the following formula in cell G2:

=IF(F2<20, "Under 20", IF(F2<30, "20-29", IF(F2<40, "30-39", IF(F2<50, "40-49", "50+"))))

1. This formula checks the age in cell F2 and assigns it to the appropriate text bucket.
2. Drag the formula down to apply it to all rows.
3. Once complete, you can replace the formulas with values and delete the original age column.

Best Practices for De-identification

• Scan for Hidden PII: Don't just check the obvious columns. Sensitive information can hide in "Notes," "Comments," or "Description" fields. Use Excel's Find feature (Ctrl + F) to search for common PII patterns like "@" symbols for emails or area codes for phone numbers.
• Consider Combinations: Think about how different columns could be combined to re-identify someone. Even after removing names, a combination of job title, city, and age might be enough to pinpoint an executive at a small company. Generalization helps mitigate this risk.
• Document Your Steps: Keep a record of what you removed, masked, or generalized. This creates an audit log that is helpful for compliance and for explaining your process to others.
• Get a Second Pair of Eyes: After you've finished, ask a colleague to review the anonymized dataset. A fresh perspective might catch something you overlooked.

Final Thoughts

De-identifying data in Excel is a fundamental skill for anyone working with sensitive information. By mastering techniques like deletion, masking, pseudonymization, and generalization, you can protect individual privacy and ensure your datasets are safe for analysis and sharing.

While these manual steps in Excel work well, they can become time-consuming and prone to error when you're dealing with constantly updating data. To move beyond manual CSV downloads and cleaning, we built Graphed. Our platform connects directly to data sources like Shopify, Salesforce, and Google Analytics, streaming data into a secure environment where you can build reports with natural language. This lets your team safely explore insights in real-time without ever needing to handle raw, sensitive data files themselves.