Excel Formula for a List of Numbers: 10 Useful Options

Formulas & functions · Updated November 19, 2026

To create a list from 1 to 10 in Excel, enter =SEQUENCE(10) in an empty cell; it spills the numbers down a column. The options below cover generating sequences, repeating or randomizing numbers, and turning existing data into a useful list, for Microsoft 365 users who want a quick formula as well as people working with older Excel versions.

1. Generate a simple list with SEQUENCE

No source cells are needed for this formula: enter =SEQUENCE(10) in an empty cell. Excel returns the integers 1 through 10 vertically, with each value in its own cell, so you can create a numbered list without typing or dragging a fill handle. The number in the formula is the row count, which makes it easy to change the list length; if the destination cells are occupied, Excel shows a spill error rather than overwriting them.

SEQUENCE is available in Excel for Microsoft 365 and Excel 2021 or later, including supported Mac versions and Excel for the web. It is useful for invoice line numbers, test data, or a quick range of row labels, and the resulting cells update automatically if you change the formula. If you need a horizontal row instead, set the row count to 1 and the column count to 10, as in =SEQUENCE(1,10); that version fills across rather than down.

2. Set a starting number and step

No source range is required; enter =SEQUENCE(6,1,5,5) in an empty cell to create six rows, one column, starting at 5 and increasing by 5. The result is 5, 10, 15, 20, 25, and 30, which is a practical way to make multiples, numbered intervals, or repeated measurement steps without editing individual cells. SEQUENCE’s arguments are row count, column count, starting value, and step, so changing the final argument to 1 would produce consecutive values beginning at 5.

This is still a dynamic-array formula and needs Excel for Microsoft 365 or Excel 2021 or later. It can generate descending numbers too: use a negative step, such as -2, to count backward from the starting value. Choose the number of rows and columns carefully, because the formula needs that many empty cells available to display its full result.

3. Make a fill-down list with ROWS

Assume the list will start in B2 and continue through B11; enter =ROWS($B$2:B2) in B2, then fill it down to B11. The first result is 1, the next is 2, and the last is 10 because the range counted by ROWS expands one row at a time as the formula is copied. This approach works in older Excel releases as well as Microsoft 365, so it is useful when a workbook must be compatible with people who do not have dynamic-array functions.

The dollar signs keep the top of the counted range fixed, while the second reference changes from B2 to B3, B4, and so on. To fill down, select the formula cell and drag its fill handle, or copy it into the cells below; the formula does not spill into neighboring cells. It is a good fit for numbering a fixed block beside existing records, but if rows are inserted or the list grows often, SEQUENCE or a structured table may require less maintenance.

4. Create random whole numbers with RANDARRAY

No source range is needed; enter =RANDARRAY(10,1,1,100,TRUE) in an empty cell to return 10 rows of random integers from 1 through 100. The arguments specify the row count, column count, minimum, maximum, and whether the results should be whole numbers; TRUE requests integers, and both endpoints are included. This is handy for sample data, classroom exercises, or generating values to test a chart, but the output is random rather than a permanent sequence.

RANDARRAY recalculates when Excel recalculates the workbook, so the displayed values may change after an edit or recalculation. If you need to keep one draw, copy the spilled results and use Home > Paste > Paste Values to replace the formulas with their current values. RANDARRAY is available in Microsoft 365 and Excel 2021 or later; it is not a substitute for a guaranteed unique set, since repeated numbers can occur.

5. Keep only distinct numbers with UNIQUE

Assume the source values are in A2:A20 and the output area is clear; enter =UNIQUE(A2:A20) in an empty cell. Excel spills one copy of each distinct value, preserving the order in which each number first appears in the source, so a list such as 8, 3, 8, 5 becomes 8, 3, 5. This is useful for making a compact list of categories or number codes before applying another calculation.

UNIQUE removes repeated entries but does not sort the result, and it treats values according to their stored contents rather than how a custom number format displays them. For instance, a cell displayed with zero decimal places may still contain a fractional value, which can remain distinct from a nearby whole number. The function requires Microsoft 365 or Excel 2021 or later, and the source range can include blanks; check the output if you do not want a blank entry included.

6. Put a number list in order with SORT

Assume the numbers to sort are in A2:A20 and the cells below the formula are available; enter =SORT(A2:A20) elsewhere. By default, SORT arranges the numbers in ascending order, from smallest to largest, while keeping duplicate values, which is useful when you need an ordered copy without rearranging the original data. To reverse the order, use =SORT(A2:A20,1,-1), where 1 identifies the first column of the supplied range and -1 requests descending order.

The formula returns a spill range, so leave enough empty cells for every source value and avoid placing it inside the source range. SORT is available in Microsoft 365 and Excel 2021 or later, including supported Mac editions and Excel for the web. It sorts the values it receives; if the source contains text or blanks as well as numbers, decide whether those entries belong in the output before relying on the result.

7. Filter a list to numbers that meet a condition

Assume A2:A20 contains the numbers and the output area is empty; enter =FILTER(A2:A20,A2:A20>0,"No positive values"). The formula returns only values greater than zero and displays the stated message if no values meet the condition, making it helpful for separating positive measurements, balances, or scores from a mixed list. The first argument is the range to return, while the second is a same-sized test that evaluates each row.

