How to Create a Random SSN Generator in Excel: 5 Proven Methods

Are you tired of manually creating Social Security Numbers for your database or testing? Say goodbye to such boring and tiresome tasks! Bring efficiency with our guide on how to create a Random SSN Generator in Excel. 

With these random SSN generator ideas, you can easily create thousands of unique and valid SSNs with just a few clicks. Whether you’re testing a new software system or simply need a quick way to generate random SSNs for research purposes, these Excel-based solutions will help. Plus, by using Excel’s built-in functions and formulas, you’ll have complete control over the SSNs generated, including the Area Number, Group Number, and Serial Number. Let’s get started!

What Is an SSN?

SSN is the abbreviation for Social Security Number. The Social Security Administration (SSA) of the United States issues this unique nine-digit identification number to its citizens, permanent residents, and temporary residents. 

The purpose of the SSN is to track individuals for Social Security and taxation purposes. In the US, SSN is the most popular ID document you need to present to the authority to avail services like, applying for credit, opening bank accounts, and obtaining employment.

What Are the Rules Behind a Valid SSN?

Find below the underlying requirements that make an SSN valid in the US:

• An SSN must be nine digits long.
• The SSA formats the ID number in a group of three digits, as shown here: XXX-XX-XXXX.
• The first three digits (ranging from 001 to 899, excluding 900-999) of an SSN comprise the Area Number indicating where SSA issued it.
• The two digits in the middle are the Group Numbers (from 01 and 99). It breaks any ties when multiple residents get the same area number.  
• The last four digits in an SSN are the unique serial number of a resident in the SSN system. 
• The first digit of an SSN cannot be a 0 or a 9.
• It also can’t contain all zeros in any of the three parts like the Area Number, Group Number, and Serial Number.
• An SSN cannot contain any special characters like spaces, parentheses, etc., except the separator hyphens.

Why Do You Need a Random SSN Generator?

Find below the reasons why you need random and fictitious SSN generators in Excel:

• Financial application developers may use such a generator to produce thousands of imaginary SSNs for testing purposes.
• Data scientists and analysts can use fictitious SSNs for research or data insight generation purposes.
• If you’re working with real people and their SSNs, you might want to mask the real SSNs with fake ones before sharing data with a third party.
• You can also use imaginary SSNs to train AI models and apps.

Now that you’re aware of the basics of the SSNs, let’s explore various methods to create a random SSN generator in Excel:  

Generate SSN With RANDARRAY

1. Select a cell on your Excel worksheet where you want to populate some fictitious SSN.
2. Now, copy and paste the following formula in that cell and hit Enter:

=RANDARRAY(10,1,111111111,1000000000,TRUE)

3. The above Excel function will generate random SSNs in 1 column of 10 rows. If you need more rows and columns, edit the first two digits of the formula. For example, 100 for 100 rows and 10 for 10 columns.

4. Now, select all the columns and rows and hit Ctrl + 1 keys together.
5. This will pop up the Format Cells dialog box.
6. There, click on Custom and enter the following code inside the Type field:

###-##-####

7. Click OK to close the dialog box.
8. You’ll see that Excel automatically formats the imaginary SSNs with hyphens. 

Generate SSN With RANDBETWEEN

In the previous method, you’ll see some SSNs that aren’t valid according to the SSN rules. You need to find those manually and delete them. Want to avoid this manual work? Try the following RANDBETWEEN formula as the random social security number generator:

1. Go to a cell and enter the following formula:

=(RANDBETWEEN(100,899)&"-"&TEXT(RANDBETWEEN(1,99),"00")&"-"&TEXT(RANDBETWEEN(1,9999),"0000"))

2. Hit Enter to get the first SSN.
3. Select the same cell and drag the fill handle down to the cell where you want to end the list of SSNs.
4. To create new columns of imaginary SSNs, simply copy the existing column to the left or right.

5. Now, go to the Formulas tab in the Excel ribbon.
6. There, click the Calculate Now command inside the Calculation block.
7. Excel will refresh the worksheet with new imaginary SSNs so you can get thousands of SSNs without crowding the worksheet.

Generate SSN With Power Query

Generating SSNs using Power Query in Excel involves a few more steps than using a simple formula. However, it can be a useful technique for generating large datasets of unique and realistic SSNs. Here’s how to generate SSNs in Excel using Power Query:

1. Select any cell on your worksheet and click the Data tab.
2. There, select Get Data and hover the cursor over From Other Sources.
3. Hit the Blank Query option in the context menu.
4. The Power Query Editor dialog box opens.

