100+ trik pivottable tersembunyi

Ada opsi di tab “Sisipkan” untuk memeriksa tabel pivot yang direkomendasikan. Saat Anda mengeklik “PivotTable yang Direkomendasikan”, ini menunjukkan kepada Anda serangkaian tabel pivot yang mungkin dapat dilakukan dengan data yang Anda miliki.

Opsi ini sangat berguna ketika Anda ingin melihat semua kemungkinan yang Anda miliki dengan data yang tersedia.

Ada alat di Excel yang disebut “Analisis Cepat” yang terlihat seperti toolbar cepat yang muncul setiap kali Anda memilih rentang data.

Dan dari alat ini Anda juga bisa membuat tabel pivot.

Alat Analisis Cepat ➜ Tabel ➜ Tabel Pivot Kosong.

Ini adalah salah satu tip tabel pivot paling berguna dalam daftar ini yang saya ingin Anda mulai gunakan sekarang.

Katakanlah Anda ingin melakukan pivot dari buku kerja yang ada di folder berbeda dan Anda tidak ingin menambahkan data dari buku kerja tersebut ke lembar Anda saat ini.

Anda dapat menautkan file ini sebagai sumber tanpa menambahkan data apa pun ke file saat ini, berikut langkah-langkahnya.

• Di kotak dialog Buat PivotTable, pilih “Gunakan sumber data eksternal”.

• Setelah itu, buka tab Koneksi dan klik “Jelajahi lebih lanjut”.

• Temukan file yang ingin Anda gunakan dan pilih.
• Klik Oke.
• Sekarang pilih lembar di mana Anda memiliki data.

• Klik OK (dua kali).

Anda sekarang dapat membuat tabel pivot dengan semua opsi bidang dari file sumber eksternal.

Daripada membuat PivotTable dari tab Sisipkan, Anda juga bisa menggunakan “Classic PivotTable dan PivotChart Wizard”.

Satu hal yang saya suka tentang wizard klasik adalah adanya opsi untuk mengekstrak data dari beberapa lembar kerja sebelum membuat tabel pivot.

Cara mudah untuk membuka wizard ini adalah dengan menggunakan pintasan keyboard: Alt + D + P.

Dalam pengaturan bidang tabel pivot, terdapat opsi untuk mencari bidang. Anda dapat mencari bidang yang memiliki ratusan kolom besar.

Saat Anda mulai mengetik di kotak pencarian, ia mulai memfilter kolom.

Ada opsi yang dapat Anda gunakan untuk mengubah gaya “jendela bidang tabel pivot”. Klik ikon roda gigi di kanan atas dan pilih gaya yang ingin Anda terapkan.

Jika Anda memiliki kumpulan data yang besar, Anda dapat mengurutkan daftar bidang di AZ agar lebih mudah menemukan bidang yang diperlukan.

Klik ikon roda gigi di kanan atas dan pilih “Urutkan A hingga Z”. Secara default, bidang diurutkan berdasarkan data sumber.

Kebetulan ketika saya membuat tabel pivot dan mengkliknya, “Daftar Bidang” muncul di sisi kanan dan ini terjadi setiap kali saya mengklik tabel pivot.

Namun Anda dapat menonaktifkannya dan untuk melakukannya, cukup klik “Tombol Daftar Feild” di tab “Analisis PivotTable”.

Setelah Anda membuat tabel pivot, hal berikutnya yang menurut saya perlu Anda lakukan adalah memberi nama tabel pivot.

Dan untuk itu, Anda bisa masuk ke tab Analisis ➜ Tabel Pivot ➜ Opsi Tabel Pivot lalu masukkan nama baru.

Baru-baru ini, opsi untuk membuat tabel pivot telah ditambahkan ke aplikasi online Excel (opsi terbatas).

Ini semudah membuat pivot di aplikasi web Excel:

Pada tab Sisipkan, klik tombol “Tabel Pivot” di grup tabel…

…lalu pilih rentang data sumber…

…dan spreadsheet tempat Anda ingin menyisipkannya…

…dan, pada akhirnya, klik OK.

Jika Anda ingin mengotomatiskan proses pembuatan tabel pivot, Anda dapat menggunakan kode VBA untuk itu.

Dalam panduan ini , saya telah menyebutkan proses langkah demi langkah sederhana untuk membuat tabel pivot menggunakan kode makro.

Ada beberapa gaya yang telah ditentukan sebelumnya di Excel untuk tabel pivot yang bisa Anda terapkan hanya dengan satu klik.

Di tab yang dirancang, Anda dapat menemukan “Gaya Tabel Pivot” dan ketika Anda mengklik “Lainnya”, Anda cukup memilih gaya yang Anda suka.

Anda juga dapat membuat gaya kustom baru, Anda dapat melakukannya menggunakan opsi “Gaya Tabel Pivot Baru”.

Setelah Anda selesai dengan gaya kustom Anda, Anda cukup menyimpannya untuk digunakan lain kali, gaya tersebut akan tetap ada.

