Dasbor Excel - Pemformatan Bersyarat

Pemformatan Bersyarat untuk Visualisasi Data

Jika Anda telah memilih Excel untuk membuat dasbor, coba gunakan tabel Excel jika sesuai dengan tujuannya. Dengan Pemformatan Bersyarat dan Grafik Mini, Tabel Excel adalah pilihan terbaik dan sederhana untuk dasbor Anda.

Di Excel, Anda bisa menggunakan pemformatan bersyarat untuk visualisasi data. Misalnya, dalam tabel yang berisi angka penjualan untuk kuartal terakhir menurut wilayah, Anda dapat menyorot nilai 5% teratas.

Anda dapat menentukan sejumlah kondisi pemformatan dengan menentukan Aturan. Anda bisa mengambil Excel built-in Rules yang sesuai dengan kondisi Anda dari Highlight Cells Rules atau Top / Bottom Rules. Anda juga dapat menentukan Aturan Anda sendiri.

Anda memilih opsi pemformatan yang sesuai untuk visualisasi data Anda - Bar Data, Skala Warna, atau Kumpulan Ikon.

Dalam bab ini, Anda akan mempelajari Aturan pemformatan bersyarat, opsi pemformatan, dan menambahkan / mengelola Aturan.

Menyoroti Sel

Anda bisa menggunakan Sorot Aturan Sel untuk menetapkan format ke sel yang berisi data yang memenuhi salah satu kriteria berikut ini -

Angka dalam rentang numerik tertentu: Lebih Besar Dari, Kurang Dari, Antara, dan Sama Dengan.
Nilai yang Duplikat atau Unik.

Pertimbangkan ringkasan hasil berikut yang ingin Anda sajikan -

Misalkan Anda ingin menyorot nilai Jumlah Total yang lebih dari 1000000.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik pada Highlight Cells Rules di daftar dropdown.
Klik Greater Than di daftar dropdown kedua yang muncul.

Kotak dialog Greater Than muncul.

Dalam kotak Format sel yang LEBIH BESAR DARI :, tentukan kondisinya sebagai 1000000.
Di kotak dengan, pilih opsi pemformatan sebagai Isian Hijau dengan Teks Hijau Tua.

Klik tombol OK.

Seperti yang Anda amati, nilai yang memenuhi kondisi yang ditentukan disorot dengan format yang ditentukan.

Aturan Atas / Bawah

Anda dapat menggunakan Aturan Atas / Bawah untuk menetapkan format ke nilai yang memenuhi salah satu kriteria berikut -

Top 10 Items - Sel yang menempati peringkat N teratas, di mana 1 <= N <= 1000.
Top 10% - Sel yang menempati peringkat n% teratas, di mana 1 <= n <= 100.
Bottom 10 Items - Sel yang menempati peringkat N terbawah, dimana 1 <= N <= 1000.
Bottom 10% - Sel yang menempati peringkat n% terbawah, dimana 1 <= n <= 100.
Above Average - Sel yang Di Atas Rata-rata untuk rentang yang dipilih.
Below Average - Sel di Bawah Rata-rata untuk rentang yang dipilih.

Misalkan Anda ingin menyorot nilai Jumlah Total yang berada di 5% teratas.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik Aturan Atas / Bawah di daftar tarik-turun.
Klik Sepuluh% Teratas di daftar tarik-turun kedua yang muncul.

Kotak dialog Sepuluh% Teratas muncul.

Di kotak Format sel yang memberi peringkat di TOP: kotak, tentukan kondisinya sebagai 5%.
Di kotak dengan, pilih opsi pemformatan sebagai Isian Hijau dengan Teks Hijau Tua.

Klik tombol OK. Nilai 5% teratas akan disorot dengan format yang ditentukan.

Bilah Data

Anda dapat menggunakan Bilah Data berwarna untuk melihat nilai relatif terhadap nilai lainnya. Panjang Bilah Data mewakili nilainya. Bar yang lebih panjang mewakili nilai yang lebih tinggi, dan Bar yang lebih pendek mewakili nilai yang lebih rendah. Anda dapat menggunakan warna solid atau warna gradien untuk Data Bar.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik pada Data Bars di daftar dropdown.
Klik pada Blue Data Bar di bawah Gradient Fill di daftar dropdown kedua yang muncul.

Nilai dalam kolom akan disorot dengan menampilkan nilai kecil, menengah dan besar dengan bilah isian gradien berwarna biru.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik pada Data Bars di daftar dropdown.
Klik pada Orange Data Bar di bawah Solid Fill di daftar dropdown kedua yang muncul.

Nilai di kolom akan disorot dengan menampilkan nilai kecil, menengah dan besar menurut tinggi batang dengan batang berwarna oranye.

