Microsoft Excel: Automatically Number A List Of Employees

Problem: You work in Human Resources. You have a list of employees separated by department. As shown in Fig. 369, you have a numerical sequence in column A and the employees’ names in column B. Every time that you hire or fire an employee, you have to manually renumber all of the employees

Replace the numbers in column A with a formula that will count the entries in column B. The formula should count from the current row all the way up to row 1.

The COUNT function will not work, because it only counts numeric entries.

You need to use the COUNTA function.

• The range that should be counted should extend from B1 to the current row.

• The notation to always use B1 is B$1.

1) As shown in Fig. 370, enter this formula in cell A2: =COUNTA(B$1:B2).

When you copy this formula down a row, the range that is counted will extend from B1 to B3, as shown in Fig. 371. This is because the B2 portion of the above formula is a relative reference that is allowed to change as the formula is copied. The dollar sign in the B$1 reference tells Excel that no matter where you copy the formula, it should always refer to cell 1.

2) Copy the formula down to all of the names in your list. They will be numbered 1 through 19, just as when you typed the names in manually.

When an employee leaves the company, you can simply delete the row,

All of the other numbers will change to indicate that you now have a

total of 18 employees, as shown in Fig. 372.

When you hire a new marketing person, insert a blank row, enter his or her name, and then copy any formula from A to the new row. As shown in Fig. 373, all of the subsequent employees will be renumbered.

Summary: While this is a specific example, the concept of using a range as an argument where only one portion of the range contains an absolute reference is a common solution to keeping a running total of all cells above the current row.

Functions Discussed: =COUNT(); =COUNTA()