Cara Menandai Sel di Microsoft Excel Berdasarkan Nilai Tertentu Secara Otomatis

Bagaimanakah cara memberi warna pada angka tertentu secara otomatis pada excel? Untuk memudahkan analisa data pada microsoft Excel, adakalanya kita perlu menandai atau merubah format sel Excel. Misalnya merubah warna sel Excel. Format tersebut tentunya akan kita terapkan pada nilai sel yang memenuhi kriteria tertentu yang kita kehendaki.

Untuk kebutuhan di atas excel sudah menyediakan fitur Conditional Formatting. Yakni, fitur yang digunakan untuk melakukan format sel atau range sesuai kriteria tertentu atau kondisi yang kita inginkan. Tentang fitur ini sudah sedikit saya tulis pada tutorial yang lalu. Jika anda belum membacanya silahkan baca pada tautan berikut:

• Menandai Sel, Range atau Tabel dengan Conditional Formatting Pada Microsoft Excel

Pada tutorial kali ini kita akan belajar Excel dalam menggunakan fitur conditional formatting pada microsoft excel untuk melakukan format sel atau merubah warna sel sesuai dengan nilai angka tertentu.

Dengan menggunakan fitur Conditional formatting kita dapat menandai sel tertentu secara otomatis berdasarkan kriteria tertentu. Salah satu kriteria tersebut adalah berdasarkan nilai atau value dari sel. Value atau isi sel yang saya maksud disini adalah berupa nilai angka (numeric).

Setidaknya ada 8 (Delapan) kriteria yang bisa kita gunakan dalam kasus ini, yakni:

1. Between : Menandai sel yang memiliki value atau nilai DIANTARA dua angka tertentu.
2. Not Between :Menandai sel yang memiliki value atau nilai TIDAK diantara dua angka tertentu.
3. Equal To : Menandai sel yang memiliki value atau nilai SAMA DENGAN angka tertentu.
4. Not Equal To : Menandai sel yang memiliki value atau nilai TIDAK SAMA DENGAN angka tertentu.
5. Greater Than : Menandai sel yang memiliki value atau nilai LEBIH DARI angka tertentu.
6. Less Than : Menandai sel yang memiliki value atau nilai KURANG DARI angka tertentu.
7. Greater Than or Equal To : Menandai sel yang memiliki value atau nilai LEBIH DARI atau SAMA DENGAN angka tertentu.
8. Less Than or Equal To ; Menandai sel yang memiliki value atau nilai KURANG DARI atau SAMA DENGAN angka tertentu.

Sebagai contoh kita akan menandai sel-sel excel pada data berikut yang berisi nilai diantara angka 10.000 dan 20.000.

Langkah-langkah yang perlu kita lakukan adalah:

1. Seleksi seluruh sel atau range yang akan kita terapkan conditional formatting didalamnya. Sesuai data diatas range yang kita maskud adalah H2:M7.
2. Pilih menu Home-Condotional formatting-New Rule
3. Pada menu yang muncul pilih : Format only cells that contain
4. Tantukan kondisi format only cells with : Cell Value -- between
5. Pada kotak disebelahnya masukkan rentang angka yang kita kehendaki. dalam contoh ini adalah 10.000 dan 20.000
6. Berikutnya tentukan format cell yang kita kehendaki misalanya warna sel yang memenuhi kriteria tersebut kita rubah warnanya menjadi biru.
7. Kemudian Klik OK dan Apply/OK.
8. Hasil akhirnya seperti pada gambar dibawah ini.

Sekarang silahkan coba rubah nilai-nilai angka yang ada pada range H2:M7 tersebut.

Untuk lebih jelasnya silahkan perhatikan gambar urutan penerapan conditional formatting untuk menendai sel berdasarkan kriteria angka dianatara 10.000 dan 20.000 berikut :

Penerapan conditional formatting ini bisa saling tindih atau saling tumpuk. Misalnya setelah kita menandai sel yang bernilai diantara 10.000 dan 20.000 kita ingin sel yang bernilai 15.000 berwarna merah.

Caranya lakukan langkah-langkah seperti sebelumnya. Hanya saja karena yang ingin kita tandai berikutnya adalah yang bernilai sama dengan 15.000 saja maka pada option conditional formatting kita pilih  Cell Value -- equal to

Hasil akhirnya akan seperti pada gambar dibawah ini.

Untuk kriteria-kriteria yang lain silahkan dicoba sendiri. Jika ada yang perlu ditanyakan silahkan disampaikan pada kolom komentar.

Cara diatas hanya berlaku untuk menandai angka tertentu lalu bagaimana cara untuk menandai teks tertentu? Akan saya bahas pada tutorial rumus excel berikutnya.

Cara Menandai Sel Pada Microsoft Excel dengan Conditional Formatting

