Search
« Pythonista - Remote Command Execution | Main | Using AutoHotKey to Select the Last ScreenShot from Greenshot »
Saturday
Jun292013

Auto Incrementing in Excel

In this post I'm going to share a tip about auto incrementing numbers in Excel. I've frequently found myself creating incremental number lists (e.g. 1,2,3,4,5,...,etc.) and whilst Excel is quite helpful at providing ways to create such lists I find its methods are often a bit limited for my needs - so I came up with another way that I think you might find more resilient.

Typical Methods

So many Excel users are probably familiar with a few common ways to create incremental lists. In all of the methods I'll describe below I'll describe them as vertical lists, but they could just as easily be horizontal lists.

Auto-fill

  1. Enter the value 1 into a cell.
  2. Enter the value 2 into the cell beneath it.
  3. Select the two cells.
  4. Left click on the small square in the bottom right corner of the second cell and drag it down to the cell you wish to be the end of the series.

Excel will fill each cell with the incremental numeric value.

Basic Formula Fill

  1. Enter the value 1 into a cell (e.g. into C2).
  2. Enter a formula to add 1 to the value in the cell above (e.g. =C2+1).
  3. Select from the second cell down to the last cell in the series and press CTRL+D to fill down.

Excel will fill each cell with a formula to take the cell above and add one to it.

The Issue

Let me be clear at this point. These methods work absolutely fine. The issues occur when you choose to insert, duplicate and/or remove rows.

  • Duplicating Rows
    • Auto fill: produces identical number to the row from which it was duplicated.
    • Basic Formula Fill: produces identical number to the row from which it was duplicated.
  • Inserting Rows (and fill down from row above)
    • Auto fill: produces identical number to the row above.
    • Basic Formula Fill: produces the number of the row above incremented by 1.
  • Deleting Rows
    • Auto fill: subsequent rows will keep the same number as they had previously.
    • Basic Formula Fill: subsequent rows will return an error.

Alternate Method

With the methods described above you quickly start getting confusing results. The solution in each case is to simply regenerate the series by repeating the process to populate it. I find this tedious and in many cases it messes around with formatting (particularly conditional formatting) that I have in place. So my solution is to use a more complex but flexible formula and here's how it breaks down.

The first step is one that is based on an assumption ... but one that works for me and the way I tend to structure my spreadsheets. The assumption is that when a list is to increment, the cell above will be a number. So if there is text in the cell above or there is no cell above (i.e. it is in row 1) then it is the first cell in the list. For this first pass through we'll assume our list starts at 1 and so our pseudo-Excel formula is as follows.

=if(isnumber({cell above},{cell above}+1,1)

Note that we have two entries for {cell above}. Here's where we apply the resilience - rather than specifying a specific cell we need to make the reference a relative one.

The Excel function that allows us to fetch a value for a relative cell is INDIRECT. This function takes a cell reference so we still need to provide the actual cell reference. The ADDRESS function allows us to do just that, but it requires a row and column.

So we can redefine the {cell above} place holder as follows.

{cell above} = INDIRECT(ADDRESS({row above},{same column}))

This means that the overall pseudo-Excel formula becomes...

=if(isnumber(INDIRECT(ADDRESS({row above},{same column})),INDIRECT(ADDRESS({row above},{same column}))+1,1)

Excel fortunately provides the ROW and the COLUMN function. These functions return the current row and current column of the cell respectively.

We can use a simple subtraction to reference a previous row.

{row above} = ROW()-1

The {cell above} place holder would thus become.

{cell above} = INDIRECT(ADDRESS(ROW()-1,COLUMN()))

So the resulting formula becomes...

=if(isnumber(INDIRECT(ADDRESS(ROW()-1,COLUMN())),INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1,1)

You can also extend the formula to be a little more flexible by replacing the last two "1"s with references to named cells. To name a cell simply over-type the cell ID (e.g. A1) with the name you wish to use.

So we change the formula like so.

=IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),INDIRECT(ADDRESS(ROW()-1,COLUMN()))+IncrementBy,StartAt)
  • IncrementBy - the number by which to increase the number by with each row.
  • StartAt - the value of the first number in the list.

Hopefully you can follow the logic behind this, but if not then you can probably get away with just copying and pasting in the formula.

When you apply the duplicate, inserted (and filled down) and deletion of rows, the result is that the new method retains the incremental structure.

You can also download an example spreadsheet (XLSX) with the comparison of the techniques including the extended formula.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (41)

I want to increment the B cell value by 1 if the A cell values are same and if it differes then it should start from 1.

A. B
1. 1
2. 1
3. 1
3. 2
4. 1
4. 2
4. 3
5. 1
5. 2
6. 1
7. 1
7. 2

July 15, 2015 | Unregistered CommenterDany

Hi Dany.

The formula you would want to put in the cells in column B could be this:


=IF(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),
IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))),
IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))=INDIRECT(ADDRESS(ROW(),COLUMN()-1)),
INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1,1),1),"")