Buka Opsi PivotTable (klik kanan pada PivotTable dan buka Opsi PivotTable) dan centang kotak “Pertahankan pemformatan sel saat memperbarui”.

Keuntungan dari opsi ini adalah setiap kali Anda memperbarui tabel pivot, Anda tidak akan kehilangan format yang Anda miliki.

Selain memformatnya, Anda juga perlu menyimpannya yaitu “Lebar Kolom”.

Untuk melakukannya, buka “Opsi Tabel Pivot” dan hapus centang “Paskan otomatis lebar kolom saat diperbarui” lalu klik OK.

Saat menggunakan beberapa item dalam tabel pivot, Anda cukup mengulangi label untuk item teratas. Ini memudahkan untuk memahami struktur tabel pivot.

• Pilih tabel pivot dan navigasikan ke tab “Desain”.
• Pada tab Desain, buka Tata Letak ➜ Tata Letak Laporan ➜ Ulangi Semua Label Item.

Dalam kebanyakan kasus, Anda harus memformat nilai setelah membuat tabel pivot.

Misalnya, jika Anda ingin mengubah jumlah desimal suatu angka. Yang perlu Anda lakukan adalah memilih kolom nilai dan membuka opsi “Format Sel”.

Dan dari opsi ini Anda dapat mengubah angka desimal. Dari opsi “Format”, Anda bahkan dapat mengubah opsi lainnya.

Salah satu hal favorit saya tentang pemformatan adalah mengubah “Gaya Font” untuk PivotTable.

Anda dapat menggunakan opsi format, namun cara termudah adalah melakukannya dari tab Beranda. Pilih seluruh tabel pivot, lalu pilih gaya font.

Saat Anda menambahkan tabel pivot dengan lebih dari satu bidang item, Anda mendapatkan subtotal untuk bidang utama.

Namun terkadang tidak perlu menampilkan subtotal. Dalam situasi ini, Anda dapat menyembunyikannya dengan melakukan hal berikut:

• Klik pada tabel pivot dan buka tab Analisis.
• Pada tab Analisis, buka Tata Letak ➜ Subtotal ➜ Jangan Tampilkan Subtotal.

Sama seperti subtotal, Anda juga dapat menyembunyikan dan menampilkan total keseluruhan dan berikut adalah langkah sederhana untuk melakukannya.

• Klik pada tabel pivot dan buka tab Analisis.
• Pada tab Analisis, buka Tata Letak ➜ Total Keseluruhan ➜ Nonaktif untuk Baris dan Kolom.

Dalam tabel pivot normal, kita hanya memiliki satu format nilai di kolom nilai.

Namun, ada beberapa situasi (yang jarang terjadi) di mana Anda perlu memiliki format berbeda dalam satu tabel pivot, seperti di bawah ini. Untuk melakukan ini, Anda perlu menggunakan pemformatan khusus .

Di Excel, ada tema warna standar yang bisa Anda gunakan. Tema-tema ini juga dapat diterapkan pada tabel pivot. Buka tab “Tata Letak” dan klik menu tarik-turun “Tema”.

Ada lebih dari 32 tema yang dapat Anda terapkan hanya dengan satu klik atau Anda dapat menyimpan gaya pemformatan Anda saat ini sebagai sebuah tema.

Untuk setiap tabel pivot, Anda dapat memilih tata letak.

Di Excel (jika Anda menggunakan versi 2007 atau lebih tinggi) Anda bisa memiliki tiga tata letak berbeda. Pada tab Desain, buka Laporan tata letak ➜ Tata letak dan pilih tata letak yang ingin Anda terapkan.

Salah satu hal pertama yang saya lakukan saat membuat tabel pivot adalah menerapkan “Baris dan Kolom Bermerek”.

Anda dapat menerapkannya dari tab desain dan mencentang “Kolom Berpita” dan “Garis Berpita”.

Sama seperti filter biasa, Anda dapat mengaktifkan/menonaktifkan filter di tabel pivot. Di “Tab Analisis”, Anda dapat mengklik tombol “Header Bidang” untuk mengaktifkan atau menonaktifkan filter.

Anda telah memilih satu atau lebih sel dalam tabel pivot dan Anda hanya ingin memfilter sel tersebut, berikut adalah opsi yang dapat Anda gunakan.

Setelah memilih sel, klik kanan dan buka “Filter”, lalu pilih “Simpan item yang dipilih saja”.

Sama seperti memfilter sel yang dipilih, Anda juga dapat menyembunyikannya. Untuk melakukan ini, buka “Filter” dan setelah itu pilih “Sembunyikan item yang dipilih”.

Selain filter normal, Anda menggunakan filter label dan filter nilai untuk memfilter dengan nilai atau kriteria tertentu.

Penyaring label:

Filter nilai:

Seperti yang saya katakan pada tip di atas, Anda dapat memiliki bidang Label dan Nilai, tetapi Anda harus mengaktifkan opsi untuk menggunakan kedua opsi filter ini secara bersamaan.