5. There, click the View tab and then choose Advanced Editor.
6. Into the Advanced Editor dialog box, copy and paste the following code:

let
    GenerateSSN = () => 
        let
            Part1 = Text.PadStart(Number.ToText(Number.RoundDown(Number.RandomBetween(1, 999))), 3, "0"),
            Part2 = Text.PadStart(Number.ToText(Number.RoundDown(Number.RandomBetween(1, 99))), 2, "0"),
            Part3 = Text.PadStart(Number.ToText(Number.RoundDown(Number.RandomBetween(1, 9999))), 4, "0")
        in
            Part1 & "-" & Part2 & "-" & Part3,
    Source = List.Generate(
        () => 0,
        each _ < 10,
        each _ + 1,
        each GenerateSSN()
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"SSN"})
in
    #"Converted to Table"

7. Click Done to generate the SSNs.

8. Now, simply click the Close & Load button from the Power Query Home tab and again select Close & Load in the context menu to add the SSNs to your Excel worksheet.

If you need to generate more than 10 SSNs, simply edit the digit in the code where it says each _ < 10, to a desired number of SSNs like 100, 1,000, etc.

Generate SSN With a VBA Script

1. Open an Excel sheet and hit the Alt + F11 keys to open the VBA Editor.
2. On the VBA Editor, click Insert and select Module.
3. A new Module will show up. There, copy and paste the following VBA script to generate random SSNs from cell A1 to cell A10:

Sub GenerateRandomSSNs()
    ' Generate 10 random SSNs and insert them into cells A1 to A10
    Dim i As Integer
    For i = 1 To 10
        Range("A" & i).Value = RandomSSN()
    Next i
End Sub

Function RandomSSN() As String
    ' Generate a random SSN
    Dim part1 As String
    Dim part2 As String
    Dim part3 As String
    
    ' Generate the first part of the SSN (3 digits)
    part1 = Format(Int(Rnd * 999) + 1, "000")
    
    ' Generate the second part of the SSN (2 digits)
    part2 = Format(Int(Rnd * 99) + 1, "00")
    
    ' Generate the third part of the SSN (4 digits)
    part3 = Format(Int(Rnd * 9999) + 1, "0000")
    
    ' Combine the three parts with hyphens
    RandomSSN = part1 & "-" & part2 & "-" & part3
End Function

4. Now, click the Save button on the VBA Editor toolbar and save the Excel file as an Excel Macro-Enabled Workbook. 

5. Hit Alt + F8 to bring up the Macro dialog box.
6. There, select the GenerateRandomSSNs macro and hit the Run button.

If you need mode SSNs, simply change the digit 10 to any number in the For i = 1 To 10 part of the code.

Generate SSN With Office Scripts

1. Open your Excel worksheet on the Excel desktop or web app.

2. Click the Automate tab in the Excel ribbon menu and then select New Script.

3. In the Code Editor field on the right side of the worksheet, copy and paste the following script:

function main(workbook: ExcelScript.Workbook) {
  // Generate 10 random SSNs and insert them into cells A1 through A10
  for (let i = 1; i <= 10; i++) {
    let ssn = generateSSN();
    workbook.getWorksheet('Sheet1').getRange(`A${i}`).setValue(ssn);
  }
}

function generateSSN() {
  // Generate a random SSN
  let part1 = Math.floor(Math.random() * 900) + 100;
  let part2 = Math.floor(Math.random() * 90) + 10;
  let part3 = Math.floor(Math.random() * 9000) + 1000;
  return `${part1}-${part2}-${part3}`;
}

4. Click the Save script button on the Code Editor.
5. Now, hit the Run button to generate imaginary SSNs between cell range A1 and A10.

If you need more SSNs, edit the cell range inside the code element for (let i = 1; i <= 10; i++) to a desired range like for (let i = 1; i <= 100; i++).

Conclusions

So, now you know how to create a random SSN generator on Excel using built-in features like formulas, VBA scripting, Excel Automate, and Power Query advanced query editing. If you simply need a few SSNs to create dummy reports or databases on Excel, you can use the RANDARRAY or RANDBETWEEN formula. This is less complicated than the other methods.

Contrarily, if you’re a data scientist or app developer and need a huge collection of imaginary SSNs to test data analysis models or app performance, you can use Power Query, VBA Editor, or Excel Automate (Office Scripts).

Choose a method of your choice and give it a spin on your end. Don’t forget to leave a comment if you find the above methods useful.