Khi làm danh sách nhân viên, danh sách khách hàng hay bảng kê hàng hóa, bạn gần như luôn cần một cột số thứ tự (STT) ở đầu bảng. Gõ tay 1, 2, 3 cho vài chục dòng thì mất thời gian, và chỉ cần xóa một dòng hoặc sắp xếp lại danh sách là cột STT bị nhảy số hoặc xáo trộn.

Bài viết hướng dẫn 10 cách đánh số thứ tự tự động trong Excel, đi từ thao tác kéo chuột đơn giản tới các công thức tự cập nhật khi bạn xóa dòng, lọc dữ liệu hay gộp ô. Mọi công thức và kết quả trong bài đã được kiểm tra bằng Excel 365.

Bạn chuẩn bị bảng dữ liệu mẫu để làm theo từng bước

Các ví dụ dùng chung một danh sách 10 nhân viên. Dòng 1 là dòng tiêu đề, cột A dành cho STT, cột B ghi họ và tên, cột C ghi phòng ban, và dữ liệu nằm từ dòng 2 tới dòng 11:

Dòng

Họ và tên (cột B)

Phòng ban (cột C)

2

Nguyễn Văn An

Kế toán

3

Trần Thị Bình

Kinh doanh

4

Lê Hoàng Cường

Kế toán

5

Phạm Thu Dung

Nhân sự

6

Hoàng Minh Đức

Kinh doanh

7

Vũ Thị Hà

Kế toán

8

Đặng Quốc Huy

Kinh doanh

9

Bùi Ngọc Lan

Nhân sự

10

Đỗ Văn Minh

Kinh doanh

11

Ngô Thị Nga

Kế toán

Bài viết dùng dấu phẩy để ngăn cách các đối số, tức các giá trị trong ngoặc của hàm, ví dụ =COUNTIF($C$2:C2,C2). Nếu máy bạn dùng dấu phẩy làm dấu thập phân, bạn gõ dấu chấm phẩy thay thế: =COUNTIF($C$2:C2;C2).

Cách 1: Bạn kéo chuột bằng nút điền để tạo dãy 1, 2, 3

Khi bạn chọn một ô, Excel hiện một hình vuông nhỏ màu xanh ở góc dưới bên phải ô, gọi là fill handle hay nút điền. Bạn đưa chuột vào hình vuông này cho tới khi con trỏ đổi từ dấu cộng trắng to thành dấu cộng đen mảnh, rồi nhấn giữ chuột trái và kéo xuống.

Bạn gõ 1 và 2 rồi kéo cả hai ô

  1. Bạn gõ số 1 vào ô A2 và số 2 vào ô A3.

  2. Bạn bôi đen cả hai ô, tức nhấn giữ chuột trái ở ô A2 rồi kéo xuống ô A3, để Excel biết hai số cách nhau 1 đơn vị.

  3. Bạn kéo nút điền ở góc ô A3 xuống tới ô A11 rồi thả chuột.

Excel điền tiếp 3, 4, 5 cho tới số 10 ở ô A11. Nếu bạn gõ 1 và 3, Excel hiểu bước nhảy là 2 và điền 1, 3, 5, 7.

Bạn gõ 1 rồi giữ phím Ctrl khi kéo

Khi bạn chỉ gõ số 1 vào ô A2 rồi kéo, Excel chép nguyên số 1 xuống mọi ô. Để Excel tăng dần, bạn nhấn giữ phím Ctrl trong lúc kéo. Nếu lỡ kéo ra toàn số 1, bạn bấm vào nút Auto Fill Options, một biểu tượng nhỏ hiện ở góc dưới vùng vừa điền, rồi chọn Fill Series, và Excel đổi các số 1 thành dãy 1, 2, 3.

Ba cách kéo chuột đánh số thứ tự trong Excel: gõ 1 và 2 rồi kéo, chỉ gõ 1 rồi kéo, gõ 1 và giữ Ctrl khi kéo
Chỉ gõ số 1 rồi kéo thì Excel chép số 1 xuống mọi ô, còn gõ 1 và 2 hoặc giữ Ctrl khi kéo thì Excel tạo dãy 1, 2, 3

