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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Range of Numbers in Excel



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

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 shows 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)))

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


Download Practice Book

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


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


<< Go Back to Dynamic Range | Named Range | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo