Cara Merapikan Data Berantakan di Excel Sebelum Diolah

Data yang kamu terima jarang sekali rapi. Hasil ekspor dari aplikasi kasir, isian Google Form, atau rekap yang diketik lima orang berbeda biasanya penuh masalah kecil: nama ditulis "budi SANTOSO", ada spasi ganda, nomor HP jadi angka aneh, satu pelanggan tercatat tiga kali. Kelihatannya sepele, tapi masalah-masalah ini yang bikin rumus SUMIF salah hitung, VLOOKUP keluar #N/A, dan Pivot Table menampilkan "Jakarta" dan "jakarta " sebagai dua kota berbeda.

Analis data sering bilang bahwa sebagian besar waktu kerja mereka habis untuk membersihkan data, bukan menganalisisnya. Kabar baiknya, Excel punya alat yang lengkap untuk ini. Artikel ini membahas urutan kerja yang bisa kamu pakai setiap kali menerima data baru.

Langkah 0: simpan data asli

Sebelum mengubah apa pun, duplikat sheet aslinya (klik kanan nama sheet, pilih Move or Copy, centang Create a copy). Beri nama "Asli" dan jangan disentuh. Kalau ada langkah yang salah, kamu selalu bisa kembali tanpa harus minta file ulang.

Lalu biasakan satu prinsip: bersihkan pakai kolom bantu. Jangan menimpa kolom asli dengan rumus. Buat kolom baru di sebelahnya, isi dengan rumus pembersih, cek hasilnya, baru setelah yakin salin dan tempel sebagai nilai (Paste Special, Values) ke kolom aslinya.

Spasi berlebih: TRIM

Ini masalah nomor satu dan paling sulit dilihat mata. "Budi" dan "Budi " kelihatan sama di layar, tapi bagi Excel berbeda. Fungsi TRIM membuang spasi di awal dan akhir teks, dan merapatkan spasi ganda di tengah jadi satu:

=TRIM(A2)

"  Budi   Santoso " menjadi "Budi Santoso".

Ada satu kasus yang TRIM nggak bisa atasi: spasi khusus dari halaman web (non-breaking space, kode karakter 160). Kalau setelah TRIM teksnya masih "nggak cocok", ganti dulu karakter itu dengan spasi biasa:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Untuk karakter aneh lain yang tidak bisa dicetak, misalnya enter yang ikut terbawa dari sistem lain, tambahkan CLEAN: =TRIM(CLEAN(A2)).

Huruf besar-kecil acak: PROPER, UPPER, LOWER

Tiga fungsi ini mengubah kapitalisasi teks:

• =PROPER(A2): huruf pertama tiap kata kapital. "budi SANTOSO" jadi "Budi Santoso". Cocok untuk nama orang dan nama kota.
• =UPPER(A2): semua huruf kapital. Cocok untuk kode barang, plat nomor, atau kode cabang.
• =LOWER(A2): semua huruf kecil. Cocok untuk alamat email.

Fungsi-fungsi ini bisa digabung. Rumus pembersih nama yang paling sering dipakai: =PROPER(TRIM(A2)).

Perhatikan juga bahwa PROPER bukan solusi sempurna. Singkatan seperti "PT" atau "CV" bisa jadi "Pt" dan "Cv". Untuk kasus seperti itu, cek ulang hasilnya atau perbaiki dengan SUBSTITUTE.

Mengganti teks tertentu: SUBSTITUTE

SUBSTITUTE mengganti potongan teks dengan teks lain. Contoh penggunaan yang sering:

• Menyeragamkan penulisan: =SUBSTITUTE(A2, "Jkt", "Jakarta")
• Membuang tanda strip dari nomor HP: =SUBSTITUTE(A2, "-", "")
• Mengubah awalan nomor HP dari 0 ke 62: ="62" & MID(A2, 2, 20) (ini memakai MID, bukan SUBSTITUTE, supaya hanya angka 0 di depan yang diganti)

Untuk penggantian sekali jalan di seluruh sheet tanpa rumus, pakai Find and Replace dengan Ctrl + H. Lebih cepat, tapi langsung menimpa data, jadi pastikan kamu sudah menyimpan salinan aslinya.

Memecah satu kolom jadi beberapa: Text to Columns

Data ekspor sering menggabungkan beberapa informasi dalam satu kolom, misalnya "Bandung;Jawa Barat;40111" atau nama lengkap yang ingin dipisah jadi nama depan dan belakang.

1. Pastikan kolom-kolom di sebelah kanannya kosong, karena hasilnya akan mengisi kolom-kolom itu.
2. Blok kolomnya, lalu buka tab Data, pilih Text to Columns.
3. Pilih Delimited, lalu centang pemisahnya: titik koma, koma, spasi, atau ketik sendiri di Other.
4. Di langkah terakhir, kamu bisa memilih format tiap kolom. Untuk kode pos atau nomor HP, pilih Text supaya angka 0 di depan tidak hilang.

