How to return a column of n consecutive values in Microsoft Excel

The fill handle in Microsoft Excel accommodates most serial list requirements, and it’s one of the first features users learn how to use. By serial, I mean a list of consecutive values, such as 1, 2, 3, 4 and so on. Microsoft Excel refers to these lists as a series.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

As powerful as it is, it won’t handle every serial list you need. Let’s suppose you need a series where each serial value is repeated n times. If n is 5, the list would be 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3 and so on. In this tutorial, I’ll show you how to combine two simple Excel functions, ROW() and ROUNDUP(), to return a series of n consecutive values.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can user earlier versions. Excel for the web supports both functions.

How to use ROW() and ROUNDUP() together in Excel

The expression we’ll use combines Excel’s ROW() and ROUNDUP() functions. Let’s learn a bit about these two functions before using them.

Excel’s ROW() function returns the row number of a reference, using the syntax ROW([reference]) where reference is optional. When omitted, ROW() returns the row number of the current cell. When reference is a vertical range, ROW() returns the row numbers in a vertical array. For example, =ROW() entered into C2 returns 2. Whereas ROW(D2:D5) will return the values 2, 3, 4 and 5 in a column, as shown in Figure A.

Figure A

We’ll also use Excel’s ROUND() function, which rounds a value up, away from 0. This function uses the following syntax ROUND(number, num_digits) where Excel requires both arguments. Number can be any real number or reference to a number.

num_digits is the number of digits that you want to round the number. For example, Figure B shows this function rounding up the number 2 with various decimal values. You can change the results by changing the num_digits argument, which in this case is 1.

Figure B

Because num_digits is 1, ROUNDUP() returns a single decimal digit when rounding warrants. If num_digits were 2, the function would return two decimal digits where warranted.

Neither of these functions seems to support a list of serial values, but combined, that’s exactly what they do.

How to return a set number of consecutive numbers in Excel

By combining ROW() and ROUND() with a couple of input values, you can create a very flexible consecutive numbering function. You’ve probably already considered dividing by n, which is part of the solution, but alone, it doesn’t work, as you can see in Figure C. In this case, n is 5, therefore, 1/5 is .20. 2/5 is .40, and so on.

Figure C

This is where ROUNDUP() comes in. Figure D shows the results of using ROUNDUP() on the simple expression ROW()/n.

Figure D

Let’s evaluate the expression in row 1:

=ROUNDUP(ROW()/5,0)

=ROUNDUP(1/5,0)

=ROUNDUP(0.2,0)

1

The num_digit argument, 0 will return only integers, and the closest integer to .2, rounding up is 1. Rows 1 through 5 will also return 1. Now let’s look at how the function evaluates in row 6:

=ROUNDUP(ROW()/5,0)

=ROUNDUP(6/5,0)

=ROUNDUP(1.2,0)

2

The expression works as required, but it isn’t flexible enough because it always returns a set of 5 serial values. Fortunately, we can fix that by adding input values.

How to add input values to make the expression more flexible in Excel

So far, the expression returns consecutive integers in groups of 5, but what if you want to modify this expression so n truly is n? The addition of an input value will make this expression flexible enough to handle this request.

Figure E shows the simple setup. C1 is the input value for n. When C1 is blank, nothing happens because the IFERROR() function hides the division by 0 error. This function isn’t necessary, but you probably won’t want to display a list of error values — it’s up to you.

Figure E

By entering 5 in C1, the expressions in E1:E10 — =IFERROR(ROUNDUP(ROW()/$C$1,0),"") — return the two groups of 1s and 2s we saw earlier. Change the value in C1 to 2, and the expression returns a list of five groups as shown in Figure F. If you enter 1 for n, the expression returns 1, 2, 3, 4 and so on.

Figure F

The input value can be any whole number, positive or negative.

This isn’t the only way you can get a list of n consecutive values, but it’s quick and easy.