How to Mask Data in Excel

Masking sensitive data in Excel is one of those tasks that sounds complicated but is actually quite simple once you know the right formulas. Whether you need to obscure personally identifiable information (PII) for privacy reasons or create anonymous datasets for testing, Excel has the built-in functions to get it done without needing any special add-ins. This article will walk you through several practical methods to mask data, from simple formulas to a quick dip into VBA for more automated solutions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Data Masking and Why Is It So Important?

Data masking, also known as data scrambling or anonymization, is the process of hiding original data with modified content. The goal is to protect sensitive information while maintaining a dataset that's still usable for testing, analysis, or training. Instead of a real credit card number, you see **** **** **** 1234, and instead of john.doe@email.com, you might see j***.***@*****.com.

This is more than just a "nice-to-have" practice, it's often a requirement. Here’s why it matters:

• Data Privacy and Compliance: Regulations like GDPR and CCPA mandate strict protection of personal data. Masking is a key technique to help comply with these rules, especially when sharing data with third parties or even internal teams who don't need access to the raw PII.
• Security: If a spreadsheet containing masked data is ever accidentally exposed in a data breach, the sensitive information remains protected. This adds a critical layer of security to your data handling practices.
• Software Development and Testing: Developers and QA testers need realistic-looking data to test new software applications. Using masked production data is far better and safer than using fake, completely random data, as it preserves the structure and character of real-world information without exposing actual customers.
• Training and Analytics: You might need to use a dataset for a company-wide training session or to build an analytical model. Masking allows you to share this information widely without revealing confidential details about customers, employees, or financials.

Getting Started: A Quick Pre-Flight Check

Before you start replacing your sensitive data, take a moment to follow these best practices. They’ll save you from potential headaches down the road.

1. Always Work on a Copy: This is the golden rule. Before applying any masking techniques, save a copy of your workbook. Data masking is a destructive process - once you overwrite the original data, it's gone for good. Always keep the original, secure copy separate.
2. Identify Your Sensitive Columns: Go through your spreadsheet and clearly identify which columns contain sensitive data that needs to be masked. Common examples include:
3. Choose Your Masking Strategy: Think about what the end result should look like. Do you just need to hide a few characters? Do you need to replace a real name with a fake one? Your goal will determine which technique below is the right fit.

How to Mask Data Using Excel Formulas

For most day-to-day masking needs, standard Excel formulas are more than capable. The process usually involves creating a new column next to your sensitive data, applying the formula there, and then using copy/paste to replace the original data.

Let's say your sensitive data is in column A, starting from cell A2.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Technique 1: Masking All But the Last 4 Characters

This is the most common scenario, perfect for credit card numbers, account IDs, or phone numbers where you just need to see the ending digits.

Goal: Turn 4111-2222-3333-4444 into ************4444.

The Formula:

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

How it works:

• RIGHT(A2, 4): This function grabs the last 4 characters from the text in cell A2.
• LEN(A2): This calculates the total number of characters in cell A2.
• REPT("*", LEN(A2)-4): The REPT function repeats a text string a given number of times. Here, we're telling it to repeat the asterisk * for the total length of the cell minus the last 4 characters we're keeping.
• &: This symbol combines the repeated asterisks with the last 4 characters, giving you the final masked result.

Technique 2: Masking All But the First Few Characters

This is useful for things like transaction IDs or user identifiers where the beginning of the string is the most important part.

Goal: Turn ACCT-987654321 into ACCT-*******.

The Formula:

=LEFT(A2, 5) & REPT("*", LEN(A2)-5)

How it works:

• LEFT(A2, 5): This function grabs the first 5 characters from the text in cell A2.
• REPT("*", LEN(A2)-5): This repeats the asterisk * for the total length of the cell minus the first 5 characters we want to show.

Technique 3: Masking Part of an Email Address

Email addresses are a bit trickier because their lengths vary. You need a more dynamic formula to handle them correctly.