(I've broken it up with some new lines to make it easier to read, just make sure to paste all of it into a single cell)

I've knocked up an example spreadsheet for you showing how it manages starting at arbitrary positions and also how it searches for numerics as well otherwise it outputs a null string ("").

I've not thoroughly tested it you understand, but hopefully its suitable for your purposes. Let me know how it goes :-)

Regards,

Stephen.

July 23, 2015 | Registered CommenterStephen Millard

Thank you so much

August 13, 2015 | Unregistered CommenterAhmet

Thanks. This is exactly what I need. I spent hours trying to figure it out myself but couldn't.

June 11, 2016 | Unregistered Commenterjibis

Thanks so very much! I had given up trying to figure this out, and stumbled across it looking for something else! A little too late for previous projects but I will definitely use this in the future!

June 14, 2016 | Unregistered CommenterDon Faison

Hey! I have a question. I'd like to increment a counter by 1 every time the cell next to it increases in value (not necessarily by 1). Here is an example... I have the values of the A column, but I would like to have a function that creates the B column.

A. B
1 1
1 1
1 1
1 1
13 2
13 2
13 2
13 2
13 2
15 3
16 4
17 5
20 6
20 6

etc. etc.

August 28, 2016 | Unregistered CommenterEd

Hi Stephen,

I realize this is an old post but hopefully you are still monitoring it. I really hoping your smarts can help me out! I read through your post and the comments and took a look at that example excel sheet you provided - that is exactly along the lines of what I need..

Basically I need a column that displays an incremental numeric value for each unique value displayed in another column.

Example:
Column A | Column B
ABC1234 1
ABC1234 2
ABC1234 3
DEF1234 1
DEF1234 2
GHI1234 1
GHI1234 2
GHI1234 3
GHI1234 4
JKL1234 1
MNO1234 1
MNO1234 2
MNO1234 3

.....and so on. Column A is given, column B is what I need to come up with. The increment in column B has to reset at each break in recurring value listed in column A.

Hopefully I explained that well enough. It seems like it would be simple but I'm having trouble figuring it out/finding the answer online.

Thanks in advance for your help!!

Justin

August 30, 2016 | Unregistered CommenterJustin

Hi Ed.

That's tricky. You'll either need some VBA and use an onChange event (I did something like that once on quite a grand scale ... very cool but hellish to support!) or you'll need to maintain a history of some sort ... but I think again that might need to come back to some VBA. Spreadsheets are based around existing data and at best what can be forecast from that data. When you start talking about history of data changes I tend to start thinking database.

Perhaps there is a way, but nothing springs immediately to mind that's just comprised of standard Excel formulae.

Regards,

Stephen.

September 1, 2016 | Registered CommenterStephen Millard

Hi Justin.

I think that the formula you're after would be...

=IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))=INDIRECT(ADDRESS(ROW()-1,COLUMN()-1)),INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1,1)

I've used your example and this formula (column B) in a spreadsheet you can download here. Let me know if that sorts it for you.

Regards,

Stephen.

September 1, 2016 | Registered CommenterStephen Millard

i been trying to write a formula for excel to take 2 known values that are different but increments the rows below by one

example:
in Colum B o have
1
101
2
102

what i am trying to do is write a formula to drag down the colum and have the ending results
1
101
2
102
3
103
4
104
5
105
6
106
etc...

is this possible??

September 9, 2016 | Unregistered CommenterKirby

I need one help for the following problem :
compare two rows A and B,
if B have just one increment in digit the column should change its color by green
if more than one increment it must be red
if less than the value then yellow

September 25, 2016 | Unregistered CommenterChaitra Gopi

I need one help for the following problem :
compare two rows A and B,
if B have just one increment in digit the column should change its color by green
if more than one increment it must be red
if less than the value then yellow
A B
D3 D4 green
F3 F7 red
H7 H3 Yellow

September 25, 2016 | Unregistered CommenterChaitra Gopi

I need to create a LONG numerical drop down list to record voltages. It would start at 90.0V up to 610V. I am trying to avoid dragging down 500 rows to do it... Is there a way to do that? Thanks.

September 30, 2016 | Unregistered CommenterBig Will

I would like to thank Stephen very much for preparing the formula for Justin above.
This helped very much. Thank you!!! I don't want to bore people to bits so I will keep the details to myself. Other than that, I used this excel code to create an order for the drugs used per person that I downloaded from a NHANES dataset. I can share what I did if people are interested. All the best!!
Thank you again very much!

October 13, 2016 | Unregistered CommenterEmre