Bạn nhấp đúp vào nút điền để điền tới hết danh sách

Với danh sách dài, bạn chọn hai ô A2 và A3 rồi nhấp đúp vào nút điền thay vì kéo. Excel nhìn sang cột B bên cạnh và tự điền tới dòng cuối cùng có tên. Cột bên cạnh cần liền mạch, tức không có ô trống xen giữa: nếu ô B6 trống, Excel dừng ở dòng 5 và bạn phải điền tiếp phần còn lại.

Mọi số tạo ra bằng cách kéo chuột đều là số cố định, nên Excel không tự đánh lại khi bạn xóa hay sắp xếp dòng.

Cách 2: Fill Series giúp bạn đánh số tới 1.000 hoặc 10.000 dòng mà không cần kéo

Khi cần sẵn một cột STT dài để dán danh sách 1.000 khách hàng vào sau, bạn dùng lệnh Fill Series:

  1. Bạn gõ số 1 vào ô A2 rồi chọn lại ô A2.

  2. Bạn vào thẻ Home, bấm Fill trong nhóm Editing rồi chọn Series.

  3. Bạn chọn Columns ở mục Series in để điền theo cột, và chọn Linear ở mục Type.

  4. Bạn gõ 1 vào ô Step value, tức bước nhảy giữa hai số, rồi gõ 1000 vào ô Stop value, tức giá trị dừng.

  5. Bạn bấm OK, và Excel điền từ số 1 ở ô A2 tới số 1.000 ở ô A1001.

Khi đổi Stop value thành 10000, bạn có ngay 10.000 số. Nếu bắt đầu từ 10 với Step value là 10 và Stop value là 100, Excel điền 10, 20, 30 tới 100.

Cách 3: Hàm ROW giúp số thứ tự tự đánh lại khi bạn xóa dòng

Hàm ROW trả về số dòng của một ô. Khi bạn để trống phần trong ngoặc, =ROW() trả về số dòng của chính ô chứa công thức, nên ô A2 cho ra 2. Số thứ tự cần bắt đầu từ 1, vì vậy bạn trừ đi 1:

  1. Bạn gõ =ROW()-1 vào ô A2 rồi nhấn Enter, và ô A2 hiện 1.

  2. Bạn chọn lại ô A2 rồi nhấp đúp vào nút điền để chép công thức xuống tới ô A11.

  3. Ô A3 tính 3 - 1 = 2, ô A11 tính 11 - 1 = 10, nên cả cột hiện từ 1 tới 10.

Ưu điểm của công thức này thể hiện rõ khi bạn xóa dòng. Trong ảnh dưới đây, cột D là số gõ tay để so sánh. Sau khi bạn bấm chuột phải vào số 5 ở cột số dòng bên trái, tức dòng của Phạm Thu Dung, rồi chọn Delete, cột A tự đánh lại từ 1 tới 9, còn cột D nhảy từ 3 lên 5.

Công thức =ROW()-1 tự đánh lại số thứ tự sau khi xóa dòng, còn cột số gõ tay nhảy từ 3 lên 5
Sau khi xóa dòng của Phạm Thu Dung, cột A dùng =ROW()-1 vẫn liền mạch, còn cột D gõ tay bị mất số 4

Một số hướng dẫn dùng =ROW(A1), và công thức này cũng cho ra 1, 2, 3 nhưng dễ lỗi hơn. Khi bạn xóa dòng 5, công thức =ROW(A5) ở dòng ngay dưới mất ô A5 mà nó tham chiếu tới, nên báo lỗi #REF!.

Cách 4: Hàm SEQUENCE tạo cả dãy số theo số dòng dữ liệu

Nếu dùng Excel 365 hoặc Excel 2021, bạn có thể tạo cả cột STT bằng một công thức duy nhất ở ô A2: =SEQUENCE(COUNTA(B2:B1000)). Hàm COUNTA đếm số ô có dữ liệu trong vùng B2:B1000, ở đây là 10 tên, và vùng kéo dài tới dòng 1000 để chừa chỗ cho tên thêm sau. Hàm SEQUENCE(10) sau đó tạo dãy 10 số từ 1 tới 10.

