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 ô
Bạn gõ số 1 vào ô A2 và số 2 vào ô A3.
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ị.
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.

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:
Bạn gõ số 1 vào ô A2 rồi chọn lại ô A2.
Bạn vào thẻ Home, bấm Fill trong nhóm Editing rồi chọn Series.
Bạn chọn Columns ở mục Series in để điền theo cột, và chọn Linear ở mục Type.
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.
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:
Bạn gõ
=ROW()-1vào ô A2 rồi nhấn Enter, và ô A2 hiện 1.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.
Ô 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.

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.

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.

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.

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 |
|---|---|---|
| NV001 | NV010 |
| KH0001 | KH0010 |
| 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:
Bạn bôi đen toàn bộ vùng STT, ở ví dụ này là A2:A11.
Bạn gõ
=MAX($A$1:A1)+1nhưng chưa nhấn Enter.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.

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):
Bạn bấm vào một ô bất kỳ trong vùng A1:C11 rồi nhấn Ctrl + T.
Bạn kiểm tra ô vuông My table has headers đã có dấu tích, rồi bấm OK.
Bạn gõ
=ROW()-1và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 |
|
Bạn dùng Excel 365 | Cách 4: SEQUENCE |
|
Danh sách có dòng trống | Cách 5: IF và COUNTA |
|
Mỗi phòng đánh số riêng | Cách 6: COUNTIF |
|
Bạn hay lọc dữ liệu | Cách 7: SUBTOTAL |
|
Bạn cần mã NV001 | Cách 8: TEXT |
|
Cột STT có ô gộp | Cách 9: MAX |
|
Danh sách hay thêm dòng mới | Cách 10: Bảng | Ctrl + T, |
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.

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.