MrExcel.com - Your One Stop for Excel Tips & Solutions

Automatically Number a List of Employees

January 11, 2022 - by

Automatically Number a List of Employees

Problem: I work in human resources, and I have a list of employees, separated by department. I have a numeric sequence in column A and the employees’ names in column B. Every time the company hires or fires an employee, I have to manually renumber all the employees. How can I make this job easier?

Names appear in column B. Headings identifying the department appear in column A. For example, A1 says Marketing Department. B2:B6 contains five names. Row 7 is blank. Cell A8 says Human Resources. Cells B9:B12 contain names. Row 13 is blank. A14 says Manufacturing. B15 contains a name. The goal is to add numbers in column A next to each name that only counts the name cells.
Figure 268. Numbering the employees manually is an HR nightmare.

Strategy: You can 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 and keep in mind the following points:

  • The range that should be counted should extend from B1 to the current row.
  • The notation to always use B1 is B$1.

Here’s what you do:

  • 1. Enter the formula =COUNTA(B$1:B2) in cell A2.

The formula in A2 is an expanding range with a single dollar sign: =COUNTA(B$1:B2).
Figure 269. Count from B1 to the current row.

When you copy this formula down a row, the range that is counted will extend from B1 to B3. This is because the B2 portion of the 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 when you copy the formula, it should always refer to row 1.

As you copy that formula to row 2, you get =COUNTA(B$1:B3). The answer is 2.
Figure 270. The range now extends from B1 to B3.

The range now extends from B1 to B3.

  • 2. Copy the formula down to all the names in your list. They will be numbered just as when you typed in the names in manually.

Results: When an employee leaves the company, you can simply delete the row, and all of the later rows will be renumbered. When you hire a new person, you can insert a blank row, enter the new hire’s name, and then copy any formula from another cell in A to the new row.

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.

Problem: What if you don’t delete the past employees, but you hide the rows? The newer AGGREGATE function can ignore hidden rows.

In the figure below, the first argument of 3 tells Excel to use the COUNTA function. The second argument of 5 tells Excel to ignore hidden rows.

What happens when an employee leaves? If your plan is to hide the row instead of deleting it, then you can use =AGGREGATE(3,5,B$1:B2) in A2.
Figure 271. AGGREGATE can ignore errors, other subtotals, or hidden rows.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jeffrey Brandjes on Unsplash