Rumus OFFSET Excel: Bikin Range Dinamis buat Dashboard

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.

Apa itu rumus OFFSET?

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 — dan 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.

Gimana cara bikin range yang melar sendiri?

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:

1. $B$2 — titik awal, sel data pertama.
2. 0;0 — gak digeser sama sekali. Kita mulai persis dari B2.
3. COUNTA($B:$B)-1 — hitung semua sel terisi di kolom B, kurangi 1 buat buang headernya. Ini jadi tinggi range-nya.
4. 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.

Gimana cara bikin grafik yang update sendiri?

Ini yang bikin dashboard kamu berhenti minta perhatian tiap bulan.

Langkah 1: bikin named range

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)

Langkah 2: pasang ke grafik

Klik kanan grafik > Select Data. Di kolom Series values, ganti isinya jadi:

=Sheet1!data_penjualan

Nama sheet-nya wajib ditulis di depan, kalau nggak Excel nolak.

Buat label sumbu horizontal, pakai =Sheet1!label_bulan.

Langkah 3: tes

Ketik data bulan baru di baris kosong berikutnya. Grafiknya langsung nambah satu titik. Kelar.

Gimana cara bikin tren 12 bulan terakhir?

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.

Contoh kasus: dashboard penjualan Toko Berkah

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.

Kenapa OFFSET bikin file lemot?

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.

Alternatifnya: INDEX

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:

• Di bawah 10 ribu baris dan cuma dipakai buat named range grafik? OFFSET aja, lebih gampang dibaca.
• Di atas itu, atau OFFSET-nya ditarik ke ribuan sel? Pindah ke INDEX.

Perbandingan lengkapnya ada di halaman fungsi INDEX.

Kesalahan umum

1. Lupa kurangi header di COUNTA

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.

2. Ada sel kosong di tengah data

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.

3. Error #REF! karena geser keluar sheet

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.

4. Lupa tulis nama sheet waktu pasang ke grafik

Di Select Data, kamu harus tulis =Sheet1!data_penjualan, bukan cuma =data_penjualan. Excel nolak yang kedua tanpa penjelasan yang jelas.

5. Lupa kunci referensi

OFFSET(B2;...) tanpa tanda dolar bakal geser tiap kali rumusnya dicopy. Selalu pakai $B$2.

FAQ

Apa fungsi rumus OFFSET di Excel?

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.

Kenapa OFFSET disebut fungsi volatile?

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.

Gimana cara bikin grafik yang otomatis nambah kalau ada data baru?

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.

Lebih bagus pakai OFFSET atau INDEX buat range dinamis?

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.

Apa yang bikin OFFSET keluar error #REF!?

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.

Penutup

Tiga hal yang perlu kamu bawa pulang:

• OFFSET + COUNTA = range yang melar sendiri. Ini kombinasi yang paling sering kepake.
• Pasang hasilnya jadi named range, terus jadiin sumber grafik. Dashboard berhenti minta diurus tiap bulan.
• OFFSET itu volatile. Pakai secukupnya, jangan ditarik ke ribuan sel.

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.