Conditional Formatting di Excel: Tutorial dan Contoh

Pelajari cara menggunakan format kondisional dalam MS-Excel. Anda dapat menyorot sel dengan nilai kosong dan duplikat dan juga sesuai dengan nilai sekarang.

A+ A-

Conditional Formatting adalah fitur Microsoft Excel yang memungkinkan kita untuk mengubah tampilan dari sel atas dasar kondisi yang ditetapkan. Kita bisa menetapkan kedua formula kondisional dan format. Excel akan menerapkan format ini pada sel-sel yang nilai akan cocok dengan kondisi set.

Conditional Formatting adalah fitur Excel lain yang membantu kita dalam visualisasi data. Ini membantu kita dalam pemahaman yang lebih baik dan menganalisis data. Kami baru-baru ini menerbitkan artikel tentang fitur analisis data lain seperti VLOOKUP dan Pivot Tables . Dalam tutorial ini, kita akan belajar apa yang format bersyarat dan bagaimana menggunakannya untuk memformat sel atas dasar kondisi.

Anda dapat men-download sampel lembar kerja Excel yang akan kita gunakan untuk tutorial. worksheet berisi data sampel unit pemrosesan order dan ini adalah bagaimana tampak seperti:

Conditional Formatting sampel datasheet.

Conditional Formatting: Aturan Highlight Sel

Jika Anda ingin menyorot sel tertentu berdasarkan kondisi, Peraturan Highlight Sel datang sangat berguna. Pada tab Home, pergi ke menu Conditional Formatting dan kemudian di bawah opsi Highlight Aturan Sel Anda akan melihat beberapa aturan pre-built yang dapat Anda gunakan:

  1. Lebih besar dari…
  2. Kurang dari…
  3. Antara…
  4. Sama dengan…
  5. Teks yang Mengandung ...
  6. Sebuah Tanggal Terjadi ...
  7. Nilai duplikat ...
  8. Aturan lebih ...

menu Conditional Formatting di Excel

Untuk memulai tutorial kami, pertama kami akan menerapkan aturan sederhana. Kami akan menyoroti sel dengan warna merah di mana Quantity lebih besar dari 20.

  1. Pilih rentang sel yang ingin Anda format. Seperti yang kita ingin memformat kolom Quantity, jadi kami pilih.
  2. Buka tab Home> tombol Conditional Formatting> Sorot Aturan Sel option> Lebih Besar dari ...
  3. Sebuah kotak akan muncul yang akan menawarkan dua pilihan. Dalam Format Cells yang LEBIH DARI kotak, masukkan 20 dan pilih Lampu Merah Isi dengan Dark Red Text dari daftar dropdown.
    format kondisional dengan lebih dari aturan
  4. Anda akan melihat bahwa semua sel yang mengandung nilai-nilai yang lebih besar dari 20 telah disorot dengan warna merah.
  5. Klik OK untuk membuat perubahan permanen.

Demikian pula, Anda dapat menyorot sel atas dasar berbagai kondisi lainnya.

Uji!

Anda baru saja belajar bagaimana menggunakan Conditional Formatting, sehingga, Anda sekarang untuk tes sederhana ?! Mari kita lihat apakah Anda dapat melakukan hal berikut:

  1. Sorot sel dengan warna hijau di kolom Kategori mana nilai adalah Office
  2. Sorot sel dengan warna kuning di kolom Kategori mana nilai adalah Furniture
  3. Sorot sel dalam rentang A2: H21 dengan teks merah yang memiliki nilai yang tepat 1.39
  4. Sel sorot di Unit kolom Harga mana nilai-nilai antara 20 dan 40

Conditional Formatting: Atas / Bawah Aturan

Excel juga dapat secara otomatis menyorot sel dengan Top / kondisi Bawah. Contoh kondisi seperti ini akan menyoroti 10% pesanan dengan total biaya pesanan minimal. Mengikuti aturan yang tersedia di Top / aturan Bawah menu:

  1. Top 10 Produk ...
  2. 10 besar%…
  3. Bawah 10 Produk ...
  4. Bawah 10% ...
  5. Diatas rata-rata…
  6. Dibawah rata-rata…
  7. Aturan lebih ...

Gambar berikut menunjukkan hasil yang kita dapatkan ketika kita menggunakan aturan 10% Bawah pada Total kolom Biaya. Dua sel mendapatkan disorot (jumlah baris adalah 20 ... dan 10% dari 20 adalah 2).

format kondisional dengan 10 persen aturan bawah

Uji!

Cobalah untuk menerapkan Top / Aturan Bawah pada Kategori kolom. Apakah itu menyorot setiap sel? Jika tidak, mengapa?

Jika Anda tidak bisa memikirkan jawaban, baca terus ... Anda akan menemukannya bawah halaman!

Conditional Formatting: Data Bar

Excel dapat dengan cepat membuat pekerjaan Anda jauh lebih rapi! Anda dapat mengesankan bos Anda dengan menggunakan fitur highlighting mewah seperti Data Bar. Ketika Anda menerapkan data Bar format pada kolom numerik, sel-sel mendapatkan sebagian diisi dengan warna; menciptakan grafik seperti efek. Lebar bar data ini akan ditentukan oleh nilai dalam sel. Semakin tinggi akan nilai lagi akan menjadi bar data. Anda dapat memilih dari berbagai warna dan gradien di bar ini.

LIHAT JUGA: Pelajari cara cepat menyalin format di sel

Gambar berikut menunjukkan hasil ketika kita menerapkan format data Bar bersyarat pada kolom Quantity. Seperti fitur visualisasi data baik itu!