• Pertama, buka “Opsi Tabel Pivot” dan buka tab “Total & Filter”.
• Di tab “Total dan filter”, centang kotak “Izinkan beberapa filter per bidang” .
• Setelah itu klik OK.

Salah satu opsi favorit saya dalam filter adalah memfilter “10 Nilai Teratas” . Opsi filter ini berguna saat membuat laporan instan.

Untuk melakukan ini, Anda harus pergi ke “Value Filter” dan klik “Top 10” lalu klik OK.

Jika Anda ingin memfilter saat membuat tabel pivot, Anda dapat melakukannya dari jendela “Bidang Pivot”.

Untuk memfilter nilai dalam kolom, Anda dapat mengklik panah bawah di sebelah kanan dan memfilter nilai sesuai kebutuhan.

Salah satu hal terbaik yang saya temukan untuk memfilter data dalam tabel pivot adalah dengan menggunakan “Pemotong”.

Untuk menyisipkan alat pengiris, Anda hanya perlu masuk ke “Tab Analisis” dan di grup “Filter” klik tombol “Sisipkan Alat Pengiris”, setelah itu pilih bidang yang ingin Anda masukkan alat pengirisnya lalu klik OK.

Terkait: Excel SLICER – Panduan Lengkap tentang Cara Memfilter Data dengannya

Setelah Anda menyisipkan segmen, Anda dapat mengubah gaya dan formatnya.

• Pilih pemotong dan buka tab Opsi.
• Di “Gaya Pemotong”, klik menu tarik-turun dan pilih gaya yang ingin Anda terapkan.

Selain gaya, Anda dapat mengubah pengaturan dari jendela pengaturan: klik tombol “Pengaturan Pemotong” untuk membuka jendela pengaturan.

Terkadang ketika Anda memiliki beberapa tabel pivot, sulit untuk mengontrol semuanya. Namun jika Anda menghubungkan satu segmen dengan beberapa tabel pivot , Anda dapat mengontrol semua pivot dengan mudah.

• Pertama, masukkan alat pengiris.
• Dan, setelah itu, klik kanan pada alat pengiris dan pilih “Laporkan Koneksi”.

• Di kotak dialog, pilih semua pivot dan klik OK.

Sekarang Anda cukup memfilter semua tabel pivot dengan satu alat pengiris.

Berbeda dengan segmen, garis waktu adalah alat pemfilteran khusus untuk memfilter tanggal dan jauh lebih canggih dibandingkan filter biasa.

Untuk menyisipkan pemotong, Anda hanya perlu masuk ke “Tab Analisis” dan di grup “Filter” klik tombol “ Sisipkan Timeline ” dan setelah itu pilih kolom tanggal dan klik OK.

Setelah Anda menyisipkan garis waktu, Anda dapat mengubah gaya dan formatnya.

• Pilih garis waktu dan buka tab Opsi.
• Di “Gaya Garis Waktu”, klik menu tarik-turun dan pilih gaya yang ingin Anda terapkan.

Selain gaya, Anda juga dapat mengubah pengaturan.

Anda dapat menggunakan wildcard Excel di semua opsi filter di mana Anda perlu memasukkan nilai yang akan difilter. Lihat contoh di bawah ini di mana saya menggunakan tanda bintang untuk memfilter nilai yang dimulai dengan huruf A.

Jika Anda telah menerapkan filter pada beberapa bidang, Anda dapat menghapus semua filter ini dari tab Analisis ➜ Tindakan ➜ Hapus ➜ Hapus filter.

Tabel pivot bersifat dinamis, jadi saat Anda menambahkan data baru atau memperbarui nilai di data sumber, Anda perlu menyegarkannya agar tabel pivot mendapatkan semua nilai tambah baru dari sumbernya. Menyegarkan tabel pivot itu sederhana:

• Pertama, klik kanan pada pivot dan pilih “Refresh”.
• Kedua, buka tab “Analisis” dan klik tombol “Segarkan”.

Ada opsi sederhana di Excel yang bisa Anda aktifkan dan buat tabel pivot disegarkan secara otomatis setiap kali Anda membuka buku kerja. Untuk melakukannya, berikut langkah sederhananya:

• Pertama, klik kanan pada tabel pivot dan buka “Opsi Tabel Pivot”.
• Setelah itu, buka tab “Data” dan centang kotak “Refresh data saat membuka file”.

• Pada akhirnya, klik OK.

Sekarang, setiap kali Anda membuka buku kerja, tabel pivot ini akan diperbarui secara instan.

Jika Anda ingin memperbarui tabel pivot Anda secara otomatis setelah interval tertentu, tip ini cocok untuk Anda.

…Begini cara melakukannya.

• Pertama, saat membuat PivotTable, di jendela “Buat PivotTable”, centang kotak “Tambahkan data ini ke model data”.

