Into the fill handle Microsoft Excel meets most serial listing requirements, and it’s one of the first features users learn to use. By serial I mean a list of sequential values, such as 1, 2, 3, 4 and so on. Microsoft Excel refers to these lists as a series.
TO SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
As powerful as it is, it can’t handle every series list you need. Let’s assume you need a sequence 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 sequence of n consecutive values.
I use Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. Microsoft Excel for the web supports both features. You can download the demo .xls and .xlsx files for this Excel tutorial.
Using ROW() and ROUNDUP() together in Excel
The expression we’ll be using combines Excel’s ROW() and ROUNDUP() functions. Let’s learn a bit about these two features before using them.
Excel’s ROW() function returns the row number of a reference, using the syntax ROW([reference]) where reference is optional. If omitted, ROW() returns the row number of the current cell. If reference is a vertical range, ROW() returns the row numbers in a vertical array. For example =ROW() entered in C2 returns 2. While ROW(D2:D5) returns the values 2, 3, 4, and 5 in a column, as shown in Figure A.
Image A
We 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 a reference to a number.
num_digits is the number of digits you want to round to the number. For example, Figure B shows that this function rounds the number 2 up with several decimal values. You can change the results by changing the num_digits argument, which in this case is 1.
Figure B
Since 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 necessary.
None of these functions seem to support a list of serial values, but combined that is exactly what they do.
How to return a certain number of consecutive numbers in Excel
By combining ROW() and ROUND() with a number of input values, you can create a very flexible sequential numbering function. You’ve probably already considered dividing by n, which is part of the solution, but it just doesn’t work, as you can see in Figure C. In this case, n is 5, so 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 argument num_digit, 0 returns only integers, and the nearest 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 is not flexible enough because it always returns a set of 5 serial values. Fortunately, we can solve that by adding input values.
Add input values to make expression more flexible in Excel
So far, the expression returns consecutive integers in groups of 5, but what if you want to change this expression so that n is really n? The addition of an input value makes this expression flexible enough to handle this request.
Figure E shows the simple setup. C1 is the input value for n. If C1 is empty, nothing happens because the IFERROR() function hides the division by 0 error. This feature isn’t necessary, but you probably don’t want to display a list of error values - that’s up to you.
Digits E
Entering 5 in C1 makes the expressions in E1:E10 — =IFERROR(ROUNDUP(ROW()/$C$1,0),"") – return the two groups 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 integer, positive or negative.
This isn’t the only way to get a list of n consecutive values, but it’s quick and easy.
