Advanced Filter Excel: 7+ Contoh Kriteria AND, OR, Rumus, dll.)

Advanced Filter pada Excel adalah fitur filter tingkat lanjut yang berfungsi untuk menyaring data berdasarkan kriteria dari sebuah range / cell tertentu.

Advanced Filter memungkinkan Anda untuk menggunakan kriteria yang kompleks dan sangat bisa disesuaikan.

Ada 2 keuntungan menggunakan Advanced Filter ini.

Pertama, Hasil filter bisa langsung disalin. Jadi Anda tidak perlu menggunakan fitur Copy Visible Cells Only untuk menyalin hasil filter ini.

Kedua, Anda bisa memfilter 2 atau lebih kolom secara bersamaan. Kriteria setiap Filter terdapat pada sebuah cell / range.

Cara kerja Advanced Filter Excel mirip dengan Multi Filter pada Pivot Table (Walaupun lebih powerfull Pivot Table).

Meskipun tidak se-powerfull Pivot Table Excel, Anda tetap bisa memaksimalkan penggunakan Advanced Filter sehingga bisa membuat pemfilteran dengan tujuan yang lebih kompleks.

Saran Saya, ketahui dahulu Syarat Penggunaan Fitur Advanced Filter Excel ini:

Syarat Menggunakan Fitur Advanced Filter Excel

Dalam menggunakan fitur Advanced Filter, Anda membutuhkan 2 bagian utama yaitu Tabel Data Sumber dan Range Kriteria.

Tabel Data Sumber adalah tabel yang akan menerapkan filter. Sedangkan Range Kriteria adalah kriteria-kriteria pemfilteran yang akan diterapkan ke tabel data sumber.

Sama seperti menggunakan rumus / fungsi. Advanced Filter Excel memberlakukan syarat untuk ke dua bagian ini seperti berikut:

1. Syarat Untuk Tabel Data Sumber:
   • Tabel Data Sumber harus memiliki maksimal 1 baris Header (judul kolom) dengan nama yang unik (tidak boleh sama dengan nama kolom lainnya).
   • Judul Kolom dan semua isi tabel masing-masing harus berupa 1 cell tunggal. Jadi tidak boleh menggunakan fitur Merge and Center.
   • Tidak boleh ada baris kosong dalam tabel.
2. Syarat Untuk Range Kriteria
   • Judul Kriteria harus sama dengan judul kolom pada tabel (tidak peka huruf besar / kecil).
   • Masukkan setiap kriteria pada baris berikutnya (dibawah judul) yang sesuai dengan judul kolom dalam range kriteria.
   • Kriteria yang berada dalam 1 baris akan memberlakukan AND Kriteria.
   • Sedangkan jika kriteria berada dalam baris berbeda, maka Excel memberlakukan OR Kriteria (Selengkapnya akan Saya bahas pada bagian berikutnya).

Penting! Untuk menghindari kesalahan, ada baiknya pertimbangkan untuk menggunakan fitur Tabel Bernama Excel. Meskipun tanpa Tabel Bernama pun juga bisa.

Cara Menggunakan Advanced Filter Pada Excel

Sebagai contoh, Saya menggunakan Tabel Pembelian Produk dari beberapa suplier berikut:

Note: Untuk sementara kita mengabaikan beberapa parameter pada Window Advanced Filter seperti Copy to another location, Copy to, dan Unique records only. Ini akan Saya jelaskan pada contoh berikutnya.

Oh ya, dari awal panduan ini sudah Saya jelaskan bahwa hasil Advanced Filter bisa langsung dicopy tanpa menggunakan fitur Copy Visible Cells Only.

Sekarang coba buktikan…

Copy Paste hasil filter tersebut ke bawah tabel dan lihat hasilnya:

Benar bukan ?

Penting! Ada 15 jenis Copy Paste Excel. Silahkan tambah wawasan Anda pada Panduan Copy Paste Special Excel.

Pahami Parameter Advanced Filter

Pada Window Advanced Filter Excel, ada 5 parameter yang perlu Anda sesuaikan.

Pertama Action, merupakan jenis output Advanced Filter yang akan Anda gunakan.

Ada 2 pilihan yaitu Filter the list, in-place, ini akan memfilter sumber data asli dan menyembunyikan baris yang tidak sesuai dengan kriteria. (ini sudah kita bahas pada contoh pertama).

Sedangkan Output Copy the results to another location akan menyalin hasil filter ke cell lain tanpa mengubah data sumber.

Kedua List Range, ini sudah jelas merupakan range tabel data sumber.