• Setelah itu, setelah Anda membuat tabel pivot, pilih salah satu sel dan buka “Tab Analisis”.
• Pada tab Analisis, Data ➜ Edit Sumber Data ➜ Properti Koneksi.

• Sekarang, di “Connection Properties”, di tab penggunaan, centang kotak “Refresh every” dan masukkan menitnya.

• Pada akhirnya, klik OK.

Sekarang, setelah jangka waktu tertentu yang Anda masukkan, pivot Anda akan otomatis disegarkan.

Terkadang ketika Anda memiliki kesalahan dalam data sumber, kesalahan tersebut mencerminkan hal yang sama di pivot dan itu sama sekali bukan hal yang baik.

Cara terbaik adalah mengganti kesalahan ini dengan nilai yang berarti.

Berikut adalah langkah-langkah yang harus diikuti:

• Pertama, klik kanan pada tabel pivot Anda dan buka Opsi Tabel Pivot.
• Sekarang, di “Tata Letak dan Format”, centang kotak “Untuk menampilkan nilai kesalahan” dan masukkan nilai di kotak masukan.

• Pada akhirnya, klik OK.

Sekarang untuk semua kesalahan Anda akan mendapatkan nilai yang Anda tentukan.

Katakanlah Anda memiliki pivot untuk data penjualan dan beberapa sel kosong.

Karena seseorang yang tidak mengetahui mengapa sel-sel ini kosong dapat menanyakannya kepada Anda. Oleh karena itu, lebih baik menggantinya dengan kata yang bermakna.

…langkah sederhana yang perlu Anda ikuti untuk ini.

• Pertama, “klik kanan” pada tabel pivot Anda dan buka opsi tabel pivot.
• Sekarang, di “Tata Letak dan Format”, centang kotak “Untuk menampilkan sel kosong” dan masukkan nilainya di kotak masukan.

• Pada akhirnya, klik OK.

Sekarang untuk semua sel kosong Anda akan mendapatkan nilai yang Anda tentukan.

Katakanlah Anda memiliki pivot untuk data penjualan dan beberapa sel kosong.

Karena seseorang yang tidak mengetahui mengapa sel-sel ini kosong dapat menanyakannya kepada Anda. Oleh karena itu, lebih baik menggantinya dengan kata yang bermakna.

…langkah sederhana yang perlu Anda ikuti untuk ini.

• Pertama, “klik kanan” pada tabel pivot Anda dan buka opsi tabel pivot.
• Sekarang, di “Tata Letak dan Format”, centang kotak “Untuk menampilkan sel kosong” dan masukkan nilainya di kotak masukan.

• Pada akhirnya, klik OK.

Sekarang untuk semua sel kosong Anda akan mendapatkan nilai yang Anda tentukan.

Sekarang anggaplah Anda memiliki tabel pivot besar dengan beberapa item.

Di sini Anda dapat menyisipkan baris kosong setelah setiap elemen sehingga tidak ada kekacauan di pivot.

Lihat langkah-langkah berikut untuk diikuti:

• Pilih tabel pivot dan buka tab Desain.
• Pada tab Desain, buka Tata Letak ➜ Garis Kosong ➜ Sisipkan Baris Kosong Setelah Setiap Elemen.

Hal terbaik tentang opsi ini adalah memberikan gambaran yang lebih jelas tentang laporan Anda.

Saat Anda memiliki daftar panjang item di pivot, Anda dapat mengatur semua item tersebut dalam urutan khusus hanya dengan menyeret dan melepas.

Katakanlah Anda membuat tabel pivot dari data penjualan bulanan dan menggunakan produk sebagai filter laporan.

Dengan opsi “Tampilkan Halaman Filter Laporan”, Anda dapat membuat beberapa spreadsheet dengan tabel pivot untuk setiap produk.

Katakanlah jika Anda memiliki 10 produk dalam filter pivot, Anda dapat membuat 10 spreadsheet berbeda hanya dengan satu klik.

Ikuti langkah ini:

• Pilih pivot Anda dan buka tab analisis.
• Pada tab Analisis, navigasikan ke PivotTable ➜ Opsi ➜ Tampilkan Halaman Filter Laporan.

Anda sekarang memiliki empat tabel pivot di empat lembar kerja terpisah.

Saat Anda menambahkan kolom nilai di bidang nilai, kolom tersebut menampilkan SUM atau COUNT (terkadang), namun ada beberapa hal lain yang dapat Anda hitung di sini:

Untuk membuka opsi “Pengaturan Nilai”, pilih sel di kolom nilai dan klik kanan.

Dan di menu konteks, buka “Value Field Settings”, lalu klik

Di kolom “Ringkas nilai berdasarkan”, pilih jenis penghitungan yang ingin Anda tampilkan di pivot.

Katakanlah Anda memiliki satu penjualan tabel pivot per bulan.

Sekarang Anda ingin memasukkan total berjalan ke dalam tabel pivot Anda untuk menunjukkan pertumbuhan penjualan penuh selama sebulan penuh.

