TL;DR
Rumus OFFSET di Excel ngambil range yang posisinya digeser dari satu titik awal, dengan format =OFFSET(titik_awal; geser_baris; geser_kolom; [tinggi]; [lebar]). Kegunaan utamanya bikin range dinamis: dipasangin COUNTA, range-nya ikut melar sendiri tiap ada data baru, jadi grafik dashboard gak perlu diedit manual tiap bulan. Tapi OFFSET itu fungsi volatile: dia ngitung ulang tiap kali ada perubahan apa pun di file, jadi jangan dipakai di ribuan baris.
Rumus OFFSET di Excel ngambil range yang posisinya digeser dari satu titik awal. Formatnya: =OFFSET(titik_awal; geser_baris; geser_kolom; [tinggi]; [lebar]).
Yang bikin OFFSET kepake banget: dia bisa bikin range yang ukurannya berubah-ubah. Data nambah 10 baris? Range-nya ikut melar. Grafik dashboard kamu update sendiri.
Gak perlu lagi buka Select Data tiap awal bulan cuma buat ganti B2:B14 jadi B2:B15.
OFFSET adalah fungsi Excel yang ngembaliin referensi ke sel atau range, berdasarkan titik awal yang digeser sejauh sekian baris dan sekian kolom. Kamu juga bisa nentuin tinggi dan lebar range hasilnya. Bagian inilah yang bikin OFFSET beda dari fungsi lookup biasa.
| Argumen | Wajib? | Isinya |
|---|---|---|
| reference | Ya | Titik awal. Biasanya satu sel, misalnya A1. |
| rows | Ya | Geser berapa baris ke bawah. Angka minus = ke atas. |
| cols | Ya | Geser berapa kolom ke kanan. Angka minus = ke kiri. |
| height | Nggak | Tinggi range hasil, dalam jumlah baris. |
| width | Nggak | Lebar range hasil, dalam jumlah kolom. |
Contoh paling gampang:
=OFFSET(A1;2;1)Mulai dari A1, geser 2 baris ke bawah dan 1 kolom ke kanan. Hasilnya isi sel B3.
Sekarang tambahin tinggi dan lebar:
=SUM(OFFSET(A1;2;1;3;2))Mulai dari B3, ambil range setinggi 3 baris dan selebar 2 kolom. Jadinya B3:C5. SUM jumlahin isinya.
OFFSET sendirian gak berguna. Dia baru nendang kalau dipasangin fungsi lain kayak SUM, AVERAGE, atau dipakai sebagai sumber grafik.
Ini kegunaan utama OFFSET. Kuncinya gabung sama COUNTA.
Anggap data penjualan bulananmu ada di kolom B, mulai B2, dengan header di B1. Tulis ini:
=OFFSET($B$2;0;0;COUNTA($B:$B)-1;1)Bedah pelan-pelan:
$B$2: titik awal, sel data pertama.0;0: gak digeser sama sekali. Kita mulai persis dari B2.COUNTA($B:$B)-1: hitung semua sel terisi di kolom B, kurangi 1 buat buang headernya. Ini jadi tinggi range-nya.1: lebarnya 1 kolom.Sekarang kalau kamu nambah data di B15, COUNTA naik jadi 15, tinggi range otomatis jadi 14. Range-nya melar tanpa kamu sentuh.
Cara ngitung sel terisi dijelasin lebih lengkap di halaman fungsi COUNTA.
Ini yang bikin dashboard kamu berhenti minta perhatian tiap bulan.
Buka Formulas > Name Manager > New. Kasih nama, misalnya data_penjualan. Di kolom Refers to, tulis:
=OFFSET(Sheet1!$B$2;0;0;COUNTA(Sheet1!$B:$B)-1;1)Bikin satu lagi buat labelnya, misalnya label_bulan:
=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1;1)Klik kanan grafik > Select Data. Di kolom Series values, ganti isinya jadi:
=Sheet1!data_penjualanNama sheet-nya wajib ditulis di depan, kalau nggak Excel nolak.
Buat label sumbu horizontal, pakai =Sheet1!label_bulan.
Ketik data bulan baru di baris kosong berikutnya. Grafiknya langsung nambah satu titik. Kelar.
Dashboard sering butuh jendela bergulir: selalu nunjukin 12 bulan terakhir, gak peduli datanya udah 40 bulan.
Kombinasi OFFSET dan COUNTA lagi:
=OFFSET($B$2;COUNTA($B:$B)-13;0;12;1)Kalau data ada 40 baris, COUNTA-1 = 40. Geser 40-12 = 28 baris ke bawah dari B2, terus ambil 12 baris. Hasilnya baris ke-29 sampai ke-40. Persis 12 terakhir.
Bulan depan datanya jadi 41 baris, jendelanya otomatis geser satu. Gak ada yang perlu diedit.
Dataset toko_berkah dari ngulikdata punya rekap penjualan bulanan sejak Januari 2024, 26 bulan waktu aku pertama kali bikin dashboardnya.
Masalahnya klasik: tiap awal bulan, orang yang megang file harus buka Select Data di 4 grafik berbeda dan ganti range-nya manual. Satu grafik kelewat, angkanya salah, dan gak ada yang sadar sampai rapat.
Aku ganti semua sumber grafik pakai named range OFFSET. Hasilnya dari 26 bulan data:
| Metrik | Sebelum | Sesudah |
|---|---|---|
| Waktu update bulanan | 12 menit | 0 menit |
| Grafik yang perlu diedit | 4 | 0 |
| Salah range dalam 6 bulan | 3 kali | 0 |
Yang menarik bukan penghematan 12 menitnya. Yang menarik: 3 kali salah range dalam 6 bulan itu semuanya bikin angka pertumbuhan bulanan ke-report lebih tinggi dari kenyataan, karena barisnya kepotong di bulan yang penjualannya jelek.
Setup sekali 15 menit, error jadi nol.
Ini bagian yang jarang disebut di tutorial lain.
OFFSET itu fungsi volatile. Artinya dia ngitung ulang tiap kali ada perubahan apa pun di workbook, bukan cuma waktu sel yang dia rujuk berubah.
Kamu ngetik satu angka di sheet lain? Semua rumus OFFSET ngitung ulang. Kamu ngapus satu baris? Ngitung ulang lagi.
Di file dengan 10 rumus OFFSET, ini gak kerasa. Di file dengan 5.000 rumus OFFSET, Excel bakal nge-lag tiap kali kamu ngetik satu huruf.
INDEX bisa bikin range dinamis juga, dan dia non-volatile:
=$B$2:INDEX($B:$B;COUNTA($B:$B))Sintaksnya lebih ribet dibaca. Tanda titik dua di tengah itu yang bikin dia jadi range, bukan satu sel. Tapi buat file besar, ini jauh lebih enteng.
Aturan praktisnya:
Perbandingan lengkapnya ada di halaman fungsi INDEX.
COUNTA($B:$B) ngitung header juga. Kalau kamu gak kurangi 1, range-nya kepanjangan satu baris, dan grafik kamu bakal punya satu titik kosong di ujung.
COUNTA cuma ngitung sel terisi. Kalau ada 3 sel kosong di tengah kolom B, tinggi range-nya kurang 3 baris, dan data terakhirmu kepotong tanpa satu pun error muncul.
Ini bug paling jahat di OFFSET, karena diam-diam. Pastiin kolom yang kamu COUNTA gak punya lubang.
Titik awal A1 lalu digeser -2 baris? Gak ada baris di atas baris 1. Excel nyerah dan ngeluarin #REF!.
Cek juga argumen tinggi dan lebar. Nilai minimalnya 1, bukan 0.
Di Select Data, kamu harus tulis =Sheet1!data_penjualan, bukan cuma =data_penjualan. Excel nolak yang kedua tanpa penjelasan yang jelas.
OFFSET(B2;...) tanpa tanda dolar bakal geser tiap kali rumusnya dicopy. Selalu pakai $B$2.
OFFSET ngambil range yang posisinya digeser dari satu titik awal, sejauh beberapa baris dan kolom yang kamu tentuin. Kegunaan utamanya bikin range yang ukurannya bisa berubah-ubah: dipakai buat grafik dashboard yang harus ikut nambah tiap ada data baru.
Karena OFFSET ngitung ulang tiap kali ada perubahan apa pun di workbook, bukan cuma waktu sel yang dia rujuk berubah. Di file kecil ini gak kerasa. Di file dengan 5.000 rumus OFFSET, Excel bisa nge-lag tiap kali kamu ngetik. Batasi jumlahnya, atau ganti pakai INDEX.
Bikin named range pakai OFFSET dan COUNTA, lalu pasang named range itu jadi sumber data grafik lewat Select Data. COUNTA ngitung berapa baris yang terisi, dan angka itu jadi tinggi range-nya. Tiap kamu nambah baris data, tingginya nambah sendiri.
INDEX lebih bagus buat file besar karena non-volatile. Cuma ngitung ulang waktu data yang dirujuk beneran berubah. OFFSET lebih gampang dibaca dan lebih fleksibel buat range 2 dimensi. File di bawah 10 ribu baris pakai OFFSET, di atas itu pindah ke INDEX.
Karena range hasil gesernya jatuh di luar area sheet. Misalnya titik awalnya A1 lalu kamu geser -2 baris. Penyebab lain: argumen tinggi atau lebar diisi 0, padahal minimalnya 1. Detail argumennya bisa kamu cek di dokumentasi fungsi OFFSET Microsoft.
Coba di satu grafik dashboard kamu yang paling sering diedit. 15 menit setup, gak pernah disentuh lagi.
Mau lanjut ngulik referensi dinamis yang lain? Baca panduan rumus INDIRECT. Dia bisa nunjuk sheet lain lewat dropdown. Buat yang mau ngerti dasar lookup dulu, ada panduan INDEX MATCH lengkap.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.