How to Create Random Data in Excel

Ever found yourself needing a big chunk of data to test a new formula, build a dashboard prototype, or practice your pivot table skills? Manually typing out hundreds of rows of fake names, dates, and numbers is nobody’s idea of a good time. Luckily, Excel has a set of powerful functions designed specifically for creating random data on the fly. This guide will walk you through everything you need to know, from generating a single random number to building a complete, realistic-looking dataset in minutes.

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 Bother Creating Random Data?

Creating dummy or sample data might seem like a niche skill, but it's incredibly useful for a variety of tasks that come up all the time. Here’s why you might need it:

• Testing Formulas and Models: Before you apply a complex formula to your critical real-world data, you can test its logic and behavior on a large random dataset to make sure it works as expected.
• Building Dashboards and Reports: You can’t build a chart without data. Random data allows you to create prototypes of your dashboards and reports, letting you perfect the design and layout before your actual data is ready.
• Training and Demonstrations: If you're creating a tutorial or training someone on an Excel feature, you need a dataset to work with. Generating random data is faster than trying to invent a plausible dataset by hand.
• Stress-Testing Your Spreadsheets: How will your spreadsheet perform with 10,000 rows of data instead of 100? Generating a large random dataset can help you identify performance bottlenecks before they become a problem.
• Anonymizing Information: Sometimes you need to share a file's structure or a VLOOKUP example without exposing sensitive company information. You can replace the real data with randomly generated values to protect privacy.

Generating Random Numbers in Excel

Excel offers a few core functions for generating random numbers. The one you choose will depend on what kind of numbers you need: decimals, whole numbers, or a full array of values.

The RAND() Function: For Random Decimals

The simplest way to get a random number is with the RAND() function. It’s incredibly easy to use because it takes no arguments at all.

What it does: Generates a random decimal number greater than or equal to 0 and less than 1.

Syntax:

=RAND()

How to Use It:

1. Click on the cell where you want the random number (e.g., A1).
2. Type =RAND() and press Enter.
3. Excel will instantly display a random decimal, like 0.482915….
4. To fill a column with random numbers, click back on cell A1, grab the small square in the bottom-right corner (the fill handle), and drag it down as far as you need.

A key thing to remember about RAND() is that it’s a volatile function. This means it recalculates a new random number every single time a change is made to your worksheet. If you type something in another cell, all your random numbers will change. We’ll cover how to "freeze" these values later.

Pro Tip: To generate a random number within a larger range (e.g., between 0 and 500), you can use the formula =RAND() * 500.

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

The RANDBETWEEN() Function: For Random Integers

When you need random whole numbers within a specific range — like generating random ages, sales units, or scores — RANDBETWEEN() is your go-to function.

What it does: Generates a random integer (whole number) between two numbers you specify.

Syntax:

=RANDBETWEEN(bottom, top)

• bottom: The lowest integer you want in the range.
• top: The highest integer you want in the range.

Example: Generating Random Test Scores

Imagine you need to generate a list of random test scores between 65 and 100.

1. Click on a cell (e.g., B1).
2. Type the formula: =RANDBETWEEN(65, 100) and press Enter.
3. Excel will return a whole number like 82, 95, or 67.
4. Just like with RAND(), you can click the cell and drag the fill handle down to create a full list of random scores.

Like RAND(), RANDBETWEEN() is also volatile and will recalculate with every sheet change.

The RANDARRAY() Function: The Modern Powerhouse

Available in newer versions of Excel (Excel 2021 and Microsoft 365), RANDARRAY() is the most flexible function for creating random data. Instead of generating a single number, it can fill an entire range of cells with random numbers at once.

What it does: Generates an array of random numbers.

Syntax:

=RANDARRAY([rows], [columns], [min], [max], [integer])

The arguments in [brackets] are optional, which makes it very versatile:

• rows: The number of rows you want to fill.
• columns: The number of columns you want to fill.
• min: The minimum random number. Defaults to 0.
• max: The maximum random number. Defaults to 1.
• integer: Set to TRUE for whole numbers (integers) or FALSE for decimals. Defaults to FALSE.

Example: Building a Project Data Table

Let’s say you need to generate 10 rows and 3 columns of data. The first two columns should have random task hours (integers between 5 and 40), and the last column a percent-complete value (decimals).

For the task hours (10 rows, 2 columns):

