How to Create Dummy Data in Excel

Ever found yourself staring at a blank spreadsheet, needing to test a formula or build a dashboard mockup but having no data to work with? Creating realistic-looking dummy data is a fundamental skill for anyone who works in Excel. This guide will walk you through several methods for generating sample data, from simple, built-in functions to more advanced techniques.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why You Need to Generate Dummy Data

Creating dummy or sample data isn't just busy work, it's a practical step for a variety of tasks. You might need it to:

• Test Formulas and Functions: Before applying a complex formula like VLOOKUP or a PivotTable to thousands of rows of real, sensitive data, you can build and validate its logic using a safe, fake dataset.
• Design Dashboards and Reports: It's nearly impossible to design a meaningful chart or dashboard without data. Dummy data helps you visualize how your final report will look and feel, allowing you to fine-tune your design.
• Practice Data Analysis: If you're learning new Excel skills, a good set of sample data gives you the material you need to practice sorting, filtering, and creating PivotTables without any risk.
• Create Tutorials or Demonstrations: When you need to show someone how something works in Excel, using generic dummy data is far better than sharing confidential company or customer information.

Method 1: Using Excel's Built-in Functions (The Easiest Way)

For most day-to-day needs, Excel’s native functions are more than powerful enough to create a robust set of sample data. Let’s break down the most useful ones.

Generating Random Numbers with RANDBETWEEN

This is your go-to function for creating random whole numbers within a specific range. It’s perfect for columns like Unit Sales, Age, or Inventory Count.

The syntax is simple: =RANDBETWEEN(bottom, top) where "bottom" is the lowest number and "top" is the highest number you want to generate.

Example: Create a list of sales quantities between 50 and 500.

1. Click on the cell where you want your first data point (e.g., B2).
2. Type the formula: `=RANDBETWEEN(50, 500)`
3. Press Enter. You'll see a random number between 50 and 500 appear.
4. To fill a column with these random numbers, click back on cell B2, grab the small square at the bottom-right corner (the fill handle), and drag it down as far as you need.

Pro Tip: Convert Formulas to Static Values.

Functions like RANDBETWEEN are "volatile," meaning they recalculate and generate a new random number every time you change anything on the worksheet. To lock in your dummy data, you need to convert the formulas to static values. Simply select all the cells with the formula, copy them (Ctrl+C), then right-click on the selection and choose Paste Special > Values. This replaces the formula with its result.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Generating Random Decimal Numbers with RAND

If you need random decimal numbers between 0 and 1, the RAND() function is perfect. It takes no arguments - you just type `=RAND()`. This is great for generating things like percentages or scores.

Example: Create a list of "Discount Rates" between 0% and 15%.

Since RAND() gives you a number between 0 and 1, you can simply multiply it by your maximum desired value.

1. In a cell, type: `=RAND() * 0.15`
2. Press Enter and drag the fill handle down.
3. To make it look like a percentage, select the column and format it as a percentage from the Home tab.

You can also adapt the RANDBETWEEN logic for decimals by multiplying: `=RANDBETWEEN(100, 1000)/100` would give you a random number between 1.00 and 10.00.

Generating Random Text From a List

Numbers are great, but what about categorical data like product names, regions, or status types? You can generate these by cleverly combining the CHOOSE and RANDBETWEEN functions.

The CHOOSE function returns a value from a list based on an index number. For example, =CHOOSE(2, "Apple", "Banana", "Cherry") would return "Banana." By using RANDBETWEEN as the index number, you can randomly pick from your list.

Example: Create a list of random U.S. regions.

1. Let's say you want to choose from "North," "South," "East," and "West." That's 4 options.
2. In a cell, type the following formula: `=CHOOSE(RANDBETWEEN(1, 4), "North", "South", "East", "West")`
3. Press Enter. Excel will randomly select one of the four regions. Drag the formula down to populate your column.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Generating Random Dates

Excel stores dates as sequential serial numbers, which means you can use RANDBETWEEN to generate random dates, too. You just need to provide the start and end dates using the DATE function.

Example: Generate random dates within the year 2023.

1. In a cell, type the formula: `=RANDBETWEEN(DATE(2023, 1, 1), DATE(2023, 12, 31))`
2. Press Enter. You'll likely see a number like "45015." Don't panic! That’s just Excel's serial number for the date.
3. Select the cell (or the entire column), go to the Home tab, find the Number format dropdown, and select either Short Date or Long Date.

Method 2: Using the Analysis ToolPak Add-in

If you need to generate data that follows a specific statistical distribution (like a normal distribution, or "bell curve"), Excel's functions can be limiting. This is where the free Analysis ToolPak add-in comes in handy.

Step 1: Enable the Analysis ToolPak

• Go to File > Options.
• Click on Add-ins on the left.
• At the bottom, where it says "Manage," make sure "Excel Add-ins" is selected and click Go....
• In the pop-up window, check the box for Analysis ToolPak and click OK.

Step 2: Generate Random Data

You'll now see a new "Data Analysis" button on your Data tab.

1. Click on Data > Data Analysis.
2. Scroll down and select Random Number Generation, then click OK.
3. A dialog box will appear with several options.

• Number of Variables: How many columns of data you want.
• Number of Random Numbers: How many rows of data you want.
• Distribution: This is the key part. You can choose from various distributions like Uniform, Normal, Bernoulli, etc. For many real-world simulations (like test scores or sales figures around an average), Normal is very useful.
• Parameters: Based on the distribution, you can specify values like Mean and Standard Deviation.
• Output Range: Choose where you want Excel to place the data.

This method doesn't generate "easy to read" categorical data, but it's fantastic for creating more statistically valid numerical data for simulations and advanced modeling.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 3: Leverage AI to Create Your Dataset

For more complex or realistic datasets, especially those involving text, using an AI tool like ChatGPT or Microsoft Copilot can be a massive time-saver. These tools are excellent at creating patterned data or realistic-sounding names and descriptions that would be incredibly tedious to write with formulas.

The trick is to write a clear, descriptive prompt.

Example Prompt:

Act as a data generator. Create a table of 25 rows of dummy sales data for a fictional tech company. Provide the output in a comma-separated format I can copy into Excel. The columns should be: OrderID (e.g., ORD-1001), CustomerName (a realistic-sounding full name), ProductName (like 'Quantum Laptop Pro', 'Photon Mouse', 'Giga-Router X1'), Quantity (between 1 and 10), and OrderDate (a random date in the last six months).

The AI will generate text that you can copy directly. In Excel, click on a single cell (like A1), paste the data, and then use the Data > Text to Columns feature to split it into a proper table using the comma as a delimiter. This approach is unmatched for its speed in producing very specific, context-rich mock data.

Final Thoughts

Generating dummy data in Excel is an essential skill that saves time and empowers you to build, test, and learn without risk. Whether you're using simple functions like RANDBETWEEN and CHOOSE, the more advanced Analysis ToolPak, or the speed of an AI assistant, you now have the tools to create any sample dataset you need.

As you move from creating data in spreadsheets to analyzing it across different platforms, you might find yourself spending hours just pulling reports from all your different tools. To solve this, we built Graphed to help connect datasources like Google Analytics and Salesforce automatically. You can then use plain English to build real-time dashboards and reports, saving the time you used to spend wrangling CSVs and updating a dozen different spreadsheets.