Kerja kantorExcel untuk admin kantor
Rumus Excel menghitung gaji dan lembur karyawan: tabel gaji, upah lembur sesuai PP 35/2021, potongan BPJS, dan pembulatan Rupiah
Cara menyusun lembar gaji di Excel yang bisa diperiksa: satu baris satu karyawan, tunjangan dari tabel master dengan XLOOKUP, upah lembur hari kerja sesuai PP 35/2021 (upah sejam 1/173 upah sebulan), potongan BPJS Kesehatan, JHT, dan Jaminan Pensiun lengkap dengan batas upahnya, iuran bagian perusahaan, pembulatan Rupiah, dan pemeriksaan total sebelum slip dicetak.
5 menit baca
Daftar isi · 6 bagian
Lembar gaji adalah berkas Excel yang paling mahal bila salah: kekurangan bayar merusak kepercayaan, kelebihan bayar sulit ditarik kembali. Artikel ini menyusun lembar gaji yang setiap angkanya bisa ditelusuri ke satu rumus dan satu sumber aturan. Bagian lembur mengikuti Peraturan Pemerintah Nomor 35 Tahun 2021; bagian lain adalah struktur Excel yang bisa dipakai kebijakan gaji perusahaan mana pun. Penilaian redaksi di artikel ini berdasarkan sumber yang dicantumkan di bagian akhir.
Susunan tabel: satu baris satu karyawan
| Kolom | Isi | Sumber |
|---|---|---|
| A · NIK | Nomor karyawan, teks | Data karyawan |
| B · Nama | Teks | Data karyawan |
| C · Jabatan | Teks, ejaan sama persis dengan tabel master | Data karyawan |
| D · Gaji pokok | Angka | Kontrak kerja |
| E · Tunjangan jabatan | Rumus XLOOKUP dari tabel master | Kebijakan perusahaan |
| F · Jam lembur hari kerja | Angka jam bulat | Rekap lembur yang disetujui |
| G · Upah lembur | Rumus | PP 35/2021 |
| H · Potongan | Rumus BPJS (bagian di bawah) + PPh 21 + pinjaman | Perpres 64/2020, PP 45/2015, PP 46/2015, HR |
| I · Gaji bersih | Rumus D+E+G−H, dibulatkan | — |
Gaji pokok dan potongan diketik; tunjangan, lembur, dan gaji bersih selalu rumus. Aturan itu sederhana tetapi menentukan: angka yang diketik tangan di kolom hasil berhenti benar pada perubahan pertama, dan tidak ada yang tahu.
Tunjangan dari tabel master dengan XLOOKUP
Simpan besaran tunjangan per jabatan di lembar terpisah, misalnya tabel bernama Tunjangan dengan kolom Jabatan dan Nominal. Kolom E lalu mengambilnya, dan menulis 0 dengan tanda jelas bila jabatan tidak ditemukan, supaya salah ketik jabatan tidak diam-diam menghapus tunjangan seseorang.
=XLOOKUP(C2, Tunjangan[Jabatan], Tunjangan[Nominal], "CEK JABATAN") ' Excel tanpa XLOOKUP: =IFERROR(VLOOKUP(C2, Tunjangan!$A$2:$B$20, 2, FALSE), "CEK JABATAN")
Upah lembur hari kerja sesuai PP 35/2021
PP 35/2021 Pasal 32 ayat (2) menetapkan upah sejam sebesar 1/173 kali upah sebulan. Pasal 31 ayat (1) menetapkan upah lembur pada hari kerja: jam pertama 1,5 kali upah sejam, setiap jam berikutnya 2 kali upah sejam. Pasal 26 ayat (1) membatasi lembur paling lama 4 jam sehari dan 18 jam seminggu. Lembur pada hari istirahat mingguan dan hari libur resmi memakai pengali yang berbeda, diatur di Pasal 31 ayat (2); kalkulator lembur di menu Alat menghitung keempat keadaan itu.
Untuk lembur hari kerja dengan jam bulat, jumlah pengalinya adalah 1,5 untuk jam pertama ditambah 2 untuk setiap jam sesudahnya. Dengan upah sebulan di kolom D dan jam lembur di kolom F:
=IF(F2<=0, 0, (1.5 + (F2-1)*2) * D2/173) ' Contoh: upah sebulan Rp5.200.000, lembur 3 jam ' upah sejam = 5.200.000 / 173 = 30.057,80 ' pengali = 1,5 + 2 + 2 = 5,5 ' upah lembur = 5,5 × 30.057,80 = 165.317,92
Rumus di atas memakai gaji pokok di kolom D sebagai upah sebulan. Komponen upah yang menjadi dasar perhitungan juga diatur di Pasal 32; bila gaji di perusahaanmu terdiri dari beberapa tunjangan, baca pasal itu lengkap dan ganti D2 dengan dasar yang benar. PP 35/2021 juga tidak mengatur cara menghitung pecahan jam, jadi catat jam lembur dalam jam bulat sesuai kebijakan perusahaan yang tertulis.
Potongan BPJS dari gaji karyawan
Tiga iuran dipotong dari gaji karyawan penerima upah. BPJS Kesehatan 1% dari gaji atau upah sebulan (Perpres 64/2020, Pasal 30 ayat 1 Perpres 82/2018), dengan batas atas Rp12.000.000 dan batas bawah upah minimum provinsi atau kabupaten/kota, kecuali usaha mikro dan kecil (Perpres 59/2024, Pasal 32). Jaminan Hari Tua 2% dari upah (PP 46/2015 Pasal 16 ayat 1). Jaminan Pensiun 1% dari upah (PP 45/2015 Pasal 28 ayat 3), dengan upah pokok ditambah tunjangan tetap sebagai dasar dan batas upah tertinggi yang disesuaikan BPJS Ketenagakerjaan setiap tahun (Pasal 29).
' Upah pokok + tunjangan tetap di J2; sel bernama BatasJP berisi batas upah JP tahun berjalan ' BPJS Kesehatan 1% (batas atas Rp12.000.000) =ROUND(MIN(J2, 12000000) * 1%, 0) ' JHT 2% =ROUND(J2 * 2%, 0) ' Jaminan Pensiun 1% (batas atas BatasJP) =ROUND(MIN(J2, BatasJP) * 1%, 0) ' Contoh: upah Rp8.000.000 -> Kesehatan 80.000, JHT 160.000, JP 80.000 ' Upah Rp15.000.000 -> Kesehatan 120.000 (dibatasi Rp12 juta)
| Iuran bagian perusahaan (tidak dipotong dari gaji) | Besaran | Dasar |
|---|---|---|
| BPJS Kesehatan | 4% dari upah, batas Rp12.000.000 | Perpres 64/2020, Pasal 30 ayat 1 huruf a |
| Jaminan Hari Tua | 3,7% | PP 46/2015 Pasal 16 ayat 1 |
| Jaminan Pensiun | 2%, batas upah sama dengan di atas | PP 45/2015 Pasal 28 ayat 3 |
| Jaminan Kecelakaan Kerja | 0,24%–1,74% sesuai tingkat risiko | PP 44/2015 Pasal 16 ayat 1 |
| Jaminan Kematian | 0,30% | PP 44/2015 Pasal 18 |
Iuran Jaminan Kehilangan Pekerjaan juga tidak dipotong dari gaji: menurut BPJS Ketenagakerjaan iurannya dibayar Pemerintah Pusat dan rekomposisi iuran JKK dan JKM. Iuran bagian perusahaan untuk JKK, JKM, dan BPJS Kesehatan menambah penghasilan bruto untuk PPh 21, sedangkan iuran JHT dan Jaminan Pensiun bagian karyawan mengurangi penghasilan neto di hitungan Desember; rinciannya ada di artikel cara hitung PPh 21 karyawan.
Gaji bersih dan pembulatan Rupiah
=ROUND(D2 + E2 + G2 - H2, 0)
Bulatkan di kolom hasil akhir, bukan di setiap kolom antara, supaya selisih pembulatan tidak menumpuk. Kalau kebijakan perusahaan membulatkan ke ratusan terdekat, ganti argumen kedua ROUND dengan -2. Format sel menampilkan angka tanpa desimal, tetapi tidak mengubah nilainya; hanya ROUND yang benar-benar membulatkan.
Tiga pemeriksaan sebelum slip dicetak
- Hitung baris bermasalah: =COUNTIF(E2:E200, "CEK JABATAN") harus 0.
- Bandingkan total: =SUM(I2:I200) harus sama dengan =SUM(D2:D200)+SUM(E2:E200)+SUM(G2:G200)−SUM(H2:H200), dengan selisih paling banyak sebesar pembulatan.
- Ambil satu karyawan secara acak dan hitung lemburnya dengan kalkulator lembur. Kalau angkanya berbeda, rumusnya yang diperiksa, bukan kalkulatornya yang disesuaikan.
Fungsi yang dipakai di artikel ini dilatih di empat kelas: SUM dan ROUND di Excel dari Nol, IF dan COUNTIF di Rumus Logika dan Syarat, XLOOKUP di Mencari Data, dan rekap per bulan di Rekap dan Laporan Penjualan. Kelas Rumus Logika juga punya misi rekap absensi dan lembur dengan data latihan.
Lanjut dari sini
Tanya jawab
Pertanyaan yang sering muncul
- Apakah rumus lembur ini berlaku untuk hari libur?
- Tidak. Rumus di artikel ini hanya untuk lembur pada hari kerja biasa sesuai Pasal 31 ayat (1) PP 35/2021. Lembur pada hari istirahat mingguan atau hari libur resmi memakai pengali di Pasal 31 ayat (2), yang dihitung oleh kalkulator lembur di menu Alat.
- Kenapa pembaginya 173?
- PP 35/2021 Pasal 32 ayat (2) menetapkan upah sejam sebesar 1/173 kali upah sebulan. Angka 173 berasal dari peraturan itu, bukan pilihan redaksi, jadi jangan diganti dengan jumlah hari atau jam kerja sebenarnya di bulan tersebut.
- Bagaimana dengan potongan BPJS dan PPh 21?
- Potongan BPJS karyawan dihitung dengan rumus di bagian BPJS: Kesehatan 1% dengan batas Rp12.000.000, JHT 2%, dan Jaminan Pensiun 1% dengan batas upah tahunan. PPh 21 bulanan dihitung dengan tarif efektif PP 58/2023; gunakan kalkulator PPh 21 di menu Alat atau artikel cara hitung PPh 21 karyawan.
Tentang artikel ini
Untuk siapa: Admin, staf HR, dan pemilik usaha kecil yang menghitung gaji dan lembur karyawan setiap bulan di Excel dan ingin rumusnya benar serta mudah diperiksa.
Penulis: Irfan Akbar Wildani, S.Kom., pendiri dan pengelola tunggal kelasgratis. Memegang semua peran di KelasGratis: menulis dan memverifikasi materi terhadap dokumentasi resmi, menyusun soal dan pembahasan, mengajar, merancang tampilan, sampai membangun, merawat, dan membiayai situsnya. Setiap artikel mencantumkan sumbernya. Cara kami memverifikasi materi.
Sumber (13)
- JDIH BPK — PP Nomor 35 Tahun 2021 (Pasal 26, 31, dan 32) peraturan.bpk.go.id
- JDIH BPK — PP Nomor 58 Tahun 2023 tentang tarif pemotongan PPh Pasal 21 peraturan.bpk.go.id
- JDIH BPK — Perpres Nomor 64 Tahun 2020 (Pasal 30 iuran PPU BPJS Kesehatan) peraturan.bpk.go.id
- JDIH BPK — Perpres Nomor 59 Tahun 2024 (Pasal 32 batas gaji iuran PPU) peraturan.bpk.go.id
- JDIH BPK — PP Nomor 46 Tahun 2015 Jaminan Hari Tua (Pasal 16) peraturan.bpk.go.id
- JDIH BPK — PP Nomor 45 Tahun 2015 Jaminan Pensiun (Pasal 28–29) peraturan.bpk.go.id
- JDIH BPK — PP Nomor 44 Tahun 2015 JKK dan JKM (Pasal 16 dan 18) peraturan.bpk.go.id
- BPJS Ketenagakerjaan — Manfaat dan iuran penerima upah bpjsketenagakerjaan.go.id
- BPJS Ketenagakerjaan — Berapa besaran iuran JHT, JKK, JKM, JP, dan JKP bpjsketenagakerjaan.go.id
- Microsoft Support — Fungsi XLOOKUP support.microsoft.com
- Microsoft Support — Fungsi IF support.microsoft.com
- Microsoft Support — Fungsi ROUND support.microsoft.com
- Microsoft Support — Fungsi COUNTIF support.microsoft.com
