Cara Grouping Di Excel + Menampilkan Subtotal Otomatis

Note: Panduan ini akan menunjukkan kepada Anda bagaimana cara grouping di Excel berdasarkan baris, kolom, serta mengelompokkan data multi level dan menampilkan Subtotal secara otomatis.

Jika Anda bekerja menganalisa data dalam jumlah yang sangat besar, ini lah saatnya Anda menggunakan fitur Grouping pada Microsoft Excel.

Secara garis besar, grouping pada Excel dapat membantu Anda untuk collapse atau expand row dan column hingga menampilkan subtotal. Hal ini dapat membantu untuk meringkas tampilan data agar lebih mudah dipahami.

Sebelum kita mulai, silahkan download file latihan berikut:

Cara Grouping di Excel Berdasarkan Baris dan Kolom

Sebagai contoh, saya memiliki data total penjualan berdasarkan region yang telah diurutkan berdasarkan kolom region seperti gambar berikut:

Kemudian, untuk membuat grouping di Excel (row region 1), silahkan ikuti tahap-tahap berikut:

1. Pilih semua row yang akan masuk kedalam grouping.
2. Klik Tab Data
3. Klik Group. Berikut hasilnya:

Jika ingin membuat grouping kolom, Anda harus memilih semua kolom yang akan dikelompokkan.

Penting! Cara tercepat membuat grouping dapat menggunakan pintasan keyboard Alt + Shift + Panah Kanan setelah memilih row atau column yang akan dikelompokkan.

Sesederhana itu…

Cara Membuat Grouping Multi Level

Anda bisa membuat grouping multi level yang membagi pengelompokkan data bertingkat. Hal ini memungkinkan Anda untuk meringkas tampilan berdasarkan pengelompokkan yang diinginkan.

Misalnya, Saya ingin membuat grouping berdasarkan region dan procuct.

Pertama-tama, untuk mendapatkan hasil yang optimal, pahami dahulu model data Anda. Dalam contoh ini, dalam region terdapat berbagai macam product. Oleh karena itu, grouping product harus berada di dalam grouping region.

Jadi dalam contoh ini, Anda dapat membuat grouping di Excel dengan cara berikut:

1. Urutkan data berdasarkan grouping bagian dalam (level 2). Dalam contoh ini, silahkan urutkan data pada kolom product. Kemudian urutkan data berdasarkan grouping bagian luar (level 1). Untuk mempermudah pekerjaan, Anda dapat menggunakan fitur Custom Sort.
2. Pilih ROW 2 sampai 6 untuk membuat grouping berdasarkan region 1. Anda bisa membuat grouping seperti cara sebelumnya.
3. Pilih ROW 2 dan 3 untuk membuat Grouping level 2 berdasarkan data product pada region 1. Berikut hasilnya:

Bagaimana, mudah bukan ?

Sekilas cara tersebut hanya untuk mengelompokkan data. Mungkin Anda hanya mendapatkan fitur hide dan unhide saja.

Tapi tenang…

Bukan hanya itu yang bisa Anda dapatkan dari fitur Grouping Excel. Bahkan Excel dapat membuat outline dan menghitung Subtotal secara otomatis tanpa menggunakan Rumus SUBTOTAL.

Mari kita pelajari lebih lanjut…

Cara Grouping Multi Level di Excel + Subtotal Otomatis

Dalam contoh ini, Saya akan menghitung Subtotal Sales per region berdasarkan product. Ingat! Ada dua kategori, yaitu Subtotal Sales berdasarkan Region dan Subtotal Sales berdasarkan Product.

Untuk tujuan tersebut, silahkan ikuti tahap-tahap berikut:

1. Pilih salah satu cell dalam tabel data. Misalnya cell A₂.
2. Klik Tab Data, lalu pilih Subtotal pada Outline Group.
3. Pada window Subtotal, pilih Region pada dropdown “At each change in”.
4. Karena kita akan menghitung total sales, maka gunakan Fungsi SUM.
5. Pada section Add Subtotal to, pilih Sales, karena kita akan menjumlahkan sales.
6. Centang Summary below data untuk menampilkan Subtotal disetiap grouping. Kemudian klik Ok, berikut hasilnya:

Great! Anda sudah bisa menampilkan Grouping Region + Subtotal dari Sales.

