Data Cleaning di Excel: Langkah Rapi Sebelum Analisis
Data cleaning di Excel adalah proses merapikan data mentah sebelum dianalisis: menghapus duplikat, membuang spasi berlebih, menyeragamkan format tanggal dan angka, lalu memisah atau menggabung kolom. Kalau langkah ini dilewati, pivot table dan rumus Anda akan memberi angka yang salah tanpa terlihat error.
Artikel ini untuk admin dan analis yang setiap minggu menerima file dari berbagai sumber: export dari aplikasi kasir, rekap dari cabang, laporan dari marketplace. Kita bahas masalah yang paling sering muncul dan cara membereskannya, dari yang manual sampai yang otomatis. Kalau Anda ingin memahami konsep umumnya dulu, baca data cleaning adalah.
Masalah data yang paling sering muncul
Sebelum membersihkan, kenali dulu jenis kotorannya. Ini yang paling sering ditemui di data bisnis Indonesia:
- Duplikat. Transaksi yang sama tercatat dua kali karena file digabung manual.
- Spasi tersembunyi. “Toko Maju “ dan “Toko Maju” terlihat sama, tapi dianggap berbeda oleh Excel.
- Tanggal campur format. Ada yang 01/10/2026, ada yang 10/01/2026, ada yang “1 Okt 2026”, dan sebagian tersimpan sebagai teks.
- Angka tersimpan sebagai teks. Biasanya dari export sistem, ditandai segitiga hijau kecil di pojok sel. Rumus SUM tidak menghitungnya.
- Pemisah desimal berbeda. Rp1.250.000 dari satu cabang, 1,250,000 dari cabang lain.
- Penulisan tidak konsisten. “Jakarta”, “JKT”, “DKI Jakarta” untuk kota yang sama.
Satu kebiasaan penting: jangan pernah membersihkan data di file asli. Simpan salinan data mentah di sheet terpisah, lalu kerjakan di sheet baru. Kalau ada yang salah, Anda bisa mulai lagi.
Hapus duplikat dan spasi
Duplikat. Cara tercepat adalah menu Data > Remove Duplicates (Hapus Duplikat). Pilih kolom yang menentukan keunikan, misalnya nomor invoice. Hati-hati: kalau Anda mencentang semua kolom, baris yang hanya berbeda di satu kolom tidak dianggap duplikat.
Sebelum menghapus, sebaiknya tandai dulu dengan rumus agar bisa dicek:
=COUNTIF($A$2:$A$5000; A2)>1
Hasil TRUE berarti nomor tersebut muncul lebih dari sekali. Catatan: pemisah argumen di Excel dengan pengaturan regional Indonesia adalah titik koma, di pengaturan Inggris memakai koma.
Spasi. Fungsi TRIM membuang spasi di awal, di akhir, dan spasi ganda di tengah. Fungsi CLEAN membuang karakter yang tidak tercetak, misalnya line break dari sistem lain. Keduanya bisa digabung:
=TRIM(CLEAN(A2))
Kalau TRIM tidak berhasil, kemungkinan ada spasi jenis lain (non-breaking space) yang sering terbawa dari data web. Ganti dulu dengan SUBSTITUTE(A2; CHAR(160); " ") sebelum di-TRIM.
Standarisasi tanggal dan angka
Bagian ini yang paling sering membuat laporan salah tanpa ketahuan.
Tanggal. Cek dulu apakah tanggal benar-benar berupa tanggal. Caranya, ubah format sel ke General. Tanggal asli akan berubah jadi angka (misalnya 46296). Kalau tetap tampil sebagai teks, berarti Excel belum mengenalinya.
Untuk tanggal teks, beberapa pilihan:
- Text to Columns. Pilih kolom, buka Data > Text to Columns, lanjut sampai langkah terakhir, lalu pilih format kolom Date dengan urutan yang sesuai, misalnya DMY. Cara ini cepat untuk satu kolom.
- Rumus
DATE. Kalau formatnya konsisten, misalnya “01-10-2026”, pecah denganLEFT,MID, danRIGHT, lalu susun denganDATE(tahun; bulan; hari). - Power Query. Paling andal untuk data rutin, dibahas di bagian akhir.
Waspadai tanggal yang ambigu. 03/10/2026 bisa berarti 3 Oktober atau 10 Maret. Tanyakan ke pengirim data format apa yang mereka pakai, jangan menebak.
Angka. Untuk angka yang tersimpan sebagai teks dengan pemisah yang berbeda, fungsi NUMBERVALUE sangat membantu karena Anda bisa menentukan pemisah desimal dan ribuannya:
=NUMBERVALUE(A2; ","; ".")
Rumus di atas membaca “1.250.000,50” sebagai angka satu juta dua ratus lima puluh ribu koma lima. Untuk teks yang mengandung “Rp”, buang dulu dengan SUBSTITUTE.
Memisah dan menggabung kolom
Data dari sistem sering menggabungkan beberapa informasi dalam satu kolom, misalnya “JKT-001-Toko Maju”. Atau sebaliknya, nama depan dan belakang terpisah padahal Anda butuh satu.
Pilihan untuk memisah kolom:
- Text to Columns dengan pemisah tanda hubung, koma, atau spasi.
- Flash Fill (Ctrl+E). Ketik contoh hasil yang Anda mau di satu atau dua baris, lalu tekan Ctrl+E. Excel meniru polanya. Cepat, tapi cek hasilnya karena Flash Fill bisa salah menebak pola.
TEXTSPLITdi Excel versi Microsoft 365, yang memecah teks ke beberapa kolom dengan satu rumus.
Untuk menggabung, pakai TEXTJOIN atau operator &:
=TEXTJOIN(" "; TRUE; B2; C2)
Untuk penulisan yang tidak konsisten seperti nama kota, buat tabel pemetaan kecil (kolom “tulisan asli” dan “tulisan baku”), lalu cocokkan dengan XLOOKUP atau VLOOKUP. Kalau tabel pemetaan dan data Anda sudah puluhan ribu baris dan rumus mulai lambat, baca VLOOKUP, XLOOKUP, atau database.
Otomatisasi dengan Power Query
Semua langkah di atas masuk akal untuk sekali jalan. Masalahnya, data yang sama biasanya datang lagi minggu depan, dengan kotoran yang sama. Mengulang langkah manual setiap minggu adalah pekerjaan yang paling layak diotomatisasi.
Power Query adalah fitur bawaan Excel (menu Data > Get Data) yang merekam setiap langkah pembersihan. Anda cukup menyusunnya sekali:
- Hubungkan ke file sumber atau ke satu folder berisi file dari semua cabang.
- Terapkan langkah: hapus baris kosong, Trim, hapus duplikat, ubah tipe data.
- Untuk tanggal dan angka dengan format Indonesia, pakai Change Type > Using Locale dan pilih Indonesian.
- Muat hasilnya ke sheet atau langsung ke pivot table.
Minggu depan, taruh file baru di folder yang sama, lalu klik Refresh. Semua langkah berjalan ulang dalam hitungan detik. Contoh: admin di sebuah distributor yang menerima rekap dari lima cabang bisa menggabung dan membersihkan semuanya dengan satu klik, bukan satu jam copy-paste. Panduan lengkapnya ada di Power Query Excel.
Kalau datanya sudah terlalu besar untuk Excel, atau sumbernya langsung dari aplikasi dan bukan file, pembersihan sebaiknya dipindah ke pipeline data yang berjalan otomatis. Itu salah satu pekerjaan yang kami tangani di bagian layanan data.
Kalau tim Anda masih menghabiskan berjam-jam setiap minggu untuk merapikan file sebelum laporan bisa dibuat, Aqilatic bisa membantu melihat bagian mana yang bisa diotomatisasi lewat audit proses gratis.
Pertanyaan yang sering muncul
Apa langkah pertama data cleaning di Excel?
Simpan salinan data mentah, lalu periksa jenis masalahnya: duplikat, spasi, format tanggal, dan angka yang tersimpan sebagai teks. Setelah tahu masalahnya, baru pilih cara membersihkan yang sesuai.
Kenapa rumus SUM hasilnya nol padahal ada angka?
Kemungkinan besar angkanya tersimpan sebagai teks. Ubah dengan VALUE atau NUMBERVALUE, atau pilih selnya lalu klik ikon peringatan dan pilih Convert to Number.
Apakah Power Query tersedia di semua versi Excel?
Power Query sudah menjadi fitur bawaan di Excel untuk Windows sejak versi 2016, dengan nama menu Get & Transform. Di Excel untuk Mac tersedia di Microsoft 365 dengan fitur yang lebih terbatas dibanding versi Windows.