Misalkan Anda ingin menyoroti penjualan dibandingkan dengan target penjualan, misalnya 800.000.

Buat kolom dengan nilai = [@ [Jumlah Total]] - 800000.
Pilih kolom baru.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik pada Data Bars di daftar dropdown.
Klik pada Green Data Bar di bawah Gradient Fill di daftar dropdown kedua yang muncul.

Bilah Data akan dimulai di tengah setiap sel, dan membentang ke kiri untuk nilai negatif dan ke kanan untuk nilai positif.

Seperti yang dapat Anda amati, Batang yang membentang ke kanan berwarna hijau yang menunjukkan nilai positif dan Batang yang membentang ke kiri berwarna merah yang menunjukkan nilai negatif.

Timbangan Warna

Anda dapat menggunakan Skala Warna untuk melihat nilai dalam sel yang berhubungan dengan nilai di sel lain dalam kolom. Warna menunjukkan di mana setiap nilai sel berada dalam kisaran itu. Anda dapat memiliki skala 3 warna atau skala 2 warna.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik Color Scales di daftar dropdown.
Klik Skala Warna Hijau-Kuning-Merah di daftar tarik-turun kedua yang muncul.

Seperti dalam kasus Aturan Sel Sorotan, Skala Warna menggunakan bayangan sel untuk menampilkan perbedaan nilai sel. Seperti yang dapat Anda amati di pratinjau, perbedaan bayangan tidak mencolok untuk kumpulan data ini.

Klik More Rules di daftar dropdown kedua.

Kotak dialog Aturan Pemformatan Baru muncul.

Klik pada Format semua sel berdasarkan nilainya di kotak Select a Rule Type.
Di kotak Edit Deskripsi Aturan, pilih yang berikut ini -
- Pilih 3-Color Scale dalam kotak Format Style.
- Di bawah Titik Tengah, untuk Nilai - ketik 75.

Klik tombol OK.

Seperti yang dapat Anda amati, dengan skala warna yang ditentukan, nilai-nilai diarsir secara jelas yang menggambarkan rentang data.

Set Ikon

Anda dapat menggunakan kumpulan ikon untuk memvisualisasikan perbedaan numerik. Di Excel, Anda memiliki serangkaian Kumpulan Ikon -

Jenis Set Ikon	Set Ikon
Terarah
Bentuk
Indikator
Peringkat

Seperti yang bisa Anda amati, Set Ikon terdiri dari tiga hingga lima simbol. Anda dapat menentukan kriteria untuk mengaitkan ikon dengan nilai dalam rentang sel. Misalnya panah bawah merah untuk angka kecil, panah hijau ke atas untuk angka besar, dan panah horizontal kuning untuk nilai tengah.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik pada Set Ikon di daftar tarik-turun.
Klik pada 3 Panah (Berwarna) di grup Directional di daftar dropdown kedua yang muncul.

Panah berwarna muncul di kolom yang dipilih berdasarkan nilai.

Menggunakan Aturan Kustom

Anda dapat menentukan Aturan Anda sendiri dan memformat rentang sel yang memenuhi kondisi tertentu.

Pilih kolom - Jumlah Total.
Klik Pemformatan Bersyarat dalam grup Gaya di bawah tab Beranda.
Klik Aturan Baru di daftar tarik-turun.

Kotak dialog Aturan Pemformatan Baru muncul.

Klik Gunakan rumus untuk menentukan sel mana yang akan diformat, di kotak Select a Rule Type.
Di kotak Edit Deskripsi Aturan, lakukan hal berikut -
- Ketikkan rumus di kotak - Format nilai di mana rumus ini benar. Misalnya, =PercentRank.INC($E$3:$E$13,E3)>=0.7
- Klik pada tombol Format.
- Pilih format. Misalnya Font - tebal dan Isi - oranye.
- Klik OK.
Periksa Pratinjau.

Klik OK jika Pratinjau baik-baik saja. Nilai dalam kumpulan data yang memenuhi rumus akan disorot dengan format yang Anda pilih.

Mengelola Aturan Pemformatan Bersyarat

Anda dapat mengelola Aturan pemformatan bersyarat menggunakan kotak dialog Pengelola Aturan Pemformatan Bersyarat.

Klik Conditional Formattingdi grup Gaya di bawah tab Beranda. Klik Kelola Aturan di daftar tarik-turun.

Kotak dialog Pengelola Aturan Pemformatan Bersyarat muncul. Anda dapat melihat semua Aturan yang ada. Anda dapat menambahkan Aturan baru, menghapus Aturan dan / atau mengedit Aturan untuk mengubahnya.