Bayangkan kondisi ini. Lagi asik-asiknya main dengan rumus array (karena keren, simple, efektif, dan efisien bahwa satu rumus kok hasilnya banyak), tau-tau muncul #SPILL.
Setelah dicek, tidak ada satu pun hal yang salah dengan rumus. Tapi, kok error.
Lewat artikel ini, kami berharap teman-teman bisa belajar tentang error #SPILL Excel, apa saja penyebabnya, dan bagaimana cara mengatasi error ini.
Apakah Error #SPILL Excel Itu?
Error #SPILL Excel adalah error yang terjadi ketika rumus memiliki hasil lebih dari satu cell, tapi Excel tidak bisa menampilkan semuanya.
Jadi, error #SPILL tidak selalu terjadi karena rumusnya sendiri salah.
Salah satu contoh #SPILL adalah ketika kita edit salah satu cell di mana hasil rumus array ditampilkan.
#SPILL termasuk error yang relatif lebih mudah didapatkan solusinya ketimbang error lain seperti #VALUE.
Bila teman-teman senang dengan yang mudah-mudah, yuk maju.
#SPILL: Range Spilling Tidak Kosong
Hasil spilling selalu akan tumpah ke cell-cell di sekitarnya.
Bila salah satu cell di ke mana spilling terjadi sudah memiliki nilai, #SPILL akan muncul, alih-alih hasil yang diinginkan.
Pada gambar di atas, kami menggunakan fungsi SORT untuk mengurutkan data A2:B6 dari penjualan terendah ke tertinggi.
Hasilnya, bila sesuai harapan, akan tumpah ke range D2:E6.
Tapi, cell E4 sudah memiliki isi. Konsekuensinya, hasil pengurutan tidak tampil dan Excel memberikan #SPILL.
Kondisi #SPILL lainnya adalah setelah menggunakan fungsi SORT, kita edit cell E4 atau cell-cell lain di dalam D2:E6.
Cara mengatasi error #SPILL Excel karena range spilling tidak kosong adalah dengan menghapus nilai yang berada pada range spilling. Sebagai alternatif, pindahkan rumus ke lokasi lain di mana hasil spilling tidak tabrakan dengan nilai lain.
Catatan: #SPILL yang terjadi karena range spilling tidak kosong tidak eksklusif untuk SORT. Penggunaan rumus apa pun yang menghasilkan array, termasuk rumus array sendiri, beresiko terjadi #SPILL.
Perintah Select Obstructing Cells di Excel
Cara termudah untuk menuju cell dengan nilai yang tabrakan dengan hasil spilling adalah dengan klik tanda seru yang berada di sisi kiri (atau kanan) cell #SPILL.
Dengan mengklik tanda seru tersebut (Shortcut: Alt+Kunci Menu), sebuah popup pesan error akan tampil.
Perhatikan judul error adalah Spill range isn’t blank. Artinya, salah satu cell range spilling memiliki nilai.
Judul ini berguna sebagai informasi apa sebenarnya penyebab #SPILL yang kita alami saat ini.
Perintah Select Obstructing Cells akan memilih semua cell di range spilling yang tabrakan dengan dengan hasil spilling.
Bila range data cukup besar, cara ini sangat berguna untuk hemat waktu daripada hanya mengandalkan mata.
Select Obstructing Cells juga berguna bila cell yang tabrakan ternyata menyimpan karakter yang tidak kelihatan seperti spasi.
Setelah klik Select Obstructing Cells dan Excel memilih semua cell yang tabrakan, kita hanya perlu menekan Delete di keyboard untuk menghapus semua nilai cell tersebut.
#SPILL: Ada Merge Cell di Range Spilling
Spilling juga tidak akur dengan merge cell.
Pada gambar di atas, tidak ada cell di range spilling yang memiliki nilai. Tapi, cell E4 dan E5 digabungkan menjadi satu cell dengan perintah Merge Cells.
Bila teman-teman buka popup pesan error dengan shortcut Alt+Kunci menu, judul error adalah Spill range has merged cells.
Merged cell (untuk cell tanpa nilai) sulit dideteksi karena warna cell yang sama dengan warna cell bukan merge.
Karena itu, gunakan perintah Select Obstructing Cells di popup error untuk mendeteksi cell-cell hasil merge dengan lebih cepat. Setelahnya, gunakan perintah Unmerge Cells (Shortcut: Alt,H,M,U) untuk memisahkan cell-cell tersebut.
#SPILL: Besaran Spilling Tidak Diketahui
Fungsi SEQUENCE akan menghasilkan angka secara berurutan sesuai dengan parameter yang diberikan.
=SEQUENCE(10)
Rumus di atas akan menampilkan sepuluh baris nilai 1 sampai 10.
Sementara itu, rumus di bawah ini akan menghasilkan #SPILL.
=SEQUENCE(RANDBETWEEN(1;10))
Silakan buka popup pesan error dengan Alt+Kunci Menu. Perhatikan bahwa pesan error adalah Spill range is unknown.
Kondisi ini juga berlaku terhadap beberapa fungsi array dinamis lain, seperti RANDARRAY.
Solusi terhadap masalah di atas adalah menggunakan nilai pasti sebagai parameter fungsi SEQUENCE, alih-alih nilai acak yang dihasilkan oleh RANDBETWEEN.
#SPILL: Spilling Range adalah Tabel Data
Secanggih-canggihnya Excel keterbatasan selalu ada. Salah satunya adalah kita tidak bisa menggunakan fungsi array dinamis di tabel data Excel. Bayangkan itu.
Pesan error (Shortcut: Alt+Kunci Menu) untuk #SPILL ini adalah Spill range in table.
Bila penggunaan fungsi array dinamis seperti SEQUENCE memang diperlukan, ubah tabel data menjadi rentang data biasa.
Sebagai alternatif, bisa juga kita menambahkan fungsi array dinamis di luar area tabel data.
#SPILL: Range Spilling Terlalu Besar
Perhatikan rumus di bawah ini.
Silahkan buka pesan error (Shortcut: Alt+Kunci Menu). Lihat judul error adalah Spill range is too big.
Rumus di cell C2 adalah sederhana namun efisien.
Tujuan yang ingin dicapai adalah mendapatkan nilai komisi sebesar 1.7% dari setiap nilai penjualan (Kolom B) dengan hanya satu rumus.
Di Excel versi terkini, rumus tersebut akan menghasilkan #SPILL karena mencoba menghitung semua nilai di Kolom B dengan meletakkan cell pertama di C2. Teman-teman paham maksud kami?
Excel memiliki 1,048,576 baris. Dengan rumus =B:B*1.7%, Excel akan menghitung 1,048,576 baris dan menempatkan hasil perhitungan pertama di Baris 2 (karena rumus ada di cell C2) dan hasil terbawah di Baris 1048577. Masalahnya, tidak ada Baris 1048577 (mentok di 1048576). Akibatnya? #SPILL.
Bila teman-teman pindahkan rumus di C2 ke C1, error #SPILL akan hilang. Tapi, kan, aneh kalo di C1.
Memang.
Berikut beberapa solusi yang mungkin.
Gunakan rumus array dengan range hanya di mana data berada.
Perhatikan Gambar 08. Pada gambar tersebut, kami menggunakan array dengan batasan B2:B5 dan mengalikannya dengan persentase komisi.
Buat rumus hanya untuk cell C2, lalu copykan ke baris-baris di bawahnya.
Cara ini lebih tradisional. Tapi, selama hasilnya sesuai harapan, ya, kenapa tidak.
Cara Mencari Error #SPILL Excel
Sampai saat ini, semua error terlihat jelas di worksheet.
Namun, seringnya, error tersembunyi di balik ribuan, bahkan puluhan ribu hingga ratusan ribu cell.
Untuk membantu menemukan pesan-pesan error tersebut, kita dapat menggunakan Find and Replace atau fitur-fitur lain.
Lewat artikel berikut kita akan belajar bagaimana menemukan error #SPILL.
Baca: Menemukan Pesan Error di Excel.