format kondisional dengan bar data

Conditional Formatting: Timbangan Warna

Timbangan warna mungkin format kondisional paling keren. Ketika Anda menerapkan Scales warna pada kolom numerik, semua sel dalam kolom yang akan diisi dengan warna solid. warna akan ditentukan oleh nilai dalam sel. Misalnya, sel dengan nilai tertinggi akan merah dan sel dengan nilai terendah akan menjadi hijau. Semua sel-sel lain akan menerima warna di antara warna merah dan hijau. Mewarnai ini menciptakan panas-peta seperti penyajian data. Ada beberapa jenis warna Timbangan untuk memilih dari!

format kondisional dengan skala warna

Conditional Formatting: Set Icon

Anda dapat menambahkan ikon keren dalam sel lembar kerja Anda. Ikon ini akan menjadi indikasi nilai kini dalam sel. Icon Set dapat diterapkan hanya pada kolom numerik. Ada sejumlah besar ikon gaya untuk memilih dari; misalnya, lampu lalu lintas, peringkat bintang, wifi atau kekuatan jaringan seluler gaya dll

TIP: Jika Anda menggunakan ikon untuk memformat kolom numerik, Anda juga dapat mengurutkan kolom atas dasar ikon !

Kami telah diformat tiga kolom lembar kerja kami dengan gaya yang berbeda dari ikon. Lihatlah:

format kondisional dengan icon set

Conditional Formatting: Sorot Sel Kosong

Di atas menjelaskan, tersedia pilihan format sel adalah untuk orang-sel yang memiliki beberapa nilai di dalamnya. Tapi bagaimana jika Anda ingin menyorot sel kosong? Apakah mungkin untuk melakukannya?

Ya tentu saja! Pilihan yang diberikan di atas hanya mereka yang paling sering digunakan. Tapi Anda bisa memformat sel dengan kondisi kustom dan format kustom! Kami akan menunjukkan titik ini dengan menunjukkan Anda bagaimana untuk menyoroti sel-sel kosong di lembar Excel:

  1. Pilih rentang sel yang ingin Anda format.
  2. Buka tab Home> tombol Conditional Formatting> option Aturan Sorot Sel
  3. Pilih Aturan More ...
  4. Anda akan mendapatkan kotak dialog Aturan Baru Formatting
  5. Dalam kotak ini, Anda dapat membuat kondisi kustom dan format. Untuk menyorot sel kosong, kita pilih Kosong dari Format satu-satunya sel dengan daftar dropdown.
    Conditional Formatting: New kotak dialog aturan format
  6. Sekarang klik pada Format ... tombol. Kotak dialog Format Sel akan muncul. Di sini Anda dapat memilih warna isi, gaya font, gaya perbatasan dll untuk memformat sel dengan nilai kosong.
    Conditional Formatting: kotak dialog Format sel
  7. Klik tombol OK dan semua sel kosong dalam kisaran yang dipilih akan diformat dengan gaya yang dipilih oleh Anda.

Conditional Formatting: Sorot Nilai Duplikat

Ini adalah persyaratan umum lain dari pengguna Excel. Kita sering perlu untuk menyorot sel yang memiliki nilai ganda di dalamnya. Karena merupakan kebutuhan yang sangat umum, Excel menyediakan built-in aturan untuk itu.

  1. Pilih rentang sel yang ingin Anda format.
  2. Buka tab Home> tombol Conditional Formatting> option Aturan Sorot Sel
  3. Pilih Nilai Duplikat ...
  4. Semua nilai-nilai duplikat akan langsung disorot di kisaran sel yang dipilih.
    bersyarat-format-duplikat-nilai
  5. Klik tombol OK untuk membuat perubahan permanen.

Buat Aturan Khusus Sendiri untuk Conditional Formatting

Standar format aturan, seperti dijelaskan di atas, yang cukup untuk sebagian besar situasi. Tapi jika Anda memiliki persyaratan format tertentu, Anda selalu dapat membuat aturan kustom Anda sendiri dan format. Terlebih lagi, Anda bahkan dapat menyimpan aturan adat ini untuk kemudian digunakan!

  1. Pilih rentang sel yang ingin Anda format.
  2. Buka tab Home> tombol Conditional Formatting> Manage Rules ...
  3. Conditional Formatting Rules Manager kotak dialog akan muncul.
  4. Katakanlah kita ingin menyoroti sel di kolom Quantity mana nilai sel adalah antara 20 dan 40. Untuk melakukan hal ini membuat aturan dengan mengklik Aturan Baru ... tombol. Pada mengklik tombol ini, Anda akan mendapatkan kotak dialog Aturan Baru Formatting. Di sini Anda membuat aturan baru untuk kebutuhan Anda.
    Conditional Formatting: kotak dialog aturan format baru dengan aturan nilai sel
  5. Setelah menetapkan aturan dan format, klik tombol OK. Aturan baru Anda akan disimpan di Conditional Formatting Aturan Manager
    Bersyarat manager aturan format
  6. Klik tombol Apply untuk menerapkan aturan baru dibuat. Seperti yang Anda lihat, semua sel dengan nilai antara 20 dan 40 telah disorot dengan warna kuning. Aturan ini juga sekarang disimpan dan Anda dapat menggunakannya dari Aturan Manager kapan saja di masa mendatang.

Kami berharap bahwa tutorial ini pada Conditional Formatting di Excel berguna untuk Anda. Jika Anda memiliki pertanyaan terkait dengan topik ini, jangan ragu untuk bertanya di bagian komentar. Kami akan mencoba yang terbaik untuk membantu Anda. Terima kasih telah menggunakan TechWelkin!