excel generate list of numbers between two numbers

Excel: Generate Number List Between Two

By Michael Jones
AD

Ever found yourself in need of a list of numbers between two given numbers? Microsoft Excel, with its powerful features, provides a simple and efficient way to generate such lists. This guide will walk you through the process, ensuring you understand the steps involved and can apply this technique in various scenarios.

Before delving into the methods, let's ensure we're on the same page. When we say 'list of numbers between two numbers', we're referring to a sequence of numbers that starts from a given number, includes all the numbers up to another given number, and excludes the latter. For instance, if you want the list to be 3 to 8, the sequence will be 3, 4, 5, 6, 7.

Using AutoFill to Generate a List of Numbers

The AutoFill feature, which extends values from one cell to another, is the quickest way to generate a list of numbers.

AD
Excel Formula to Find Difference Between Two Numbers - ExcelDemy
Excel Formula to Find Difference Between Two Numbers - ExcelDemy
Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List
Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List
How to compare two Excel files or sheets for differences
How to compare two Excel files or sheets for differences

First, ensure your starting and ending numbers are in two separate cells. Suppose you want numbers from 3 to 8, put a 3 in A1 and an 8 in A2.

Filling Down

Place your cursor on the small square at the bottom right corner of the cell containing the first number (A1). When the cursor changes to a plus sign, drag it down to the cell below the cell containing the last number (A2). A numbered list will auto-generate, starting from the number in A1 and ending at the number in A2.

How to Make a Numbered List in Excel (8 Methods) - ExcelDemy
How to Make a Numbered List in Excel (8 Methods) - ExcelDemy
Excel IF Statement Between Two Numbers: 4 Ideal Examples - ExcelDemy
Excel IF Statement Between Two Numbers: 4 Ideal Examples - ExcelDemy
Mastering Excel Date & Time: Serial numbers, Networkdays, Datevalue, and more
Mastering Excel Date & Time: Serial numbers, Networkdays, Datevalue, and more

For an autofill sequence, Excel uses the first two numbers to determine the pattern. If you drag down into a specific range, the sequence should stop at the last number you have. However, if it fails to stop at the expected number, check that your cells are formatted as General. You can do this by right-clicking, selecting Number Format, and choosing General.

Filling Right or Up

You can also use the AutoFill feature to generate a list that moves right or up. To do this, follow the same initial steps, but drag from A1 into the cell to the right (B1) or above (A2) instead. The sequence will continue from the starting number until reaching the last number.

AD

Keep in mind that Excel will determine the sequence based on the first two numbers. If the sequence isn't as expected, it's likely that Excel assumed a different pattern. To fix this, right-click within the list and select Remove Duplicates, then adjust your starting and ending numbers accordingly.

How to Find a Significant Difference Between Two Numbers in Excel (2 Methods) - ExcelDemy
How to Find a Significant Difference Between Two Numbers in Excel (2 Methods) - ExcelDemy
How to Check If a Value Lies Between Two Numbers and Return a Result in Excel - ExcelDemy
How to Check If a Value Lies Between Two Numbers and Return a Result in Excel - ExcelDemy
an excel spreadsheet with the text generator to create a large sequence of numbers in excel
an excel spreadsheet with the text generator to create a large sequence of numbers in excel
Excel VLOOKUP is Not Working with Numbers - 2 Solutions - ExcelDemy
Excel VLOOKUP is Not Working with Numbers - 2 Solutions - ExcelDemy
an image of a computer screen with numbers in the bottom left corner and on the top right
an image of a computer screen with numbers in the bottom left corner and on the top right
an excel tip is shown in the middle of a page with numbers and symbols on it
an excel tip is shown in the middle of a page with numbers and symbols on it

Using the Sequences Function to Generate a List of Numbers

Excel's Sequences function is a newer addition to generate arithmetic sequences. It's powerful, flexible, and can handle complex scenarios. Its syntax is =SEQUENCE(rows, cols, start, step).

Let's break this down. 'rows' specify the number of rows, 'cols' the number of columns, 'start' the starting number, and 'step' the common difference between terms. If left blank, 'start' defaults to 1 and 'step' to 1.

AD

Applying the Sequences Function

For generating a list from 3 to 8, use the formula =SEQUENCE(6,1,3). The 6 specifies the number of items, the 1 sets a step of 1 (we want consecutive numbers), and the 3 is our starting number.

How to Use RANDBETWEEN Function in Excel: 5 Ideal Examples - ExcelDemy
How to Use RANDBETWEEN Function in Excel: 5 Ideal Examples - ExcelDemy

Press Enter, and you'll get a list of your required numbers. Plus, this function allows for dynamic lists. If you change the starting number, the sequence will adjust accordingly.

Flexibility with the Sequences Function

The Sequences function offers flexibility in generating sequences. For instance, you can specify different numbers of rows and columns to create unique layouts. You can also specify a step value to generate non-consecutive lists (like a countdown timer or a list of even or odd numbers).

AD

Explore different combinations of rows, columns, starting numbers, and steps to see the results. This function's versatility will help you in various scenarios, from tracking project milestones to creating number patterns for data analysis.

In the world of Excel, there's always a multiple ways to achieve something. The AutoFill feature and the Sequences function both accomplish the task of generating a list of numbers between two numbers. The choice between them depends on your needs, your comfort with the tool, and your desire to automate. With practice, you'll find your preference and approach to such tasks.

AD