Change the condition to A2:A20<0 to return negative values, or use A2:A20>=10 to keep values of at least 10. FILTER does not change the source list, and its spilled result updates when source values change; keep the output cells clear to avoid a spill error. FILTER requires Microsoft 365 or Excel 2021 or later, and the optional third argument is worth using because otherwise a no-match result produces a calculation error.

8. Sort numbers using a separate key with SORTBY

Assume numbers are in A2:A4, matching sort keys are in B2:B4, and the output area is clear; enter =SORTBY(A2:A4,B2:B4,1). If A contains 12, 7, 18 and B contains 2, 1, 3, respectively, the result is 7, 12, 18 because the formula orders the numbers by their corresponding keys in ascending order. This is useful when a list should follow a priority, date, rank, or other helper column rather than its own numerical size.

The values and sort keys must line up row for row and have compatible dimensions; otherwise the result may not represent the intended pairings. Use -1 instead of 1 for descending order, and add another sort-key range and order if you need a tie-breaker. SORTBY is available in Microsoft 365 and Excel 2021 or later, and it returns a dynamic array while leaving the original values in place.

9. Repeat a short number pattern with MOD

No input range is needed; enter =MOD(SEQUENCE(12)-1,4)+1 in an empty cell. The twelve results are 1, 2, 3, 4 repeated three times, since MOD cycles the position back to a remainder from 0 to 3 and the final +1 shifts that pattern to 1 through 4. This can build repeating labels for a schedule, a set of test categories, or a sequence that assigns each row to one of four groups.

Change 12 to the total number of values you need and change both occurrences of 4 to the pattern length; for example, a length of 3 cycles through 1, 2, 3. The SEQUENCE function means this exact spill formula needs Microsoft 365 or Excel 2021 or later, even though MOD itself is available in many older Excel versions. The formula repeats numbers by position, not by copying a source pattern, so use a different method if the pattern contains irregular values.

10. Turn a number block into one column with TOCOL

Assume a 3-by-3 block of numbers occupies A2:C4 and the output cells are empty; enter =TOCOL(A2:C4,1). TOCOL converts that block into a single vertical list and the 1 tells it to ignore blanks; by default it scans down each column before moving to the next, rather than reading each row across. This is useful when numbers have been pasted into a grid but need to feed a chart, lookup, or single-column calculation.

Because the default scan is column-first, a block with rows 1, 2, 3; 4, 5, 6; and 7, 8, 9 produces 1, 4, 7, 2, 5, 8, 3, 6, 9. To scan across rows instead, use =TOCOL(A2:C4,1,TRUE), which returns 1 through 9 in row order for that example. TOCOL is available in Microsoft 365 and Excel 2024, and its result spills into a column, so make sure the destination area has room.

How to Choose

Start by asking whether you are creating new values or reorganizing values that already exist. For a plain consecutive list or a custom interval, SEQUENCE is the most direct choice; for workbooks that must support older Excel versions, a ROWS formula copied down is a dependable alternative. If you need random sample values, RANDARRAY is convenient, but remember that recalculation can change them and that it does not promise uniqueness.

For existing data, match the formula to the task: UNIQUE removes duplicates, SORT changes order, FILTER applies a condition, SORTBY uses a companion key, and TOCOL reshapes a block. These functions are not interchangeable: SORT retains duplicate values, UNIQUE does not automatically sort, and FILTER returns only rows that pass its condition. Consider whether blanks, negative values, duplicates, or mixed data types should remain before building a longer formula around the result.

Compatibility and layout matter as much as the calculation. Dynamic-array formulas need open cells to spill into, and their newer functions generally require Microsoft 365 or Excel 2021; TOCOL requires Microsoft 365 or Excel 2024. If colleagues use older releases, use a fill-down option where practical or confirm the workbook’s target version first. On Microsoft 365 for Mac and Excel for the web, the listed functions are also available when supported by that version; the main practical concern is still a clear destination area and the correct function support.

Frequently Asked Questions

How do I make Excel automatically number a list?

In Microsoft 365 or Excel 2021 and later, enter =SEQUENCE(10) in a blank cell to spill numbers 1 through 10. Change 10 to the count you need; for a fixed workbook compatible with older Excel, enter a ROWS formula in the first row and fill it down.

Why does my formula show a #SPILL! error?

A dynamic-array formula needs a clear rectangle of cells for its results, and #SPILL! usually means something is blocking that area. Check for existing values, merged cells, or a formula placed inside an Excel table, then move or clear the obstruction; Excel outlines the intended spill area when you select the formula.

Can I create a list without duplicate numbers?

Use UNIQUE on the source range to return each distinct value once, for example, a list of values in A2:A20. It preserves first-seen order; combine it with SORT if you also need ascending order, provided your Excel version supports both functions.

How can I keep random numbers from changing?

RANDARRAY recalculates, so copy its results and use Home > Paste > Paste Values to keep the current draw as fixed numbers. This replaces the formulas in the pasted cells, so retain a copy of the original formula elsewhere if you may need to generate another set.

Do these formulas work in Excel for Mac or Excel for the web?

The dynamic-array functions described here are available in Microsoft 365 for Mac and Excel for the web, subject to the same function and version requirements stated above. Older Excel installations may not recognize functions such as SEQUENCE, FILTER, or UNIQUE, so check the version used by everyone who needs to open the workbook.

Final Thoughts

For a straightforward list of numbers, start with SEQUENCE; for existing data, choose the function that matches the task, such as UNIQUE, SORT, or FILTER. Check version support and leave room for spilled results, and the formula will be easier to maintain as your list changes.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.