Berikut langkah-langkahnya:

• Klik kanan padanya dan klik “Pengaturan Bidang Nilai”.
• Di daftar tarik-turun “Tampilkan nilai sebagai”, pilih “Total kumulatif masuk.”

• Pada akhirnya, klik OK.

Pelajari lebih lanjut tentang menambahkan total berjalan ke tabel pivot .

Pemeringkatan memberi Anda cara yang lebih baik untuk membandingkan berbagai hal satu sama lain…

…dan untuk memasukkan kolom peringkat ke dalam tabel pivot, Anda dapat mengikuti langkah-langkah berikut:

• Pertama, masukkan kolom data yang sama dua kali ke dalam pivot.
• Setelah itu, untuk kolom kedua, klik kanan padanya dan buka “Value Field Settings”.
• Buka tab “Tampilkan Nilai Sebagai” dan pilih “Urutkan Dari Yang Terbesar ke Terkecil”.

• Pada akhirnya, klik OK.

…klik di sini untuk mempelajari lebih lanjut tentang peringkat dalam tabel pivot .

Bayangkan Anda memiliki tabel pivot untuk penjualan produk.

Dan sekarang Anda ingin menghitung persentase bagian seluruh produk dalam total penjualan.

Langkah-langkah untuk menggunakan:

• Pertama, masukkan kolom data yang sama dua kali ke dalam pivot.
• Setelah itu, untuk kolom kedua, klik kanan padanya dan buka “Value Field Settings”.
• Buka tab “Tampilkan nilai sebagai” dan pilih “% dari total keseluruhan”.

• Pada akhirnya, klik OK.

Ini juga merupakan pilihan sempurna untuk membuat laporan cepat.

Saat Anda membuat PivotTable, Excel meminta Anda menambahkan lembar kerja baru untuk PivotTable…

…tetapi ia juga memiliki opsi untuk memindahkan tabel pivot yang ada ke lembar kerja baru.

• Untuk melakukannya, buka “tab Analisis” ➜ Tindakan ➜ Pindahkan tabel pivot.

Ada situasi di mana Anda perlu merujuk ke sel di pivot.

Namun, mungkin terdapat masalah karena saat Anda merujuk ke sel dalam pivot, Excel secara otomatis menggunakan fungsi GetPivotData sebagai referensi.

Hal terbaiknya adalah Anda dapat menonaktifkannya dan berikut langkah-langkahnya:

• Buka tab File ➜ Opsi.
• Di opsi, buka Rumus ➜ Bekerja dengan rumus ➜ hapus centang “Gunakan fungsi GetPivotData untuk referensi tabel pivot”.

Anda juga dapat menggunakan kode VBA untuk ini:

SubnonaktifkanDapatkanPivotData()

Aplikasi.GenerateGetPivotData = Salah

Mengakhiri subjudul

Lihat ini ➜ 100 Kode VBA Excel Berguna + File PDF

Bayangkan, Anda ingin membuat tabel pivot berdasarkan bulan tetapi Anda memiliki tanggal di datanya.

Dalam situasi ini, Anda harus menambahkan kolom tambahan selama berbulan-bulan.

Namun cara terbaiknya adalah dengan membuat menggunakan metode pengelompokan tanggal pada tabel pivot. Dengan menggunakan metode ini Anda tidak perlu menambahkan kolom pembantu.

Gunakan langkah-langkah di bawah ini:

• Pertama, Anda perlu memasukkan tanggal sebagai item baris di tabel pivot Anda.

• Klik kanan pada tabel pivot dan pilih “Grup…”.

• Pilih “Bulan” di bagian menurut dan klik OK.

Ini akan mengelompokkan semua tanggal ke dalam beberapa bulan dan jika Anda ingin tahu lebih banyak tentang opsi ini, berikut adalah panduan lengkapnya.

Sama seperti tanggal, Anda juga dapat mengelompokkan nilai numerik.

Langkah-langkahnya sederhana.

• Klik kanan pada tabel pivot dan pilih “Grup…”.
• Masukkan nilai untuk membuat rentang grup di kotak “oleh” dan klik OK.

…klik di sini untuk mempelajari bagaimana opsi pengelompokan tabel pivot dapat membantu Anda membuat histogram di Excel.

Untuk mengelompokkan kolom seperti baris, Anda dapat mengikuti langkah yang sama seperti baris. Namun Anda harus memilih tajuk kolom sebelum itu.

Jika Anda tidak memerlukan grup dalam tabel pivot, Anda cukup memisahkan grup tersebut dengan mengklik kanan dan memilih “Batalkan grup”.

Untuk menjadi pengguna tabel pivot tingkat lanjut, Anda perlu mempelajari cara membuat bidang terhitung dan item dalam tabel pivot.

Katakanlah dalam tabel pivot di bawah, Anda perlu membuat data baru per kolom dengan mengalikan kolom data saat ini dengan 10.

