![Belajar Custom Number Format Excel [Panduan Mendalam]](https://i0.wp.com/mjurnal.com/wp-content/uploads/2021/10/custom-number-format-excel-15-min.png?resize=768%2C400&ssl=1)
Perhatian! Panduan ini bukan sekedar membahas tentang pengertian Custom Number Format di Excel ataupun kegunaannya. Melainkan Saya akan menjelaskan dari aturan dasar custom number format, cara kerja, hingga arti setiap kode dan contoh penyusunannya secara mendalam.
Seperti yang sudah Saya bahas dalam SUB-BAB Sebelumnya, Excel memiliki banyak jenis format number bawaan seperti general, text, currency, accounting, hingga special.
Jika pemformatan number bawaan ini tidak cocok dengan kebutuhan Anda, inilah saatnya menggunakan custom number format
Sebelum melanjutkan, seduh kopi Anda terlebih dahulu. Karna ini akan menjadi panduan yang sangat panjang.
Table of Contents
Apa Itu Custom Number Format di Microsoft Excel ?
Custom Number Format Excel adalah fitur pemformatan cell (yang berisi angka atau tanggal dan waktu) tingkat lanjut yang berguna untuk mengubah tampilan data sesuai keinginan tanpa mengubah nilai data tersebut.
Salah satu contohnya, Anda bisa menyingkat data angka bernilai besar seperti 2.360.000.000 (Dua Miliar Tiga Ratus Enam Puluh Juta) menjadi 2M atau 2,36M tanpa mengubah nilainya.
Excel tetap mengenali angka 2,36M “valid” sebagai angka 2.360.000.000, sehingga Anda tidak perlu takut kesalahan terjadi dalam proses kalkulasi rumus / fungsi terhadap data tersebut.
Bukan cuma untuk menyisipkan satuan, Anda juga bisa memformat angka, tanggal & waktu, mengubah warna hingga menerapkan kriteria dalam pemformatan sesuai keinginan.
Semua itu bisa diterapkan asalkan tidak ada kesalahan dalam penulisan custom number format.
Aturan Penulisan Custom Number Format Excel
Penting! Sebelum melanjutkan, pahami bahwa format angka ribuan dan desimal di Excel untuk regional English tidak sama dengan Indonesia. Dalam Format English, tanda koma (,) adalah pemisah ribuan dan titik (.) adalah pemisah desimal. Sebaliknya untuk format Indonesia.
Tidak banyak yang tahu bahwa custom number format dalam Excel juga memiliki aturan penulisan seperti rumus dan fungsi Excel lainnya.
Misalnya seperti rumus VLOOKUP yang terdiri dari 4 argument yaitu lookup_value, table_array, col_index_num dan [range_lookup].
Begitu juga dengan Custom Number Format yang memiliki argument tersendiri yaitu:
- POSITIVE merupakan format untuk cell yang berisi angka positif.
- NEGATIVE merupakan format untuk cell yang berisi angka negatif.
- NOL merupakan format untuk cell yang berisi angka nol.
- TEXT merupakan format untuk cell yang berisi teks.
Semua argument bersifat optional. Artinya boleh digunakan maupun tidak. Jadi jika ingin menggunakan custom number format, Anda hanya perlu menggunakan minimal 1 argument sesuai kebutuhan.
Note: Dalam rumus / fungsi Excel, operator pemisah setiap argument untuk format English & Indonesia berbeda ( , dan ; ). Sementara itu, custom number format Excel hanya menggunakan titik koma ( ; ). Jadi apapun format regional Excel Anda, tetap menggunakan titik koma ( ; ).
Cara Membuat Custom Number Format Excel
Untuk membuat custom number format di Excel, silahkan ikuti tahap-tahap berikut:
- Pilih (blok) data yang akan menggunakan custom number format.
- Klik Tab Home pada Ribbon.
- Dalam Number Grup, klik Format Number Option (atau tekan CTRL + 1).
- Klik Category Custom.
- Pilih format kustom bawaan yang tersedia, atau
- Masukkan kode custom pada bagian Type. Misalnya #,##0.00;[Red]-#,##0.00;”-“;[Blue]@
- Klik Ok untuk menerapkan. Ini hasilnya:
Saya harap, minimal Anda sudah paham tahap 1 sampai 4. Selanjutnya, ada banyak hal yang bisa Anda eksplor mulai dari tahap 5.
Jadi Saya mulai terlebih dahulu cara kerja Custom Number Format Excel.
Cara Kerja Custom Number Format Excel
Coba kita ambil kembali kode yang digunakan sebelumnya.
Custom Number Format ini akan mengubah tampilan nilai dalam cell dengan cara berikut:
- #,##0.00 (argument POSITIVE), kode ini akan menampilkan angka dalam cell menggunakan format ribuan dengan pemisah berupa koma #,##0, serta memiliki 2 angka desimal dengan pemisah berupa titik #,##0.00. Ingat! ini format regional English ya.
- [Red]-#,##0.00 (argument NEGATIVE). Perbedaan dengan argument pertama hanya pada kode warna dan simbol negatif [Red]-#,##0.00. Artinya, kode ini akan menampilkan angka negatif menggunakan format ribuan dengan 2 angka desimal serta menggunakan font berwarna merah.
- “-“ (argument NOL), kode ini untuk memformat cell yang berisi angka 0 (nol) menjadi simbol strip/minus.
- [Blue]@ (argument TEKS), kode ini untuk memformat cell yang berisi teks dan mengubah warna font menjadi biru.
Note: Setiap argument memiliki kode masing-masing seperti pound (#), [Red], @ dan sebagainya. Tenang, ini akan Saya jelaskan dibagian selanjutnya.
Yang perlu Anda ketahui adalah Anda tidak wajib menggunakan ke empat argument tersebut.
Misalnya, jika hanya menggunakan argument POSITIVE #,##0.00, maka akan mengubah cell yang berisi angka positif, negatif dan angka 0 (nol) seperti berikut:
Sementara jika hanya menggunakan 2 argument (POSITIVE & NEGATIVE), maka argument POSITIVE akan memformat angka positif dan angka 0 (nol), sedangkan argument NEGATIVE akan memformat angka negatif saja seperti berikut:
Jika menggunakan argument POSITIVE;NEGATIVE;NOL, maka argument POSITIVE hanya memformat angka positif saja. Sedangkan argument NOL mengambil tugasnya sendiri (untuk cell berisi angka nol).
Begitu juga dengan argument TEXT yang hanya akan memformat cell yang berisi teks.
Anda juga bisa menggunakan salah satu argument saja dengan cara mengosongkan argument sebelumnya.
Misalnya, jika menggunakan argument NEGATIVE saja, maka susunan kode seperti berikut:
Yang terpenting: setiap mengosongkan argument, Anda tetap wajib memasukkan operator pemisah argument (tanda titik koma “;”).
Pahami Arti Setiap Kode Dalam Custom Number Format Excel
Dalam penulisan custom number format, Anda tidak bisa sembarangan menggunakan kode. Karena setiap kode memiliki kegunaannya masing-masing.
Ada kode yang digunakan untuk angka dan tanggal, mengubah warna serta menyisipkan teks.
#1 Kode Desimal, Ribuan dst…
1. Angka 0 (nol) untuk menampilkan digit desimal serta secara optional akan menyisipkan angka 0 (dalam desimal). Satu buah angka 0 mewakili 1 digit desimal.
Misalnya jika Anda menerapkan kode 0.0 ke angka 22000 dan 22000.55, maka data akan selalu ditampilkan dalam 1 digit desimal yaitu 22000.0 dan 22000.6. Lihat contoh berikut:
Penting! Kode 0.0 ini yang digunakan dalam format number (desimal) default Excel.
2. Karakter # (pound) untuk menampilkan digit angka desimal tanpa menyisipkan angka 0 tambahan.
Berbeda dengan Kode angka 0, karakter # tidak akan menampilkan digit desimal pada angka yang tidak memiliki nilai desimal selain 0 (nol).
Misalnya jika menerapkan kode #.# pada angka 0.5 dan 5, maka akan tampil menjadi .5 dan 5.
Note: Anda juga bisa menggabungkan karakter # dan angka 0. Misalnya kode #.0 untuk angka 5.55 akan tampil menjadi 5.6. Jadi nilai akan selalu ditampilkan dalam 1 digit desimal. Dan Excel akan membulatkan angka jika perlu seperti contoh berikut:
Apabila bilangan asli tidak memiliki angka desimal selain nol, maka Anda tidak akan bisa menampilkan 3 digit desimal menggunakan kode ini #.### sekalipun.
Jadi jika ingin menyisipkan nol, gunakan kombinasi kode # dan 0 seperti ini #.000.
3. Karakter ? (question mark) sedikit hampir mirip dengan 0.
Bedanya, karakter ini akan menyisipkan spasi (bukan angka 0). Jadi kode #.??? untuk angka 22000 dan 22000.555 akan menjadi 22000. dan 22000.555 seperti berikut:
Karakter ini menyisipkan spasi kosong dan menyajarkan posisi pemisah desimal untuk semua hasil.
4. Karakter , (koma) dalam regional English sebagai pemisah ribuan. Sedangkan regional Indonesia sebagai pemisah desimal.
5. Karakter . (titik) dalam regional English sebagai pemisah desimal. Sedangkan regional Indonesia sebagai pemisah ribuan.
Dalam praktiknya, Anda bisa menggabungkan karakter koma (,) dan titik (.) secara bersamaan untuk menampilkan pemisah ribuan dan desimal.
Misalnya, dengan menerapkan kode #,##0.00 untuk angka 22000 dan 22000.555 akan menampilkan 22,000.00 dan 22,000.56 seperti berikut:
Satu Pertanyaan… Bagaimana Cara Menentukan Jumlah Digit Desimal ?
Penempatan desimal dalam kode custom number format berada setelah karakter titik (.) untuk regional English dan karakter koma (,) untuk regional Indonesia.
Sedangkan jumlah digit desimal ditentukan dari berapa banyak angka nol (0) setelah pemisah desimal dalam kode.
Jadi, kode 0 atau # saja tidak akan menampilkan satupun digit desimal. Silahkan ganti dengan 0.0 atau #.0, kode ini akan menampilkan 1 digit desimal.
Untuk menambahkan digit desimal, silahkan tambahkan kode nol seperti 0.00 atau #.00 untuk 2 digit, kode 0.000 atau #.000 untuk 3 digit desimal, dan begitu seterusnya.
#2 Kode Teks dan Spasi
Dengan Custom Number Format, Anda juga bisa menyisipkan teks atau spasi ke dalam cell tanpa mengubah nilai cell.
Note: Ada 1 karakter khusus untuk mewakili teks dalam cell yang akan diformat serta 4 karakter untuk menyisipkan teks atau spasi.
1. Karakter @ (et) mewakili teks yang akan diformat namun tidak berlaku untuk data berupa angka. Karakter @ hanya berlaku untuk argument TEKS dalam susunan kode.
Misalnya seperti contoh pertama diatas, Saya menggunakan kode #,##0.00;[Red]-#,##0.00;"-";[Blue]@, kode ini hanya mengubah warna font dari cell yang berisi teks saja.
Anda juga bisa menggunakan format general untuk argument POSITIVE, NEGATIVE dan NOL, sementara menggunakan [Blue]@ untuk argument TEXT seperti berikut:
2. Karakter \ (backslash) untuk menampilkan karakter yang diketik setelah karakter backslash ini. Misalnya kode #.0\g akan menampilkan angka 36.2 menjadi 36.2g.
3. Karakter _ (underscore) untuk membuat karakter yang diketik setelah underscore ini menjadi tidak terlihat.
Biasanya karakter underscore sering digunakan untuk menyembunyikan buka kurung “(” dan tutup kurung “)” agar angka positif dan negatif tampil sejajar seperti berikut:
Jadi dengan kode #,##0_);(#,##0) akan menyisipkan tanda tutup kurung setelah angka positif, namun tidak menampilkannya. Sementara angka negatif tampil didalam tutup kurung.
Sehingga ini lah yang membuat angka positif tampil sejajar dengan angka negatif.
4. Karakter * (asterisk) untuk membuat karakter setelahnya tampil berulang hingga memenuhi cell. Misalnya dengan menggunakan kode #*- maka simbol minus akan tampil penuh setelah isi cell.
5. Karakter ” “ (kutip dua) untuk menampilkan teks dan spasi yang diketik dalam kutip dua. Anda dapat menyisipkan teks tepat sebelum atau sesudah kode angka.
Misalnya menggunakan kode "Up: "#,##0.00 untuk angka 20500 akan menampilkan Up: 20,500.00.
Dan tentunya Anda juga selalu bisa menerapkannya dalam argument NEGATIVE. Misalnya kode "Up: "#,##0.00;"Down: "-#,##0.00;"-" untuk data yang bervariasi seperti berikut:
Note: Untuk menyisipkan spasi: Masukkan spasi diawal atau akhir teks dalam kutip dua. Ini akan tergantung apakah teks berada diawal atau diakhir.
Kode Simbol Khusus: Trade Mark (™), Copyright (©), Degree (°) dll
Anda juga bisa menerapkan simbol khusus seperti Trade Mark (™), Copyright (©), Degree (°) dsb. ke dalam custom number format. Semua simbol khusus ini juga harus diapit kutip dua.
Sebagai contoh, susunan kode #.0"°C" akan menyisipkan simbol degree seperti berikut:
Sebagai pengingat, jika data dalam cell berupa teks serta Anda ingin menampilkan Trade Mark (™), maka gunakan kode ini pada argument TEXT.
Jadi, coba gunakan custom number format berikut ;;;[Blue]@"™". Hasilnya seperti berikut:
Dengan mengosongkan argument POSITIVE, NEGATIVE dan NOL, maka Excel hanya akan mengeksekusi argument TEXT.
Karakter Special Tanpa Perlu Diapit Kutip Dua
Pada dasarnya, untuk menambahkan teks ke dalam custom number format, Anda harus mengapitnya dengan kutip dua ” “.
Namun aturan ini dapat dikecualikan untuk beberapa karakter berikut:
KarakterNama
| $ | Dollar sign |
| + | Plus sign |
| : | Colon |
| = | Equal sign |
| – | Minus sign |
| / | Slash mark |
| ^ | Circumflex accent (caret) |
| ‘ | Apostrophe |
| ( | Left parenthesis |
| ) | Right parenthesis |
| { | Left curly bracket |
| } | Right curly bracket |
| < | Less-than sign |
| > | Greater-than sign |
| & | Ampersand |
| ! | Exclamation point |
| ~ | Tilde |
| Space character |
Tabel: Karakter yang dikecualikan dalam kutip dua custom format number.
Jadi, Anda boleh-boleh saja menggunakan susunan kode >#,##0.00 untuk menambahkan simbol lebih besar dari pada angka 2000 menjadi >2,000.00.
Ada baiknya pahami pula berbagai jenis operator perbandingan di Excel.
#3 Kode Mata Uang
Seperti yang Saya sebutkan diatas, simbol mata uang $ (dollar) termasuk simbol yang tidak perlu diapit kutip dua.
Jadi jika Anda ingin menampilkan simbol mata uang dollar, maka ketik langsung simbol tersebut ke dalam susunan kode seperti berikut: $#,##0.0.
Sementara untuk menampilkan simbol Rp (Rupiah), silahkan apit menggunakan kutip dua menjadi "Rp "#,##0.0.
Jika Anda ingin menyisipkan simbol mata uang khusus seperti Euro (€), Poundsterling (£) dll yang tidak ada dalam keyboard, maka Anda bisa menggunakan pintasan keyboard.
Caranya, aktifkan NUM LOCK pada keyboard dan tekan kombinasi kode berikut menggunakan tombol numeric.
SimbolMata UangKode
| € | Euro | ALT+0128 |
| £ | Poundsterling (Inggris) | ALT+0163 |
| ¥ | Yen (Jepang) | ALT+0165 |
| ¢ | Cent Sign | ALT+0162 |
Tabel: Pintasan Simbol Mata Uang Populer Menggunakan Keyboard
Sebagai contoh, kode ALT+0128 artinya tekan dan tahan tombol ALT pada keyboard sambil menekan tombol angka 0 1 2 8 secara berurutan.
#4 Kode Persentase, Pecahan dan Eksponensial
1. Karakter % (percentage) untuk menampilkan angka dalam persentase. Anda bisa memasukkan simbol % secara langsung ke dalam susunan kode.
Misalnya, kode ini #% akan menampilkan angka 0.025 menjadi 3%. Sementara kode #.0% akan menampilkan 2.5% seperti berikut:
Note: Dengan kode persen %, maka Excel akan menampilkan bilangan asli yang dikali dengan 100%. Misalnya cell C1 = 0.025 x 100% = 2.5%.
Jadi jika Anda ingin menampilkan 25%, maka nilai asli harus senilai 0.25 (bukan 25). Jika Anda menerapkan kode #% ke angka 25, maka Excel akan menampilkannya sebagai 25 x 100% = 2500%.
2. Karakter / (slash) untuk menampilkan angka dalam bentuk pecahan. Tanda slash berguna sebagai pemisah antara penyebut dan pembilang.
Contoh paling sederhana, coba gunakan #/# untuk berbagai macam bilangan desimal. Dan lihat hasilnya:
Seperti yang Anda lihat, Anda bisa menggunakan berbagai macam format untuk menentukan jumlah digit pecahan.
Note: Perlu diketahui bahwa Excel memberikan perlakuan khusus untuk nilai pecahan.
Misalnya seperti hasil pada cell E6 yaitu 69/8 dengan nilai asli adalah 8.6. Coba hitung menggunakan kalkulator online (Tools M Jurnal), 69 / 8 = 8.625. Dan hasil ini tidak sama dengan nilai asli yaitu 8.6.
Penting! Ini bukan hasil yang salah. Tapi perlakuan khusus yang diberlakukan Excel terhadap bilangan pecahan, bahwa Excel akan menampilkan nilai paling terdekat dari format pecahan yang Anda gunakan.
Selain menggunakan tanda pound (#), Anda juga bisa menggunakan question mark (?) seperti kode ini # ?/?, dan lihat hasilnya:
Anda bisa menilai sendiri hasil mana yang lebih rapi.
3. Huruf E (exponent) untuk menampilkan angka dalam bilangan Scientific Notations (Exponential Format). Untuk menggunakannya, Anda hanya perlu menambahkan huruf E ke dalam susunan kode.
Misalnya, jika menggunakan kode 0.00E+00 ke angka 25000, maka akan tampil sebagai 2.50E+04 seperti berikut:
#5 Kode Warna dan Syarat (Kriteria) Tertentu
Untuk mengubah warna font menggunakan custom number format, Anda bisa menggunakan kode warna yang diapit menggunakan kurung siku [ ].
Saat ini Excel mendukung 8 warna utama yaitu:
KodeWarna
| [Black] | Hitam |
| [Green] | Hijau |
| [White] | Putih |
| [Blue] | Biru |
| [Magenta] | Magenta |
| [Yellow] | Kuning |
| [Cyan] | Cyan |
| [Red] | Merah |
Tabel: Kode Warna untuk custom number format Microsoft Excel
Note: Kode warna harus berada di awal setiap argument. Misalnya untuk mengubah warna angka positif menjadi biru, Anda bisa menggunakan kode berikut: [Blue]#_);(#);"-";@.
Selain digunakan untuk mengubah warna, Karakter [ ] juga digunakan untuk memasukkan syarat pengujian logika ke dalam kode custom number format Excel. Misalnya Saya memiliki data seperti gambar berikut:
Kemudian, Saya menerapkan potongan kode custom number format Excel berikut:
Ini hasilnya:
Seperti yang Anda lihat, Excel hanya akan mengubah tampilan nilai yang memenuhi kriteria >25. Itu sebabnya cell B4 dan B5 tidak berubah sama sekali sesuai format #.00.
#6 Kode Tanggal dan Waktu
Custom Number Format Excel juga bisa digunakan untuk data berupa tanggal atau waktu yang valid.
Untuk tanggal ada 3 kode umum yang bisa digunakan yaitu:
- d (day) untuk hari.
- m (month) untuk bulan.
- y (year) untuk hari.
Dalam penggunaannya, Anda harus menggunakan format tertentu seperti berikut:
KodeHasil
| d | Digit angka hari (1-31). |
| dd | 2 digit angka hari (01-31). |
| ddd | Singkatan hari (Sen, Sel, Rab, sampai Mgg). |
| dddd | Nama lengkap hari (Senin, Selasa, Rabu,sampai Minggu). |
| m | Digit angka bulan (1-12). |
| mm | 2 digit angka bulan (01-12). |
| mmm | Singkatan bulan (Jan, Feb, Mar, sampai Des). |
| mmmm | Nama lengkap bulan (Januari, Februari, Maret, sampai Desember). |
| mmmmm | Huruf awal bulan (J, F, M sampai D). |
| yy | 2 digit terakhir angka tahun. |
| yyyy | 4 digit angka tahun. |
Tabel: Kode Tanggal dalam Custom Format Cell Microsoft Excel
Sementara untuk waktu, ada beberapa jenis kode yang bisa digunakan yaitu:
- h (hour) untuk jam.
- m (minute) untuk menit.
- s (second) untuk detik.
- AM/PM untuk format 12 jam.
Sementara kombinasinya bisa menggunakan kode seperti berikut:
KodeHasil
| h | Digit angka jam (0-23). |
| hh | 2 digit angka jam (00-23). |
| m | Digit angka menit (0-59). |
| mm | 2 digit angka menit (00-59). |
| s | Digit angka detik (0-59). |
| ss | 2 digit angka detik (00-59). |
| [h] atau [m] atau [s] | Khusus angka yang lebih dari 24 jam atau 60 menit atau 60 detik (bisa hasil dari sebuah rumus) tanpa pembulatan. |
| AM/PM | Waktu 12 jam: AM untuk waktu tengah malam sampai tengah hari. PM untuk waktu tengah hari sampai tengah malam. |
Tabel: Kode Waktu dalam Custom Format Cell Microsoft Excel
Dalam praktiknya, Anda juga bisa menggunakan am/pm atau A/P, atau a/p sebagai ganti AM/PM.
Note: Untuk contoh-contoh komplit cara menggunakan custom number format Excel serta penjelasan mendetail akan Saya bahas dalam SUB-BAB berikutnya.