Md. Sourov Hossain Mithun
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.
Step 1:
⭆ Select the whole Age column.
⭆ Then go to Data > Data Tools > Data Validation
A dialog box will open up.
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
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.
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.
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.
Look at the image below that it’s showing the assigned value.
Step 2:
⭆ Now just use the Fill Handle to copy the formula for the next two rows.
📓 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: Dynamic Range for Multiple Columns with Excel OFFSET
Similar Readings
- Excel VBA: Dynamic Range Based on Cell Value
- How to Use Dynamic Range for Last Row with VBA in Excel
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.
Step 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.
Step 2:
⭆ Now just use the AutoFill Handle tool to copy the formula for the next two rows by using a mouse.
Read More: OFFSET Function to Create & Use Dynamic Range 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.