Untuk mempercantik tampilan atau memudahkan penglihatan kita terhadap data-data dalam Microsoft Excel, terkadang kita perlu menandai sel-sel tertentu sesuai kriteria yang kita inginkan. Misalnya, kita ingin merubah warna font atau backgroundmenjadi merah pada angka yang bernilai negatif dalam suatu data atau tabel. Untuk kebutuhan ini Excel memberikan fasilitas Conditional Formatting.

Setidaknya ada enam (6) Jenis kelompok Conditional Formatting yang disediakan Excel.

1. Menandai (Format) semua sel terpilih berdasarkan nilai (isi sel tersebut)
2. Menandai (Format) sel yang hanya berisi nilai tertentu
3. Menandai (Format) sel berdasarkan nilai terendah atau tertinggi
4. Menandai (Format) sel berdasarkan nilai rata-rata
5. Menandai (Format) sel berdasarkan keunikannya (ada tidaknya nilai ganda/duplikat)
6. Menandai (Format) sel berdasarkan formula atau rumus excel tertentu

Masing-masing jenis/kelompok kondisi ini akan saya jelaskan secara terpisah dari artikel ini (Jika saya rasa memang diperlukan).

CARA MENANDAI SEL DENGAN CONDITIONAL FORMATTING EXCEL

Untuk menandai sel berdasarkan kriteria tertentu ikuti langkah-langkah berikut.

1. Seleksi seluruh cell atau range yang ingin kita tandai.
2. Pilih menu HOME-->Conditional Formatting-->Pilih kondisi/kriteria yang diinginkan
3. Sesuaikan setting kondisi yang diharapkan.

Dalam contoh gambar berikut saya menandai cell yang berisi angka dibawah 200:

Cara Menandai Sel/Tabel Excel dengan Conditional Formatting 1

Cara Menandai Sel/Tabel Excel dengan Conditional Formatting 2

MERUBAH SETTING/PENGATURAN CONDITIONAL FORMATTING EXCEL

Untuk alasan tertentu kita juga bisa merubah conditional formatting yang telah kita terapkan. Misalnya format warna font hijau pada conditional formatting ingin kita rubah menjadi Hijau. Untuk malakukannya ikuti langkah-langkah berikut:

1. Seleksi cell yang ingin kita rubah conditional formattingnya.
2. Pilih menu HOME-->Conditional Formatting-->Manage Rules
3. Setelah muncul option Box "Conditional Formatting Rules Manager" pilih condional formatting yang ingin kita edit lalu pilih "Edit Rule"
4. Sesuaikan/rubah setting konditional formatting kemudian klik OK-->Apply/OK.

Dalam contoh gambar berikut saya ingin merubah conditional formatting sebelumnya menjadi hanya sel yang berisi angka dibawah 250:

 Cara Merubah Setting Conditional Formatting pada Sel/Tabel Excel 1

Cara Merubah Setting Conditional Formatting pada Sel Tabel Excel 2 

Cara Merubah Setting Conditional Formatting pada Sel Tabel Excel 1

CARA MENGHAPUS CONDITIONAL FORMATTING EXCEL

Terlalu banyak menerapkan conditional formatting sebenarnya kurang bagus. Karena hal ini akan menyebabkan kinerja komputer/excel akan lebih berat, sehingga menyebabkan excel sering hang. Jika merasa bahwa conditional formatting tidak diperlukan lagi langkah-langkah untuk menghapusnya sebagai berikut.

Cara Pertama

1. Seleksi cell/range data, tabel atau PivotTable yang ingin kita hapus condittional formattingnya
2. Pilih menu HOME-->Conditional Formatting-->Clear Rules
3. Ada 4 pilihan (Pilih Salah Satu) :
• Clear Rules from Selected Cells : Menghapus conditional formatting hanya pada sel terpilih
• Clear Rules from Entire Sheet : Menghapus semua conditional formatting pada sheet yang aktif
• Clear Rules from This Table : Menghapus conditional formatting pada tabel terpilih
• Clear Rules from This PivotTabel : Menghapus conditional formatting pada pivot tabel terpilih
4. Untuk contoh dibawah saya pilih Selected Cells.

Cara Menghapus Conditional Formatting Excel 1

Cara Kedua

1. Seleksi cell/range data, tabel atau PivotTable yang ingin kita hapus condittional formattingnya
2. Pilih menu HOME-->Conditional Formatting-->Manage Rules
3. Pilih conditional formatting yang ingin kita hapus. kemudian Klik Delete Rule
4. Klik Apply/OK

Cara Menghapus Conditional Formatting Excel 2

Cara Menghapus Conditional Formatting Excel 3

Saya kira cukup ini untuk penjelasan tentang Conditional Formatting Pada Microsoft Excel. Selebihnya akan kita sambung lain kali.