Next, untuk membuat grouping berdasarkan product, silahkan ulangi tahap 1 – 6 diatas dan pilih opsi berikut pada window Subtotal:

• At each change in: Product
• Use Function: Sum
• Add subtotal to: Sales
• Centang Summary below data, lalu klik Ok. Lihat hasilnya:

Sekarang outline Anda telah menampilkan Subtotal Sales berdasarkan Region dan Product.

Namun perlu Anda pahami, pada mistar vertical, Anda akan melihat angka dan icon + dan -. Mari kita pelajari kegunaannya.

Cara Expand dan Collapse Grouping di Excel

Dalam grouping Excel, Anda bisa Expand dan Collapse untuk meringkas tampilan data. Pada contoh sebelumnya, terdapat beberapa tombol pada kerangka outline yang memiliki fungsi untuk menampilkan data Anda.

Klik tombol 1, 2, 3, dan 4 pada mistar berguna untuk expand dan collapse grouping. Jumlah tombol akan tergantung dengan jumlah grouping dan level grouping Anda.

Tombol 1 sebanyak 1x akan collapse/expand semua grouping. Sedangkan tombol 2 akan collapse/expand grouping level 1 seperti gambar berikut:

Selain itu, Anda juga bisa klik tombol - dan + untuk collapse dan expand grouping.

Sangat praktis dan mudah bukan ?

Cara Menghapus Grouping di Excel

Anda dapat menghapus semua grouping dengan cara menghapus Outlinenya secara keseluruhan.

Silahkan klik Tab Data, kemudian klik panah kebawah pada menu Ungroup, lalu klik Clear Outline seperti gambar berikut:

Note: Setelah menghapus Outline, Anda tidak bisa mengembalikannya (CTRL + Z). Jadi pertimbangkan terlebih dahulu sebelum menghapusnya. Selain itu, ketika Anda menghapus Outline ketika collapse grouping, maka row/column akan tetap tersembunyi.

Untuk tujuan lain, jika Anda hanya ingin menghapus salah satu grouping tanpa menghapus Outline, silahkan ikuti tahap-tahap berikut:

1. Pilih baris atau kolom yang akan di ungroup. Dalam contoh ini, saya ingin menghapus grouping pada ROW 2 dan 3.
2. Klik Tab Data, lalu klik Ungrouping. Anda bisa mengganti tahap ini dengan pintasan keyboard Alt + Shift + Panah Kiri. Lihat hasilnya:

Note: Anda hanya bisa menghapus grouping yang memiliki jarak berdekatan sekaligus. Jadi, jika ingin menghapus grouping yang tidak berdekatan, Anda harus mengulangi tahap-tahap diatas.

Menyembunyikan dan Menampilkan Icon Grouping di Excel

Terkadang, saya merasa layar terlalu sempit ketika kita menggunakan grouping karena terdapat kerangka Outline. Terlebih lagi level grouping yang banyak.

Nah saat ini, Anda bisa menyembunyikan icon / kerangka Outline tanpa menghapus Outline keseluruhan dengan cara tekan tombol Ctrl + 8 secara bersamaan.

Untuk menampilkannya kembali, Anda juga bisa menggunakan pintasan keyboard tersebut.

Trik Copy Paste Data Grouping di Excel Ala Profesional Data Analyst

Dalam beberapa keadaan, ketika Anda menyalin data yang memiliki hidden ROW, Excel secara default akan menyalin semua data termasuk ROW yang tersembunyi. Begitu juga dengan data pada grouping yang di ciutkan.

Misalnya, Saya collapse grouping level 3 dan hanya ingin menyalin nilai total Sales per product dan per region seperti gambar berikut:

Jika Anda menyalin seluruh tabel menggunakan cara tradisional, Excel tetap akan menyalin seluruh cell, termasuk cell yang tersembunyi seperti hasil paste tabel kedua.

Jadi, untuk menyalin cell yang terlihat saja, Anda bisa menggunakan fitur Go To Special. Dalam window Go To Special, Anda bisa memilih Visible cells only. Kemudian copy paste data tersebut, berikut hasilnya:

Penting! Umumnya, para profesional Data Analyst menggunakan Go To Special untuk menyalin nilai pada cell yang terlihat saja. Silahkan pelajari fitur tersebut pada panduan Copy visible Cells Only.