Excel sering digunakan untuk mengolah data operasional, penjualan, keuangan, dan bentuk laporan lainnya. Namun, data yang masuk ke Excel tidak selalu rapi karena berbagai sebab. Ada data yang formatnya tidak konsisten, mengandung karakter tersembunyi, duplikat, atau struktur tabel yang buruk.
Tujuan data cleaning atau pembersihan data di Excel adalah memperbaiki kesalahan yang ada, menghapus duplikasi, dan menyeragamkan informasi untuk meningkatkan akurasi dan keandalan data agar siap digunakan untuk laporan atau analisis.
Artikel ini membahas:
- penyebab umum data tidak rapi,
- kategori masalah data cleaning dan solusinya,
- langkah-langkah pembersihan data,
- contoh kasus lengkap,
- kapan harus beralih ke Power Query,
- tips melakukan data cleaning dengan aman dan efisien.
Tujuan artikel ini adalah membuat kita mampu membersihkan data dengan cepat, presisi, dan profesional.
File: data-cleaning-excel.xlsx
Penyebab Data Tidak Rapi
Memahami sumber masalah adalah penting sebelum melakukan pembersihan data.
Berikut beberapa kondisi yang mungkin mengapa data bisa tidak rapi atau berantakan.
Format Data Tidak Konsisten
Excel sangat sensitif terhadap format. Format yang berbeda dapat mengakibatkan kegagalan konsolidasi data.
Contoh:
- Data penjualan dari marketplace menggunakan format dd-MM-yyyy, sementara sistem internal menggunakan MM-dd-yyyy.
- Sistem eksternal dan internal menggunakan pemisah ribuan yang berbeda. Contoh: Rp 1.250 dan Rp 1,250.
Data Berisi Karakter Tidak Terlihat
Salah satu penyebab data memiliki karakter tidak terlihat adalah copy-paste dari PDF atau website tertentu.
Contoh:
- Nama pelanggan mengandung line break tersembunyi.
- Angka berubah menjadi teks karena simbol mata uang ikut tersalin.
Selain spasi, tab dan line break juga sering menjadi karakter tidak terlihat di dalam data.
Tidak Ada Validasi Data
Entri manual di Excel banyak dilakukan tanpa validasi. Akibatnya, setiap pengguna dapat memasukkan data apa pun, dengan format apapun, yang menyebabkan data memiliki banyak variasi entri.
Contoh:
- Jumlah penjualan dapat diisi “12”, “1 2”, atau “dua belas”.
- Barang dengan harga yang belum diketahui diisikan 0, “nol”, “Belum Ada”, atau – (tanda minus).
- “Jakarta Selatan”, “Jaksel”, atau “JKT SEL” secara terminologi mewakili daerah yang sama, yaitu Jakarta Selatan. Secara nilai digital, ketiganya dianggap berbeda.
Impor Data dari Sistem Lama
Sistem lama sering menyimpan data dalam format yang tidak sesuai dengan standar penyimpanan data saat ini.
Contoh:
- Tanggal disimpan sebagai teks “20240105”.
- Kolom angka berisi karakter aneh seperti  atau .
Duplikasi Data
Ada banyak sebab mengapa data mengalami duplikasi, seperti tidak adanya validasi atau konsolidasi yang baik.
Contoh:
- File Januari dan Februari sama-sama berisi transaksi tanggal 31 Januari.
- Pelanggan yang sama muncul dua kali dengan nama yang sedikit berbeda.
Struktur Tabel Buruk
Struktur buruk membuat data sulit dibaca.
Contoh:
- Baris atau kolom data memiliki merged cell.
- Cell dengan data wajib tapi kosong.
Scratch Data, Stray Data, Temporary Data, dan Helper Cell
Siapa pun yang menggunakan Excel, biasa menggunakan sembarang cell sebagai wadah penampung sementara, pembantu perhitungan, atau tujuan lainnya.
Semakin banyak cell-cell seperti ini, semakin besar sampah yang menumpuk dan dapat mengganggu kinerja workbook, seperti menyimpan file jadi lama.
Kesalahan Pengkodean Saat Impor File
Terjadi saat membuka CSV dari sistem yang berbeda.
Contoh:
- Nama berubah menjadi karakter aneh: “Rizki” → “RizkÔ.
- Tanda koma berubah menjadi titik atau sebaliknya.
Kategori Masalah Data Cleaning dan Solusinya
Masalah Format
Format data yang tidak seragam dapat menyebabkan data sulit diubah ke format lain, tidak dapat diproses sebagai mana mestinya di dalam rumus, serta masalah-masalah lainnya.
Contoh:
- Angka disimpan sebagai teks.
- Tanggal tidak dikenali Excel.
- Format ribuan dan desimal tidak konsisten.
- Angka rata kiri, bukan kanan.
- Tanggal tidak berubah meski format diganti.
- Setiap pengguna memiliki gaya penandaan sendiri-sendiri.
Solusi:
- Gunakan fungsi VALUE untuk mengubah angka dalam format teks menjadi angka yang sesungguhnya.
- Gunakan fungsi DATEVALUE untuk melihat apakah nilai tanggal sesuai ketentuan Excel atau tidak.
- Text to Columns untuk memecah teks menjadi kolom yang berkesesuaian.
- Input angka tanpa pemisah ribuan. Terapkan Number Format untuk format yang diinginkan.
- Gunakan fungsi TIME atau DATE untuk membentuk nilai waktu dan tanggal, lalu gunakan format Time atau Date yang sesuai.
- Gunakan Power Query untuk transformasi data.
- Gunakan conditional formatting atau cell style.
Karakter-karakter Tidak Terlihat
Karakter tidak terlihat umumnya berasal dari copy-paste dari PDF, WhatsApp, atau website atau dari input manual.
Contoh:
- Spasi berlebih (leading, trailing, multiple spaces).
- Karakter tersembunyi (line break, tab, non‑printable).
- Huruf besar atau kecil tidak konsisten.
- Nama atau kategori terlihat sama tapi dianggap berbeda.
Solusi:
- Gunakan TRIM untuk menghapus kelebihan spasi.
- Gunakan CLEAN untuk menghilangkan karakter yang tidak terlihat saat worksheet dicetak.
- Gunakan SUBSTITUTE untuk mengganti karakter-karakter tertentu.
- Power Query untuk membersihkan memotong, membersihkan, dan menggantikan nilai.
Label dan Kategori Tidak Konsisten
Masalah ini membuat PivotTabel atau filter memiliki kategori lebih dari yang seharusnya.
Contoh:
- “Jakarta Selatan”, “Jaksel”, “Jakarta selatan”, “JKT SEL”.
- “Pending”, “pending”, “Pending ”.
- Kategori produk tidak distandarkan.
Solusi:
- Find & Replace untuk mengganti nilai-nilai variasi dengan yang standar.
- Data Validation untuk mencegah kemungkinan variasi entri.
- Dropdown untuk memaksa pengguna memilih nilai yang seharusnya.
- Fungsi-fungsi UPPER, LOWER, atau PROPER untuk menyesuaikan pemakaian huruf besar atau kecil.
- Power Query untuk mengganti nilai.
- Mapping data menggunakan fungsi-fungsi lookup seperti XLOOKUP.
Duplikasi Data
Duplikasi data adalah ketika suatu data muncul lebih dari satu kali. Duplikasi data membuat workbook jadi lebih berat, salah analisa, serta merusak integrasi data.
Contoh:
- Baris duplikat.
- Customer ID muncul dua kali.
- Transaksi ganda.
- Data lama yang tidak relevan.
Solusi:
- Gunakan fungsi-fungsi perhitungan jumlah cell untuk menghitung duplikasi setiap nilai, seperti COUNTIF atau COUNTIFS.
- Gunakan Power Query untuk memeriksa dan menghapus duplikasi.
- Gunakan conditional formatting atau data validation untuk menemukan duplikasi.
Masalah Struktur Data
Struktur data adalah masalah umum Excel, terutama bagi pemula atau mereka yang tidak paham bagaimana membuat struktur yang baik.
Contoh:
- Header tidak di baris pertama.
- Merged cells di badan data.
- Baris kosong di tengah tabel.
- Tabel tidak berbentuk bujur sangkar atau persegi panjang.
- Sheet-sheet yang menampung data serupa dalam satu workbook memiliki struktur berbeda.
Solusi:
- Gunakan table data. Tabel data menuntut agar struktur tabel sumber bersih.
- Bongkar merged cell ke cell-cell pembentuknya.
- Hapus baris kosong.
- Gunakan Power Query untuk memperjelas header.
- Tata ulang struktur data secara manual atau gunakan Power Query.
Error Values
Error values atau nilai kesalahan adalah salah satu indikator termudah bahwa ada kemungkinan data tidak bersih.
Contoh:
- Lookup gagal menghasilkan #N/A.
- Referensi cell rusak di dalam suatu rumus mengembalikan #REF!.
- Tipe data tidak sesuai menampilkan #VALUE!.
- #DIV/0! dihasilkan dari pembagian terhadap nol.
Solusi:
- Gunakan fungsi IFERROR atau IFNA untuk mengantisipasi kesalahan.
- Gunakan ISERROR atau ISNA untuk memeriksa kesalahan.
- Rapikan nilai-nilai kunci dengan TRIM, CLEAN, atau VALUE.
- Pastikan nilai-nilai yang diperbandingkan atau dicari adalah sama, yaitu tidak berbeda tipe data atau huruf besar atau kecil, atau perbedaan nilai digital lainnya.
- Gunakan Power Query untuk menggantikan cell-cell yang berisi nilai kesalahan dengan nilai lain, seperti 0 (nol) atau spasi kosong.
Tipe Data Berbeda di Dalam Satu Kolom
Satu kolom data umum berada dalam kategori yang sama. Kategori yang sama berarti tipe data yang sama.
Pada kenyataannya, tidak jarang satu kolom memiliki tipe data yang berbeda.
Contoh:
- Kolom Kuantitas, yang seharusnya hanya angka, berisi campuran angka, teks, dan simbol.
- Kolom tanggal berisi tanggal valid dan teks “kemarin”.
- Kolom angka diformat manual, tidak melalui fitur pemformatan Excel. Contoh: menuliskan langsung Rp 5.000,00 tanpa mengetahui format yang berlaku.
Solusi:
- Gunakan Text to Columns untuk membantu menyeragamkan tipe data.
- Fungsi VALUE untuk mengonversi angka yang diformat sebagai teks, tanggal, dan waktu ke dalam nilai angka yang sesungguhnya.
- Power Query menuntut agar data pada suatu kolom memiliki tipe data yang sama.
- Find & Replace membantu menghapus simbol atau karakter-karakter tidak terbaca.
Masalah Encoding dan Impor Data
Encoding dan impor data ke Excel dapat menyebabkan teks data berubah menjadi karakter aneh.
Contoh:
- “Rizki” menjadi “RizkÔ
- CSV terbuka dengan format salah
- Format pemisah ribuan dan desimal (titik dan koma atau sebaliknya) berubah saat impor.
Solusi:
- Jika file sumber berupa CSV, buka dengan format UTF‑8.
- Gunakan Power Query untuk mengubah pengodean.
- Walau pun tidak memperbaiki masalah pengodean secara langsung, Text to Columns memiliki import wizard yang menangani pengodean.
Data Hilang: Nulls, Blanks, atau Celah
Masalah:
- Banyak cell kosong.
- Data hilang di tengah tabel.
- Kolom penting tidak terisi.
Solusi:
- Fungsi COUNTBLANK menghitung berapa banyak cell kosong dalam suatu rentang.
- Gunakan Go To Special untuk mencari cell kosong dalam suatu rentang.
- Gunakan Power Query untuk menggantikan nilai null.
- Gunakan Data Validation sejak awal, sebelum entri, untuk mencegah adanya data kosong.
Langkah-langkah Data Cleaning
Berikut adalah contoh framework umum untuk membuat proses pembersihan data. Setiap kasus bisa jadi memiliki rangka kerja yang berbeda dan jumlah langkah dan detail penyelesaian yang berbeda pula.
Langkah 1: Identifikasi Masalah
Identifikasi masalah berguna untuk mengambil langkah-langkah yang diperlukan untuk pembersihan.
Contoh alat-alat yang digunakan:
- ISTEXT: memeriksa apakah suatu nilai teks atau bukan.
- ISNUMBER: memeriksa apakah suatu nilai merupakan angka murni atau bukan.
- LEN: memeriksa panjang nilai untuk melihat apakah mengandung spasi berlebih atau karakter tidak terlihat lainnya.
- CODE: membantu mengidentifikasi karakter tersembunyi, tak terlihat, atau karakter khusus yang menyebabkan kesalahan dalam pencocokan teks dan pemrosesan data.
Langkah 2: Normalisasi Format
Normalisasi format adalah penerapan format standar terhadap data.
Contoh tindakan:
- Penyesuaian pemakaian huruf besar atau kecil.
- Standarisasi penggunaan angka atau teks.
- Format tanggal ke dalam format yang sesuai.
Langkah 3: Bersihkan Karakter-karakter tidak terbaca
Karakter-karakter tidak terbaca dapat berupa karakter-karakter tidak terlihat atau pun karakter-karakter yang tidak lazim dalam konteks bahasa bersangkutan.
Fungsi-fungsi berikut, tapi tidak terbatas pada, adalah contoh fungsi-fungsi yang dapat membersihkan data dari karakter-karakter tersebut.
- TRIM
- CLEAN
- SUBSTITUTE
Langkah 4: Restrukturisasi Data
Proses pembersihan terkadang tidak berjalan linear. Ada kalanya kita harus memecah data untuk mempermudah proses.
Restrukturisasi data adalah proses menyusun ulang data.
Beberapa fungsi yang umum dipakai adalah:
- TEXTSPLIT
- TEXTAFTER
- TEXTBEFORE
Langkah 5: Validasi Hasil
Hasil restrukturisasi data tidak boleh diterima begitu saja. Kita perlu melakukan validasi sebagai konfirmasi apakah data tersebut dapat menggantikan data kotor.
Validasi dapat dilakukan dengan berbagai cara, baik melalui rumus atau fitur Excel.
- COUNTIF
- UNIQUE
- FILTER
- Conditional Formatting
- Validasi Data
Contoh Kasus Data Cleaning
Kami menggunakan data pada file Excel yang bisa ditemukan pada bagian awal artikel ini.
Membersihkan Nama
Langkah 1: Identifikasi Masalah – Periksa Apakah Nama adalah Teks atau Bukan
Pada konteks ini, gunakan fungsi ISTEXT untuk memeriksa tipe data setiap nama. Jika semua baris TRUE, artinya setiap baris adalah teks. Jika bukan, berarti ada tipe data lain atau kolom mengandung error.
Pada cell G2, ketikkan rumus berikut.
=ISTEXT(A2:A101)
Langkah 2: Normalisasi Format Dasar
Mulai dari tahap ini, setiap hasil rumus dapat menjadi kolom pembantu bagi rumus berikutnya. Hal ini untuk mempermudah melihat tahapan-tahapan perubahan dan hasil rumus.
Pada cell H2, bersihkan spasi dan karakter tidak terlihat dengan menggunakan kombinasi rumus TRIM dan CLEAN.
=TRIM(CLEAN(A2:A101))
Rumus di atas akan membuang:
- spasi di awal dan/atau akhir.
- spasi ganda di tengah.
- karakter non-printable.
Gunakan hasil pembersihan pada cell H2:H101 sebagai dasar untuk rumus G2 berikut:
=PROPER(H2:H101)
PROPER akan mengubah huruf pertama setiap kata dalam teks menjadi huruf kapital dan mengubah semua huruf lainnya menjadi huruf kecil.
Langkah 3: Bersihkan gelar di depan
Umumnya, nama resmi di Indonesia tidak memiliki gelar. Berangkat dari asumsi tersebut, kita dapat membuang gelar-gelar dari nama.
Untuk itu, kita menggunakan rumus SUBSTITUTE bertingkat.
=TRIM(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(I2:I101;"Dr.";"");
"Prof.";"");
"Ir.";"");
"Drg.";"");
"H.";"");
"Hj.";"");
"S.E.";"");
"M.M.";"");
",";"")
)