Công thức trả về nhiều giá trị như vậy gọi là mảng tràn (spill): kết quả bắt đầu ở ô A2 rồi tự tràn xuống các ô bên dưới, nên bạn không cần kéo công thức. Khi bạn gõ thêm tên Lý Thanh Phong vào ô B12, cột STT tự giãn thêm số 11.

Hàm SEQUENCE kết hợp COUNTA đánh số thứ tự tự động trong Excel, kết quả tràn từ ô A2 xuống ô A12
Chỉ ô A2 chứa công thức, các ô từ A3 tới A12 là kết quả tràn và tự giãn khi bạn thêm tên mới

Bạn cần lưu ý ba điểm khi dùng SEQUENCE:

  • Các ô trong vùng tràn phải trống. Nếu ô A6 đang chứa chữ, ô A2 báo lỗi #SPILL!, và dãy số hiện lại ngay khi bạn xóa nội dung ô A6.

  • Dòng trống xen giữa danh sách làm STT thiếu số. Nếu ô B6 trống, COUNTA chỉ đếm được 10 tên nên dãy dừng ở dòng 11, trong khi dòng 6 trống vẫn nhận số 5 còn tên cuối cùng ở dòng 12 không có số.

  • Vùng sắp xếp không được chứa cột SEQUENCE. Nếu bạn sắp xếp cả vùng có cột A, Excel báo "You can't change part of an array", nên bạn chỉ bôi đen các cột B và C, và chọn Continue with the current selection nếu Excel hỏi có mở rộng vùng chọn không.

Excel 2019 trở về trước không có hàm SEQUENCE, nên bạn dùng hàm ROW ở cách 3 thay thế.

Cách 5: Hàm IF kết hợp COUNTA bỏ qua dòng trống

Một số danh sách có dòng trống để ngăn cách các nhóm, và bạn không muốn dòng trống nhận số. Bạn gõ =IF(B2="","",COUNTA($B$2:B2)) vào ô A2 rồi kéo xuống tới dòng cuối.

Phần $B$2:B2 là một vùng mở rộng dần. Dấu $ đặt trước chữ cột và số dòng tạo tham chiếu tuyệt đối, nên điểm đầu $B$2 luôn đứng yên khi bạn kéo công thức, còn điểm cuối B2 đổi theo dòng, ví dụ ở ô A5 vùng này trở thành $B$2:B5. Nhờ vậy, COUNTA đếm số tên từ dòng 2 tới dòng hiện tại. Bạn không cần gõ dấu $ bằng tay mà nhấn F4 khi con trỏ đang nằm ở chữ B2 đầu tiên. Hàm IF kiểm tra trước: nếu ô ở cột B trống, IF trả về "", tức một chuỗi rỗng khiến ô trông như trống, còn nếu có tên, IF trả về kết quả của COUNTA.

Dòng

STT bằng IF và COUNTA (cột A)

Họ và tên (cột B)

So sánh với =ROW()-1

2

1

Nguyễn Văn An

1

3

2

Trần Thị Bình

2

4

(trống)

(trống)

3

5

3

Lê Hoàng Cường

4

6

4

Phạm Thu Dung

5

7

(trống)

(trống)

6

8

5

Hoàng Minh Đức

7

Công thức ROW()-1 vẫn đánh số cho dòng trống, còn cột A bỏ qua dòng 4 và dòng 7 rồi đếm tiếp.

Cách 6: Hàm COUNTIF đánh số theo nhóm, mỗi nhóm bắt đầu lại từ 1

Khi cần biết mỗi nhân viên là người thứ mấy trong phòng của mình, bạn gõ tiêu đề "STT trong phòng" ở ô D1, gõ =COUNTIF($C$2:C2,C2) vào ô D2 rồi nhấp đúp vào nút điền.

