Cách tạo bảng lương cơ bản bằng Excel (kèm công thức tính BHXH, thực lĩnh)

Bảng lương cơ bản trong Excel cần những cột nào?

Với doanh nghiệp nhỏ hoặc hộ kinh doanh chưa dùng phần mềm nhân sự riêng, một bảng lương làm bằng Excel chỉ cần đủ các cột sau là có thể tính lương hàng tháng chính xác:

• Thông tin nhân viên: STT, Họ và tên, Chức vụ.
• Thu nhập: Lương cơ bản, Phụ cấp (ăn trưa, xăng xe, điện thoại...).
• Khấu trừ: BHXH, BHYT, BHTN (phần người lao động phải đóng).
• Kết quả: Tổng thu nhập, Thực lĩnh (số tiền nhân viên thực nhận).

Bài này hướng dẫn dựng đúng cấu trúc này bằng công thức Excel, không cần macro hay add-in gì thêm.

Bước 1: Dựng khung bảng và nhập tiêu đề cột

Mở một sheet mới, nhập tiêu đề cột ở dòng 1 theo đúng thứ tự tính toán (từ trái qua phải là các cột dùng để tính ra cột bên phải nó):

Cột	Tiêu đề
A	STT
B	Họ và tên
C	Chức vụ
D	Lương cơ bản
E	Phụ cấp
F	Tổng thu nhập
G	Khấu trừ BHXH/BHYT/BHTN
H	Thực lĩnh

Bôi đen dòng 1, vào Home → Font, bật Bold, sau đó vào View → Freeze Panes → Freeze Top Row để tiêu đề luôn hiển thị khi cuộn xuống các dòng nhân viên phía dưới.

Bước 2: Nhập dữ liệu và công thức tính từng cột

Nhập lương cơ bản, phụ cấp cho từng người ở cột D và E (đây là số liệu nhập tay, lấy từ hợp đồng lao động). Ví dụ dữ liệu mẫu:

STT	Họ và tên	Chức vụ	Lương cơ bản	Phụ cấp
1	Nguyễn Văn A	Nhân viên kinh doanh	8,000,000	700,000
2	Trần Thị B	Kế toán	9,000,000	500,000
3	Lê Văn C	Nhân viên kho	7,000,000	500,000

Cột F — Tổng thu nhập

Tại ô F2, nhập:

=SUM(D2:E2)

Công thức này cộng lương cơ bản và phụ cấp. Dùng SUM thay vì D2+E2 để khi sau này cần thêm cột phụ cấp khác (ví dụ thêm cột "Thưởng"), chỉ cần mở rộng vùng SUM mà không phải viết lại công thức cộng tay.

Cột G — Khấu trừ BHXH/BHYT/BHTN

Theo quy định hiện hành, người lao động trích một phần lương để đóng bảo hiểm, mức phổ biến là 8% BHXH + 1.5% BHYT + 1% BHTN = 10.5%, tính trên lương đóng BHXH (thường là lương cơ bản ghi trong hợp đồng, không tính phụ cấp). Tại ô G2, nhập:

=D2*10.5%

Lưu ý: tỷ lệ này áp dụng theo quy định bảo hiểm xã hội tại thời điểm tính lương — nên đối chiếu lại với quy định mới nhất trước khi dùng cho bảng lương thật, vì tỷ lệ đóng có thể thay đổi theo từng giai đoạn.

Cột H — Thực lĩnh

Tại ô H2, nhập:

=F2-G2

Thực lĩnh = Tổng thu nhập trừ đi phần khấu trừ bảo hiểm. Nếu công ty còn khấu trừ thêm khoản khác (tạm ứng, phạt đi muộn...), thêm cột riêng rồi trừ tiếp vào công thức này.

Sao chép công thức xuống các dòng còn lại

Bôi đen vùng F2:H2, đưa chuột tới góc dưới bên phải ô H2 tới khi con trỏ thành dấu +, rồi kéo xuống hết danh sách nhân viên (hoặc double-click vào dấu + để Excel tự điền hết theo chiều dài cột tên).

Bước 3: Tính tổng cộng ở dòng cuối bảng

Thêm một dòng "Tổng cộng" ngay dưới nhân viên cuối cùng. Giả sử danh sách kết thúc ở dòng 4, tại ô F5 (và tương tự cho G5, H5), nhập:

=SUM(F2:F4)

Dòng tổng này giúp đối chiếu nhanh tổng quỹ lương phải chi trong tháng mà không cần cộng tay từng nhân viên.

