Generating Mock Excel Data with Mockaroo

Image by Mackaroo
 

To test applications, simulation, and learn data analysis mock data is essential. Mockaroo is a powerful online tool that allows users to generate realistic, structured mock data in various formats, including Excel (XLSX).

In this tutorial, we will show how to generate mock Excel data with Mockaroo.

What is Mockaroo?

Mockaroo is a free online tool that generates custom datasets with information such as names, phone numbers, addresses, etc. It’s particularly useful for:

• Testing applications.
• Creating demo data.
• Populating development databases.
• Practicing data analysis without real user data.

Step 1: Accessing Mockaroo

• Go to Mockaroo.
• You will see a default dataset with columns like id, first_name, last_name, email, etc.
• You can either use the default fields or customize the dataset according to your needs.
• Each row represents a field in your dataset.
• For each field:
  • Enter a name in the Field Name column.
  • Select a Data Type from the dropdown menu.
  • Configure any specific Options for that data type.

Step 2: Choose Field Types

Mockaroo offers dozens of data types. You can select based on your dataset type.

• Add New Columns: Click the + Add Another Field button to create a new column.

• Rename Columns: Click on the field name and edit/rename it.
• Select Data Types: Choose from Mockaroo’s wide range of predefined data types, including:
  • Personal Data: First Name, Last Name, Email, Address, Phone Number, etc.
  • Business Data: Company Name, Job Title, Industry, etc.
  • Numeric Data: Random Integers, Decimals, and Currency values.
  • Date & Time: Timestamps, Birthdates, Future/Past Dates.
  • Custom Formulas: Use SQL-like expressions for advanced data generation.

Step 3: Set Field Options

Mockaroo offers customizable options:

• Format Options: If you choose Date, you can select a specific date format (YYYY-MM-DD, MM/DD/YYYY, etc.).

Date Format:

• Range Constraints: For numbers, you can set min/max values.
• Custom Lists: You can define your values using the Custom List data type.

Step 4: Add Formulas and Dependencies

For more advanced datasets:

• You can use the Formula data type to create calculated fields.

• Set up correlations between fields (e.g., city depends on state).
• You can create custom distributions using weights.

Step 5: Configure Output Settings

• Set the number of rows to generate (up to 1,000 with a free account)
• Choose Excel as the format.
• Configure additional options:
  • Include header row.
  • Set file name.

Step 6: Generate and Download

• You can directly download data by selecting GENERATE DATA.
• Or, preview the data by selecting the PREVIEW option then download the data.

• Open the file to verify your mock data.

Save and Reuse Schemas

If you create an account, you can save your schemas.

• Click on the Save Schema button.
• Give your schema a name.
• Access saved schemas from your dashboard for future use.

Creating Customer Database

Let’s create a realistic customer database.

• Field Name: id, Type: Row Number
• Field Name: first_name, Type: First Name
• Field Name: last_name, Type: Last Name
• Field Name: email, Type: Formula, Value: lower(first_name) + “.” + lower(last_name) + “@example.com”
• Field Name: phone, Type: Phone
• Field Name: address, Type: Street Address
• Field Name: city, Type: City
• Field Name: state, Type: State (US)
• Field Name: zip, Type: Postal Code
• Field Name: signup_date, Type: Date (past 2 years)
• Field Name: last_purchase, Type: Date (past 6 months)
• Field Name: lifetime_value, Type: Number (decimal, 0-5000)

• Now, select PREVIEW to check the dataset
• Click on #Rows: then select GENERATE DATA.

• Save the Mock_Data.xlsx.

• Open the Mock data to verify it.

Mockaroo is a simple yet powerful tool for generating mock Excel data tailored to your needs. With Mockaroo, you can quickly generate thousands of rows of realistic data, saving hours of manual entry and ensuring your systems are tested with diverse, representative information. Just by following the simple guide you can create a realistic dataset within a few clicks. It saves time and effort in testing and development workflows.