Dalam situasi ini, alih-alih membuat kolom terpisah di tabel pivot, Anda bisa menyisipkan item terhitung.

➜ panduan lengkap untuk membuat item dan bidang terhitung dalam tabel pivot

Setelah Anda menambahkan perhitungan ke tabel pivot atau memiliki tabel pivot dengan bidang atau item terhitung, Anda bisa melihat daftar rumus yang digunakan.

Untuk melakukan ini, cukup buka “Tab Analisis” ➜ Perhitungan ➜ Bidang, Elemen dan Himpunan ➜ Daftar Rumus.

Anda akan langsung mendapatkan lembar kerja baru dengan daftar rumus yang digunakan dalam tabel pivot.

Jika Anda memiliki nilai duplikat di tanggal Anda, Anda bisa menggunakan tabel pivot untuk mendapatkan daftar nilai unik.

• Pertama, Anda perlu menyisipkan tabel pivot dan kemudian menambahkan kolom tempat Anda memiliki nilai duplikat sebagai bidang baris.
• Setelah itu salin bidang baris ini dari pivot dan tempel sebagai nilai.
• Sekarang daftar yang Anda miliki sebagai nilai adalah daftar nilai unik.

Yang saya suka tentang penggunaan tabel pivot untuk memeriksa nilai unik adalah pengaturannya yang universal.

Anda tidak perlu membuatnya lagi dan lagi.

Katakanlah Anda memiliki entri dalam data sumber yang tidak memiliki nilai atau nilai nol.

Anda dapat mengaktifkan opsi “Tampilkan elemen tanpa data” dari lapangan.

• Pertama, klik kanan pada bidang dan buka “Pengaturan Bidang”.
• Sekarang pergi ke “Tata Letak dan Pencetakan” dan centang “Tampilkan item tanpa data” dan klik OK.

Ledakan! Item apa pun yang datanya tidak Anda miliki akan ditampilkan di tabel pivot.

Ini adalah salah satu opsi tabel pivot favorit saya.

Dengan ini Anda dapat membuat kolom yang menunjukkan perbedaan nilai saat ini dibandingkan dengan nilai sebelumnya.

Katakanlah Anda memiliki pivot dengan nilai bulan,…

…lalu, dengan opsi ini…

…Anda dapat menambahkan kolom nilai selisih dari bulan sebelumnya seperti di bawah ini.

Berikut langkah-langkahnya:

• Pertama, Anda perlu menambahkan kolom tempat Anda memiliki nilai, dua kali di bidang nilai.

• Setelah itu, untuk kolom kedua, buka “Value Setting” dan “Show Value As”.
• Sekarang, di daftar tarik-turun “Tampilkan Nilai Sebagai”, pilih “Perbedaan Dari” dan pilih “Bulan” dan “(Sebelumnya)” dari “Item Dasar”.
• Pada akhirnya, klik OK.

Ini akan langsung mengonversi kolom nilai ke kolom berbeda dari kolom sebelumnya.

Saat Anda mengklik dua kali sel nilai dalam tabel pivot, data yang mendasari nilai tersebut akan ditampilkan.

Ini adalah hal yang baik, tetapi tidak selalu Anda perlukan agar hal ini terjadi dan itulah mengapa Anda dapat mematikannya jika perlu.

Yang perlu Anda lakukan adalah membuka Opsi PivotTable dan pergi ke “Tab Data” dan hapus centang “Aktifkan Detail Tampilan”.

Dan kemudian klik OK.

Berikut adalah langkah sederhana untuk menempelkan tabel pivot ke slide PowerPoint.

• Pertama, pilih tabel pivot dan salin.
• Setelah itu, buka slide PowerPoint dan buka opsi tempel khusus.

• Sekarang, di kotak dialog tempel khusus, pilih “Objek Bagan Microsoft Excel” dan klik OK.

Sisipkan gambar

Untuk melakukan perubahan pada tabel pivot, Anda harus mengklik dua kali pada grafik.

Untuk menambahkan tabel pivot di Microsoft Word, Anda harus mengikuti langkah yang sama seperti di PowerPoint.

Jika Anda memiliki beberapa bidang dimensi dalam satu baris atau kolom, Anda dapat memperluas atau menciutkan bidang luar.

Anda perlu mengklik tombol + untuk memperluas dan tombol – untuk menutup…

…dan untuk memperluas atau menciutkan semua grup sekaligus, Anda dapat mengklik kanan dan memilih opsi.

Jika Anda memiliki beberapa bidang dimensi dalam satu baris atau kolom, Anda dapat memperluas atau menciutkan bidang luar.

Ada opsi di tabel pivot tempat Anda dapat menghitung jumlah sel dengan nilai numerik.

Untuk ini, yang perlu Anda lakukan hanyalah membuka “Value Option” dan memilih “Count Number” di “Summary Value Field By” lalu klik OK.

Ya, Anda dapat mengurutkan berdasarkan nilai yang cocok.

• Yang harus Anda lakukan adalah membuka filter dan memilih “Opsi penyortiran lainnya”.

