Membuat Data Bars Conditional Formatting Excel

Data bars Excel adalah alat bantu visual dalam membandingkan data.

Data bars memiliki kesamaan dengan bar chart. Keduanya adalah grafik batang, bersifat kategoris, dan setiap bar (batang) mewakili nilai tertentu.

Namun, data bars lebih mudah digunakan untuk analisa cepat ketimbang bar chart.

Data bars adalah bagian dari conditional formatting Excel.

Sebagai bahan demo, kami menggunakan data dengan file yang bisa teman-teman download pada link di bawah ini. Tapi, monggo aja misalkan mau pakai data sendiri.

File: 00-data-bars.xlsx.

Apakah Data Bars Conditional Formatting Excel Itu?

Data bars Excel adalah fitur conditional formatting yang menampilkan bar persegi panjang berwarna di dalam cell. Panjang bar ditentukan oleh nilai di dalam cell bersangkutan dan dibandingkan dengan data yang lain pada range yang sama. Perhatikan Gambar 01.

Data bars Excel memudahkan kita melihat data dengan angka tertinggi atau terendah. Contoh: Memilah penjualan terbaik dan terburuk. Selain itu, dengan fitur angka negatif memungkinkan untuk membandingkan penjualan dari satu periode ke period lain.

Apa Perbedaan Data Bars dan Bar Charts?

Walau pun memiliki kesamaan, data bars berbeda dari bar chart dalam beberapa hal.

• Pada data bars, setiap bar berada pada cell di mana nilai sumber berasal. Pada bar chart, setiap bar terkumpul di satu objek tersendiri terpisah dari sumber data.
• Bar chart adalah objek yang berdiri sendiri. Data bars adalah bentuk pemformatan cell dan bukan objek.
• Seperti halnya objek di Excel, kita bisa memindahtempatkan bar chart. Tapi bar pada data bars hanya berdiam di dalam cell nilai sumber.

Cara Membuat Data Bars di Excel

(Demo 01) Berikut cara membuat data bars di Excel.

1. Pilih cell atau range di mana data bars akan ditampilkan.
2. Buka Conditional Formatting.
3. Pilih Data Bars.
4. Pilih salah satu format.

Hasil dari pemformatan bisa dilihat pada gambar di bawah.

Perbedaan Gradient Fill dan Solid Fill di Data Bars Excel

Perhatikan Gambar 03. Excel memberikan dua kategori data bars, yaitu Gradient Fill dan Solid Fill.

Secara prinsip, tidak ada perbedaan di antara keduanya. Kita bisa pakai salah satunya untuk tujuan yang sama.

Umumnya, Gradient Fill dipakai ketika nilai ditampilkan berdampingan dengan bar pada cell yang sama. Warna dari sisi terkanan Gradient Fill yang cenderung kontras dengan warna font memungkinkan teks terbaca lebih baik.

Di sisi lain, Solid Fill lebih cocok ketika kita hendak menampilkan bar saja tanpa nilai.

Batas Minimum dan Maksimum Data Bars Excel

Setiap bar pada data bars dihasilkan melalui perhitungan dengan menggunakan batas bawah dan atas.

Format pada Gambar 05 dihasilkan dengan menerapkan perhitungan otomatis untuk batas bawah dan atas.

Tapi, bila dibutuhkan, kita bisa menentukan sendiri batas terendah dan tertinggi.

Pada demo kali ini, kami berniat untuk menampilkan angka penjualan di antara 2000 dan 20000.

(Demo 02) Berikut cara membuat data bars conditional formatting Excel dengan menentukan sendiri batas minimum dan maksimum:

1. Pilih cell atau range di mana data bars akan diterapkan.
2. Klik Conditional Formatting → Manage Rules. Pilih aturan yang diinginkan, lalu klik Edit Rules.
3. Pada Minimum Type, pilih Number.
4. Isikan 2000 pada Minimum Value.
5. Pada Maximum Type, pilih Number.
6. Isikan 20000 pada Maximum Value.
7. Klik semua tombol OK untuk menutup kotak-kotak dialog.

Hasil dari pemformatan bisa dilihat pada gambar di bawah.

Catatan: Untuk mengetahui bagaimana membuka kotak dialog Rule Manager, monggo baca dasar conditional formatting.

Data Bars Conditional Formatting Berdasarkan Cell Lain

Salah satu pilihan Minimum dan Maximum adalah Formula.

Penggunaan rumus adalah cara menggunakan conditional formatting berdasarkan cell lain.

Conditional formatting berdasarkan cell lain adalah cara menggunakan conditional formatting dengan menggunakan nilai referensi yang berada di cell lain.

Bandingkan dengan Demo 02 di mana kita menuliskan langsung batas bawah dan atas di kotak dialog Formatting Rule.

Secara logika, rumus akan menghasilkan nilai tertentu yang digunakan untuk menentukan batas atas dan bawah data bars.

Pada demo berikut, kami mengambil nilai minimum dari nilai terendah penjualan dan maksimum dari nilai tertinggi. Untuk itu kami akan menggunakan fungsi MIN dan MAX.

(Demo 03) Berikut cara menggunakan data bars conditional formatting berdasarkan cell lain.

1. Pilih cell atau range yang ingin diformat.
2. Klik Conditional Formatting → Manage Rules. Pilih aturan yang diinginkan, lalu klik Edit Rules.
3. Pada Minimum Type, pilih Formula.
4. Pada Minimum Value, masukkan rumus yang diinginkan (kami menggunakan fungsi MIN).

Tips: Alih-alih repot menuliskan range, kita bisa memilih range langsung dengan click and drag pada worksheet.

