Generate large set of test data in excel

In this blog we will look at how to create a large set of test data in excel using some excel functions. This will be useful when you are trying to test the web application or API that needs to be tested with large set of record.

When creating a big batch of test data in Excel, it's important to think about how the data will be used. Test data is really important for things like checking software, analyzing data, and making models. Whether you're testing how well an app works, checking if the data is correct, or getting a dataset ready for training a machine learning model, having well-organized and different kinds of test data is key. In this blog post, we'll go through the steps to make big datasets in Excel. We'll talk about ways to make random data, use Excel's tools, and make sure the data looks like real-world examples. By the end of this guide, you'll know how to make strong test data that can help you fully test your apps or do detailed data analysis.

Let's dive into the world of Excel to see how we can effectively generate large datasets to meet our testing and analytical needs.

Create a excel sheet to generate a test data.

Get the column name that represent the data.

Generate a unique number in EmployeeID column using different functions in excel.

Using Sequence() function: The SEQUENCE() function in Excel is a useful and strong tool for creating lists of numbers in order. This function was added in Excel 365 and Excel 2019, and it can make series of numbers that can be set up in rows, columns, or both. The Sequence function will create a special number, and the highest number it can make is 10000. If you need more than 10000 or need special IDs, you can't use this function.

Syntax:

SEQUENCE(rows, [columns], [start], [step])

Arguments:

Rows: (Required) The total number of rows you need in the sequence.

Columns: (Optional) The total number of columns you need in the sequence. If not provided, the function will use 1 column by default.

start: (Optional) The first number in the sequence. If not provided, the sequence will begin at 1.

step: (Optional) The amount to add between each number in the sequence. If not provided, the step will be 1 by default.

Using Row() function: The ROW() function in Excel is a simple but useful tool that gives you the row number of a cell. You can use it by itself or with other functions for different tasks, like creating flexible ranges, applying special formatting, and working with data.

Syntax:

ROW([reference])

Arguments

• Reference: (Optional) The cell or group of cells you want to find the row number for. If you don't specify any cell, Excel will give you the row number of the cell where the ROW() function is used.

• Using the Fill Series Feature: The "Fill Series" feature in Excel is a helpful tool that lets you quickly create a series of numbers, dates, or other data types based on a pattern you choose. This feature is very useful for making tasks that involve entering the same data over and over easier, like making lists of numbers in order, dates, or custom patterns.

  
  

  How to Use Fill Series

  
  

  In Excel, you can find the Fill Series option in a few different places:

  
  

  Drag and Fill:

  
  

  You can use the fill handle (a small square at the bottom-right corner of a selected cell or group of cells) to drag and fill nearby cells with data that follows a pattern you set.

  
  

  Ribbon Menu:

  
  

  Go to the Home tab, then to the Editing group, and click on Fill → Series to manually choose the fill options.

  
  

  Right-Click Menu:

  
  

  Right-click the fill handle and drag, then let go and pick the fill option from the menu that shows up.

  
  

Generating a random alphabet using functions. Generate a employee name using Concatenate and RandBetween function:

RandBetween():

This function gives a random whole number between two chosen numbers each time the sheet is updated. Its format is:

RANDBETWEEN(bottom, top)

It needs two parts: Bottom is the smallest number it can give, and top is the biggest number it can give.

CHAR(): This gives a character based on a number. Its format is:

CHAR(value)

It only needs one part, which is a number from 1 to 255. These numbers stand for characters in the system's character set, which started with ANSI but in Excel is now ASCII decimal.

Now, we'll mix CHAR() and RANDBETWEEN() to make a random string that uses numbers between two chosen values and a function that turns numbers into letters. By mixing them, we can make a function that gives random letters.

TextJoin() function:

A simpler way to generate random text in Excel is to use the TEXTJOIN function along with CHAR and RANDBETWEEN. This method helps you avoid manually combining the characters.

=TEXTJOIN("", TRUE, CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)), CHAR(RANDBETWEEN(65, 90)))

Generate a email Id using Concatenate() function:

Concatenate() function:

The word "concatenate" means "to combine" or "to join together". The Concatenate() function lets you join text from two or more cells into one cell. In this example, you'll see how to combine the values from Column A and Column B into Column C to create an email address.

Generate a random date in excel

To create a random date in Excel, we need to use two functions: RANDBETWEEN() and DATE().

RANDBETWEEN():

This function gives a random whole number between two numbers you choose. It will give a new random number each time you open or recalculate the worksheet.

Formula:

=RANDBETWEEN(bottom,top)

The RANDBETWEEN function uses these parts:

Bottom (required) – This is the smallest whole number the function will give.

Top (required) – This is the largest whole number the function will give.

DATE:

Excel has many functions for working with dates and times. Each function does a simple task, but by using several functions together in one formula, you can solve more complex problems. Here, we will combine DATE and RANDBETWEEN to make a random date.

Conclusion - We now have learned how to create a unique number, letters, email id and Dates in the excel using the pre-defined function in excel. We can use this to generate a large random set of data for the test cases.