Hàm COUNTIF đếm số ô trong một vùng có giá trị bằng điều kiện bạn đưa vào. Vùng $C$2:C2 mở rộng dần giống cách 5, nên ở ô D7, công thức trở thành =COUNTIF($C$2:C7,C7) và đếm xem chữ "Kế toán" ở ô C7 đã xuất hiện bao nhiêu lần từ dòng 2 tới dòng 7. Kết quả là 3, vì Vũ Thị Hà là người thứ ba của phòng Kế toán tính từ trên xuống.

Đánh số thứ tự theo phòng ban bằng công thức =COUNTIF($C$2:C2,C2), mỗi phòng bắt đầu lại từ 1
COUNTIF đếm số lần phòng ban của dòng hiện tại đã xuất hiện tính từ dòng 2 tới dòng đó

Danh sách không cần sắp xếp trước: phòng Kế toán nằm rải rác ở các dòng 2, 4, 7 và 11 nhưng vẫn được đánh 1, 2, 3, 4. Bài đếm tần suất xuất hiện trong Excel giới thiệu thêm các cách dùng COUNTIF.

Cách 7: Hàm SUBTOTAL giữ số thứ tự liên tục khi bạn lọc dữ liệu

Khi bạn lọc danh sách bằng Filter, các dòng không khớp bị ẩn đi, và cột STT dùng ROW hay số gõ tay hiện các số nhảy cóc. Để các dòng đang hiện luôn được đánh 1, 2, 3, bạn gõ =SUBTOTAL(3,$B$2:B2)*1 vào ô A2 rồi kéo xuống tới ô A11.

Hàm SUBTOTAL chỉ tính trên các dòng đang hiện. Số 3 là mã của phép đếm ô có dữ liệu, giống hàm COUNTA, còn vùng $B$2:B2 mở rộng dần như cách 5. Bạn bật bộ lọc bằng Ctrl + Shift + L, bấm vào mũi tên ở ô C1 rồi chỉ đánh dấu Kinh doanh. Cột số dòng bên trái lúc này chỉ còn 1, 3, 6, 8, 10, còn cột A đánh lại từ 1 tới 4.

Đánh số thứ tự liên tục khi lọc dữ liệu bằng =SUBTOTAL(3,$B$2:B2)*1, các dòng 3, 6, 8, 10 hiện số 1 tới 4
Sau khi lọc phòng Kinh doanh, cột A đánh lại 1, 2, 3, 4, còn cột D dùng =ROW()-1 vẫn giữ số cũ

Phần *1 nhân kết quả với 1 nên không làm đổi số, nhưng giúp bộ lọc hoạt động đúng ở dòng cuối. Với =SUBTOTAL(3,$B$2:B2) không nhân 1, dòng của Ngô Thị Nga thuộc phòng Kế toán vẫn hiện ra khi lọc Kinh doanh, vì Excel xem dòng cuối chứa SUBTOTAL như một dòng tổng cộng. Khi công thức nhân thêm 1, hoặc khi bạn dùng =AGGREGATE(3,5,$B$2:B2) với số 3 là phép đếm và số 5 là lựa chọn bỏ qua dòng ẩn, dòng cuối được lọc như mọi dòng khác. Nếu thay số 3 bằng 103, công thức bỏ qua thêm các dòng bạn tự ẩn bằng lệnh Hide. Bài tính tổng khi lọc dữ liệu trong Excel giải thích thêm các mã của SUBTOTAL.

Cách 8: Hàm TEXT tạo mã có tiền tố như NV001, KH0001

Nhiều công ty dùng mã nhân viên dạng NV001 hay mã khách hàng dạng KH0001. Bạn gõ ="NV"&TEXT(ROW()-1,"000") vào ô A2 rồi kéo xuống. Phần ROW()-1 tạo số 1, 2, 3 như cách 3. Hàm TEXT đổi số thành chữ theo định dạng "000", tức luôn đủ 3 chữ số và thêm số 0 vào đầu khi thiếu, rồi dấu & nối chữ NV với kết quả đó.

Công thức ở ô A2

Kết quả ở ô A2

Kết quả ở ô A11

="NV"&TEXT(ROW()-1,"000")

NV001

NV010

="KH"&TEXT(ROW()-1,"0000")

KH0001

KH0010

="NV"&ROW()-1

NV1

NV10

