How to Anonymise Excel/Google Sheets & Shopify Data

Some of the most useful findings in a sales audit come from your customer data: who comes back, how often, what they spend, and where they live. Understandably, many brand owners hesitate before handing over spreadsheet rows filled with real names, emails, and home addresses.

The good news is that you do not have to expose private data. With ten minutes in Google Sheets or Excel, you can strip every trace of personally identifiable information (PII) from your export while keeping the one thing that matters for analysis: the ability to track repeat-purchase behaviour.

What we need to keep

The value in your data is not in knowing that Sarah in Leeds bought a lamp. It is in knowing that Customer 47 has ordered four times, spends more on each visit, and always uses a discount code. The goal is simple: replace every identity with a unique code, ensuring that the same customer receives the exact same code across every order row.

A simple find-and-replace will not do this reliably. The steps below will.

Step 1: Export your orders

1. Log into Shopify, navigate to Orders, and click Export.
2. Select CSV for Excel, Sheets, or other spreadsheet applications and export all orders.
3. Open the downloaded CSV file in Excel or Google Sheets.
4. Locate the column containing customer emails. In a standard Shopify export, this is typically Column B (titled Email).

Step 2: Build the customer map

1. Create a new tab at the bottom of your spreadsheet and name it "map".
2. In your new "map" tab, click cell A1 and type Real Email. Click cell B1 and type Customer ID.
3. In cell A2, extract every unique email from your main order sheet (assuming your main tab is named "Orders"):

=UNIQUE(Orders!B2:B)

Next, generate a matching set of unique codes. In cell B2 of your "map" tab, enter this formula and drag it down alongside your email list:

="CUST-" & TEXT(ROW()-1, "0000")

This creates clean, sequential identifiers like CUST-0001, CUST-0002, and so on.

Step 3: Map the anonymised IDs back to your main sheet

Return to your main sheet ("Orders"). Insert a new blank column right next to the original Email column and title it Anonymised ID.

In cell C2, enter a lookup formula to pull the matching code from your "map" tab for each order:

=XLOOKUP(B2, map!A:A, map!B:B, "Guest")

Double-click the fill handle (the small green square in the bottom-right corner of cell C2) to apply this formula down the entire column. Every order placed by the same customer will now show their matching CUST code.

Step 4: Lock the formulas and strip out the PII

Before deleting any sensitive columns, you must convert your formulas into static text. Otherwise, removing the email column will break your lookup formulas.

1. Highlight your new Anonymised ID column.
2. Copy the selection (Ctrl + C on Windows / Cmd + C on Mac).
3. Without changing your selection, right-click and choose Paste as Values (or Paste Special > Values Only).

Now that your codes are locked in as plain text, safely delete the original columns containing real names, emails, phone numbers, and street addresses.

Step 5: Export your clean audit file

1. Save a master copy of your spreadsheet on your secure local drive. This keeps your "map" tab intact if you ever need to re-identify top customers later.
2. Delete the "map" tab from the file you plan to share.
3. Save the clean file under a new name, for example Shopify_Orders_Anonymised.csv.

In under ten minutes, you have turned a sheet of sensitive personal data into an anonymised dataset ready for Claude, ChatGPT, or an external auditor. You get total analytical clarity on customer lifetime value and repeat purchase patterns, while your customers' privacy stays completely protected.