Punya ribuan baris transaksi tapi butuh laporan ringkas “total per produk” atau “omzet per bulan”? Pivot Table bikin itu dalam hitungan klik — tanpa nulis satu rumus pun. Ini senjata paling cepat buat meringkas data besar, dan di artikel ini kita bedah dari cara bikin sampai bedanya dengan QUERY.
Got thousands of transaction rows but need a tidy “total per product” or “revenue per month” report? A Pivot Table does it in a few clicks — without writing a single formula. It's the fastest weapon for summarizing big data, and here we cover it from creation to how it differs from QUERY.
Rumus seperti SUMIFS butuh kamu tahu persis mau ngerekap apa, dan nulis satu per satu. Pivot Table beda: kamu bisa geser-geser dimensinya — coba lihat per produk, lalu ganti per bulan, tambah filter cabang — semua dalam hitungan detik tanpa ngerusak data asli.Formulas like SUMIFS require you to know exactly what to summarize, and write them one by one. A Pivot Table is different: you drag dimensions around — view per product, then switch to per month, add a branch filter — all in seconds without touching the raw data.
Blok datamu termasuk baris judul kolom, lalu buka menu Sisipkan → Tabel pivot. Pilih taruh di sheet baru biar rapi. Panel pengaturan muncul di sebelah kanan — dari situ kamu susun laporannya.Select your data including the header row, then open the menu Insert → Pivot table. Choose to place it on a new sheet to keep things tidy. A settings panel appears on the right — that's where you build the report.
Syarat penting sebelum mulai: data sumber harus rapi — satu baris satu record, tiap kolom punya judul yang jelas, dan nggak ada baris kosong di tengah. Pivot bekerja paling baik di atas data yang “bersih” seperti ini.One important prerequisite: the source data must be tidy — one row per record, each column with a clear header, and no blank rows in the middle. Pivots work best on “clean” data like this.
Tiga tombol kunci di panel: Baris (dimensi utama yang jadi daftar ke bawah, misal Produk), Kolom (pecah menyamping, misal Bulan), dan Nilai (angka yang dihitung, misal SUM dari Omzet). Geser field ke tempat yang pas, laporan langsung kebentuk.Three key buttons in the panel: Rows (the main dimension listed downward, e.g. Product), Columns (split sideways, e.g. Month), and Values (the number computed, e.g. SUM of Revenue). Drag fields into place and the report forms instantly.
Contoh gampang: taruh Produk di Rows, Omzet di Values — langsung dapat total omzet per produk. Tambah Bulan di Columns, dan kamu dapat matriks produk × bulan. Satu susunan, satu laporan.Easy example: put Product in Rows and Revenue in Values — instant total revenue per product. Add Month to Columns and you get a product × month matrix. One arrangement, one report.
Di bagian Nilai kamu bisa pilih cara ngitungnya: SUM (jumlah), COUNTA (hitung), AVERAGE (rata-rata), MAX/MIN, bahkan “% dari total” buat lihat kontribusi tiap baris. Satu klik ganti dari total jadi rata-rata — nggak perlu rumus baru.In the Values area you pick how it computes: SUM, COUNTA, AVERAGE, MAX/MIN, even “% of total” to see each row's contribution. One click switches from total to average — no new formula needed.
Dari sheet transaksi (tanggal, produk, nominal): buat pivot, taruh Produk di Rows, Nilai = SUM nominal. Buat pecahan bulanan, tambah field tanggal di Columns dan grup jadi per bulan (klik kanan di pivot → Buat grup berdasarkan bulan). Hasilnya tabel produk ke bawah, bulan ke samping, omzet di tiap sel.From a transactions sheet (date, product, amount): create a pivot, put Product in Rows, Values = SUM of amount. For a monthly breakdown, add the date field to Columns and group it by month (right-click in the pivot → Create group by month). The result: products down, months across, revenue in each cell.
Dari situ kamu langsung lihat produk mana yang naik-turun tiap bulan, mana yang nyumbang omzet terbesar, dan tren musiman — semua tanpa satu rumus pun. Buat presentasi ke klien atau evaluasi bulanan, ini cepet banget.From there you instantly see which products rise and fall each month, which contributes the most revenue, and seasonal trends — all without a single formula. For a client presentation or monthly review, it's very fast.
Tambah Filter di panel buat batasi laporan (misal cuma cabang Jakarta). Buat yang lebih interaktif, pasang Slicer (Data → Tambahkan slicer) — tombol filter yang bisa diklik langsung di atas pivot, enak buat dashboard.Add a Filter in the panel to narrow the report (e.g. only the Jakarta branch). For something more interactive, add a Slicer (Data → Add a slicer) — a clickable filter button right above the pivot, nice for dashboards.
Soal update: pivot menyegarkan diri otomatis saat data sumber berubah, asal rentang sumbernya mencakup baris baru. Makanya set rentang sumber terbuka (A:F tanpa batas baris) biar data baru otomatis ikut terhitung.On updates: a pivot refreshes itself when the source changes, as long as the source range covers new rows. So set an open-ended source range (A:F with no row limit) to auto-include new data.
Tips: Biar pivot ikut nambah sendiri pas ada data baru, set rentang sumbernya terbuka (misal A1:F) tanpa batas baris, bukan A1:F500. Kalau pivot nggak update, cek dulu apakah data baru masih di dalam rentang sumber.Tip: To make the pivot grow with new data, set the source range open-ended (e.g. A1:F) with no row limit, not A1:F500. If the pivot doesn't update, first check whether the new data is still inside the source range.
(1) Data mentah harus rapi — satu record satu baris, tanpa sel gabungan (merge) atau baris subtotal di tengah. (2) Pivot nggak bisa diedit manual — kamu nggak bisa ketik di sel hasil pivot; kalau mau ubah, ubah susunan field-nya. (3) Tanggal yang disimpan sebagai teks nggak bisa digrup per bulan — pastikan kolom tanggal beneran format tanggal.(1) Raw data must be tidy — one record per row, no merged cells or subtotal rows in the middle. (2) A pivot can't be edited manually — you can't type into result cells; to change it, change the field arrangement. (3) Dates stored as text can't be grouped by month — make sure the date column is a real date format.
Keduanya ngerekap data, tapi beda gaya. Pivot cepat, visual, dan enak buat eksplorasi tarik-geser — cocok kalau kamu belum tahu persis mau lihat apa. QUERY berbasis rumus, jadi hasilnya bisa dipakai ulang di rumus lain dan update otomatis tanpa klik. Buat laporan tetap di dashboard, QUERY sering lebih praktis; buat analisa sekali-dua kali, pivot lebih gesit.Both summarize data, but in different styles. Pivot is fast, visual, and great for drag-and-drop exploration — ideal when you don't yet know exactly what to look at. QUERY is formula-based, so its result can feed other formulas and updates automatically without clicking. For a fixed dashboard report, QUERY is often handier; for one-off analysis, a pivot is nimbler.
Taruh field di mana:Where to put each field:
| AreaArea | FungsiFunction | ContohExample |
|---|---|---|
| RowsRows | Dimensi utama (ke bawah)Main dimension (down) | Produk, KategoriProduct, Category |
| ColumnsColumns | Pecah menyampingSplit sideways | Bulan, CabangMonth, Branch |
| ValuesValues | Angka dihitungComputed number | SUM(Omzet)SUM(Revenue) |
| Values → %Values → % | Kontribusi persenPercent contribution | % of grand total% of grand total |
| Filter / SlicerFilter / Slicer | Batasi dataNarrow data | Cabang = JakartaBranch = Jakarta |
Sekali kamu paham Rows-Columns-Values, Pivot Table jadi cara tercepat bikin laporan apa pun. Coba seret-seret field-nya — nggak ada yang rusak, data sumber tetap aman.Once you get Rows-Columns-Values, the Pivot Table becomes the fastest way to build any report. Just drag the fields around — nothing breaks, the source data stays safe.
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.