• Kemudian pilih “Access (A to Z) by:” dan pilih kolom yang akan diurutkan lalu klik OK.

Catatan:

Jika Anda memiliki beberapa kolom nilai, Anda hanya dapat menggunakan satu kolom untuk urutan.

Ya, Anda dapat menggunakan urutan khusus untuk tabel pivot Anda.

• Untuk melakukannya, saat Anda membuka “Opsi penyortiran lainnya”, klik “Opsi lainnya” dan hapus centang pada kotak “Urutkan secara otomatis saat laporan diperbarui”.
• Setelah itu, pilih urutan pengurutan dan klik OK di akhir.

Anda perlu membuat susunan pengurutan kustom baru, lalu Anda dapat membuatnya dari tab File ➜ Opsi ➜ Lanjutan ➜ Umum ➜ Edit Daftar Kustom.

Jika Anda mengaktifkan “Pembaruan Tertunda Tata Letak” dan kemudian menarik dan melepas bidang antar area.

PivotTable Anda tidak akan diperbarui kecuali Anda mengklik tombol Perbarui di bawah, di sudut bidang PivotTable.

Ini memudahkan Anda untuk memeriksa tabel pivot.

Saat Anda memasukkan bidang nilai, nama yang Anda peroleh untuk bidang tersebut terlihat seperti “Jumlah Jumlah” atau “Jumlah Unit”.

Namun terkadang (yah, sepanjang waktu) Anda perlu mengubah nama ini menjadi nama tanpa “Jumlah” atau “Hitungan”.

Untuk melakukan ini, yang perlu Anda lakukan hanyalah menghapus “Jumlah” atau “Jumlah” dari sel dan menambahkan spasi di akhir nama.

Ya itu dia.

Jika Anda ingin memilih seluruh tabel pivot sekaligus:

Pilih salah satu sel tabel pivot dan gunakan pintasan keyboard Kontrol + A.

Atau…

Buka tab Analisis ➜ Pilih ➜ Seluruh PivotTable.

Jika Anda ingin mengonversi tabel pivot menjadi nilai, cukup pilih seluruh tabel pivot, lalu:

Gunakan Control + C untuk menyalinnya, lalu Tempel Nilai Spesial ➜.

Saat memproteksi lembar kerja yang berisi tabel pivot, pastikan untuk memeriksa:

“Gunakan PivotTable dan PivotChart”

dari “Izinkan semua pengguna spreadsheet ini untuk:”.

Jika Anda ingin membuka “Pengaturan Nilai” untuk kolom nilai tertentu…

…JADI…

…cara terbaik adalah dengan mengklik dua kali header kolom.

Jika Anda berpikir seperti ini: Saat Anda membuat tabel pivot dari awal, Excel membuat cache pivot.

Jadi semakin banyak tabel pivot yang Anda buat dari awal, semakin banyak cache pivot yang dibuat Excel dan file Anda harus menyimpan lebih banyak data.

Jadi apa gunanya?

Pastikan semua tabel pivot yang berasal dari sumber data harus memiliki cache yang sama.

Tapi Puneet, bagaimana aku bisa melakukan ini?

Sederhana, kapan pun Anda perlu membuat yang kedua, ketiga, atau keempat… cukup salin dan tempel yang pertama dan buat perubahan padanya.

Hal lain yang dapat Anda lakukan sebelum mengirimkan tabel pivot kepada seseorang adalah menghapus data sumber.

Tabel pivot Anda akan tetap berfungsi dengan benar.

Dan, jika ada yang perlu memiliki sumber datanya, mereka bisa mendapatkannya dengan mengklik total keseluruhan tabel pivot.

Cara lain untuk berbagi tabel pivot dengan seseorang adalah dengan membuat halaman web.

Ya, file HTML sederhana dengan tabel pivot.

• Untuk melakukan ini, cukup simpan buku kerja sebagai halaman web [html].

• Pada halaman Publikasikan sebagai Web, pilih tabel pivot dan klik “Terbitkan”.

Sekarang Anda dapat mengirimkan halaman web HTML ini kepada siapa pun dan mereka dapat melihat tabel pivot (tidak dapat diedit) bahkan di ponsel mereka.

Misalkan Anda memiliki link web untuk file Excel, seperti di bawah ini:

 https://exceladvisor.org/book1.xlsx

Di buku kerja ini Anda memiliki data dan dengan data ini Anda perlu membuat tabel pivot.

Untuk ini, POWER QUERY adalah kuncinya.

Lihat ini: Contoh Power Query + Tips dan Trik

• Pertama, buka tab Data ➜ Dapatkan dan Transformasikan Data ➜ Dari Web.

• Sekarang, di kotak dialog “Dari Web”, masukkan alamat web buku kerja dan klik OK.

• Setelah itu, pilih spreadsheet dan klik “Muat ke”.

• Selanjutnya, pilih laporan PivotTable dan klik OK.