Ketiga Criteria Range, ini juga sudah jelas range dari kriteria yang digunakan.

Penting! Untuk List dan Criteria Range, Anda bisa menggunakan Reference Cell (seperti contoh diatas) atau Named Range ataupun Table Name dari Format Tabel Bernama.

Keempat Copy to, merupakan lokasi tujuan untuk hasil filter. Parameter ini hanya akan aktif jika menggunakan output Copy the results to another location.

Kelima Unique Records Only, berguna untuk memfilter data unik saja. Artinya tidak akan ada hasil data duplikat dari filter ini.

Parameter ini sangat berguna untuk menghapus data duplikat tanpa mengubah data sumber seperti gambar berikut:

Selengkapnya bisa Anda pelajari pada Panduan Menghapus Data Duplikat.

7 Contoh Memfilter Data Menggunakan Advanced Filter Excel

Dalam contoh sebelumnya, Saya memasukkan kriteria secara langsung (berupa teks) ke dalam Range Kriteria. Kemudian Excel memfilter kolom Suplier yang berisi kriteria tersebut.

Sedangkan Advanced Filter memungkinkan Anda memfilter tabel dengan kriteria yang jauh lebih kompleks.

• Memfilter Angka / Tanggal / Teks dengan kriteria menggunakan operator perbandingan (seperti =, >, < dll).
• Memfilter Teks menggunakan Karakter Wildcard.
• Menggunakan banyak kriteria dengan AND maupun OR Kriteria.
• Menggunakan Rumus / Fungsi sebagai kriteria Filter.
• Mengombinasikan Advanced Filter dengan Dropdown List.

#1 Advanced Filter untuk Data Berupa Angka atau Tanggal Valid

Untuk memfilter data berupa angka / tanggal valid, Anda bisa menggunakan operator perbandingan seperti =, >, <, >=, <=, dan <>.

Seperti contoh pertama dalam panduan ini menggunakan operator perbandingan Lebih Besar Dari “>1000000″ sebagai kriteria.

Penting! Sebelum melanjutkan, pastikan Anda sudah memahami penggunaan dasar operator perbandingan. Selengkapnya sudah Saya bahas pada SUB-BAB Operator Perbandingan Rumus.

Anda juga bisa menggunakan operator perbandingan ini untuk data berupa tanggal valid. Misalnya, Saya ingin menampilkan data pembelian sampai tanggal 22 Februari 2021 saja. (Dalam bahasa lain, menyembunyikan data setelah tanggal 22 Februari 2021).

Silahkan buat kolom baru untuk range kriteria tanggal dan gunakan kriteria ini: <=22/02/2021

Kemudian, buka Window Advanced Filter. Cara cepat: Tekan tombol Alt – A – Q secara berurutan. Jangan lupa sesuaikan Criteria Range pada Window Advanced Filter dan tekan Enter. Lihat hasilnya:

Benar bukan ? Excel hanya menampilkan data sampai tanggal 22/02/2021 dan menyembunyikan data lain yang tidak sesuai dengan kriteria.

Penting! Pastikan data tanggal menggunakan Format Tanggal & Waktu yang Valid. Jika ragu, silahkan pelajari cara memasukkan tanggal yang benar pada Panduan Format Date & Time Excel.

Contoh lainnya, jika Saya menggunakan kriteria ini: <>22/02/2021

Excel akan menyembunyikan data pada tanggal 22/02/2021. Karena operator perbandingan <> berarti tidak sama dengan.

Silahkan latihan menggunakan operator perbandingan lainnya…

#2 Advanced Filter Untuk Data Berupa Teks

Pada dasarnya, Anda juga bisa menggunakan Operator Perbandingan untuk memfilter teks. Hanya saja arti Operator Perbandingan pada data teks tidak sama dengan data Angka / Tanggal Valid.

Berikut arti dari operator perbandingan untuk memfilter teks.

Kriteria	Artinya
Toko Bunga	Filter Kolom yang diawali dengan Toko Bunga
=Toko Bunga	Filter Kolom yang hanya berisi Toko Bunga
>Toko Bunga	Filter Kolom berdasarkan urutan data (sortir) setelah Toko Bunga
<Toko Bunga	Filter Kolom berdasarkan urutan data (sortir) sebelum Toko Bunga
<>Toko Bunga	Filter Kolom yang tidak berisi Toko Bunga
>=Toko Bunga	Filter Kolom berdasarkan urutan data (sortir) setelah dan termasuk Toko Bunga
<=Toko Bunga	Filter Kolom berdasarkan urutan data (sortir) sebelum dan termasuk Toko Bunga

