Two ways to make fake data lists for Excel

Here are two ways to quickly make up list or tables of fake data to use in Excel worksheets. Use them to test a workbook or code.

GenerateData.com

There are a few websites that can make fake lists, we prefer https://GenerateData.com for its simple interface and many options.

Just some of the options available:

Names – first, last, male, female, Western or various Regional.
Email – random strings, name fields, selected domains etc.
Phone / Fax – for different countries and selected formats.
Street Address, City, Postcode/Zip, Region, Country, Latitude/Longitude.
Credit Card – fake numbers and CVV codes for various types of card.
Date / Time – pick a range and format.
List – pick randomly from a selection e.g product names or marital status.

In short, look at the list of possibilities and don’t forget to click on the Options (settings, customize) buttons to see the impressive choices available for each data type.

Free (not registered) accounts at GenerateData.com are limited to making 500 rows of data.

Exporting to Excel

Excel can import various formats downloaded by GenerateData.com – HTML, CSV, JSON or XML. All but HTML need Get & Transform/PowerQuery.

For a “quick and dirty” copy/paste try this.

1. Download from GenerateData.com in HTML format
2. Open the HTML file in a browser
3. Copy the whole table. Select All (Ctrl + A) then Copy (Ctrl + C)
4. Paste into an Excel worksheet

That works for simple data types but others need Get & Transform to ensure data is imported in the correct format. For example, credit card numbers are assumed to be numbers rather than text strings.

The better, albeit slower way is to save your fake data to any of the Excel compatible formats (like JSON or XML) then use Data | Get & Transform to import the data file and control the data types.

Faker Python library

Another, more powerful option is the Faker Python library. If your Excel 365 supports Python, it’s a powerful way to drop a table of fake data into a worksheet.

There are many ways to code using Faker. We prefer the coding method below because it’s simple and includes column headers. The column headers mean a simple conversion into an Excel Table.

Change Fake data rows

Modify/add any of the code lines in this block …

fakedata.loc[i,'email address']= fake.email()

The text in single quotes becomes the Excel column header.

Change the part to the right of the equals sign for the fake data you want.

Faker has many, many options, check out the Faker documentation or do a web search for “Faker …” plus the type of data you’re trying to make.

Choose random item from list

A common need in fake data is to choose one item from a list of products, categories, titles, countries etc.

Do that by making a string array then using random.choice() to select one

random.choice(['Lawyers','Guns','Money'])

The array can be ‘in-line’ or setup earlier in the code.

We’ve used the same trick to get a binary choice like Yes/No, On/Off etc. instead of the Boolean True/False. See the example code below.

Faker Locale

One option we’d like to highlight is Locale which changes the Faker output to suit a chosen language and region. Not just language but name, addresses and other details.

Faker(“en_US”) – USA
Faker(“en_CA”) – English in Canada
Faker(“fr_CA”) – French in Canada
Faker(“en_UK”) – United Kingdom
Faker(“en_AU”) – Australia
Faker(“de_DE”) – Deutsch for Germany
Faker(“pt_PT”) – Portuguese

from random import randint 
import pandas as pd 

# array to select from in 'Pets' 
PetList = ['cat', 'dog', 'mouse','aardvark']

# OPTIONAL - add locale preference
fakedata = Faker()
 
def fake_input_data(x):
    fakedata = pd.DataFrame()
    for i in range(0, x):
        fakedata.loc[i,'name']= fake.name()
        fakedata.loc[i,'address']= fake.address()
        fakedata.loc[i,'email']= fake.email()
       
        # Two different ways to get binary True/False
        fakedata.loc[i,'True / False']= fake.pybool()  
        fakedata.loc[i,'Yes / No '] = random.choice(['Yes','No'])

        # random.choice to choose one item from a list / array
        fakedata.loc[i,'What to bring ..'] = random.choice(['Lawyers','Guns','Money'])

        # with list / array as a list variable, set above.
        fakedata.loc[i,'Pet'] = random.choice(PetList)

        # random integer with min / max range and step
        fakedata.loc[i,'Integer']= fake.pyint(min_value=0, max_value=99, step=1)

        # dollars only 1 to 1000
        fakedata.loc[i,'Price']= random.randint(1,1000)

        # dollars and cents 1 to 1000 - i.e to two decimal places
        fakedata.loc[i,'Price with cents']= random.randint(1,100000)/100

        fakedata.loc[i,'licence plate']= fake.license_plate()
        fakedata.loc[i,'latitude']= str(fake.latitude())
        fakedata.loc[i,'longitude']= str(fake.longitude())
    return fakedata

# change value to number of data rows wanted. 
fake_input_data(10)

Excel gets Python – who, when and why

Python Editor for Excel is out and essential

Get current UTC time into Excel with Python