Goal: Turn robert.smith@somecompany.net into r********.*****@somecompany.net.

The Formula:

=LEFT(A2,1) & REPT("*", FIND("@",A2)-2) & RIGHT(A2, LEN(A2)-FIND("@",A2)+1)

How it works:

This one combines a few more functions:

• LEFT(A2,1): Grabs the very first letter of the email.
• FIND("@",A2): Finds the position of the @ symbol in the email address. This is our anchor point.
• REPT("*", FIND("@",A2)-2): Repeats the asterisk. The number of asterisks is the position of the @ symbol minus two (one for the first letter we're showing and one for the @ symbol itself). This effectively masks everything between the first letter and the @.
• RIGHT(A2, LEN(A2)-FIND("@",A2)+1): This part grabs the domain name. It calculates the total length of the string and subtracts the position of the @ symbol, effectively giving us the length of the domain part which it then extracts.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Technique 4: Completely Randomizing Numerical Data

Sometimes you don't want to show any part of the original data. Instead, you need to replace it with fake but realistic-looking numbers. This is great for anonymizing salaries or sales figures.

Goal: Replace a column of salaries (e.g., $62,500, $81,000, $49,800) with random salaries between $45,000 and $95,000.

The Formula:

=RANDBETWEEN(45000, 95000)

How it works:

It's incredibly simple! The RANDBETWEEN(bottom, top) function generates a random integer between the two numbers you specify. Just drag the formula down the column to generate a randomized salary for each row.

Note: The numbers generated by RANDBETWEEN will recalculate every time you make a change to the worksheet. To "lock" them in place, copy the column with the formulas, right-click on the original column, and choose Paste Special > Values.

Using VBA for Automated Data Masking

If you find yourself masking data frequently or need to apply a complex set of rules, creating a simple macro with VBA can save you a ton of time. This is less scary than it sounds!

Here’s a basic script that will mask all but the last four characters for any cells you have selected.

Step 1: Open the VBA Editor Press Alt + F11 (or Fn + Option + F11 on a Mac) to open the VBA editor.

Step 2: Insert a New Module In the VBA editor menu, go to Insert > Module. A new blank code window will appear.

Step 3: Paste the Code Copy and paste the following code into the module window:

Sub MaskSelectedCells()
    Dim cell As Range
    'Ensure you have made a selection before running
    If Selection.Cells.Count = 0 Then
        MsgBox "Please select the cells you want to mask first.", vbInformation
        Exit Sub
    End If
    'Loop through each cell in the current user selection
    For Each cell In Selection
        If Not IsEmpty(cell) And Len(cell.Value) > 4 Then
            'Replace cell content with asterisks and the last 4 characters
            cell.Value = String(Len(cell.Value) - 4, "*") & RIGHT(cell.Value, 4)
        End If
    Next cell
End Sub

Step 4: Run the Macro Close the VBA editor to return to your worksheet. Select the cells you want to mask (e.g., your column of credit card numbers). Press Alt + F8 to open the Macro dialog box, select MaskSelectedCells from the list, and click Run.

That's it! Your selected data is now masked. Remember to save your file as a macro-enabled workbook (.xlsm) if you want to keep the macro for future use.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Final Thoughts

Mastering these data masking techniques in Excel adds a valuable skill to your data management toolkit. Using simple formulas handles most common tasks with ease, while a basic VBA script can automate the process for larger or more frequent jobs, all of which are essential for keeping sensitive information secure.

The manual work of preparing, cleaning, and masking data in spreadsheets is often the first step in a long reporting process. After you've spent an hour wrangling a CSV in Excel, you still have to build the charts and reports to find your insights. At Graphed, we automate that entire workflow. We allow you to securely connect directly to your data sources like Google Analytics, Shopify, or Salesforce, and use simple, natural language to build live dashboards. It eliminates the need for manual data exports and masking, ensuring that your team can get real-time answers without passing unsecured spreadsheets around.