Thiếu hàm TEXT, mã không có số 0 ở đầu như dòng cuối của bảng. Khi danh sách vượt quá 999 người, mã thứ 1.000 hiện NV1000 chứ không bị cắt bớt. Bài chuyển số thành text trong Excel giải thích thêm các định dạng của hàm này.

Cách 9: Hàm MAX và Ctrl + Enter đánh số cho ô đã gộp

Trong danh sách chia theo phòng ban, cột STT và cột Phòng ban thường được gộp ô (Merge Cells). Khi các ô gộp có kích thước khác nhau, ví dụ phòng Kế toán gộp 3 dòng còn phòng Nhân sự gộp 2 dòng, Excel báo "To do this, all the merged cells need to be the same size" nếu bạn kéo nút điền. Bạn làm như sau:

  1. Bạn bôi đen toàn bộ vùng STT, ở ví dụ này là A2:A11.

  2. Bạn gõ =MAX($A$1:A1)+1 nhưng chưa nhấn Enter.

  3. Bạn nhấn Ctrl + Enter để đưa cùng một công thức vào mọi ô đang chọn, và Excel tự đổi tham chiếu theo vị trí của từng ô.

Hàm MAX tìm số lớn nhất trong vùng từ ô A1 tới ô ngay phía trên. Ở ô A2, vùng $A$1:A1 chỉ chứa chữ STT, MAX bỏ qua chữ và trả về 0, nên kết quả là 1. Ở ô gộp A5, vùng $A$1:A4 có số lớn nhất là 1, nên kết quả là 2. Tương tự, ô A7 ra 3 và ô gộp A8 ra 4.

Đánh số thứ tự cho các ô đã gộp có kích thước khác nhau bằng =MAX($A$1:A1)+1 và Ctrl + Enter
Hàm MAX tìm số lớn nhất ở phía trên rồi cộng thêm 1, nên mỗi ô gộp nhận số tiếp theo

Khi bạn xóa cả nhóm Nhân sự ở dòng 5 và 6, hai nhóm phía dưới tự đổi thành 2 và 3.

Cách 10: Bảng Excel tự điền công thức số thứ tự cho dòng mới

Nếu danh sách thường xuyên có người mới, bạn chuyển vùng dữ liệu thành Bảng (Table):

  1. Bạn bấm vào một ô bất kỳ trong vùng A1:C11 rồi nhấn Ctrl + T.

  2. Bạn kiểm tra ô vuông My table has headers đã có dấu tích, rồi bấm OK.

  3. Bạn gõ =ROW()-1 vào ô A2 và nhấn Enter, và Excel tự điền công thức xuống cả cột A của Bảng.

