## Create number series

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, you need to rely on formulas.

The Autofill is also able to create date series and values containing both text and numbers, shown in columns F, H and J above.

**What's on this page**

- Create number sequences (Autofill)
- Create a repeating number sequence
- Create a number sequence and restart when a cell value equals a condition
- Create a number sequence to count records by year and month (sorted list)
- Create a number sequence to count records by year and month (unsorted list)
- Create a number sequence to count dates based on year
- Create a number sequence to count items
- Create a number sequence to count prices within given amounts
- Create a number sequence to count records by individual products and years
- Watch a video explaining these methods

### 1. Where is the Autofill button on the ribbon?

The Autofill button is located on tab "Home" on the ribbon. Click the "Fill" button and a pop-up menu appears.

The pop-up menu shows:

- Down
- Right
- Up
- Left
- Across Worksheets...
- Series...
- Justify
- Flash Fill

You can also create a number series using the dot in the lower right corner of the selected cell.

The two first examples below demonstrate how to use the dot to create a series of numbers.

#### 1.1 How to create a number series from 1 to n

The following two examples show you how to create a number sequence using two different techniques. The Autofill feature allows you to quickly create a series of numbers.

##### Example 1

The animated image above shows how to create a number sequence from 1 to 5. You can create a much larger series

- Type 1 in cell B2.
- Press Enter.
- Right-click on the black dot and drag down as far as needed.

A pop-up menu appears. - Click "Fill series".

##### Example 2

- Type 1 in cell A3 and 2 in cell A4
- Select A3 and A4
- Left click and hold on the black dot and drag down as far as needed.

##### Example 3,

If you prefer a formula, try this one out:

The image above shows the formula entered in cell B2. Copy cell B2 and paste to cells below as far as needed.

The formula works as intended even if you insert rows above the cell B2.

#### 1.2 How to create a number series with every other number

The image above shows a list with every other number starting from 1 to n, in column D. To create this list follow these steps:

- Select cell D2.
- Type 1 and then press Enter.
- Select cell D3 if it is not already selected.
- Type 3 and press Enter on your keyboard.
- Select cell range D2:D3.
- Click and hold with left mouse button on the dot in the lower right corner of the selected cell range.
- Drag with mouse downwards as far as needed.
- Release left mouse button.

#### 1.3 How to create a series of dates

Column F in the image above shows a date series from 1/1/2020 to 1/5/2020. Here is how to quickly create it:

- Select cell F2.
- Type 1/1/2020 and then press Enter on your keyboard.
- Select cell F2 again.
- Click and hold with right mouse button on the dot located in the lower right corner of the select cell, in this case, cell F2.
- Drag with mouse downwards as far as needed.
- Release right mouse button.
- A pop-up menu appears.
- Click "Fill Series".

#### 1.4 How to create a series of dates based on a given period

Column H shows a date series based on every other week or 14 days.

- Select cell H2.
- Type 1/1/2020 or the start date you want to use, and then press Enter.
- Select cell H3 if it is not already selected.
- Type 1/15/2020 or the date you want to use, and press Enter on your keyboard.

The Autofill tool will use the difference in days between the first date (H2) and the second date (H3) to create the remaining dates. - Select cell range H2:H3.
- Click and hold with left mouse button on the dot located in the lower right corner of the selected cell range.
- Drag with mouse to cells below as far as needed.
- Release left mouse button.

#### 1.5 How to create a a series of values based on text and numbers

The Autofill tool allows you to use values containing both text and numbers as well. Column J shows this in the image above.

- Select cell J2.
- Type Item 1
- Select cell J2 again.
- Click and hold with left mouse button on the dot located in the lower right corner of the selected cell.
- Drag with mouse to cells below as far as needed.

### 2. Create a repeating number sequence using a formula

In this example, I am going to create a repeating number sequence 1, 2, 3, 4.

This formula checks if the previous sequence number is 4, if true it restarts with value 1. If false it adds the previous sequence number with 1.

Select cell B3 and type 1. Then press Enter.

**Formula in B4:**

Type above formula in cell B4. Press Enter.

Note, you don't need to enter all the formulas in cell range B2:B10. Only the number in cell B3 and the formula in cell B4.

Copy cell B4 and paste to cells below as far as needed. The formula uses relative cell references that change automatically when you copy the cell.

#### Explaining formula in cell B4

The IF function allows you to control the outcome based on a condition or multiple conditions, in other words, it returns one value if the logical test is TRUE and another value if the logical test is FALSE.

It contains three parts, logical expression, value to return if the logical expression evaluates to true, and another value to return if the logical expression evaluates to false.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

**Step 1 - Logical expression**

B3=4

