#REF! berarti rumus merujuk ke sel yang tidak ada. Penyebab umumnya adalah baris, kolom atau sheet yang dihapus: ketika kolom C dihapus, Excel menulis ulang =B2*C2 menjadi =B2*#REF!, dan sejak itu hasilnya #REF!. Tekan Ctrl+Z (Cmd+Z di Mac) tepat setelah menghapus untuk mengembalikan kolom dan rumusnya.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Qty | Total |
| 2 | Apple | 1.2 | 10 | #REF! |
| 3 | Pear | 1.5 | 20 | #REF! |
| 4 | Plum | 0.8 | 15 | #REF! |
| 5 | Bread | 2.4 | 5 | #REF! |
#REF! Rumus merujuk ke sel yang tidak ada.Kolom jumlah barang sudah dihapus lalu diketik ulang, tapi rumusnya masih berisi #REF!: Excel tidak pernah memperbaiki referensi yang sudah hilang. Klik D2, ganti #REF! dengan C2 lalu tekan Enter. Seluruh kolom ikut berubah, dan D2 menampilkan 12.
Bagaimana #REF! masuk ke rumus
Excel menulis #REF! di rumus setiap kali sel yang dipakai rumus itu hilang:
| Yang kamu lakukan | =B2*C2 di D2 menjadi |
|---|---|
| Menghapus kolom C | =B2*#REF! |
| Menghapus baris 2 | rumusnya ikut terhapus bersama barisnya; rumus di baris lain yang merujuk ke baris 2 mendapat #REF! |
| Menghapus sheet yang dirujuk rumus | =#REF!B2*2 (untuk rumus seperti =Prices!B2*2) |
| Memotong sebuah sel lalu menempelkannya di atas sel yang dipakai rumus | #REF! menggantikan referensi yang tertimpa |
Menghapus sel di dalam sebuah rentang aman: =SUM(B2:D2) menjadi =SUM(B2:C2) ketika kolom C dihapus. Menghapus sel pertama atau terakhir sebuah rentang hanya membuatnya menyusut. Jadi =SUM(B2:D2) lebih aman daripada =B2+C2+D2, yang berubah menjadi =B2+#REF!+C2.
Kenapa VLOOKUP menghasilkan #REF!
Argumen ketiga VLOOKUP menghitung kolom di dalam rentang tabel. Kalau angkanya lebih besar dari jumlah kolom di rentang, hasilnya #REF!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Pear | #REF! | |
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
#REF! Rumus merujuk ke sel yang tidak ada.A2:C6 punya tiga kolom, jadi kolom 4 tidak ada. Ubah 4 menjadi 3, maka F2 menampilkan 25. Ini paling sering terjadi setelah sebuah kolom dihapus dari tabel lookup: rentangnya menyusut, tapi nomor kolom yang ditulis langsung di rumus tidak. XLOOKUP atau INDEX dengan MATCH menghindari masalah ini karena langsung menyebut kolom hasilnya, seperti =XLOOKUP(E2,A2:A6,C2:C6). Lihat VLOOKUP untuk argumen lainnya.
#REF! dengan INDEX dan OFFSET
INDEX mengembalikan #REF! ketika nomor baris atau kolomnya berada di luar rentangnya, dan OFFSET ketika bergerak ke atas baris 1 atau ke kiri kolom A.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Result | What it asks for |
| 2 | 88 | #REF! | 6th value of 5 |
| 3 | 72 | 95 | 3rd value of 5 |
| 4 | 95 | #REF! | 2 rows above A2 |
| 5 | 64 | 81 | 4 rows below A2 |
| 6 | 81 |
#REF! Rumus merujuk ke sel yang tidak ada.A2:A6 berisi lima skor, jadi INDEX(A2:A6,6) menghasilkan #REF!, sedangkan INDEX(A2:A6,3) mengembalikan 95. Baris 0 tidak ada, jadi OFFSET(A2,-2,0) menghasilkan #REF!, dan OFFSET(A2,4,0) sampai di A6: 81. Kalau posisinya berasal dari rumus lain (sebuah MATCH, sebuah COUNT), periksa rumus itu lebih dulu. Selengkapnya di halaman INDEX.
INDIRECT juga menghasilkan #REF! ketika teksnya bukan alamat yang valid (=INDIRECT("ZZZ1"), karena kolom terakhir adalah XFD) atau merujuk ke workbook yang sedang tertutup.
#REF! saat menyalin rumus
Referensi relatif ikut bergeser bersama rumusnya. Salin cukup jauh ke atas atau ke samping, maka referensinya jatuh keluar dari sheet:
C3: =B2*2 (one row up, one column back)
copy C3 to B2: =A1*2
copy C3 to A2: =#REF!*2 (there is no column before A)
Hal yang sama terjadi ketika rumus yang disalin ke sheet atau workbook lain merujuk ke sel yang tidak ada di sana. Kunci sel yang tidak boleh bergeser dengan $ (=$B$2*2), atau salin teks rumusnya dari formula bar, bukan selnya. Halaman referensi absolut menjelaskan tanda $.
Temukan dan hapus setiap #REF! di sebuah workbook
- Tekan Ctrl+F (Cmd+F di Mac), ketik
#REF!, buka Options, atur Look in ke Formulas, lalu klik Find All. Daftarnya menampilkan setiap rumus dengan referensi yang rusak. - Untuk memperbaiki banyak sekaligus, pakai Ctrl+H (Control+H di Mac): cari
#REF!, ganti dengan referensi yang benar, tapi hanya kalau setiap hasil pencarian memang harus mendapat sel yang sama. - Periksa Formulas > Name Manager: nama yang kolom Refers To-nya menampilkan
#REF!merusak setiap rumus yang memakainya. - Kalau data yang terhapus sudah hilang dan rumusnya tidak dibutuhkan lagi, pilih selnya lalu ganti rumusnya dengan nilainya (Copy, lalu Home > Paste > Values). Nilai error tetap menjadi error, jadi hapus sel-sel itu sesudahnya.
Perbaiki lookup yang menghasilkan #REF!
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Plum | ||
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
Giliranmu: =VLOOKUP(E2,A2:C6,4,FALSE) menghasilkan #REF!. Tulis lookup yang berfungsi di F2 untuk mengembalikan stok produk di E2.
Lookup apa pun yang mengembalikan 60 di sini dan mengikuti datanya akan lolos: VLOOKUP dengan kolom 3, =XLOOKUP(E2,A2:A6,C2:C6) atau =INDEX(C2:C6,MATCH(E2,A2:A6,0)).
Pertanyaan yang sering diajukan
Apa arti #REF! di Excel?
Rumusnya merujuk ke sel yang tidak ada. Paling sering, baris, kolom atau sheet yang dipakai rumus itu dihapus, lalu Excel mengganti referensinya dengan #REF!, sehingga =B2*C2 menjadi =B2*#REF!. VLOOKUP dan INDEX juga mengembalikan #REF! ketika nomor kolom atau barisnya lebih besar dari rentangnya.
Bagaimana cara memperbaiki #REF! setelah menghapus kolom?
Langsung tekan Ctrl+Z (Cmd+Z di Mac) untuk membatalkan penghapusan. Kalau sudah terlambat, klik rumusnya lalu ganti #REF! dengan sel yang seharusnya dipakai, kemudian salin rumusnya ke bawah lagi.
Kenapa VLOOKUP menghasilkan #REF!?
Nomor kolomnya lebih besar dari jumlah kolom di rentang tabel. =VLOOKUP(E2,A2:C6,4,FALSE) meminta kolom ke-4 dari rentang 3 kolom. Pakai 3, atau perlebar rentangnya menjadi A2:D6.
Bagaimana cara menemukan semua error #REF! di sebuah workbook?
Tekan Ctrl+F (Cmd+F di Mac), cari #REF!, atur Look in ke Formulas lalu klik Find All. Excel menampilkan setiap rumus yang berisi referensi rusak. Periksa juga Formulas > Name Manager: nama bisa merujuk ke #REF! setelah ada yang dihapus.
Bagaimana cara menghindari #REF! saat menghapus baris atau kolom?
Rujuk rentang, bukan sel satu per satu. =SUM(B2:D2) menyusut menjadi =SUM(B2:C2) ketika kolom C atau D dihapus, sedangkan =B2+C2+D2 berubah menjadi =B2+#REF!+C2. Lookup yang menyebut kolom hasilnya, seperti =XLOOKUP(E2,A2:A6,C2:C6), tetap aman saat ada kolom yang disisipkan atau kolom lain yang tidak dipakainya dihapus.