Pada titik ini, Anda memiliki tabel pivot kosong yang tersambung ke buku kerja dari alamat web yang Anda masukkan.

Anda sekarang dapat membuat tabel pivot sesuai keinginan.

Ada semua opsi CF yang tersedia untuk digunakan dengan tabel pivot.

➜ berikut adalah panduan yang dapat membantu Anda mempelajari berbagai cara menggunakan CF dalam tabel pivot .

Daripada memfilter, Anda dapat menyorot 10 nilai pertama dalam tabel pivot.

Untuk melakukan ini, Anda harus menggunakan pemformatan bersyarat.

Langkah-langkahnya di bawah ini:

• Pilih salah satu sel di kolom nilai tabel pivot Anda.
• Buka tab Beranda ➜ Gaya ➜ Pemformatan Bersyarat.
• Sekarang dalam Pemformatan Bersyarat, buka Aturan Atas/Bawah ➜ 10 Elemen Teratas.

• Pilih warna di jendela yang Anda miliki.

• Dan pada akhirnya klik OK.

Opsi ini sangat berguna saat membuat laporan cepat dengan tabel pivot dan sekaligus Anda.

Anda cukup menghapus pemformatan bersyarat dari tabel pivot dengan mengikuti langkah-langkah di bawah ini:

• Pertama, pilih salah satu sel tabel pivot.
• Setelah itu, buka tab Beranda ➜ Gaya ➜ Pemformatan Bersyarat ➜ Hapus Aturan ➜ “Hapus aturan untuk tabel pivot ini”.

Jika Anda memiliki beberapa tabel pivot, Anda harus menghapus CF satu per satu.

Saya telah membagikan pintasan keyboard sederhana untuk menyisipkan tabel pivot , tetapi Anda juga menggunakan langkah-langkah di bawah ini:

• Pilih sel di tabel pivot dan buka “Tab Analisis” .
• Di tab “Analisis”, klik “Bagan Pivot”.

Ini akan langsung membuat tabel pivot dari tabel pivot yang Anda miliki.

Tabel pivot dan diagram pivot adalah cara favorit saya untuk membuat histogram di Excel.

Berikut adalah panduan langkah demi langkah untuk itu.

Saat Anda memasukkan tabel pivot baru, tabel tersebut dilengkapi dengan tombol untuk memfilternya yang terkadang tidak terlalu berguna.

Dan jika Anda berpikir seperti itu, Anda dapat menyembunyikan semua atau sebagiannya.

Klik kanan tombol tersebut dan pilih “Hide Value Field Button on Chart” untuk menyembunyikan tombol yang dipilih atau klik “Hide All Field Buttons on Chart” untuk menyembunyikan semua tombol.

Saat Anda menyembunyikan semua tombol di PivotTable, tombol filter dari bagian bawah bagan juga disembunyikan , namun Anda masih bisa memfilternya menggunakan filter PivotTable, Pemotong, atau Garis Waktu.

Berikut adalah langkah sederhana untuk menempelkan tabel pivot ke slide PowerPoint.

• Pertama, pilih tabel pivot dan salin.
• Setelah itu, buka slide PowerPoint dan buka Special Paste Options .

• Sekarang, di kotak dialog tempel khusus, pilih “Objek Bagan Microsoft Excel” dan klik OK.

Untuk membuat perubahan pada tabel pivot, Anda perlu mengklik dua kali padanya.

Alt+N+V

Untuk menggunakan tombol pintas ini, pastikan Anda telah memilih data sumber atau sel yang aktif berasal dari data sumber.

Alt + Shift + Panah Kanan

Katakanlah Anda memiliki tabel pivot dengan bulan dan Anda ingin mengelompokkan enam bulan pertama atau enam bulan terakhir.

Yang harus Anda lakukan adalah memilih enam sel ini dan cukup gunakan tombol pintas ini.

Alt + Shift + Panah Kiri

Sama seperti Anda dapat membuat grup item, pintasan ini membantu Anda memisahkan item tersebut dari grup.

Ctrl + –

Tombol pintas ini hanya akan menyembunyikan sel yang dipilih.

Ini sebenarnya tidak menyembunyikan sel tetapi memfilternya, yang kemudian dapat Anda hapus dari opsi filter.

Ctrl + –

Untuk menggunakan tombol pintas ini, Anda harus memilih sel di kolom bidang nilai.

Dan saat Anda menekan tombol pintas ini, jendela “ Bidang Terhitung ” akan terbuka.

Alt + D dan P

Dalam pintasan keyboard ini, Anda harus menekan tombol setelahnya.

Alt + panah bawah

Kunci ini membuka daftar bidang.

Alt+F1

Untuk menggunakan pintasan keyboard ini, Anda harus memilih sel di tabel pivot. Kunci ini menyisipkan tabel pivot ke dalam lembar yang ada.

F11

Dan, jika Anda ingin memasukkan pivot ke dalam lembar kerja baru, Anda hanya perlu menggunakan tombol di atas.