B3 is a relative cell reference, relative meaning it changes when the cell is copied and then pasted to another cell, in this case, the adjacent cell below.

For example, cell reference B3 changes to cell B4 when cell B4 is copied to cell B5.

Remember that this formula is in cell B4 so a cell reference to cell B3 is the adjacent cell above. B3 is 1.

B3=4

becomes

1=4

and returns boolean value FALSE. This value determines if the second argument or third argument will be evaluated next.

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

**Step 2 - Next argument**

IF(B3=4, 1, B3+1)

becomes

IF(FALSE, 1, B3+1)

The logical expression returns FALSE, this means that the third argument will now be calculated.

IF(FALSE, 1, B3+1)

becomes

IF(FALSE, 1, 1+1)

and returns 2 in cell B4.

**Next cells**

It is not until cell B7 something unexpected happens. The logical expression now evaluates to TRUE.

IF(B6=4, 1, B6+1)

becomes

IF(4=4, 1, B6+1)

becomes

IF(TRUE, 1, B6+1)

This will make the IF function calculate the second argument instead of the third as before.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(TRUE, 1, B6+1)

returns 1. The series starts all over again beginning with 1.

### 3. Create a number series and restart when a cell value equals a given condition

In this example, the number sequence restarts every time the adjacent cell value in column B is equal to "A".

Select cell C3 and type 1. Then press Enter.

Formula in C4:

Copy cell C4 and paste to cells below as far as needed.

#### Explaining formula in cell C4

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

**Step 1 - Calculate logical expression**

The logical expression is the first argument, in this case: B4="A". Cell reference B4 is a relative cell reference, it changes when the cell is copied to cells below. Note, you need to copy the cell not the formula for this to work.

The text string "A" is the condition, if cell B4 is equal to the condition it returns TRUE, otherwise FALSE. TRUE and FALSE are boolean values in Excel.

B4="A"

becomes

"D"="A"

and returns FALSE.

**Step 2 - Return second argument if TRUE and third argument if FALSE.**

The second argument is 1 and the third argument adds 1 to the number in cell C3.

IF(B4="A",1,C3+1)

becomes

IF(FALSE,1,C3+1)

becomes

IF(FALSE,1,1+1)

and returns 2 in cell C4.

**Next cells**

It is not until cell C7 things change in the formula.

IF(B7="A",1,C6+1)

becomes

IF("A"="A",1,C6+1)

becomes

IF(TRUE,1,C6+1)

and returns 1. The series now start all over with number 1.

### 4. Create a number sequence to count records by year and month (sorted list)

This formula checks if the previous date has the same year and month as the current cell date. If true the previous sequence number is added by 1. If false the sequence starts all over again with 1.

The following formula will only work if the dates in column B are sorted from earliest to latest.

**Formula in C4:**

Copy cell C4 and paste to cells below as far as necessary.

#### Explaining formula in cell C4

##### Step 1 - Convert dates to month and year

TEXT(B4, "M-YYYY")

becomes

TEXT(B3, "M-YYYY")

becomes

##### Step 2 - Compare values

TEXT(B4, "M-YYYY")=TEXT(B3, "M-YYYY")

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

##### Step 3 - If function returns one value if True and another value if False

IF(TEXT(B4, "M-YYYY")=TEXT(B3, "M-YYYY"), C3+1, 1)

### 5. Create a number sequence to count records based on year and month (unsorted list)

**Array formula in B32:**

Copy cell C3 and paste to cells below as far as necessary.

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to use the SUMPRODUCT function

### 6. Create a number sequence to count dates based on year

This formula works fine with both sorted and unsorted lists. It simply counts the current cell year in the previous years cell range.

**Formula in B39:**

Copy cell C3 and paste to cells below as far as necessary.

### 7. Create a number sequence to count individual products

The COUNTIF function simply counts how many times the Item value has been displayed.

**Formula in C3:**

The first argument has a cell range that expands when you copy the cell to cells below.

Copy cell C3 and paste to cells below as far as necessary.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

### 8. Create a number sequence to count prices within specific ranges

The formula in cell C9 creates a sequence depending on which range the price is in.

**Formula in C9:**

Copy cell C9 and paste to cells below as far as necessary.

### 9. Create a number sequence to count records by individual products and years

**Formula in D3:**

Copy cell D3 and paste to cells below as far as needed.

#### Watch a video where I demonstrate the techniques described below

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

Find the longest/smallest consecutive sequence of a value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

### 11 Responses to “Create number series”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I have tried using the "Create a number sequence to count individual products" formula but when I copy it down it moves the second $ to before the first number. I would like to know if there is anyway of stopping this from happening

Paul,

I don´t think I understand:

when I copy it down it moves the second $ to before the first number=SUMPRODUCT(--(A52:$A$52=A52))