Hi,

In Excel, I would like to automatically increment a value by a set amount each month.

For example:
A1=1000 (starting value).
B1=250 (increment value)

On the 1st of each subsequent month, A1 will automatically increment by the amount in B2 so:

01/11/2016 - A1=1000
01/12/2016 - A1=1250
01/01/2017 - A1=1500
01/02/2017 - A1=1750
...etc

The solution needs to allow for the starting value to change, e.g, if A1 was currently equal to 1750, I'm then able to amend this to say 900.
On the 1st of each subsequent month, A1 will continue to automatically increment by the amount in B2:

01/02/2017 - A1=900
01/03/2017 - A1=1150
01/04/2017 - A1=1400
01/05/2017 - A1=1650
...etc

Hopefully somebody is able to help me solve this, if it's at all possible in Excel...?

Kind regards,
Alex

November 10, 2016 | Unregistered CommenterAlex

Hi there,

Thanks for all the information but I have been through your page on different occasions but still unable to find a solution for me. I am after a formula when I insert a row above, the value from one cell (Say B3) should be incremented by 1 to the one above (which is now B3). Everytime I insert a row above, it brings all the conditional formatting and data formatting but not bringing through the formula (B3+1). Any help is much appreciated.

Thanks

November 22, 2016 | Unregistered CommenterCleo

Hi Stephen,
Thank GOD I found your website. I urgently need a solution for this. My scenario:
http://www.domain.com/1.jpg
http://www.domain.com/2.jpg
http://www.domain.com/3.jpg
Is there a formula to help to fill up the html link from 1.jpg-100.jpg?
Tqvm!

December 6, 2016 | Unregistered Commenteralec

Hi,
Can someone please help me out on this,. I would like to increment number by one with fixed alphabet at the beginning and the end. For example: v-123m. I would like to increment the numbers 123 by 1 with the fixed alphabet v and m.

Regards,
Amit

January 4, 2017 | Unregistered CommenterAmit

1
95
92
89
86
83
80
77
74
71
68
65
62
59
56
53
50
47
44
41
38
35
32
29
26
23
20
17
14
11
8
5
2
96
93
90
87
84
81
78
75
72
69
66
63
60
57
54
51
48
45
42
39
36
33
30
27
24
21
18
15
12
9
6
3
97
94
91
Can anybody help me to generate formaul for above numbering. There is always a decrement of 3 but when it come to 2 it jump of to 96. if it comes to 3 it jumps to 97 , if it comes to 1 it jumps to 95.
There should be no 0,100, 99, 98

January 24, 2017 | Unregistered CommenterAhsan

Hello, Stephen! I've used the formula from your article except, I'm starting at 1,000,000 and adding by increments of 1. It worked for my first cell with a return value of 1,000,000, but it seems as if it all the other cells want to return 1,000,000 as well. Do you know what I've done wrong?

April 10, 2017 | Unregistered CommenterNiq

Sorry, I figured it out:)
The problem is that's not the formula I need:(
Maybe you can help me out, I still need to begin at 1,000,000 and increase by one each row, but where I'm running into trouble is that the rows shit if I sort or insert which is not good since I'm tagging inventory. Would a formual be near impossible? Should I just manually enter those numbers?

April 10, 2017 | Unregistered CommenterNiq

I want to increment the B cell value by 1 if the A cell values are different and if it same then it should continue to fill in the same number.

A. B
1. 1
2. 1
3. 1
3. 2
4. 1
4. 2
4. 3
5. 1
5. 2
6. 1
7. 1
7. 2

April 13, 2017 | Unregistered CommenterWK

Hi Stephen,

I have 2 columns, one with Id No and the other with with date. My 3rd column should have an incremental value, if the id is same but the date has changed. Incase if the id and the date remains same as previous one, it should take the same value of the corresponding 3rd column.
could u pls help..
Output should be somewhat this..

A B C
17223 22/01/17 Increment 1
18226 15/02/17 Increment 1
17223 22/01/17 Increment 1
15227 15/01/17 Increment 1
17223 17/02/17 Increment 2

June 13, 2017 | Unregistered CommenterDev

Hi ,

I have a question.... What is the output of the below formula

=1/(numerical value)

December 6, 2017 | Unregistered CommenterSree

I would like to increment column A if there is a value in column B?
Are you able to suggest a formula like above?

Data

1 10
2 15
blank
3 7
blank
4 9

January 8, 2018 | Unregistered CommenterMartin

Hello,
Once I enter the date in Colum E, in Colum F the formula
=IF(ISBLANK(E1)," ",TEXT(ROW(E1),"1801-000"))
is pasted to display the voucher number.

My issue is to increment this number by 1 in the next row (1801-002)
and
if there are any blank rows, the numbering should continue from the previous one.