Untuk kriteria yang pertama (Toko Bunga) Saya rasa sudah sangat jelas. Hasil filter ini haya akan menampilkan baris yang berisi teks yang diawali dengan Toko Bunga. Jadi Toko Bunga Si A, Toko Bunga Si B dan seterusnya juga termasuk dalam kriteria.

Begitu juga untuk kriteria yang ke 2 dan ke 5. Saya rasa artinya sudah cukup jelas.

Sementara khusus untuk operator perbandingan >, <, >=, dan <=, Lebih baik gunakan hanya jika list data pada kolom yang menggunakan filter ini diurutkan dari abjad A-Z atau Z-A.

Misalnya seperti ini:

Lihat, ada 2 baris data untuk Suplier Toko Bunga. Jika Saya menggunakan kriteria >Toko Bunga, maka Excel hanya akan menampilkan semua baris data setelah data Toko Bunga seperti berikut:

Note: Excel juga ikut menyembunyikan baris data Suplier Sumber Rezeki.

Sebaliknya, jika Saya menggunakan kriteria <=Toko Bunga, maka Excel hanya akan menampilkan data Toko Bunga itu sendiri serta data sebelumnya seperti gambar berikut:

Sejauh ini Saya harap Anda sudah paham penggunaan operator perbandingan dalam memfilter data.

Oh ya, ada 1 yang spesial untuk memfilter teks, yaitu…

“Kriteria Teks Bisa menggunakan Karakter Wildcard”

Karakter Wildcard seperti Asterisk (simbol *), Quetion Mark (simbol ?), dan Tilde (simbol ~) dapat mengganti / mewakilkan karakter dalam sebuah teks.

Note: Tilde (~) hanya berguna jika didalam teks yang akan difilter terdapat simbol Asterisk (*) atau Quetion Mark (?).

Dalam praktiknya, Wildcard Asterik (*) dan Quetion Mark (?) lebih sering digunakan. Selain itu, Anda bisa menempatkan simbol ini pada bagian manapun (sebelum, diantara, atau setelah karakter).

Misalnya, Saya menggunakan contoh berbeda seperti berikut:

Kemudian Saya memfilter Suplier dan Produk menggunakan kriteria berikut:

• Suplier: * Kelontong
• Produk: ???? A

Sekarang lihat hasilnya:

Pada kriteria * Kelontong, simbol Asteriks (*) mewakilkan Teks Toko. Lihat, simbol asteriks hanya ada 1 yang mewakilkan 4 huruf (Toko).

Sementara, untuk simbol Quetion Mark pada kriteria ???? A ada 4 yang mana mewakilkan 4 huruf yaitu Lauk dan Buah.

Note: Jumlah tanda tanya harus mewakilkan banyak karakter yang akan digantikan.

Dalam contoh ini, Saya tidak akan mendapatkan apa-apa jika menggunakan kriteria ini: ? A. Silahkan buktikan sendiri.

#3 Advanced Filter Dengan AND Kriteria

Seperti yang Saya sebutkan pada bagian Syarat Fitur Advanced Filter, kriteria yang berada dalam 1 baris akan memberlakukan AND Kriteria (semua harus terpenuhi).

AND KRITERIA ARTINYA HASIL FILTER HARUS MEMENUHI SEMUA KRITERIA

rolan mardani

Misalnya jika Saya menggunakan kedua kriteria ini:

• Harga: >20000 dan
• Jumlah: <100

Note: Sesuaikan Parameter Criteria Range pada Window Advanced Filter Excel.

Lihat hasilnya:

Benar bukan ? Excel memfilter Kolom Harga yang lebih dari 20.000 dan jumlah kurang dari 100.

#4 Advanced Filter Dengan OR Kriteria

OR KRITERIA ARTINYA HASIL FILTER HARUS MEMENUHI SALAH SATU KRITERIA

rolan mardani

Misalnya Saya menggunakan kriteria berikut:

• Harga: >20000 atau
• Jumlah: <100

Artinya, Saya ingin memfilter kolom harga yang lebih besar dari 20000 atau kolom jumlah yang lebih kecil dari 100.

Note: Dalam Advanced Filter, Anda harus menempatkan OR Kriteria dalam baris yang berbeda. Karena ada penambahan baris baru dalam range kriteria, maka Anda harus menyesuaikan parameter Criteria Range dalam Window Advanced Filter seperti berikut:

Gunakan cara cepat: Tekan Alt – A – Q – Ubah Criteria Range – Enter. Dan lihat hasilnya:

