How to Create a Range of Numbers in Excel (3 Easy Methods)

To perform many tasks with a dataset, sometimes we need to create a range of numbers in Excel. So today I’ll show 3 easy ways how to create a range of numbers in excel. Please have a sharp look at the screenshots and follow the steps properly.


Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.


3 Easy Methods to Create a Range of Numbers in Excel

Method 1: Use Data Validation Option to Create a Range of Numbers in Excel

Let’s get introduced to our workbook first. In this datasheet, I have used 3 columns and 7 rows to represent some employees’ Names, Gender and Age. Now I’ll create a range to the Age column so that no one can input an invalid number unintentionally. We can assume that an employee’s age can’t be more than 100 years.

Method 1: Use Data Validation Option to Create a Range of Numbers in Excel

Step 1: 

⭆ Select the whole Age column.

⭆ Then go to Data > Data Tools > Data Validation

A dialog box will open up.

Method 1: Use Data Validation Option to Create a Range of Numbers in Excel

Step 2: 

⭆ Go to Settings

⭆ Select Whole Number from Allow drop-down.

⭆ Select Between from the Data drop-down tab.

⭆ Unmark Ignore Blank option.

⭆ Now input the Minimum and Maximum numbers. I have set here 0 to 100.

⭆ Then press OK

Method 1: Use Data Validation Option to Create a Range of Numbers in Excel

Now insert any number in the Age column. It will detect the validity. I put 35 in cell D5 and it has become valid. But when I put 105 in cell D6 then a dialog box opened up showing that the data doesn’t match with the validation.

Method 1: Use Data Validation Option to Create a Range of Numbers in Excel

Read More: Data Validation Drop Down List with Excel Table Dynamic Range


Method 2: Insert a Function to Create a Range of Numbers to Assign A Value Or Category in Excel

In this method, I’ll show how to apply the IF Function to create a range of numbers to assign a value or category in Excel. Here I have used a new dataset that has 2 columns. The columns are titled with Number and Assigned Value. And there are some random numbers in 3 successive rows. I want to assign a number (Let it be‘7’) for Cell C5 if the number in Cell B5 belongs between the range 0 to 1000.

For the next 2 rows I want to assign 9 for the range 1001 to 2000 and 11 for the range 2001 to 3000.

Method 2: Insert a Function to Create a Range of Numbers to Assign A Value Or Category in Excel

Step 1:

⭆ Select Cell C5 and type the formula given below.

=IF(AND(B5>=0, B5<= 1000), 7,IF(AND(B5>=1001, B5<=2000), 9, IF(AND(B5>=2001, B5<=3000),11, 0)))

👉 How Does the Formula Work?

  • The first combination of the IF and AND functions checks if the input value lies between 0 and 1000, if it does then the input value will be assigned in the cell.
  • If the first condition is not matched, then the second combination of the IF and AND functions will check if the input value lies between 1001 and 2000. If so, the formula will allow you to input the value, otherwise, it won’t.
  • Similarly, for the range of the numbers between 2001 and 3000, the third combo of the IF and AND functions will allow you to input a certain numeric value.
  • If no condition matched then it will show “0

⭆ Press Enter button.

Method 2: Insert a Function to Create a Range of Numbers to Assign A Value Or Category in Excel

Look at the image below that it’s showing the assigned value.

Method 2: Insert a Function to Create a Range of Numbers to Assign A Value Or Category in Excel

Step 2: 

⭆ Now just use the Fill Handle to copy the formula for the next two rows.

Method 2: Insert a Function to Create a Range of Numbers to Assign A Value Or Category in Excel

📓 Note: This formula also can help to assign the data with text format, please apply the formula below:

=IF(AND(B5>=0, B5<= 1000), “Seven”,IF(AND(B5>=1001, B5<=2000), “Nine”, IF(AND(B5>=2001, B5<=3000),”Eleven”, 0)))

Read More: Excel OFFSET Dynamic Range Multiple Columns in Effective Way


Similar Readings


Method 3: Use VLOOKUP Function to Create a Range of Numbers in Excel

Here in this last method, I will do the previous operation by using the  VLOOKUP Function. For that purpose, I have rearranged the dataset like the image below. We’ll apply the VLOOKUP Function for the Given Number.

Method 3: Use VLOOKUP Function to Create a Range of Numbers in ExcelStep 1:

⭆ In Cell C12 type the formula given below:

=VLOOKUP(B12,B5:D7,3)

⭆ Now just hit the Enter button. It will show the assigned value.

Method 3: Use VLOOKUP Function to Create a Range of Numbers in Excel

Step 2:

⭆ Now just use the AutoFill Handle tool to copy the formula for the next two rows by using a mouse.

Method 3: Use VLOOKUP Function to Create a Range of Numbers in Excel

Read More: OFFSET Function to Create & Use Dynamic Range in Excel


Conclusion

I hope all of the methods described above will be effective enough to create a range of numbers in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo