Serial Numbers

We often need to add serial numbers column in excel reports. It may sound very easy task to you but there are different techniques which can be used.

1> Fill handle
It is the most commonly used method to populate the column with serial numbers.

blog1_A

you just have to write two consecutive numbers 1 & 2, select the numbers and drag the fill handle till the cell you want. It essentially establishes a pattern between number and fill the rest of the column

2> Fill Series
It is little uncommon in use, but I use it often and for various other purpose.
Write 1 in a cell, select the cells in column (including first cell) till you want the serial numbers, goto->editing->fill->series. With default setting press OK. The default settings are series in: column, type: linear, step value:1.
It may sound circuitous to use this method for filling serial numbers, but there are other useful things you can do with Fill series. So once you are used to it you will find it easy.

3> +1 Formula
Another commonly used technique. Write 1 in the first cell and add 1 in the following cell (=previous cell +1). Again drag the fill handle till the cell you want.

4> Row formula
Type =ROW() in a cell will give you the row number, if it’s the row “1” you need not do anything, else subtract a number to make it 1. Then use fill handle to drag it to the cell you want.
Sometimes we want the numbers to be with preceding zeros like 001, 012 etc. use TEXT formula with ROW to do that. for example say in cell A2 Write =TEXT(ROW()-1,”000″) and drag the fill handle down. The output is below.

blog1_B

 

All these methods have their pro and cons while you add/delete rows.
like while adding rows for option 1 & 2 you will have to use fill handle selecting two number before the gap and drag to the end. For formula based just copy the formula till the last number after the gap
While deleting rows for option 1 & 2 you will have to use fill handle selecting two number before the deleted rows and drag it to the end. For +1 technique copy the formula before error to the first error cell. Row formula technique doesn’t need anything here.
Different people use different methods . To my mind row formula option is the most versatile one and Fill handle option most easy to apply.

 

Advertisements