May be you want something like this

=COUNTIF($A$52:A52,A52)

Does (=SUMPRODUCT(--(TEXT($A$64:A64, "yyyy")&(B64:$B$64)=YEAR(A64)&B64)) + Enter) work for months

Erik Reppert,

What are you trying to calculate? Can you provide some example data?

in terms of the one of re-starting the sequence when a cell value equals a certain criterion, how about continuing the sequence by ignoring that same criterion?

so, in the case of A, B, D, D, C, B, D, A

the sequence will be if we ignore all the Ds: 1, 2, 0, 0, 3, 4, 0, 5

I'm trying to add to a hyphenated range. The sequence consists of two numbers in one cell for shipping labels, and it almost always has 40 added to each of the two numbers in it (because 40 small cartons fit in the larger shipping box).

Example: Cell B2 would read "MAB0053850-MAB0053889"

Cell B3 would read "MAB0053890-MAB0053929" and so on

The "C" column cells just have one sequence that's assigned to the larger shipping box, which contains the 40 "MAB..." cartons next to it.

Any way I can create a formula to extend these sequences vertically without splitting the two numbers into separate cells? Any help is appreciated. Thank you!

i have a serious problem..

i want to make a check voucher using excel 2007.. the problem is;i don't know how to series the check no. in the next voucher that follows.. in 1 sheet i format it into 2 voucher..

I made a template about “Insertion Sequence Numbers Automatic with Macro Codes”. You can add the sequence numbers to column that you want with your msgbox answering.

https://www.youtube.com/watch?v=hsQsW26nbNo

You can Download Here : https://netmerkez.wordpress.com/2015/08/23/insert-sequence-numbers-quickly-with-codes/

I have account numbers in COLUMN A, Status in COLUMN B, and dates mm/dd/yy in COLUMN C. I have multiple line items that apply to the same account number in COLUMN A. The Status for each line item can be 1, 2, 8-1, or 9-1 in COLUMN B and the date of each line item in COLUMN C. My goal is to have a sequence of 8-1's by date. I need to create a sequence of each orders 8-1's and a sequence of orders for 9-1's. That way when they sort on an order number (Account) they can get the sequence of updates. Ive tried Countif, Countifs, if(countif(, if(countifs... and I can get counts but they wont go in seq order of date. Ive tried to countif the date is <= date in CELL Cxx but no love.

Status Nomen

1 means created order.

8-1 means applied a change to order (can have multiple changes).

2 means order approved with updates.

9-1 means had a approved update after the approval (can have multiple updates after approval).

count Account_Number Status mm/dd/yy Seq needed

1 rt25hg62 1 1/1/17 1

2 dr87jh56 1 1/6/17 1

3 df12hg35 1 1/11/17 1

2 dr87jh56 8-1 1/26/17 1

4 er55tv16 1 2/15/17 1

1 rt25hg62 8-1 3/1/17 1

2 dr87jh56 2 3/2/17 1

3 df12hg35 8-1 3/12/17 1

1 rt25hg62 8-1 4/1/17 2

5 ww15hr26 1 4/3/17 1

5 ww15hr26 8-1 4/9/17 1

6 st45yu66 1 4/14/17 1

1 rt25hg62 8-1 4/15/17 3

4 er55tv16 8-1 4/17/17 1

2 dr87jh56 9-1 4/20/17 1

1 rt25hg62 2 4/22/17 1

3 df12hg35 8-1 4/27/17 2

3 df12hg35 8-1 5/1/17 3

3 df12hg35 2 5/5/17 1

4 er55tv16 8-1 5/7/17 2

6 st45yu66 8-1 5/8/17 1

4 er55tv16 8-1 5/9/17 3

1 rt25hg62 9-1 5/12/17 1

5 ww15hr26 8-1 5/16/17 2

3 df12hg35 9-1 5/18/17 1

1 rt25hg62 2 5/20/17 1

6 st45yu66 8-1 5/21/17 2

4 er55tv16 8-1 5/22/17 4

6 st45yu66 2 5/23/17 1

6 st45yu66 9-1 5/24/17 1

4 er55tv16 2 5/26/17 1

1 rt25hg62 9-1 5/27/17 2

4 er55tv16 9-1 6/1/17 1

5 ww15hr26 2 6/9/17 1

Hello,

how do you create from this:

SKU123,2003-2006

SKU345,2000-2001

SKU678,1999-2004

this

SKU123,2003,

SKU123,2004,

SKU123,2005,

SKU123,2006,

SKU345,2000,

SKU345,2001,

SKU678,1999,

SKU678,2000,

SKU678,2001,

SKU678,2002,

SKU678,2003,

SKU678,2004

please let me know.