1. Click on cell A1.
2. Type the formula: =RANDARRAY(10, 2, 5, 40, TRUE)
3. Press Enter, and Excel will instantly populate cells A1 through B10 with random whole numbers.

For the percent complete (10 rows, a column for C):

1. Click on cell C1.
2. Type the formula: =RANDARRAY(10)
3. Press Enter, format the column as Percentage, and you're good to go!

The best part is there's no need to drag any fill handlers — the function fills the entire range for you.

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

Keeping Your Random Data Static

The volatile nature of these random functions is great for some scenarios, but often you'll want the numbers to stay put once they've been generated. Constantly changing data can make analysis impossible. Fortunately, the fix is easy: just convert your formulas to static values.

How to "Freeze" Your Data

1. Select all the cells containing your random data formulas.
2. Copy the cells (Ctrl + C on Windows, Cmd + C on Mac).
3. Keep the cells selected and right-click. In the menu, look for Paste Options and select the Values icon (it often looks like a clipboard with "123"). An even faster way is using the shortcut Ctrl + Alt + V (Windows) to open Paste Special, then hitting V for Values and pressing Enter.
4. Your formulas will be replaced with their results, effectively "freezing" them. Now they won't change every time you edit the sheet.

Creating More Complex Random Datasets

Random numbers are great, but most datasets contain more than just numbers. You often need random dates, names, or categories. By combining the random functions with other handy Excel functions like CHOOSE, INDEX, and DATE, you can build surprisingly realistic datasets.

Generating Random Dates

Generating random dates is as easy as using RANDBETWEEN with two dates as the top and bottom values. Excel stores dates as serial numbers, so you can randomize between a start date number and an end date number.

The Formula:

=RANDBETWEEN(DATE(2023, 1, 1), DATE(2024, 12, 31))

This formula generates a random date between January 1, 2023, and December 31, 2024. After you enter the formula, just make sure to format the cell(s) as a Date (either Short Date or Long Date).

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 Items from a List

This is one of the most useful tricks for creating realistic datasets. Let's say you have a list of product categories or regions and want to randomly assign one to each row.

Example: Randomly Selecting a Region

1. First, create your master list in a separate range or a different tab. For example, in cells G1:G4, list "North," "South," "East," and "West."
2. In the cell where you want the random region to appear, use this formula structure: =INDEX(range, RANDBETWEEN(1, COUNT_OF_ITEMS))
3. Applied to our list in G1:G4, the formula would be: =INDEX($G$1:$G$4, RANDBETWEEN(1, 4))

The dollar signs ($) make it an absolute reference, so the range doesn't shift when you drag the formula down.

Here’s how it works: RANDBETWEEN(1, 4) picks a random number from 1 to 4. The INDEX function then looks at the range G1:G4 (our list of regions) and returns the item from that position (e.g., if RANDBETWEEN returns a 3, INDEX returns "East").

Putting It All Together: A Sample Sales Dataset

Now, let's create a full sample sales data table with columns for Date, Product, Region, and Sales Amount.

1. Setup: First, create your master lists for products and regions somewhere on your sheet (e.g., I1:I5 for products and J1:J4 for regions).
2. Column A (Date): In cell A2, use the random date formula:
3. Column B (Product): In cell B2, use the INDEX formula pointing to your product list:
4. Column C (Region): In cell C2, do the same for your regions list:
5. Column D (Sales Amount): In cell D2, generate a random dollar amount. Let's make it more realistic by including cents:
6. Finally, select cells A2 through D2 and drag the fill handle down to generate as many rows of data as you need. Remember to copy and paste as values once you're done!

Final Thoughts

Mastering random data generation in Excel transforms a tedious manual task into a quick, automated process. Using powerful functions like RAND, RANDBETWEEN, and RANDARRAY, you can build everything from simple test cases to entire mock datasets in minutes, leaving you more time to focus on actually analyzing the information.

And once you have your data — real or sample — analyzing it is the next step. Spreadsheets are powerful, but building and managing dashboards across different data sources can still be a manual chore. At Graphed, we’ve made that part easier by letting you connect your marketing and sales platforms (like Google Analytics or Shopify) and tell our AI what you want to see. Instead of building pivot tables, you can just ask, "Show me my sales revenue by product compared to last month," and get a live, automated dashboard in seconds.