Appreciate your inputs on the same

Regards

January 24, 2018 | Unregistered CommenterAkram

Niq.

The formula approach outlined here is designed to be based on the absolute position. If the number is tied to the particular entry and that entry moves position but you want it to retain its value then you have to specify the value - you can't derive it from a changing position.

If you weren't using integer values, maybe you could assign an ID based on other properties of cells on the same row?

March 28, 2018 | Registered CommenterStephen Millard

Amit.

Take a look at the concatenate function. Something like this...

=concatenate("v-",IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),INDIRECT(ADDRESS(ROW()-1,COLUMN()))+IncrementBy,StartAt),"m")

March 28, 2018 | Registered CommenterStephen Millard

Alec.

You probably want something like this...

=CONCATENATE("http://www.domain.com/",ROW(),".jpg")

March 28, 2018 | Registered CommenterStephen Millard

Cleo.

Excel won't pre-populate rows with formulae for you. It isn't how it works I'm afraid. What you might try doing is copying a row and inserting the copied row as that will then bring the row forumlae in.

March 28, 2018 | Registered CommenterStephen Millard

Sree.

=1/(numerical value) would depend upon what the numeric value is of "numerical value". In mathematical terms it would be the reciprocal value of numerical value and is simply saying how many times does numerical value divide into 1.

March 28, 2018 | Registered CommenterStephen Millard

Chaitra.

You probably need to extrapolate the value difference from those cells before you can operate on it with conditional formatting. Use =RIGHT() to get the values and then determine the numeric difference. If necessary hide the column you put this operation into; but you should then be able to use that for your conditional formatting.

March 28, 2018 | Registered CommenterStephen Millard

Big Will.

That would certainly be the easiest way. You might be able to do it in VBA but I think doing it by values in cells is actually preferable. Simply put those figures on a separate worksheet, name your cells (look at Name Manager in the help if you want to get an idea of one way to do this), then reference the list by name when you apply the data validation. Once set-up you can, of course, hide the worksheet containing that numeric list so you don't have to look at it. I often hide the background settings worksheets in my spreadsheets.

March 28, 2018 | Registered CommenterStephen Millard

Kirby.

Yes - that should be possible. I think I'd tackle it by using two static starter rows and then back referencing two rows for incrementation rather than one.

March 28, 2018 | Registered CommenterStephen Millard

Alex.

Your monthly based variations should certainly be possible. You might want to look at storing them separately in a lookup sort of list and then using VLOOKUP to access the base values. Otherwise, that just looks like a simple incrementing list.

March 28, 2018 | Registered CommenterStephen Millard

WK.

You should just need to incorporate an IF formula to do that and do the comparison between the current row and the row above (look also at OFFSET).

March 28, 2018 | Registered CommenterStephen Millard

Dev.

I think your scenario is for all practical purposes the same as WK's. Look at using IF and OFFSET to carry out your comparison of the values held in the current and previous row.

March 28, 2018 | Registered CommenterStephen Millard

Martin.

I think you have a fairly similar scenario to WK and DEV.

Using an IF is your starting point, but then for the comparison, I would either use ISBLANK() or LEN()=0 to determine if the cell is blank and then to increment. The choice of what to use depends on how "blank" your cell really is to Excel.

March 28, 2018 | Registered CommenterStephen Millard

Akram.

Think about splitting "1801-000" into two parts and concatenating them:

CONCATENATE("1800-",{value})

Now if you need three digits, then you need to use something like this:

CONCATENATE("1800-"TEXT({value},"000"))

{value} is then the basis of your incrementation and you can take that part from the approaches described in this post along with perhaps taking a look at the responses to WK, DEV and Martin where they are also looking at including a comparison to previous row values.

March 28, 2018 | Registered CommenterStephen Millard

I cannot figure this out but need help, whenever Col A changes I need to add one starting with the value in Col B but have it stop at the value in Col C.

The End result would look like COL D.

I tried one of your above examples but it did not start counting again using the Col B value as the starting point when Col A changed.


Col A / Col B / Col C / Col D
ABC / 0 / 2 / 0
ABC / 0 / 2 / 1
ABC / 0 / 2 / 2
DEF / 99 / 100 / 99
DEF / 99 / 100 / 100
HIJ / 11 / 12 / 11
HIJ / 11 / 12 /12

April 3, 2018 | Unregistered CommenterJustin

Justin.

Here's a link to a spreadsheet download that does this using a formula that is just populated using the basic formula fill method described at the start of the post.

https://d.pr/5KPrxz

if you want to advance it using the alternate method (applying indirect and address), the details in the post above and the spreadsheet it links to should be able to guide you.

Stephen.

April 17, 2018 | Registered CommenterStephen Millard

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>