Pivot table adalah fitur Excel dan Google Sheets untuk merangkum data tanpa rumus: kamu cukup memilih kolom apa yang menjadi baris (misalnya cabang), kolom apa yang menjadi kolom (misalnya bulan), dan angka apa yang dijumlahkan di nilai (misalnya omzet). Dari 105 baris transaksi, hasilnya satu tabel ringkas berisi omzet setiap cabang per bulan, dan tabel itu bisa diubah susunannya dalam hitungan detik. Di bawah ada langkahnya untuk Excel dan Google Sheets, cara membaca hasilnya, dan jebakan yang paling sering membuat angkanya keliru.
Data yang Dipakai
Pivot table butuh tabel data yang rapi: satu baris judul, satu baris per kejadian, tanpa sel gabungan dan tanpa subtotal. Kalau tabelmu belum seperti itu, rapikan dulu dengan panduan format tabel data yang rapi.
Contoh di artikel ini memakai penjualan jaringan kedai kopi dari dataset latihan: 105 baris, satu baris per item yang dibeli, di kolom A sampai I.
| A: id_pesanan | B: tanggal | C: bulan | D: cabang | E: kategori | F: produk | G: qty | H: harga_satuan | I: omzet |
|---|---|---|---|---|---|---|---|---|
| 1001 | 2026-01-04 | 2026-01 | Tebet | Non-Kopi | Es Teh Lemon | 2 | 15000 | 30000 |
| 1002 | 2026-01-05 | 2026-01 | Dago | Non-Kopi | Matcha Latte | 1 | 27000 | 27000 |
| 1003 | 2026-01-08 | 2026-01 | Darmo | Kopi | Kopi Susu Gula Aren | 1 | 22000 | 22000 |
Tabel ini didapat dengan menjalankan query berikut di tempat latihan SQL, lalu mengunduh hasilnya sebagai CSV dan membukanya di Excel atau Google Sheets:
SELECT p.id_pesanan, p.tanggal, strftime('%Y-%m', p.tanggal) AS bulan,
c.nama_cabang AS cabang, pr.kategori, pr.nama_produk AS produk,
i.qty, i.harga_satuan, i.qty * i.harga_satuan AS omzet
FROM item_pesanan i
JOIN pesanan p ON p.id_pesanan = i.id_pesanan
JOIN cabang c ON c.id_cabang = p.id_cabang
JOIN produk pr ON pr.id_produk = i.id_produk
ORDER BY p.id_pesanan, pr.nama_produk;
Membuat Pivot Table di Excel
- Klik salah satu sel di dalam data. Kalau datanya sudah berupa tabel resmi (Ctrl+T), pivot table otomatis ikut membaca baris baru.
- Pilih Insert → PivotTable. Pastikan rentang yang terpilih mencakup seluruh data, lalu pilih New Worksheet.
- Panel PivotTable Fields muncul di kanan, berisi daftar nama kolom dan empat kotak: Filters, Columns, Rows, dan Values.
- Seret cabang ke Rows, bulan ke Columns, dan omzet ke Values.
- Di kotak Values, pastikan tertulis Sum of omzet. Kalau tertulis Count of omzet, klik → Value Field Settings → pilih Sum.
Membuat Pivot Table di Google Sheets
- Blok seluruh data, termasuk baris judul.
- Pilih Insert → Pivot table, lalu New sheet → Create.
- Di Pivot table editor di kanan, klik Add di samping Rows → pilih cabang.
- Add di samping Columns → pilih bulan.
- Add di samping Values → pilih omzet, dengan Summarize by: SUM.
Pivot table di Google Sheets ter-update otomatis saat data sumber berubah. Di Excel, klik kanan pivot table → Refresh (atau Data → Refresh All) setiap kali data berubah.
Membaca Hasilnya
Hasil kedua aplikasi sama:
| cabang | 2026-01 | 2026-02 | 2026-03 | 2026-04 | Grand Total |
|---|---|---|---|---|---|
| Buahbatu | 99.000 | 121.000 | 161.000 | 102.000 | 483.000 |
| Dago | 132.000 | 292.000 | 292.000 | 288.000 | 1.004.000 |
| Darmo | 44.000 | 55.000 | 109.000 | 172.000 | 380.000 |
| Tebet | 128.000 | 203.000 | 236.000 | 113.000 | 680.000 |
| Grand Total | 403.000 | 671.000 | 798.000 | 675.000 | 2.547.000 |
Dalam satu tabel, beberapa hal langsung terlihat: Dago menyumbang hampir 40% omzet, omzet total memuncak di Maret, dan Tebet turun lebih dari separuh di April. Cara menelusuri penurunan Tebet dengan SQL ada di studi kasus analisis penjualan.
Biasakan mencocokkan Grand Total dengan =SUM(I:I) di data sumber. Kalau berbeda, ada baris yang tidak ikut terbaca.
Mengubah Susunan dalam Hitungan Detik
Kekuatan pivot table ada di sini: pertanyaan baru tidak butuh rumus baru.
- Omzet per kategori: ganti cabang di Rows dengan kategori.
- Produk terlaris: pindahkan produk ke Rows, keluarkan bulan dari Columns, lalu urutkan nilainya dari terbesar (Excel: klik kanan angka → Sort → Sort Largest to Smallest; Google Sheets: di bagian Rows, Sort by → SUM of omzet, Descending).
- Hanya cabang Bandung: seret cabang ke Filters (Excel) atau Add di samping Filters (Google Sheets), lalu centang Dago dan Buahbatu.
- Dua tingkat baris: taruh kategori di bawah cabang di Rows untuk melihat kategori di setiap cabang.
Persentase dari Total
Untuk melihat porsi setiap kategori terhadap total omzet:
- Excel: klik kanan angka di pivot table → Show Values As → % of Grand Total.
- Google Sheets: di bagian Values, Show as → % of grand total.
Hasilnya: Kopi 54,0%, Non-Kopi 26,3%, dan Makanan 19,6%. Sumber omzet terbesar terlihat tanpa perlu menghitung manual.
Mengelompokkan Tanggal per Bulan
Data contoh sudah punya kolom bulan. Kalau datamu hanya punya kolom tanggal, pivot table bisa mengelompokkannya:
- Excel: taruh tanggal di Rows atau Columns, klik kanan salah satu tanggal → Group → pilih Months (dan Years kalau datanya lebih dari setahun). Excel versi baru sering mengelompokkan tanggal secara otomatis.
- Google Sheets: taruh tanggal di Rows, klik kanan salah satu tanggal di pivot table → Create pivot date group → Year-Month.
Kolom tanggal harus benar-benar bertipe tanggal. Kalau masih berupa teks, pilihan grup tidak muncul; perbaiki dengan cara di membersihkan data.
Jebakan: Menghitung Jumlah Transaksi
Pertanyaan: "berapa transaksi di setiap cabang?" Cara yang terasa wajar adalah menaruh id_pesanan di Values dengan Count. Hasilnya:
| cabang | Count of id_pesanan | Transaksi sebenarnya |
|---|---|---|
| Buahbatu | 24 | 14 |
| Dago | 40 | 24 |
| Darmo | 14 | 10 |
| Tebet | 27 | 20 |
Count menghitung baris, dan data ini punya satu baris per item. Pesanan berisi tiga item terhitung tiga kali, sehingga totalnya 105, padahal pesanannya hanya 68. Ini jebakan yang sama dengan baris ganda setelah JOIN di SQL.
Cara menghitung transaksi unik:
- Google Sheets: ganti Summarize by menjadi COUNTUNIQUE.
- Excel: saat membuat pivot table, centang Add this data to the Data Model. Setelah itu, di Value Field Settings tersedia pilihan Distinct Count.
Kolom Hitungan (Calculated Field)
Untuk rata-rata nilai per item (omzet dibagi qty) di setiap cabang:
- Excel: PivotTable Analyze → Fields, Items & Sets → Calculated Field, beri nama, lalu isi rumus
= omzet / qty. - Google Sheets: di Values, Add → Calculated field, lalu isi
=SUM(omzet)/SUM(qty)dan pilih Summarize by: Custom.
Untuk rata-rata nilai per pesanan, calculated field tidak cukup karena butuh jumlah transaksi unik. Hitung terpisah: omzet cabang dibagi jumlah transaksi unik dari langkah sebelumnya. Hasilnya Dago Rp41.833, Darmo Rp38.000, Buahbatu Rp34.500, dan Tebet Rp34.000.
Kesalahan yang Sering Terjadi
- Data sumber tidak rapi. Baris kosong membuat pivot table berhenti membaca di tengah, dan judul ganda membuat kolom tidak bisa dipakai.
- Count alih-alih Sum. Kalau ada satu saja sel teks di kolom angka, Excel memilih Count secara otomatis. Cek kotak Values setiap kali.
- Lupa Refresh di Excel. Data sudah berubah, pivot table masih menampilkan angka lama.
- Rentang tidak mencakup data baru. Pakai tabel resmi (Ctrl+T), atau di Google Sheets blok seluruh kolom (A:I).
- Menghitung transaksi dengan Count biasa di data per item. Pakai jumlah unik.
Langkah Berikutnya
- Rangkuman yang sama dengan rumus, sehingga bisa ditempatkan bebas di lembar laporan: fungsi QUERY di Google Sheets.
- Ubah pivot table menjadi grafik dan susun dalam satu halaman: dashboard sederhana di Google Sheets.
- Langkah ini adalah bagian dari Roadmap Spreadsheet untuk Analisis Data. Logika yang sama di SQL dibahas di GROUP BY dan HAVING.