Note: Perhatikan penempatan Kriteria dalam Range Kriteria.

#5 Advanced Filter Dengan AND + OR Kriteria

Intinya, gabungkan AND + OR kriteria. Misalnya tujuan Saya seperti ini:

• Filter Suplier: Sumber Rezeki Dan Jumlah <100. Atau
• Filter Suplier: Sumber Rezeki Dan Harga <20000

Lihat hasil filter pada baris pertama, hanya memenuhi kriteria pertama (suplier: Sumber Rezeki dan Jumlah: <100). Meskipun tidak memenuhi kriteria ke dua (Harga: <20000).

Begitu juga dengan hasil filter baris kedua. Meskipun tidak memenuhi kriteria pertama, Excel tetap menampilkannya.

Ini karena kriteria pertama dan kedua tidak berhubungan satu sama lain (OR Kriteria).

#6 Advanced Filter Dengan Kriteria Berupa Rumus / Fungsi

Ini salah satu Favorit Saya saat ini.

Anda juga bisa menggunakan rumus / fungsi Excel sebagai ganti range kriteria yang sudah ada.

Misalnya, Saya ingin memfilter dengan 2 kondisi berikut:

• Suplier: Sumber Rezeki dan Jumlah: >100 dan Total: <=AVERAGE atau
• Suplier: Toko Laris dan Jumlah: >100 dan Total: <=AVERAGE

Lihat, setiap kriteria filter harus memiliki nilai Total yang lebih kecil atau sama dengan nilai rata-rata keseluruhan.

Penting! Sebagai pembanding, Saya menghitung nilai rata-rata kolom Total menggunakan Fungsi AVERAGE pada Cell G₆. Ingat, ini hanya sebagai pembanding hasil filter.

Untuk tujuan tersebut, silahkan buat range kriteria baru (tanpa header / judul) dan gunakan Rumus ini: =E7<=AVERAGE(E7:E21) pada Cell F₃ dan F₄.

Note: Jangan beri judul baru filter yang menggunakan Rumus ini. Biarkan saja kosong atau gunakan judul lain yang tidak sama dengan judul dari tabel. Selain itu, pastikan rumus memberikan hasil TRUE atau FALSE.

Update Advanced Filter dengan cara tekan Alt – A – Q, kemudian update Criteria Range pada Window Advanced Filter.

Dalam contoh ini menjadi $A$2:$F$4. Pastikan semua kriteria masuk ke dalam Criteria Range ini. Kemudian tekan Enter dan lihat hasilnya:

Benar bukan ? Excel memberikan 2 hasil filter. Untuk kolom Suplier dan Jumlah (masing-masing) sudah memenuhi kriteria.

Begitu juga, kolom Total memenuhi kriteria <=AVERAGE.

Penting! Selalu gunakan Reference Absolute dalam Rumus. Silahkan pelajari jenis-jenis reference cell pada SUB-BAB Cell Reference Excel.

Untuk contoh ke 2, coba ubah Rumus =E7<=AVERAGE(E7:E21) menjadi =E7>=AVERAGE(E7:E21) pada Cell F₃ dan F₄. Kemudian tekan Alt – A – Q dan Enter. Lihat hasilnya:

Hanya ada 1 hasil.

Sekarang silahkan buktikan dan latihan sendiri. Gunakan kombinasi Kriteria yang berbeda, misal Suplier yang berbeda atau Jumlah yang berbeda.

Saya yakin dengan banyak latihan, Anda akan mendapatkan pemahaman yang mendalam.

#7 Kombinasi Advanced Filter Dengan Dropdown List

Sebelum Anda menggabungkan Advanced Filter dengan Dropdown List dari Conditional Formatting, ada baiknya Anda memahami cara membuat Dropdown List terlebih dahulu.

Dalam Panduan ini, Saya membuat Dropdown List untuk Criteria Range Suplier seperti ini:

Note: Saya juga menggunakan kriteria =E7<=AVERAGE(E7:E21) pada Cell F₃ dan F₄.

Kemudian, silahkan pilih dropdown list pada Range Kriteria Suplier. Misalnya Saya pilih Toko Laris dan Toko Bunga.

Terakhir, update Advanced Filter dengan cara Alt – A – Q. Sesuaikan Criteria Range (jika belum) dan tekan Enter. Lihat hasilnya:

Oh ya, selain mengombinasikan Dropdown List dengan Advanced Filter, Anda juga bisa menggunakan Dependent Dropdown List. Silahkan berkreasi…