Langkah 4: Bersihkan Semua Gelar di Belakang
=TRIM(
SUBSTITUTE(
SUBSTITUTE(J2:J101;"S.H.";"");
"S.Pd.";"")
)
Langkah 5: Validasi Hasil Pembersihan
Untuk langkah ini, kita dapat menggunakan fitur Find and Replace atau fungsi FIND atau SEARCH, atau metode lain yang sesuai, seperti conditional formatting atau data validation.
Rumus di bawah menggunakan regex untuk memeriksa apakah nama memiliki karakter-karakter bukan huruf dan spasi untuk nama.
=IF(NOT(REGEXTEST(K2:K101; "^[A-Za-z ]+$")); "Ada"; "Tidak")
Jika masih ada nama yang tidak sesuai ketentuan, ulangi Langkah 3. Demikian seterusnya sampai data mencapai standar kebersihan yang diinginkan.
Membersihkan Nomor Telepon Seluler
Pada contoh ini, kita akan membersihkan nomor seluler. Hasil akhir yang diharapkan adalah nomor seluler berformat standar 081234567890, tanpa kode negara atau tanda-tanda seperti + (tanda tambah) atau – (tanda dash).
Langkah 1: Identifikasi Masalah
Nomor seluler Indonesia memiliki rentang panjang tertentu. Dengan LEN kita memeriksa apakah ada nomor yang memiliki panjang di luar standar.
=LEN(B2:B101)
Rumus di bawah memeriksa apakah data memiliki karakter-karakter seperti Tab, nbsp, dan karakter-karakter non-printable lainnya. CLEAN akan membersihkan karakter-karakter seperti ini. Jika setelah pembersihan, nilai asli lebih panjang, maka nilai tersebut mengandung karakter tidak terlihat.
=LEN(B2:B101) - LEN(CLEAN(B2:B101))
Langkah 2: Normalisasi Format Dasar
Rumus di bawah membersihkan data dari karakter-karakter tidak terlihat menggunakan TRIM dan CLEAN.
=TRIM(CLEAN(B2:B101))
Rumus berikut menghilangkan karakter-karakter tidak terlihat dari data dan mengganti karakter “O” dengan “0”.
=SUBSTITUTE(I2:I101; "O";"0")
Langkah 3: Bersihkan Karakter Bukan Angka
Rumus berikut menghilangkan karakter-karakter bukan angka dari data.
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(J2:J101; "-";"");
" "; "");
".";"")
Langkah 4: Restrukturisasi Format
Rumus berikut mencari teks “+62” atau “62” dan mengubahnya menjadi “0”, jika ada.
=IF(
LEFT(K2:K101; 3)="+62";
"0"&MID(K2:K101; 4; 99);
IF(
LEFT(K2:K101; 2)="62";
"0"&MID(K2:K101; 3; 99);
K2:K101
)
)
Rumus berikut melihat apakah hasil terakhir restrukturisasi memiliki angka nol di depannya atau tidak. Bila tidak, rumus akan menambahkan angka tersebut.
=IF(LEFT(L2:L101; 1)="0"; L2:L101; "0"&L2:L101)
Langkah 5: Validasi Hasil
Rumus di bawah melihat apakah hasil akhir restrukturisasi nomor seluler ada yang berupa teks.
=ISNUMBER(--M2:M101)
Seperti terlihat pada gambar, data masih memiliki nilai yang mengandung huruf. Rumus di bawah akan mengubah huruf tersebut menjadi angka.
=SUBSTITUTE(M2:M101; "I";"1")
Setelah ini, terapkan kembali rumus ISNUMBER dengan parameter –O2:O101.