5. Ulangi Langkah 3 dan 4 untuk Maximum (kami menggunakan fungsi MAX).
6. Klik OK untuk menutup kotak dialog Formatting Rule.
7. Klik OK untuk menutup Rule Manager.

Sebagai hasilnya, kita akan mendapatkan data dengan format data bars seperti pada Gambar 09.

Data Bars Conditional Formatting Excel Menampilkan Bar Saja

Untuk menampilkan data bars tanpa nilai sumber, gunakan fitur Show Bar Only. Fitur ini dapat dijumpai di kotak dialog Formatting Rule, tepat di sebelah combo box Format Style.

(Demo 04) Berikut cara membuat data bars conditional formatting Excel hanya menampilkan bar saja, tanpa nilai.

1. Pilih cell atau range yang ingin diformat.
2. Klik Conditional Formatting → Manage Rules. Pilih aturan yang diinginkan, lalu klik Edit Rules.
3. Aktifkan Show Bar Only.
4. Pilih Solid Fill untuk Bar Appearance dan warna yang diinginkan.
5. Tentukan border dan warna yang diinginkan.
6. Klik OK untuk menutup kotak dialog dan mengkonfirmasi perubahan.

Hasil pemformatan bisa dilihat pada gambar di bawah.

Bila ingin tetap menampilkan nilai dan bar tapi pada cell terpisah, teman-teman bisa menyalin nilai-nilai ke kolom lain dan format kolom baru tersebut dengan Show Bar Only. Lihat Gambar 12.

Membuat Excel Custom Data Bars

Excel custom data bars adalah data bars dengan aturan dan format yang kita tetapkan sendiri, alih-alih menggunakan aturan-aturan siap pakai Excel.

(Demo 05) Berikut cara membuat sendiri Excel custom data bars.

1. Pilih cell atau range yang akan diformat.
2. Buka Conditional Formatting.
3. Klik Data Bars.
4. Klik More Rules untuk membuka kotak dialog Formatting Rule.

   

5. Pilih Format all cells based on their values.
6. Pada Format Style pilih Data Bar.
7. Pada Minimum, pilih Automatic.
8. Pada Maximum, pilih Automatic.
9. Pada Fill, pilih Gradient Fill.
10. Pilih warna yang diinginkan pada Color.
11. Pada Border, pilih Solid Border.
12. Pilih warna yang diinginkan untuk Border.
13. Pada Bar Direction, pilih Context.
    Excel menyediakan beberapa pilihan untuk Bar Direction dengan Context sebagai pilihan default. Context akan melihat tipe font yang kita gunakan dan memutuskan arah berdasarkan tipe font tersebut. Left-to-Right akan menetapkan arah bar dari kiri ke kanan tanpa melihat arah penulisan teks. Right-to-Left akan menetapkan arah bar dari kanan ke kiri tanpa menghiraukan arah penulisan teks.

14. Klik OK.

Bila dilakukan dengan benar, hasil pemformatan akan seperti pada Gambar 15 di bawah.

Membuat Data Bars Excel untuk Nilai Negatif

Seperti teman-teman perhatikan pada Gambar 15, Excel menampilkan bar-bar negatif padahal kita tidak membuat pengaturannya.

Excel sebenarnya sudah menyiapkan pengaturan default untuk nilai bar negatif. Pengaturan ini diakses melalui tombol Negative Value and Axis pada kotak dialog Formatting Rule.

Demo kali ini bertujuan untuk merubah warna bar negatif dengan menggunakan data pada Gambar 15.

(Demo 06) Berikut cara membuat data bars Excel dengan nilai negatif.

1. Pilih cell atau range yang akan diformat.
2. Klik Conditional Formatting → Manage Rules. Pilih aturan yang diinginkan, lalu klik Edit Rules.
3. Klik Negative Value and Axis untuk membuka kotak dialog Negative Value and Axis Settings.
4. Pada Negative bar fill color, pilih warna yang diinginkan.
5. Pilih warna border pada Negative bar border color.
6. Pilih posisi axis.
7. Pilih warna axis.
8. Klik semua tombol OK untuk mengkonfirmasi pengaturan dan menutup kotak-kotak dialog.

Catatan: Bila tidak ada pengaturan khusus untuk nilai bar negatif, Excel akan menggunakan warna merah untuk warna bar dan border, serta axis Automatic.

Posisi Axis

Axis adalah garis batas antara bar negatif dan positif.

Excel memberikan tiga alternatif untuk posisi axis, yaitu:

Automatic akan menempatkan posisi axis relatif terhadap besaran data positif dan negatif.

Sebagai contoh, bila data positif terbesar adalah 10000 sementara data negatif terkecil adalah -2000, maka posisi axis akan lebih ke kiri untuk memberikan ruang lebih bagi data positif.

Sebaliknya, bila data positif terbesar adalah 2000, sementara data negatif terkecil adalah -10000, maka posisi axis akan lebih ke kanan untuk memberikan ruang lebih bagi data negatif.

Cell midpoint akan mengacuhkan besaran data negatif dan positif dan menempatkan posisi aksis tepat di tengah cell.

None akan menghilangkan axis dan hanya memberikan cell bagi negatif atau positif.

Sebagai contoh, bila cell berisi angka negatif dengan warna merah, maka cell tersebut hanya berisi bar warna merah.

Pertanyaan: Teman-teman tau kenapa cell A6 gak punya bar?

Penutup

Demikian bahasan kami mengenai data bars conditional formatting Excel.

Selain format data bars siap pakai, kita juga bisa membuat data bars custom Excel. Dengan data bars custom, Excel mengijinkan kita untuk mengubah pengaturan logika yang menjadi dasar perhitungan data bars serta mengubah format untuk nilai negatif dan positif.