Asked By
boracay
250 points
N/A
Posted on  04/23/2011
I encoded thousands of names to update our employee list but I forgot to put the numbers before the names so I can monitor the list,
How do I save time from typing the numbers?
Answered By
Mikael H
0 points
N/A
#87515
How to add Employee number on Excel in an easy way?
Hi,
If you wanted to insert employee number in large Excel sheet, there are multiple ways to do it. You can pick any of these following tricks:

If you just want a sequential number as Employee number.

Add a new column for Type in the first the first number ex. E0001 select that cell.

In the left hand bottom of that cell you should see a bolded (+).

When you see that just simply drag down or across and the numbers will automatically sequence itself. It shouldn't matter if there is a letter is before the number.

If you enter a serial # E0001 in A1 and the next number E0002 in A2 and highlight both of them, then click and drag from the fill handle, Excel will compute the increment as 1 and automatically create more numbers using that increment, until the end of time, or 65,536 rows, whichever comes first.

Excel will let you increment +1, going down in a column, so start the top of the column with E0001.

Select a whole lot of the fields under that one and give the + 1 command. It may not let you do this with the E in front. In that case, put the E in the first column and the 0001 in the second column. Do the +1 increment in the second column.

Copy the E as far as you need in the first column.

Put as little space between the 2 columns as you can and orient the number to the left.

If your serial numbers begin with a specific letter, as in my example uses "E" and is followed by a set number of digits, as per your example of 4.

If you type your start serial number in for example cell C5, insert the following formula in cell C6, and copy it down as many rows as needed.
="E" & RIGHT(C5,4)+1

You can write a Macro for this.

You can create a button for increment the Serial number on every cell. It will increment the number in the active cell when the button is pressed. (Use a button from the Forms toolbar and assign this macro to it..
Sub Btn_Click()
Dim s As String, ll As Long
Dim j As Long, i As Long
Dim s1 As String, sChr As String
s = ActiveCell.Value
s = Trim(s)
ll = Len(s)
If ll = 0 Then
ActiveCell.Value = "AB/1000"
Exit Sub
End If
j = 0
i = 0
Do
i = i + 1
sChr = Mid(s, i, 1)
If IsNumeric(sChr) Then
s1 = s1 & sChr
Else
j = j + 1
End If
Loop Until i = ll
s1 = Format(Int(s1) + 1, "0000")
s1 = Left(s, j) & s1
ActiveCell.Value = s1
End Sub

Add the Employee number for the first Employee by hand (0001) and fill the remaining serial number fields (the ones below) with the following formula:
Formula: =IF(ISBLANK(A2),"",B1+1) With A2 = cell to the left and B1 = cell above
R1C1 Notation: =IF(ISBLANK(RC[1]),"",R[1]C+1)
Edit: To "add" a leading 0 to the number, you need to set the number format of your serial number to the following custom format: 000000

A1 is header, A2 is first Employee number, A3=IF(COUNTA(B3:IV3)>0,MAX(A$1:A2)+1,"")
Copy down as far as you wish.

Insert a new column for adding serial numbers.

Write 1 in the first row of that newly created column.

Select that column and click on “Fill” under “Home”. This is for MS Excel 2007. You can check for “Fill” accordingly if you are using a different version of Excel.

Under “Fill” select “Series…”

Check the option “Columns” under “Series in”. Let the type be “Linear”. Select “Step value” as 1 and “Stop value” as per your requirements, say 1000. This stop value is number where counting will end.

Click on “Ok”. That’s it your new column will now be filled with serial numbers.

Is the serial number just +1 on each row? If so try =sum (put the cell above the letter and number in these parenthesis) +1. Then copy and paste it down the column but be sure that the cell numbers are changing.
Hope this will help.
How to add Employee number on Excel in an easy way?
In either way you use when filling down the data automatically, you will find a small tooltip looks like a briefcase. Open it and select fill serial. You will find this tooltip on the Excel sheet.
Please note that in Office 2003 the end of Excel sheet is 65,553 rows and in Office 2007 undefined, so it's better to use Office 2007.

Just add new column before the name's column and type 1 or whichever the number you want to start with.

In the cell area, you will find a small square button in the left or right corner of the cell when u select it. Just double click it and it will automatically fill the next cells.

Also,instead of using this button you could select the area you want to fill the numbers in and from the ribbon select the following: Home > fill > fill down.
How to add Employee number on Excel in an easy way?
Yes, there is a simple method:

First you have to insert a column before the name column.

For that right click on the column heading and select insert. You will have a new column inserted left to the name column.

Now type the number in the first cell of the list. Suppose it is something like, ‘EMP01’.

Now select the range that you want to fill with numbers.

Click on ‘Home’ tab. Click on the down arrow of the ‘Fill’ tool in ‘Editing ‘ group.

Select the ‘Series’ option. In the ‘Series’ dialog box select the type as ‘Auto fill’ and click ‘Ok’.
Employee numbers will be filled automatically.