Random sample data generator add-in for Excel now available

As anyone in the security or financial business can tell you, data security is extremely important. Data in the wrong hands can cost a business millions of dollars, its reputation, and even it's very existence.

In The Dangers of Testing with Real Data, Mathew Schwartz writes:

Do you test applications using real, production data?

Anecdotally, many developers and QA testers say they prefer to build and test applications using the real thing: actual customer data.

Such practices, however, can violate a number of data privacy regulations. For example, the 1996 Health Insurance Portability and Accountability Act (HIPAA) mandates companies restrict access to people’s personal health data on a “need to know” basis. Likewise, the Sarbanes-Oxley Act of 2002 requires companies to control access and track changes to systems handling corporate financial information. In addition, over 30 states have passed data breach notification laws requiring companies to notify consumers if their personal information may have been compromised. This includes such things as a person’s name and address, date of birth, social security number, and credit card and bank account numbers.

So, the problem is, how can you get your hands on quality sample data that won't violate privacy regulations?

There is a limited amount of sample data available here:

Excel — Sample Data
Excel Sample Data

I'd like to announce another Excel add-in I've just completed — bringing the total of add-ins available on this site to eight — one that lets you generate your own random sample data for testing purposes!

Suppose you need some fake SSNs, names, birthdates, etc, in order to test out a macro, UDF, PivotTable or PivotChart. This add-in is for you.

I was inspired by Fake Name Generator's "order in bulk" feature (but most certainly not by their huge wait time for a bulk order) to create an Excel add-in to duplicate their functionality. Now you can generate as much fake random sample data as you need, from the privacy of your own computer, in a fraction of the time it takes to either crawl the web piecing it together yourself or placing an order with a website and waiting for your turn in the queue.

Screenshot:

Random Data Generator

You can select from the following fields:

  • Address – house number and street name

  • AutoNumber – same as ROW() function, or the Access field of the same name

  • Birthday – a valid date between today and 90 years prior

  • City/State/Zip – valid combination of city name, state abbreviation and zip code

  • Company Name – fictitious company name

  • Country - country abbreviation from a list of recognized countries

  • Credit Card Number – fake CC number using AmEx, Discover, Visa or Mastercard format

  • Date – random date between today and 90 years prior

  • Day – random day number (1 to 31)

  • Day Of Week – day of week (Monday through Sunday)

  • Email Address – a random 5-10 character username followed by one of 20 randomly selected domains

  • First Name – chosen from 4500+ first names

  • Fiscal Quarter – Q1, Q2, Q3, Q4

  • Gender – Male or Female, randomly assigned to each record

  • Last Name – chosen from 4500+ last names

  • Latitude – a randomly selected point in the United States

  • Longitude – a randomly selected point in the United States

  • Middle Initial – capital A-Z

  • Month – January through December

  • Mother's Maiden Name – chosen from 4500+ maiden names

  • Name Prefix – Mr.,Ms.,Mrs. and so on

  • Name Suffix – Jr.,Sr.,Ph.D. and so on

  • Number – random number between 1 and 100

  • Password – 8-12 character password with uppercase, lowercase and special characters

  • SAT Score – 600-2400 score in multiples of ten

  • SSN – a United States SSN formatted as NNN-NN-NNNN

  • Salary – a random number between 10,000 and 500,000

  • Season – Winter, Spring, Summer, Fall

  • Telephone (US) – U.S.-based telephone number formatted as (NNN) NNN-NNNN

  • Telephone (UK) – London formatted phone number: (020) NNNN NNNN

  • Time – AM or PM, 12-hour clock

  • Year – A random year between the current year and 90 years prior

You may also output to the current workbook (a new sheet will be created for the records). Up to 65,535 records may be created in any of these formats (1M+ records in Excel 2007).

Version 3 of the add-in also includes multiple output formats (txt, csv, mdb, and so on).

In Excel 2000-2003, Random Data Generator adds a menu item to the Data Menu titled "Random Data Generator 3.0". In Excel 2007, a new tab called Random Data Generator is added.

Credit is given to the following sources:

And also to John Walkenbach's Excel 2000 Power Programming with VBA for the usual startup routines I use in all my add-ins.

Official PayPal Seal
credit card images