Khi bạn gõ tên mới vào ô B12 ngay dưới Bảng, Bảng tự mở rộng thêm một dòng và ô A12 hiện số 11. Dòng bạn chèn vào giữa Bảng cũng tự nhận công thức. Nếu Bảng không bắt đầu ở dòng 1, bạn dùng =ROW()-ROW(Table1[#Headers]), trong đó Table1 là tên Bảng hiện ở thẻ Table Design, mục Table Name, và phần ROW(Table1[#Headers]) trả về số dòng của dòng tiêu đề. Bài phím tắt Excel thông dụng tổng hợp thêm các tổ hợp phím như Ctrl + T và Ctrl + Enter.

Bạn nên dùng cách nào khi nào

Bảng dưới đây giúp bạn chọn nhanh cách phù hợp:

Tình huống của bạn

Cách nên dùng

Công thức hoặc thao tác

Danh sách ngắn, ít thay đổi

Cách 1: kéo chuột

Gõ 1, 2 rồi kéo

Bạn cần sẵn 1.000 số

Cách 2: Fill Series

Home > Fill > Series

Danh sách hay bị xóa dòng, sắp xếp

Cách 3: hàm ROW

=ROW()-1

Bạn dùng Excel 365

Cách 4: SEQUENCE

=SEQUENCE(COUNTA(B2:B1000))

Danh sách có dòng trống

Cách 5: IF và COUNTA

=IF(B2="","",COUNTA($B$2:B2))

Mỗi phòng đánh số riêng

Cách 6: COUNTIF

=COUNTIF($C$2:C2,C2)

Bạn hay lọc dữ liệu

Cách 7: SUBTOTAL

=SUBTOTAL(3,$B$2:B2)*1

Bạn cần mã NV001

Cách 8: TEXT

="NV"&TEXT(ROW()-1,"000")

Cột STT có ô gộp

Cách 9: MAX

=MAX($A$1:A1)+1, Ctrl + Enter

Danh sách hay thêm dòng mới

Cách 10: Bảng

Ctrl + T, =ROW()-1

Những lỗi thường gặp khi đánh số thứ tự trong Excel

Bạn kéo chuột nhưng Excel chỉ chép toàn số 1

Khi bạn kéo một ô chứa số 1 mà không giữ Ctrl, hoặc nhấn Ctrl + D, Excel chép giá trị. Bạn sửa bằng Auto Fill Options và Fill Series như cách 1.

Số gõ tay bị đứt quãng hoặc xáo trộn sau khi xóa dòng và sắp xếp

Số gõ tay là giá trị cố định, nên chúng không tự đánh lại khi bạn xóa dòng và đi theo từng người khi bạn sắp xếp. Trong ảnh dưới đây, danh sách đã được sắp xếp theo phòng ban: cột A dùng =ROW()-1 vẫn hiện 1 tới 10, còn cột D gõ tay thành 1, 3, 6, 10, 2 và tiếp tục lộn xộn.

So sánh số thứ tự bằng công thức ROW và số gõ tay sau khi sắp xếp danh sách theo phòng ban
Sau khi sắp xếp theo phòng ban, cột A dùng công thức vẫn hiện 1 tới 10, còn cột D gõ tay bị xáo trộn

Công thức ROW()-1 lệch số khi bảng không bắt đầu ở dòng 2

Khi tiêu đề cột nằm ở dòng 4, =ROW()-1 ở ô A5 cho ra 4 thay vì 1. Bạn trừ đúng số dòng của tiêu đề bằng =ROW()-4, hoặc dùng =ROW()-ROW($A$4) để Excel tự lấy số dòng của ô tiêu đề A4.

Công thức đếm cả dòng tiêu đề làm STT bắt đầu từ 2

Nếu bạn viết =COUNTA($B$1:B2) ở ô A2, vùng đếm bắt đầu từ ô tiêu đề B1, nên ô A2 ra 2 và cả cột lệch thêm 1. Bạn sửa điểm đầu của vùng thành ô dữ liệu đầu tiên, tức =COUNTA($B$2:B2). Công thức SUBTOTAL ở cách 7 cũng lệch như vậy nếu vùng bắt đầu từ $B$1.

Câu hỏi thường gặp về đánh số thứ tự tự động trong Excel

Làm sao để số thứ tự bắt đầu từ một số khác 1?

Bạn đổi phần cộng trừ trong công thức ROW, ví dụ =ROW()+99 ở ô A2 cho ra 101, rồi tới 102, 103 ở các dòng dưới. Với SEQUENCE, bạn thêm số bắt đầu vào đối số thứ ba: =SEQUENCE(COUNTA(B2:B1000),1,101) tạo dãy bắt đầu từ 101.

Làm sao giữ cố định số thứ tự để chúng không thay đổi nữa?

Bạn bôi đen cột STT, nhấn Ctrl + C, rồi nhấn Ctrl + Alt + V, chọn Values và bấm OK. Excel thay công thức bằng con số đang hiện, nên các số không đổi khi bạn sắp xếp hay xóa dòng về sau.

Dòng mới chèn vào có tự nhận số thứ tự không?

Với các công thức ROW, COUNTIF hay SUBTOTAL, dòng mới chèn vào giữa bảng chưa có công thức, nên bạn chép công thức từ dòng phía trên xuống. Bảng ở cách 10 tự điền công thức cho dòng mới, còn hàm SEQUENCE ở cách 4 tự giãn dãy số khi bạn gõ tên vào dòng đó.

Bạn muốn dùng Excel thành thạo cho công việc văn phòng?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.