Rumus Excel Untuk Penjumlahan, Pengurangan, Perkalian dan Pembagian

Hal mendasar yang perlu kita kuasai dalam belajar excel adalah melakukan operasi matematika penjumlahan, pengurangan, perkalian serta pembagian. Hal ini sebenarnya sudah saya singgung sedikit pada postingan tentang Operator Perhitungan Microsoft Excel. Namun dalam artikel tersebut pnejelasannya masih umum.

Untuk melakukan pengurangan, perkalian dan pembagian, excel tidak menyediakan fungsi khusus. berbeda dengan penjumlahan dimana excel menyedikan fungsi SUM, SUMIF, SUMIFS dan lain-lainnya. untuk lebih jelasnya akan kita bahas satu per satu.

Rumus Penjumlahan Excel

Cara Pertama

Cara yang paling mendasar dalam melakukan penjumlahan didalam Excel adalah dengan menggunakan operator plus (+). Cara penulisan rumusnya sama seperti saat kita menuliskan soal penjumlahan seperti pada umumnya. Misalkan kita ingin menjumlah angka 10,20,30,40,50 maka rumus excelnya kita tulis :

=10+20+30+40+50

Jika ingin menjumlahkan angka-angka didalam sebuah cell misalkan masing-masing pada cell A2 hingga A6 terisi angka 10,20,30,40,50 dan kita ingin menjulahkan cell-cell tersebut maka rumus excelnya :

=A2+A3+A4+A5+A6

Cara Kedua

Cara kedua dalam melakukan penjumlahan adalah dengan menggunakan fungsi SUM pada Excel. Untuk fungsi SUM sendiri bisa anda pelajari pada tulisan sebelumnya. Menjumlahkan data atau angka-angka dengan fungsi SUM ini lebih sering menjadi pilihan dari pada menggunakan operator plus (+). Cara ini lebih simple dan efisien tentunya.

Sebagai contoh untuk menjumlahkan data angka diatas pada cara pertama rumus excelnya adalah

=SUM(A2:A6)

Untuk lebih jelasnya perhatikan gambar berikut.

Cara Ketiga

Selain fungsi SUM, Excel juga menyediakan beberapa fungsi lain untuk penjumlahan khusus. Diantaranya: Fungsi SUMIF untuk menjumlah data berdasarkan satu kriteria atau syarat tertentu. Fungsi SUMIFS untuk menjumlah data berdasarkan lebih dari satu kriteria atau syarat tertentu (Banyak kriteria). beberapa varian lain Fungsi SUM , SUBTOTAL, SUMPRODUCT, COUNT, COUNTAdan lain-lain yang insyaAllah akan saya bahas pada kesempatan lain.

Gunakan tanda sama dengan (=) untuk mengawali penulisan rumus excel.

Rumus Pengurangan Excel

Rumus excel ntuk melakukan pengurangan adalah menggunakan operator minus (-). Excel tidak menyediakan fungsi khusus seperti penjumlahan dengan fungsi SUM. Kenapa demikian? saya tidak tahu. ehehehehe.

Cara penulisan rumusnya sederhana. seperti menuliskan soal pengurangan. Hanya pada awal rumus harus dikasih tanda sama (=) dengan ini sebagai tanda bahwa yang kita tuliskan adalah rumus excel/formula. dan hal ini berlaku dalam penulisan rumus excel apapun. contoh : untuk mengurangi angka 50 dengan angka 10 rumus excelnya:

=50-10

Untuk mengurangi angka didalam cell tertentu misalnya cell B6 berisi angka 50 kita kurangi B2 beerisi angka 10 maka rumus excelnya menjadi:

=A6-A2

Perhatikan gambar berikut.

Rumus Perkalian Excel

Cara Pertama

Rumus excel untuk melakukan perkalian adalah menggunakan operator asterik atau tanda bintang (*).

Cara Kedua

Cara kedua untuk melakukan perkalian adalah dengan menggunakan fungsi PRODUCT.

Untuk lebih jelasnya lihat penggunaan kedua cara tersebut dalam rumus excel pada gambar dibawah:

Rumus Pembagian Excel

Untuk melakukan pembagian dalam rumus excel adalah menggunakan operator slash atau garis miring(/). Cara menggunakannya dalam rumus excel sama dengan cara=cara sebelumnya.

Perhatikan gambar berikut:

Seperti yang semestinya kita ketahui excel megnikuti aturan internasional dalam hal perhitungan sehingga peletakan tanda kurung "( )" juga akan mempengaruhi hasil perhitungan dalam penjumlahan, pengurangan, perkalian serta pembagian.

Demikian yang bisa saya bagikan kali ini, semoga bermanfaat dan menambah pengetahuan kita dalam menggunakan Microsoft Excel sebagai pemula seperti saya.