Questions, requests or suggestions? Let me know in the comments.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 34 Comment(s) on Random sample data generator add-in for Excel now available:

  1. Very neat! Do you mind if I link to this page from the Free Tools page at the Fake Name Generator?

    I tried it in Excel 2007 and got the following error: "Run-time error '1004': Add method of AddIns class failed". I'd be happy to help you troubleshoot it if you'd like, let me know..

    • Absolutely — and thanks for testing it, I just realized I made a real bonehead move with the 2007 version, which I'll be fixing right away.

      Ok, I just realized without Excel 2007, I can't debug the error, so I'm just going to remove the 2007 version for now. If you want to look at it, let me know and I'll send you the unlocked version.

  2. Email the 2007 version to me if you like. I'll take a look at it.

  3. This seems like a great idea. Should know I could use it.

  4. To fix the moveup/movedown problem

    http://www.dailydoseofexcel.com/archives/2004/06/14/move-upmove-down-in-a-listbox/#comment-37847

    and get rid of all the selecting and sendkeys stuff. It looks like ListIndex is all we need to make it work right.

    I had no problem installing it in Excel 2007 (maybe because you sent me the xlam instead of the installer). Ten thousand records took ~2 seconds, fifty thousand took ~12 seconds.

    Suggestions for v2:
    I can't run it unless I have a workbook open, but then it doesn't use the open workbook. I would prefer it just populated the open workbook or created a new open workbook for me to save. I'm very protective of my desktop.

    Disable the up and down arrows when they're not applicable.

    Enable the Submit button based on the Change event of the textbox, rather than AfterUpdate.

  5. Dave Graper writes:

    Great utility … and thanks for leaving the unlocked version, it was extremely handy to pop in a couple extra simple things I needed (random name suffix and middle initial generation functions)!

  6. Just needed to create some data so I could make a pivot table and test something out. I can't believe I used to do that manually. :)

  7. pweidman writes:

    I am a senior Mathematics teacher and I am always trying to invent math scenarios using data tables that one would see in real life applications. I stumbled on this Add-In of yours and I am eternally grateful for your work. This saves me from having to write my own VBA scripts, which should be beyond the levels of a simple math teacher. :) Again, thanks I really love this add-in.

    • Glad to hear it, although I would disagree with you about VBA being beyond anyone's skill level.

      • Hi JP:
        Could you please send me updates version of Random Data Generator 2007.
        I am working on similar project and your excel add on would be great help.

        Version I have downloaded has some error, and while opening it gives me following message Runtime Error 1004.

        Your help will be appreciated.

        Kal

  8. Aitch writes:

    Thank you for this add-in.
    I'm not sure how to edit the unlocked version, so I didn't do anything with that.
    I have a question though – how can you change the information that the zip codes use? I'm in the UK, and our postal codes are completely different.
    And it just so happen that the software I'm busy testing now requires UK post codes.

    Thanks

    • Aitch,

      If you post a sample UK postal code, I can edit the add-in so it returns UK Postal codes instead.

  9. Dalven writes:

    I have tried the add on, but it's not working on Excel french version!

    God luck

    • Dalven,
      What specifically isn't working? Are you able to install the add-in? Did you download the correct version for your version of Excel?

  10. Love this, I generate test data for developers and this addin has inspired me to take a stab at doing some coding (old old Cobol developer here).

    Thanks much for the example.

  11. FYI, Neither of the Excel 2007 zip files will unzip using Winrar, 7zip or Winzip. Not sure what you used but something is messed up.

    Thanks for the 2003 code though. I was able to work with it. :-)

    • I'm confused as well. I can open my local copies, but as soon as I upload them to the site, they can't be opened. I can email them to anyone who asks.

  12. JP,

    You wouln't have a tool that would allow me create a gender code from first name would you.
    This would be used in Excel 2003.

    Rich

    • Sorry, no. One solution is to create a table with first name and gender and then use something like VLOOKUP or INDEX/MATCH to return the gender based on the name.

  13. Do you know how to make a program where you would be able to enter in 3-5 criteria. Then have excel choose items from a number of different categories and compile them into a list?

  14. Im in Australia, I need postal codes and city names from Australia (Validation rules apply). Everything else I can work with.

  15. Stephen writes:

    This rocks. You rock for making it. Sweet.

  16. Mark61 writes:

    J.P.,

    random name generator:

    I do not write excel or basic language at all. I just downloaded the addin and clicked on the xla document and allowed it to open and get this error.

    run time error "1004"
    add method of addins class failed

    then if you run debug this comment shows up highlighted
    " AddIns.Add Filename:=ThisWorkbook.FullName"

    So, then I opened excel 2003 and went to file open and navigated to the xla document and clicked on it and i get a message that the addin was successfully installed but it opens a series of error windows basically asking if you want to run debug and if you do agree you get a set of messages much akin to this

    AddIns(AddInTitle).Installed = True and on line nine and so many messages that I do not have the time to write. There is very little user interaction on my part other then trying to see if it will work better then the other websites on version.

    thanks

    Mark

  17. Will this product work with Excel 2010

Mentions:

  1. [...] You can find the add-in at Jimmy's site Here. [...]

  2. [...] Pena has a a great application for generating random data, but sometimes it is just better to scramble the data that you already have. I suggested to the [...]

  3. [...] order to get a large test set of customers, I used a nifty free MS Excel plugin to create person-oriented sample data and exported the data from Excel to XML format. A zipped file containing a hundred customers is [...]

  4. [...] Generating Sample Data for Your Excel AdventuresDick Kusleika has taken inspiration the much loved random data generator add-in from JP and created a random data generator of his own. Very useful if you are in to excel based [...]

This article is closed to any future comments.
Random Data Generator