SPILL Excel - Penyebab Dan Cara Mengatasinya

SPILL excel error merupakan kesalahan yang terjadi saat rumus excel dihalangi oleh karakter tertentu pada sel dalam rentang kolom tersebut. #SPILL! error merupakan fitur baru yang hanya ada pada Excel 365, Excel Web, Excel 2021, Excel untuk iPhone & Android.

Apa itu SPILL Excel ?

SPILL dalam bahasa Indonesia berarti tumpah. Dalam pengertian excel, SPILL excel adalah saat memasukkan rumus di satu sel maka secara otomatis tersalin di sel sebelah aau di bawahnya. Sejak peluncuran array dinamis pada September 2018, excel telah mampu menangani beberapa nilai sekaligus dan mengembalikan hasil di lebih dari satu sel. Array dinamis merupakan array yang dapat diubah ukurannya yang memungkinkan mengembalikan beberapa hasil pada rentang sel, dari sumber rumus yang dimasukkan pada satu sel.

Excel memiliki 9 fungsi yang menggunakan array dinamis, yaitu :

SEQUENCE
UNIQUE
FILTER
SORT
SORTBY
RANDARRAY
TRANSPOSE
XMATCH
XLOOKUP

Array dinamis hanya tersedia pada excel web dan excel 365. Namun untuk penggunaan fungsi TRANSPOSE bisa digunakan pada excel versi lainnya dengan cara menambahkan CSE (Ctrl+Shift+Enter).

Penyebab SPILL Excel

Adanya karakter yang menghalangi rumus

Saat melakukan pengelompokan data pada excel dengan penggunaan rumus UNIQUE, hasil menunjukkan error SPILL pada excel.

Pada animasi di atas, sel E1 dimasukkan rumus UNIQUE, dan secara otomatis melakukan ekstrak filter dari data sebelah kiri. Namun pada sel E10 muncul hasil #SPILL! dikarenakan ada karakter “x ” pada sel E13. Karakter “x” tersebut menghalangi rumus otomatis yang dibuat excel.

Rentang SPILL excel mengandung merged cells (sel gabungan)

Dalam melakukan pengolahan database usahakan hindari penggunaan merged cells atau dua sel yang digabung menjadi satu. Pada penggunaan rumus SEQUENCE, kesalahan SPILL dapat terjadi karena adanya penggabungan dua sel menjadi satu.

Pada animasi di atas, terdapat merged cells di sel A10 yang digabungkan menjadi satu dengan sel B10. Saat memasukkan rumus untuk mengurutkan dari tanggal 1 Januari 2021 ke tanggal 10 Januari 2021 tidak bisa dilakukan, karena terhalang oleh merged cells. Rumus yang dimasukkan adalah :

=SEQUENCE(10;1;”1-Jan-2021″)

Jika tidak terhalang oleh merged cells, data yang seharusnya muncul secara berturut-turut dari sel A1 ke sel A10 adalah 1 Januari 2021 sampai 10 Januari 2021.

SPILL excel dalam format table

SPILL tidak bisa dilakukan dalam sel berformat table. Rumus dengan array dinamis hanya bisa digunakan dalam satu sel tersendiri. Contoh pada animasi di bawah ini menggunakan fungsi SEQUENCE untuk mengurutkan tanggal.

Pada animasi di atas dimasukkan rumus untuk mengurutkan tanggal dari tanggal 1-Jan-2021 sampai tanggal 10-Jan-2021. Pada sel A2 dimasukkan rumus :
=SEQUENCE(10;1;”1-Jan-2021″)
Namun hasil yang didapat berupa SPILL error. Hal ini terjadi karena pada kolom A berformat table, sehingga rumus tidak bisa digunakan.
Pada sel B2 dimasukkan rumus yang sama, dan hasil berfungsi sebagaimana mestinya.

Rentang SPILL excel terlalu besar

SPILL juga dapat terjadi pada rumus VLOOKUP. SPILL terjadi dikarenakan argumen lookup_value mengambil kolom hingga batas akhir baris excel, yaitu pada baris ke 1.048.576. Pada array dinamis pengambilan data lookup_value hingga batas akhir baris excel, tidak diperkenankan dan akan menimbulkan error SPILL.

Pada contoh gambar di atas rumus yang dimasukkan adalah :
=VLOOKUP(E:E;A:C;2;0)
argumen lookup_value mengambil seluruh baris dalam kolom E.

Cara Mengatasi SPILL

Hapus karakter yang mengahalangi SPILL

Cara terbaik untuk mengatasi karakter yang menghalangi SPILL adalah dengan cara menghapus atau memindahkan karakter tersebut.

Setelah karakter tersebut dihapus atau dipindahkan ke sel lain, maka SPILL akan berfungsi kembali dan terisi secara otomatis.

Unmerged Cells (pisahkan sel gabungan)

Seperti yang telah disebutkan di atas bahwa penggunaan sel gabungan pada database akan menyebabkan SPILL menjadi error. Apabila ada banyak terdapat merged cells dan kita tidak mengetahui dimana posisi sel yang mengalami merged cells, maka yang kita lakukan adalah dengan cara memilih seluruh sel pada excel.

Cara untuk memisahkan sel gabungan yaitu :

Tekan Ctrl+A pada keyboard atau klik tanda segitiga diantara kolom A dan baris 1.
Klik tab merged cells pada ribbon

Maka seluruh sel yang mengalami merged cells akan terpisah dan SPILL akan dapat berfungsi kembali.

Hilangkan format table

Untuk menghilangkan format table, ikuti langkah berikut ini:

Klik pada awal sel SPILL error
Pilih Tab Table Design
Pilih Command Button Tools
Pilih Convert To Range

Maka SPILL akan berpindah ke sel yang kosong yang berada di bawahnya dengan catatan tidak ada karakter yang berada pada sel yang kosong tersebut. Dan SPILL akan berfungsi kembali.

Cara untuk memisahkan sel gabungan yaitu :

Tekan Ctrl+A pada keyboard atau klik tanda segitiga diantara kolom A dan baris 1.
Klik tab merged cells pada ribbon

Maka seluruh sel yang mengalami merged cells akan terpisah dan SPILL akan dapat berfungsi kembali.

Sesuaikan lookup value dengan rentang table array

Ada tiga cara untuk memperbaiki rentang SPILL yang terlalu besar.

Ubah nilai referensi sesuai dengan rentang yang ada dengan tidak mengambil seluruh kolom.

Rumus awal :
=VLOOKUP(E:E;A:C;2;0)

Setelah diperbaiki :
=VLOOKUP(E2: E11;A:C;2;0)

Ubah nilai referensi pada baris yang sama, lalu salin rumus ke bawah . Rumus ini tidak bekerja dengan model array dinamis. Namun bila dalam format table, array dinamis akan berfungsi kembali.

Rumus awal :
=VLOOKUP(E:E;A:C;2;0)

Setelah diperbaiki :
=VLOOKUP(E2;A:C;2;0)

Tambahkan karakter @ pada referensi lookup, lalu salin rumus ke bawah . Rumus ini tidak bekerja dengan model array dinamis. Namun bila dalam format table, array dinamis akan berfungsi kembali.
Rumus awal :
=VLOOKUP(E:E;A:C;2;0)

Setelah diperbaiki :
=VLOOKUP(@E:E;A:C;2;0)