Bước 4: Định dạng số tiền cho dễ đọc

Số tiền lương thường là số lớn (7-9 chữ số), nếu để mặc định sẽ khó đọc và dễ đếm nhầm số 0. Cách định dạng:

1. Bôi đen toàn bộ vùng số tiền (cột D đến H).
2. Bấm chuột phải → Format Cells (hoặc Ctrl + 1).
3. Chọn tab Number → mục Number → đặt Decimal places: 0, tick Use 1000 Separator (,).
4. Bấm OK.

Số sẽ tự động hiện dấu phẩy ngăn cách hàng nghìn (ví dụ 8,000,000) mà không làm thay đổi giá trị thật để tính toán.

Mẹo: Dùng Conditional Formatting để kiểm tra nhanh

Muốn tự động tô màu cảnh báo khi thực lĩnh của ai đó thấp bất thường (ví dụ do nghỉ nhiều, bị trừ nhiều):

1. Bôi đen cột Thực lĩnh (H2:H4).
2. Vào Home → Conditional Formatting → Highlight Cells Rules → Less Than...
3. Nhập mức lương cần cảnh báo (ví dụ 5000000), chọn màu tô, bấm OK.

Excel sẽ tự tô màu bất kỳ ô nào trong vùng đó thấp hơn mức bạn đặt, giúp rà soát bảng lương lớn nhanh hơn thay vì đọc từng dòng.

Lỗi thường gặp

Kéo công thức xuống nhưng kết quả sai lệch dần theo từng dòng Thường do công thức tham chiếu nhầm sang ô tuyệt đối cần cố định (ví dụ tỷ lệ BHXH nằm ở một ô riêng như $J$1 thay vì gõ cứng 10.5% trong từng công thức) nhưng chưa cố định bằng dấu $. Nếu tách tỷ lệ ra ô riêng để dễ chỉnh sửa sau này, nhớ dùng phím F4 để thêm $ trước khi kéo công thức.

Tổng cộng dòng cuối bị lệch, thiếu hoặc thừa một dòng Do vùng SUM không khớp với số dòng nhân viên thực tế — hay gặp khi có người mới thêm vào giữa bảng nhưng công thức SUM ở dòng tổng không tự mở rộng theo. Kiểm tra lại vùng tham chiếu trong công thức SUM mỗi khi thêm/xóa nhân viên.

Số tiền hiển thị dạng khoa học (ví dụ 8E+06) hoặc bị cắt bớt Do cột quá hẹp so với số. Bấm đúp vào đường viền giữa hai tiêu đề cột (ví dụ giữa cột D và E) để Excel tự giãn độ rộng cột vừa với nội dung.

Nhầm lẫn giữa "lương cơ bản" và "lương đóng BHXH" Ở nhiều công ty, lương đóng BHXH có thể khác lương cơ bản ghi trên bảng lương thực nhận (do quy định mức đóng tối thiểu/tối đa). Nếu công ty tách hai khoản này, nên thêm cột riêng "Lương đóng BHXH" thay vì lấy đại cột "Lương cơ bản" để tính cột G, tránh tính sai số tiền khấu trừ.

Khi nào nên nâng cấp lên bảng lương phức tạp hơn?

Cấu trúc trên phù hợp với đội ngũ nhỏ, cách tính đơn giản. Khi quy mô lớn hơn hoặc cách tính phức tạp hơn, có thể cần thêm:

• Hàm IF để tính thuế thu nhập cá nhân theo bậc lũy tiến.
• Hàm VLOOKUP/XLOOKUP để tự động lấy lương cơ bản theo chức vụ từ một bảng lương chuẩn riêng, tránh nhập tay từng người.
• Cột chấm công (số ngày làm việc thực tế) nhân với đơn giá ngày công thay vì lấy nguyên lương tháng.

Những phần này thuộc nhóm công thức nâng cao hơn, nên nắm chắc SUM, tham chiếu tuyệt đối và định dạng số như trong bài trước khi học tiếp.

Học Excel thực chiến tại TP Vinh

Làm bảng lương chỉ là một trong rất nhiều tình huống dùng Excel thực tế trong công việc văn phòng. Nếu bạn muốn học Excel bài bản từ công thức cơ bản đến xử lý dữ liệu, làm báo cáo tự động, tham khảo khóa học Excel tại Điền Nguyễn AZ.

📞 Hotline / Zalo: 0362 465 361

👉 Xem chi tiết: Khóa học Excel thực tế tại TP Vinh