Kalau kamu pakai Microsoft 365, ada juga fungsi TEXTSPLIT yang melakukan hal yang sama dengan rumus: =TEXTSPLIT(A2, ";").

Senjata rahasia: Flash Fill

Flash Fill adalah fitur yang menebak pola dari contoh yang kamu ketik. Misalnya kolom A berisi email seperti "budi.santoso@kantor.co.id", dan kamu ingin mengambil nama penggunanya saja.

1. Di B2, ketik manual hasil yang kamu inginkan: budi.santoso.
2. Pindah ke B3, lalu tekan Ctrl + E.

Excel mengisi seluruh kolom dengan pola yang sama. Flash Fill bisa dipakai untuk mengambil nama depan, menggabungkan kolom, mengubah format nomor HP, dan banyak pola lain. Kalau tebakannya salah, ketik satu atau dua contoh lagi lalu ulangi.

Catatan penting: hasil Flash Fill berupa nilai tetap, bukan rumus. Kalau data aslinya berubah, hasilnya nggak ikut berubah. Untuk data yang terus diperbarui, pakai rumus seperti LEFT, MID, RIGHT, dan FIND.

Angka yang tersimpan sebagai teks

Tanda-tandanya: angka rata kiri (angka asli rata kanan), ada segitiga hijau kecil di pojok sel, dan SUM menghasilkan 0. Ini sering terjadi pada data hasil impor atau salin dari web.

Beberapa cara memperbaikinya, dari yang paling cepat:

• Blok selnya, klik ikon peringatan kuning yang muncul, pilih Convert to Number.
• Pakai Text to Columns pada kolom itu, lalu langsung klik Finish. Cara ini juga memperbaiki banyak kasus tanggal yang terbaca sebagai teks.
• Pakai rumus di kolom bantu: =VALUE(A2).

Kalau angkanya masih mengandung titik ribuan atau tulisan "Rp", buang dulu dengan SUBSTITUTE sebelum dijadikan angka: =VALUE(SUBSTITUTE(SUBSTITUTE(A2, "Rp", ""), ".", "")).

Sebaliknya, ada data yang harus tetap teks, seperti nomor HP, NIK, nomor rekening, dan kode pos. Kalau disimpan sebagai angka, angka 0 di depan hilang dan nomor yang panjang berubah jadi format seperti 3.27E+15. Format kolomnya sebagai Text sebelum data diketik atau ditempel.

Data dobel: Remove Duplicates

Setelah teks rapi, baru waktunya membuang data dobel. Urutannya penting: kalau dilakukan sebelum TRIM dan PROPER, "Budi Santoso" dan "budi santoso " tidak terdeteksi sebagai data yang sama.

1. Klik salah satu sel di dalam data.
2. Buka tab Data, pilih Remove Duplicates.
3. Centang kolom yang menentukan sebuah baris dianggap dobel. Untuk data pelanggan, biasanya cukup kolom email atau nomor HP, bukan semua kolom.

Excel akan memberitahu berapa baris yang dihapus. Kalau kamu ingin melihat dulu baris mana yang dobel sebelum menghapus, tandai pakai rumus =COUNTIF($B$2:$B$500, B2). Hasil lebih dari 1 berarti datanya muncul lebih dari sekali.

Urutan kerja yang disarankan

1. Duplikat sheet asli.
2. Pecah kolom gabungan dengan Text to Columns.
3. Bersihkan teks: TRIM, CLEAN, PROPER atau UPPER.
4. Seragamkan penulisan dengan SUBSTITUTE atau Find and Replace.
5. Perbaiki angka dan tanggal yang tersimpan sebagai teks.
6. Tempel hasil sebagai nilai, hapus kolom bantu.
7. Hapus data dobel.
8. Ubah jadi Table dengan Ctrl + T, siap diolah.

Kedengarannya panjang, tapi setelah beberapa kali, seluruh proses ini cuma butuh beberapa menit. Waktu yang kamu habiskan di sini akan terbayar saat rumus dan laporannya langsung benar sejak pertama kali dihitung.

Baru mulai belajar Excel? Baca dulu Belajar Excel dari Nol: Panduan Lengkap untuk Pemula untuk fondasi dan peta belajarnya.

Fungsi teks seperti TRIM, PROPER, LEFT, MID, RIGHT, dan SUBSTITUTE ada di level Intermediate Jago Excel, termasuk studi kasus "data pelanggan berantakan" yang mirip dengan kasus di artikel ini. Kamu bisa langsung mencobanya di lembar kerja.