Mau lihat “cuma transaksi bulan ini yang belum lunas, urut dari terbesar” tanpa ngutak-ngatik data aslinya? Rumus FILTER dan SORT bikin tabel hasil baru yang update otomatis — data sumbermu tetap utuh dan aman. Kita bahas dari dasar sampai gabungannya buat dashboard.
Want to see “only this month's unpaid transactions, sorted largest first” without touching the source? The FILTER and SORT formulas build a new result table that updates automatically — your source data stays intact. We cover them from basics to combining for a dashboard.
Tombol Filter di toolbar nyembunyiin baris di tabel asli — dan karena itu ngubah tampilan buat semua orang yang buka file (apalagi kalau dipakai bareng). Rumus FILTER beda: dia bikin salinan hasil di tempat/sheet lain, jadi tiap orang bisa punya tampilannya sendiri tanpa ngganggu data sumber atau tampilan orang lain.The Filter button in the toolbar hides rows in the original table — and so it changes the view for everyone opening the file (especially when shared). The FILTER formula is different: it builds a result copy on another spot/sheet, so each person can have their own view without disturbing the source or anyone else's view.
=FILTER(rentang; syarat). Contoh: =FILTER(A2:D; C2:C="Belum") nampilin semua baris yang statusnya Belum. Syaratnya bisa digabung: pakai * untuk logika DAN (semua syarat harus benar) dan + untuk ATAU (salah satu benar).=FILTER(range, condition). Example: =FILTER(A2:D, C2:C="Unpaid") shows all rows with status Unpaid. Conditions can combine: use * for AND (all must be true) and + for OR (any is true).
=SORT(rentang; nomor_kolom; TRUE/FALSE). TRUE = menaik (A-Z atau kecil-besar), FALSE = menurun. Contoh urut omzet terbesar: =SORT(A2:D; 4; FALSE) — mengurutkan seluruh tabel berdasarkan kolom ke-4 dari besar ke kecil.=SORT(range, col_number, TRUE/FALSE). TRUE = ascending (A-Z or small-large), FALSE = descending. Example, highest revenue first: =SORT(A2:D, 4, FALSE) — sorts the whole table by the 4th column, largest to smallest.
Tumpuk keduanya buat hasil yang disaring sekaligus terurut: =SORT(FILTER(A2:D; C2:C="Belum"); 4; FALSE) — nampilin cuma yang belum lunas, lalu urut dari nominal terbesar. Satu rumus, satu laporan hidup yang update sendiri tiap data berubah.Nest them for a result that's filtered and sorted at once: =SORT(FILTER(A2:D, C2:C="Unpaid"), 4, FALSE) — shows only unpaid rows, then sorts by largest amount. One formula, one live report that updates itself as data changes.
Dari sheet transaksi, kamu mau panel “piutang belum lunas” di dashboard. Di sheet Dashboard, tulis =SORT(FILTER(Transaksi!A2:E; Transaksi!D2:D="Belum"); 5; FALSE) — otomatis muncul daftar yang belum bayar, diurut dari nominal terbesar supaya yang paling perlu ditagih ada di atas.From a transactions sheet, you want an “unpaid receivables” panel on a dashboard. On the Dashboard sheet, write =SORT(FILTER(Transactions!A2:E, Transactions!D2:D="Unpaid"), 5, FALSE) — an automatic list of the unpaid, sorted by largest amount so the most urgent to collect sits on top.
Begitu ada yang bayar dan statusnya diubah jadi “Lunas” di sheet transaksi, dia otomatis hilang dari panel piutang — tanpa kamu sentuh dashboard. Ini contoh laporan yang benar-benar hidup.Once someone pays and their status is changed to “Paid” in the transactions sheet, they automatically drop off the receivables panel — without you touching the dashboard. A genuinely live report.
FILTER(A2:D; A2:A>=E1) buat batas rentang tanggal.+ dates — FILTER(A2:D, A2:A>=E1) for a date-range cutoff.UNIQUE(FILTER(...)) buat daftar unik tanpa duplikat.+ UNIQUE — UNIQUE(FILTER(...)) for a deduplicated list.FILTER(A2:A; ISNUMBER(SEARCH("kaos";A2:A))) buat cari yang mengandung kata.+ SEARCH — FILTER(A2:A, ISNUMBER(SEARCH("shirt",A2:A))) to find rows containing a word.Tips: Kalau FILTER ngasih #N/A pas nggak ada yang cocok, tambahin argumen ketiga sebagai cadangan: =FILTER(A2:D; C2:C="x"; "Tidak ada data") — jadi munculnya teks, bukan error.Tip: If FILTER returns #N/A when nothing matches, add a third argument as a fallback: =FILTER(A2:D, C2:C="x", "No data") — so you get text, not an error.
(1) Hasilnya “tumpah” (spill) — FILTER/SORT ngisi banyak sel dari satu rumus; pastikan sel di bawah & kanannya kosong, kalau nggak muncul error #REF “array result was not expanded”. (2) Area hasil nggak bisa diketik manual — itu milik rumus. (3) Rentang syarat harus setinggi rentang data, sama seperti SUMIFS. (4) Urutan kolom ngikut data asli — FILTER nggak milih kolom; kalau mau pilih kolom tertentu, gabung dengan QUERY atau CHOOSECOLS.(1) The result “spills” — FILTER/SORT fills many cells from one formula; make sure the cells below & right are empty, or you get a #REF “array result was not expanded” error. (2) The result area can't be typed into — it belongs to the formula. (3) Criteria ranges must be as tall as the data range, like SUMIFS. (4) Column order follows the source — FILTER doesn't pick columns; to choose specific columns, combine with QUERY or CHOOSECOLS.
Pola andalan:Go-to patterns:
| TujuanGoal | RumusFormula |
|---|---|
| Saring 1 syaratFilter, 1 condition | =FILTER(A2:D;C2:C="Belum")=FILTER(A2:D,C2:C="Unpaid") |
| Saring 2 syarat (DAN)Filter, 2 (AND) | =FILTER(A2:D;(C2:C="Belum")*(B2:B="A"))=FILTER(A2:D,(C2:C="Unpaid")*(B2:B="A")) |
| Urutkan menurunSort descending | =SORT(A2:D;4;FALSE)=SORT(A2:D,4,FALSE) |
| Saring + urutFilter + sort | =SORT(FILTER(...);4;FALSE)=SORT(FILTER(...),4,FALSE) |
| Cadangan kalau kosongFallback if empty | =FILTER(A2:D;C2:C="x";"Kosong")=FILTER(A2:D,C2:C="x","Empty") |
FILTER dan SORT bikin kamu bisa “ngeliat” data dari sudut mana pun tanpa pernah ngerusak sumbernya. Ini fondasi tiap dashboard yang hidup dan update sendiri.FILTER and SORT let you “see” data from any angle without ever damaging the source. It's the foundation of every live, self-updating dashboard.
Rakitdata punya puluhan template Google Sheets siap pakai — rumus, dashboard, dan rekapnya udah jalan dari awal.Rakitdata has dozens of ready-to-use Google Sheets templates — formulas, dashboards, and recaps already running from the start.