Sample Data Generator: Generate Realistic Fake Data in Excel
Need to generate sample data in Excel for testing, demos, or spreadsheet templates? You can create dummy data manually using Excel's native formulas, or use the XLclick add-in to generate realistic names, emails, phone numbers, and more in just a few clicks.
Generate Fake Data in Excel: Native Formulas vs. XLclick
Creating realistic test datasets can be time-consuming, especially when you need hundreds of records. Excel provides useful randomization functions, but generating meaningful sample information often requires additional formulas or reference lists. XLclick simplifies this process with predefined data types and customizable output settings.
| Feature | Native Excel | XLclick |
|---|---|---|
| Generate sample data | Formulas and reference lists | Dedicated Sample Data Generator |
| Realistic names and emails | Requires custom formulas or sample lists | Built-in data type options |
| Number of records | Fill formulas or configure arrays | Enter the number of rows |
| Sorting options | Additional sorting steps | Alphabetical or Random |
| Output location | Choose a formula destination | Select a starting cell |
| Data safety | Existing cells can be overwritten | Controlled destination selection; check for existing data |
Free Ways to Create Dummy Data in Excel
You can create test data in Excel for free using built-in functions. These methods work well for basic examples, although realistic datasets usually require additional preparation.
Method 1: Generate Random Numbers with RANDBETWEEN
The RANDBETWEEN function generates random integers within a specified range.
For example, enter =RANDBETWEEN(1000,9999) to create a random four-digit number. Copy the formula down to generate additional records.
Limitations: RANDBETWEEN generates numbers, not realistic names or email addresses. Results may also change when Excel recalculates. Use Paste Special > Values to freeze them.
Method 2: Create Random Names with INDEX and RANDBETWEEN
If you already have a list of sample names, Excel can randomly select one using INDEX.
For example, if your sample names are in A2:A20, enter:
=INDEX($A$2:$A$20,RANDBETWEEN(1,ROWS($A$2:$A$20)))
Fill the formula downward to produce a column of random names.
Limitations: You must build or import a reference list first. Repeated names are possible, and formula results may change during recalculation.
Method 3: Build Fake Email Addresses with Formulas
You can create fictional email addresses by combining text functions. If a sample first name is in A2 and a last name is in B2, use:
=LOWER(A2&"."&B2&"@example.com")
This generates email-style values suitable for demonstrations. The example.com domain is reserved for documentation and examples.
Limitations: You still need source names, and duplicate names can produce duplicate addresses. More realistic formats require additional logic.
Method 4: Generate Multiple Random Values with RANDARRAY
In supported Excel versions, RANDARRAY generates multiple random numbers with a single formula.
For example, =RANDARRAY(100,1,1,1000,TRUE) creates 100 random integers.
Limitations: RANDARRAY is unavailable in some older Excel versions and does not generate realistic personal information directly. Its dynamic output also requires enough empty cells.
Create Realistic Excel Test Data Faster with XLclick
Need realistic test data in Excel fast — names, emails, phone numbers — without using real customer information? Sample Data Generator creates it in seconds, as many rows as you need.
Essential Settings for Sample Data Generator

To open Sample Data Generator, go to the XLclick tab, find the Insert Data group, then click Sample Data Generator.
The panel walks you through four steps:
1. DESTINATION CELL — Click Select and choose the cell where the generated data should start. Data will be output downward from that cell.
2. DATA TYPE — Use the dropdown to choose the type of fake data to generate. Options include First Name, last names, email addresses, phone numbers, and other realistic data types.
3. ROWS TO GENERATE — Enter the number of rows of data you want to create in the input field.
4. SORT BY — Choose how the generated data is ordered:
- Alphabetical — sorts the generated values A to Z.
- Random — outputs values in a randomized order, more realistic for test datasets.
Click Generate Now to insert the data, or Cancel to exit.
Real-World Scenarios: Top Use Cases for Excel Sample Data Generator
A Developer Testing a New Excel Import Without Using Real Customer Data
A developer was building an import pipeline for a new CRM and needed a realistic Excel file to run test imports against. Using real customer data was not allowed under the company's data policy, and creating 200 fake names, emails, and phone numbers by hand would have taken hours.
Sample Data Generator filled each column with the right data type — names, emails, phone numbers — in seconds. With Random sort enabled, the test file looked completely authentic.
A Freelance Trainer Creating Demo Files for an Excel Training Course
A freelance Excel trainer needed demo spreadsheets for her training courses — files with realistic names and contact data that students could work with during exercises. Using real data was off-limits, and building believable fake datasets manually every time she created a new lesson was tedious.
Sample Data Generator populated her training files with first names, last names, and email addresses in the right quantity for each exercise. The demo files were ready in minutes.
A Small Business Owner Testing a New Email Template With Fake Subscriber Data
A small business owner was setting up an email marketing platform for the first time and needed to test the import and template personalization with subscriber data. She didn't want to use real contacts for a test run, but entering realistic-looking fake data by hand was impractical.
Sample Data Generator created a test subscriber list with 50 first names, last names, and email addresses. The import and personalization test ran perfectly without touching any real contact information.
A Marketing Agency Presenting a Dashboard Mockup to a New Client
A marketing agency was pitching a custom Excel dashboard to a prospective client. They needed to demonstrate the tool with realistic-looking data, but using data from existing clients would have been inappropriate. Creating believable placeholder data for a 100-row demo by hand was not a good use of time.
Sample Data Generator filled the dashboard with realistic names and contact data instantly. The pitch demo looked authentic, and the client approved the project the same day.
An Operations Team Running a Stress Test on a New Data Processing Script
An operations team wrote a script to process Excel files and needed to stress test it with large volumes of data — 5,000 rows of realistic input. Using production data for testing was out of the question, and generating thousands of realistic fake entries manually was not feasible.
Sample Data Generator built a 5,000-row test file with the right data types in under a minute. The stress test ran against realistic data, exposing performance issues before the script went live.
Excel has always been my laboratory. After years of navigating data-heavy workflows, I created XLclick: the definitive add-in that simplifies complex analysis into a single click. It’s built for pros who want to spend less time on spreadsheets and more time on strategy.