Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm typing a string of consecutive numbers in individual cells.

After completing the string, I go back and delete the contents of one of the cells in the column.

Is there a way that Excel can then automatically adjust the numbers in the rest of the column so that they remain consecutive?

For instance:

1
2
3
4
5

Then I delete the value (but not the cell) 2

Can Excel adjust the rest of the numbers so it looks like this:

1

2
3
4
share|improve this question

4 Answers 4

Are you just trying to insert a blank cell? If you have:

1
2
3
4
5

and you want it to be

1

2
3
4
5

just select (click on) the cell with 2 in it, right-click, select Insert..., and pick Shift cells down.

EDIT

There is also a way to do this using the IF formula, used like so:

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

Here are the first 3 cells in the column (assuming we're at the top of column A):

    A
   --------
1 |1
2 |2
3 |=IF(A2=0,A1+1,A2+1)

After this, click on cell A3, grab the "handle" in the bottom-right, and drag it down to fill in the rest of your column (or, click on A3, hit CtrlC to copy the formula, select the cells you want to fill, and hit CtrlV to paste). You'll then be able to delete any number and the list will continue below it. However, beware adding other data into the deleted cell, as that may interfere with the calculations.

share|improve this answer
    
I'm typing a string of consecutive numbers in individual cells. After completing the string, I go back and delete the contents of one of the cells in the column. Is there a way that Excel can then automatically adjust the numbers in the rest of the column so that they remain consecutive? For instance (commas delimit cells): 1,2,3,4,5 Then I delete the value (but not the cell) 2. Can Excel adjust the rest of the numbers so it looks like this: 1, ,2,3,4 this is the correct question...? pls give me a solution for this question.. –  theepan Apr 2 '13 at 19:09
    
@theepan please see my edit above –  MattDMo Apr 2 '13 at 19:52
1  
thank you but this is not suitable because if I delete more than 1 cell continuously this order will change, again it will start from 1 according to our formula, but what I need is if I delete more than 1 number ( not limited to 2) also this order will not change, –  theepan Apr 2 '13 at 20:10
    
@theepan - can I ask exactly what you're trying to do? Why enter numbers if you're just going to delete them? It seems like my original answer of inserting empty cells would work just as well. –  MattDMo Apr 2 '13 at 20:42

Enter 1 in the top cell in the column –– let’s call it S1.  Then enter =MAX(S$1:S1) + 1 into S2 and drag/fill it down as far as you want/need to go.  S$1 always refers to cell S1, whereas S1 will be transformed by the drag/fill process to refer to the cell above the current one.  For example, S7 will contain =MAX(S$1:S6) + 1.  So, this formula looks at all the cells in this column (S) above the current one, finds the highest value, and adds one.  As MattDMo points out, typing other values into the column may produce undesired results.

share|improve this answer

In MS Excel 2007 & 2010, here's a way to readjust the series even without formulas. It's not automatic, but it won't take you more than a minute to do it.

Let's say you have a series of numbers entered in A1:A10.

  1. Delete the values or clear any of the cells.

  2. To select the remaining values, highlight the entire column, press F5, click Special and then choose Constants. Click Ok.

    enter image description here

  3. Under the Home tab, click Fill > Series (or press Alt+H, F, I, S). Select Linear and set the Step Value to 1. Click Ok.

    enter image description here

Result:

enter image description here

share|improve this answer

Theepan,

I believe what you're looking for would only be possible if you write some VB script or code for a spreadsheet.

So far as I am aware, Excel has no function for automatically maintaining consecutive numerals in non-consecutive cells. This would normally be done manually by the user.

If you simply want to insert an empty or blank cell inside an otherwise consecutive list, you'll need to do what MattDMo suggests, which is to insert a cell, which would maintain the consecutive list while retaining the new blank cell.

share|improve this answer
    
check out my edit - it looks like a simple IF statement should do the trick... –  MattDMo Apr 2 '13 at 19:52
    
Yes, he'll need to use some code. Simple code, but code nonetheless. Which means that if he's trying to do this across multiple sheets frequently, it'll be a problem, but if he's just needing this capability on a single worksheet, it won't be too difficult to build and reuse. –  music2myear Apr 2 '13 at 19:56
    
@music2myear can u guide me how to write this code. is it hard? –  theepan Apr 2 '13 at 20:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.