Kapan Harus Beralih ke Power Query?
Power Query adalah alat berguna dalam proses pembersihan data. Namun tidak setiap kondisi memerlukan penggunaan fitur ini.
Gunakan Power Query jika:
- data melebihi 10.000 baris.
- data memerlukan pembersihan berulang.
- data berasal dari banyak file, sumber, atau workbook.
- data harus digabung otomatis
- data harus di-refresh setiap hari
Dalam kondisi di atas, penggunaan Power Query lebih stabil daripada rumus.
Tips Melakukan Data Cleaning
- Backup data. Usahakan tidak menggunakan workbook atau worksheet asli saat melakukan pembersihan.
- Validasi tipe data. Selalu periksa apakah suatu nilai berupa teks, angka, atau tanggal menggunakan ISTEXT, ISNUMBER, atau VALUE.
- Normalisasi format. Sesuaikan penggunaan huruf kapital atau kecil, samakan format tanggal, dan standarisasi format angka sebelum melakukan pembersihan lebih lanjut.
- Pembersihan dilakukan terarah, bukan menyeluruh. Contohnya, ISNUMBER hanya diterapkan pada kolom angka yang dicurigai tercampur dengan karakter teks. Tidak perlu menggunakan ISNUMBER pada setiap kolom, kecuali ada tujuan tertentu.
- Pembersihan tidak selalu linear. Semakin besar atau kotor data, semakin sering kita mengulang suatu langkah.
- Hindari melakukan edit data secara langsung. Gunakan kolom pembantu dan fungsi-fungsi pembantu seperti TRIM, CLEAN, REPLACE, SUBSTITUTE, atau fungsi-fungsi sejenis lainnya.
- Pecah teks terstruktur. Pisahkan kode, nama, atau kolom gabungan menjadi kolom-kolom yang rapi.
- Periksa keunikan data. Identifikasi data ganda (duplikat) sejak awal untuk menghindari kesalahan pada tahap selanjutnya.
- Filter untuk menemukan anomali. Gunakan FILTER untuk memisahkan baris yang mencurigakan agar bisa diperiksa. Dokumentasikan langkah-langkah pembersihan. Tambahkan komentar atau kolom bantu agar kita di masa depan (atau pembaca lain) memahami logikanya.
- Gunakan kolom bantu, jangan menimpa data asli. Pertahankan data mentah apa adanya; data yang sudah bersih sebaiknya ditempatkan di kolom baru.
- Uji rumus pada sampel kecil terlebih dahulu. Hindari menerapkan rumus pada 10.000 baris sebelum memastikan rumus tersebut berfungsi dengan benar.
- Bila memungkinkan, cross check data hasil pembersihan dengan kondisi lapangan.
- Tidak ada langkah-langkah baku dalam data cleaning. Setiap langkah disesuaikan dengan kebutuhan.
Penutup
Data cleaning adalah dasar dari analisis akurat. Dengan memahami penyebab data tidak rapi, menguasai teknik pembersihan, dan menerapkan langkah-langkah yang sistematis, kita dapat menghasilkan data yang bersih, konsisten, dan siap digunakan untuk laporan, dashboard, atau analisis lanjutan.
Data yang bersih bukan hanya membuat rumus bekerja dengan benar, tetapi juga meningkatkan kualitas keputusan bisnis.
