Cara Membuat Baris Pertama Tabel Otomatis Jadi Judul Header di Tiap Halaman Excel Saat Dicetak/Diprint

Suatu kali saya membuat data di Excel dengan data memanjang ke bawah seperti daftar hadir dengan jumlah peserta ratusan orang, hingga jadi beberapa halaman draft. Saya menginginkan baris pertama yang berisi judul tabel akan otomatis muncul di setiap halaman pada saat daftar hadir itu saya cetak/print. 

Contoh Daftar Hadirnya seperti ini:

Cara Membuat Baris Pertama Tabel Otomatis Jadi Judul Header di Tiap Halaman Excel Saat Dicetak/print

Untuk membuat baris pertama (saya warnai kuning) otomatis menjadi judul pada halaman cetak, begini caranya.

1. Pada bagian menu Excel, pilih Page Layout, lalu klik Print Titles
2. Jika sudah diklik akan muncul jendela Page Setup pada tab Sheet
3. Pada bagian Print Titles, klik bagian kanan dari Rows to repeat at top (saya lingkari hijau)
4. Selanjutnya pilih baris yang akan otomatis jadi judul halaman cetaknya, dalam hal ini saya ingin baris pertama saja, maka saya klik nomor barisnya di bagian paling kiri sheet (saya lingkari oranye). 
5. Kode baris yang akan saya jadikan judul otomatis pada halaman cetak akan muncul pada jendela munculan. Tutup jendela itu, hingga muncul kembali jendela Page Setup, lalu klik OK.

Selesai sudah. Otomatis baris pertama tabel akan muncul menjadi judul halaman selanjutnya pada saat dicetak/diprint. Untuk melihat hasil cetak/print sebelum kita cetak/print, kita bisa lihat pada Print Preview seperti di bawah ini. Halaman 3 pun terlihat memiliki header seperti halaman 1.

Mudah, kan. Jika bermanfaat, bantu saya share cara ini ke media sosial ya. Thanks in advance. :)

Read more »

Trik Microsoft Excel: Cara Menjumlahkan Sejumlah Sel Otomatis Tanpa Klik Satu Persatu

 Saya membuka lagi kolom komentar dan menemukan pertanyaan dari Mas Icuk Widiya Saputra. Begini pertanyaannya:

Saya punya pertanyaan mengenai penjumlahan. Sebagai contoh ada 1 kolom berisi 10 sel, bagaimana caranya menjumlahkan secara otomatis sel a1,a3,a5,a7,a9 tanpa memilih satu per satu?

Trik Microsoft Excel: Cara Menjumlahkan Sejumlah Sel Otomatis Tanpa Klik Satu Persatu

Untuk menjumlahkan sel-sel tertentu dalam Excel, biasanya kita harus satu-persatu mengeklik + tombol CTRL untuk menambahkan sel yang akan dijumlahkan. Ini tentu tidak efektif, khususnya apabila data yang dikerjakan sangat banyak.

Ada satu trik Excel yang saya tahu dalam menjumlahkan secara otomatis sejumlah sel tertentu, yaitu dengan kata kunci REFERENSI. Apabila kita bisa membuat referensi sel mana yang akan kita jumlahkan, maka Excel akan dapat menjumlahkannya secara otomatis, bahkan dengan data di sheet yang berbeda.Untuk lebih mudahnya, kita panggil data dalam satu sheet yang sama saja ya, satu sheet kita pakai dua tabel.

Kita ambil data fiktif penjualan pisang goreng keluarga kami seperti berikut:

Trik Microsoft Excel: Cara Menjumlahkan Sejumlah Sel Otomatis Tanpa Klik Satu Persatu

Ok kita akan membuat referensi dengan membuat NAMA dari sel-sel pada kolom yang akan kita jumlahkan. 

Caranya:

1. Blok semua data yang akan kita namai

2. Tekan tombol CTRL+ SHIFT + F3 untuk membuka jendela pembuatan NAMA (NAME)

3. Hilangkan centang Left Column karena kita akan menamai berdasarkan TOP ROW saja

Membuat NAMA (FUNGSI NAME) untuk data di kolom

Jadi dalam data di atas adalah nama data yang ada di kolom B berubah semua menjadi NAMA (yaitu nama orang) dan nama data di kolom PENJUALAN.

Untuk menjumlahkan hasil jualan orang tertentu pada kolom B, misalnya Raos, kita akan memanggil data Raos dengan rumus SUMIF.

Rumus SUMIF adalah =SUMIF(RANGE; CRITERIA; [SUM_RANGE])

RANGE-nya adalah data dalam sel yang sudah kita namai dengan NAMA

CRITERIA-nya kita taruh di F3

SUM_RANGE-nya adalah semua data yang akan kita jumlahkan yaitu sel yang sudah kita namai dengan PENJUALAN

Maka di sel H3, kita menulis rumus: =SUMIF(Nama;F3;Penjualan)

Lalu kita tekan ENTER, muncul hasilnya sebagai berikut:

Hasil penjumlahan sel tertentu dalam Excel

Untuk menghitung jumlah hari masuk jaga warung, kita bisa menggunakan rumus COUNTIF, yang pada dasarnya digunakan untuk menghitung jumlah sel yang terisi dengan kriteria tertentu.

Rumusnya:

=COUNTIF(RANGE;CRITERIA)

RANGE-nya adalah data dalam sel yang sudah kita namai sebagai NAMA

CRITERIA-nya kita taruh di F3

Jadi untuk data di atas kita tulis rumus:

=COUNTIF(Nama;F3)

Menghitung jumlah masuk kerja dalam Excel

Jika sudah jadi, rumus bisa kita aplikasikan untuk kriteria lainnya. Caranya 

1. tinggal lakukan klik sel yang sudah terisi rumus, 

2. arahkan kursor ke pojok kanan bawah dari sel hingga muncul tanda FILL HANDLE berupa tanda plus (+)

3. Drag and drop (seret dan letakkan) ke bawah sesuai dengan sel yang ingin anda isikan.

Hasilnya seperti di bawah ini. Jika tidak ada data yang sesuai kriteria, hasilnya pun tidak akan ditemukan dalam data itu (contohnya nomor 4 Cahyo)

Sekian trik Excel kali ini. Jika bermanfaat, bantu saya dengan klik tombol share di bawah ini. Thanks in advance :).

Read more »

Cara Membuat Kolom atau Baris Pertama Tetap di Tempatnya (Tidak Geser Saat Discroll)

Dalam Microsoft ada fitur yang berfungsi untuk membuat kolom atau baris terlihat membeku, sehingga kolam atau baris tertentu (biasanya header berupa baris atau data kolom pertama) selalu berada diposisinya meskipun ketika di scroll ke bawah maupun samping. Fitur ini berguna apabila kita punya data panjang ke bawah atau melebar ke samping sehingga memerlukan untuk scroll tetapi header atau kolom pertama tetap berada di tempatnya.

Fitur Excel ini namanya Freeze Panes (membekukan area tertentu dalam sheet).

Biasanya, fitur freeze panes ini digunakan dalam pembuatan daftar suatu hal yang memiliki jumlah banyak, agar ketika melakukan scroll secara vertikal maupun horizontal, header selalu terlihat.

Untuk lebih jelasnya, perhatikan gambar berikut ini:

Secara garis besar, pada fitur freeze panes memiliki fungsi sebagai berikut:
1. membekukan atau mengunci kolom secara bersamaan, baik satu kolom maupun lebih.
2. membekukan atau mengunci kolom secara bersamaan, baik satu baris maupun lebih.
3. membekukan atau mengunci baris pertama saja dalam lembar kerja.

Lantas bagaimana caranya untuk melakukan perintah freeze panes? Berikut tutorial excelnya!
1. dalam lembar kerja aktif, silahkan tentukan kolom atau baris yang ingin dibekukan.
2. Selanjutnya, pilih menu View dan pilih Freeze Panes.


3. pada menu Freeze panes, terdapat tiga pilihan menu.
a. Freeze Panes: untuk melakukan freeze panes pada area tertentu.
b. Freeze Top Row: untuk melakukan freeze panes pada baris paling atas.
c. Freeze first Column: untuk melakukan freeze panes pada kolom pertama.

Sekian tips menggunakan fitur freeze panes dalam micosoft Excel, dan untuk membatalkan perintah Freeze tesebut, anda tinggal melakukan layaknya peintah sebelumnya, yaitu pilihh tab View -> Freeze Panes -> Unfreeze Panes.

selamat mencoba dan semoga bemanfaat, dan jika ada yang tidak dimengerti, silahkan meninggalkan komentar pada kolom di bawah ini.

Read more »

Cara Menjabarkan Kode Barang Menjadi Rincian dengan Rumus Excel

Rumus IF kombinasi dengan LEFT, RIGHT, dan MID – Pada postingan sebelumnya saya sudah menjelaskan tentang macam – macam fungsi string dalam Microsoft Excel. Beberapa fungsi dari string tersebut adalah fungsi LEFT, RIGHT, dan MID. Pada postingan sebelumnya juga sudah saya katakan bahwa fungsi string ini memang tidak cukup penting, tapi akan menjadi penting jika dikombinasikan dengan rumus lainnya, salah satunya adalah jika dikombinasikan dengan fungsi IF. Terutama jika untuk mengerjakan kode-kode, kombinasi dua fungsi ini cukuplah penting.

Pada postingan kali ini, saya akan membahas tentang kombinasi IF dengan fungsi LEFT, RIGHT, dan MID. Pada contoh di bawah, saya akan menjabarkan tiga rincian barang dealer sepeda motor yaitu daerah, merk, dan harga barang berdasarkan kode barang di kolom awal. Untuk lebih jelasnya silahkan simak penjelasan berikut ini.

Menjabarkan Jumlah Karakter Pertama Kiri dari Kode Barang  melalui kombinasi Rumus IF dengan rumus LEFT

Rumus IF dan kombinasi dengan rumus LEFT berarti mengambil suatu teks pada cell lain dimulai dari sebelah kiri dan nantinya akan dihadapkan pada kondisi yang berbeda. Secara syntax kombinasi rumu IF dan LEFT adalah sebagai berikut ini.

=IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi1”;IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi2”))

Rumus di atas digunakan jika ada dua kondisi berbeda.

=IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi1”;IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi2”;IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi3”)))

Rumus di atas digunakan untuk tiga kondisi berbeda.

Anda dapat menambahkan lagi sesuai dengan jumlah kondisi berbeda. Sekarang contohnya adalah sebagai berikut ini. Buka Microsoft Excel pada komputer anda, lalu buatlah data pengiriman sepeda motor sebagai berikut ini.

Sheet latihan rumus IF Excel

Untuk mengisi, cell D5 yang berupa daerah pengiriman, masukkan rumus IF gabungan LEFT berikut ini, lalu anda fill ke bawah dan atur text alignnya maka akan tampak seperti gambar di bawah ini. Rumus:

=IF(LEFT(C5;3)="JTE";"Jawa Tengah";IF(LEFT(C5;3)="JTI";"Jawa Timur";IF(LEFT(C5;3)="BLI";"Bali";IF(LEFT(C5;3)="DKI";"DKI Jakarta";IF(LEFT(C5;3)="DIY";"D I Yogyakarta";IF(LEFT(C5;3)="JBA";"Jawa Barat"))))))

Keterangan :

1. C5 merupakan cell sumber karakter yang akan diambil.
2. 3 merupakan jumlah karakter yang akan diambil.
3. JTE, JTI, BLI, DKI, dan DIY merupakan hasil dari pengambilan karakter.
4. Jawa Tengah, Bali, Jawa Timur, DKI Jakarta, dan D I Yogyakarta merupakan kondisi.

Menjabarkan karakter awal dari kode barang

Menjabarkan Jumlah Karakter di Tengah Kode Barang  melalui kombinasi Rumus IF dengan rumus MID

Untuk contoh dari kombinasi rumus IF dengan MID ini saya masih akan menggunakan contoh di atas, yaitu memunculkan merk sepeda motor. Secara syntax sendiri, kombinasi rumus ini adalah sebagai berikut:

=IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi2”))

Rumus di atas dapat anda gunakan apabila terdapat dua kondisi berbeda.

=IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi2”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;)))

Sedangkan anda dapat menggunakan rumus di atas ini jika terdapat tiga kondisi berbeda. Anda dapat menambahkan lagi rumusnya tergantung dari jumlah kondisi yang berbeda.

Pada contoh, untuk mengisi data pada cell E5, anda copy rumus berikut ini, lalu anda auto fill ke bawah sampai cell E14 dan atur text alignnya, maka anda akan mendapatkan gambar sebagai berikut ini. Rumus:

=IF(MID(C5;4;1)="V";"Vario";IF(MID(C5;4;1)="M";"Megapro";IF(MID(C5;4;1)="S";"Supra";IF(MID(C5;4;1)="B";"Beat";IF(MID(C5;4;1)="C";"CBR")))))

Keterangan :

1. C5 merupakan cell sumber karakter.
2. 4 merupakan posisi awal dari karakter yang akan diambil.
3. 1 merupakan jumlah karakter yang akan diambil.
4. V, M, S, B, dan C merupakan hasil dari pengambilan karakter.
5. Vario, Megapro, Supra, Beat, dan CBR merupakan kondisi.

Menjabarkan karakter tengah kode barang

Menjabarkan Jumlah Karakter Paling Kanan dari Kode Barang  melalui kombinasi Rumus IF dengan rumus RIGHT

Secara syntax kombinasi dari kedua rumus ini sama dengan syntax dari kombinasi rumus IF dengan rumus LEFT, hanya saja kata LEFT dig anti dengan kata RIGHT. Contoh saya masih menggunakan data di atas, yaitu harga sepeda motor. Untuk mengisi cell F5 berdasarkan berdasarkan kode. Masukkan rumus berikut ini lalu tekan enter. Rumus:

=IF(RIGHT(C5;2)="15";15000000;IF(RIGHT(C5;2)="17";17000000;IF(RIGHT(C5;2)="13";13000000;IF(RIGHT(C5;2)="14";14000000;IF(RIGHT(C5;2)="24";24000000;)))))

Keterangan

1. C5 merupakan cell sumber karakter.
2. 2 merupakan jumlah karakter yang akan diambil.
3. 15, 17, 13, 14, dan 24  merupakan hasil dari pengambilan karakter.
4. 15000000, 17000000, 13000000, 14000000, dan 24000000 merupakan kondisi. Saya sengaja tidak menggunakan tanda petik (“…”) untuk mengapit kondisi. Karena kondisi berupa angka. 

Jika sudah ubah General menjadi Currency pada grup Number di  tab Home. Atur text alignnya dan auto fill ke bawah sampai pada cell F14, maka anda akan melihat gambar seperti di bawah ini.

Hasil akhir penjabaran kode pada Microsoft Excel

Demikian hasil akhir penjabaran awal, tengah, akhir kode barang menjadi tiga rincian. Selamat mencoba!

Read more »

Cara Membuat Kalender Sederhana dengan Microsoft Excel

Membuat Kalender Sederhana dengan Microsoft Office Excel – Salah satu fungsi yang terdapat di Microsoft office excel adalah fungsi DATE. Fungsi ini dapat kita manfaatkan untuk membuat kalender sederhana. Walaupun sederhana, dijamin anda akan puas dengan kalender berikut ini. Bagaimana tutorialnya, silahkan simak step by step berikut ini.

Langkah Pertama: Membuat Kalender Satu Bulan

1. Buatlah tulislah sembarang angka acuan rumus yang mewakili bulan yang diwakili (1-12). Misal, ketikkan 2 untuk Februari di sel B4. 
2. Tuliskan tahun acuan yang akan kita setting di sel H4, misalkan saja 2014. 
3. Lalu tuliskan nama-nama hari dimulai dari Minggu sampai Sabtu pada sel B5 sampai H5. Perhatikan gambar berikut untuk lebih jelasnya.
   
   

   

   Data acuan membuat kalender di Excel

4. Blok sel C4 sampai G4, dan gabungkan dengan cara klik Merge & Center. 
5. Jika sudah tuliskan rumus berikut pada kolom tadi!
   
   

   =DATE(H4,B4,1)

   
   
   Jangan heran jika nanti munculnya 01-02-2014. Untuk mengubahnya menjadi nama bulan, masih di kolom tersebut dan tekan Ctrl 1, lalu pilih Number >> Custom >> masukkan [$-21] mmmm pada kolom type. Perlu diketahui [$-21] adalah kode untuk mengubah nama bulan kedalam bahasa Indonesia. Jika anda lakukan dengan benar maka hasilnya akan seperti berikut ini.
   
   
   


6. Selanjutnya, masukkan rumus berikut di bawah hari Minggu (sel B6).
   
   

   =C4-WEEKDAY(C4)+1

   
   
   Maka anda akan meilhat nama bulan Januari. Untuk mengubahnya tekan Ctrl 1, pilih Number >> Custom >> masukkan d pada kolom type, kemudian tekan Enter.
7. Sementara untuk hari Senin anda tinggal menuliskan rumus berikut pada sel C6, yaitu:
   
   

   =B6+1

   
   
   dan untuk hari Selasa sampai dengan Sabtu anda bisa menekan drag drop AutoFill (tanda plus kecil di pojok kanan bawah sel) untuk meng-copy-nya sampai sel H6. Maka hasilnya akan seperti ini.

   

   Tanggal kalender bulan Februari 2014

8. Langkah selanjutnya adalah untuk mengisi tanggal tanggal minggu berikutnya. Pada sel B7 ketikkan rumus:
   
   

   =B6+7

   
   Gunakan AutoFill sampai B10 dan H10 terisi penuh. Hasilnya lihat gambar berikut ini.
   
   

   

   Hasil pembuatan kalender bulan Februari 2014 di Excel

Langkah Kedua: Menghias Tanggal Kalender

Seperti kita lihat pada kalender bulan Februari 2014 yang telah kita buat, di sana ada beberapa tanggal pada bulan Januari dan Maret. Untuk lebih professional, maka kita akan menyamarkannya dan membuatnya berbeda dengan tanggal bulan Februari. Berikut ini caranya:

1. Blok range sel yang akan dimodifikasi, yaitu B6:H10 (dibaca B6 hingga H10). Pada tab Home, pilih grup Styles, pilih Conditional Formatting, klik New Rule, pada Select Rule Type pilih Use formula to determine which cells to format, lalu pada kolom type yang tersedia, ketikkan rumus:
   
   

   =MONTH(B6)<>$B$4



2. Klik tombol Format... untuk menghias dengan warna (Color) yang berbeda, misalkan saja biru dengan efek Italic, klik OK. Lihat gambar jika kurang jelas.

Maka hasilnya akan seperti ini.

Hasil menghias tanggal kalender bulan sebelum dan sesudahnya.

Nah, selesai untuk membuat kalende sederhananya.

Langkah Ketiga: Membuat Kalender Bulan dan Tahun Lainnya

Sekarang kita hanya perlu membuat drop down bulan dan tahun agar kalendernya lebih dinamis dan kita gak perlu buat berulang – ulang. Berikut caranya :

1. Buatlah data acuan berupa list bulan 1-12 pada sell R4:R16. 
2. Lalu kembali lagi, klik sel B4. Lalu klik tab Data,  pada grup Data Tools pilih Data Validation.
3. Maka akan muncul kotak dialog Data Validation. Pada tab Setting, Validation Criteria, Allow, ganti Any Value menjadi List.
4. Pada bagian source, klik tombol Range Selector kemudian pilih range data acuannya, yaitu dengan blok sel R4:R16

   

   Pilih data acuan dengan klik tombol range selector

5. Dan klik OK. Maka anda akan mendapatkan hasil yang seperti ini. 

List bulan dalam 1 tahun

Lakukan hal yang sama untuk tahun, Cuma beda range sel data acuannya saja, misalkan range sel untuk tahun anda buat di sel S5:S11 (untuk 2014-2020). Kini anda tinggal mengeditnya saja agar lebih menarik. Misalkan dimodifikasi untuk seperti ini.

Hiasi kalender yang Anda buat supaya cantik.

Bagaimana, menarik bukan? Sambil menunggu tips Excel selanjutnya, Anda bisa segere mempraktikkan trik sederhana ini.Selamat mencoba! :)

Read more »

Cara Membuat Garis untuk Kolom atau Baris Di Microsoft Excel

Garis pada kolom atau baris merupakan bagian yang sangat penting dalam sebuah tabel. Jika Anda ingin membuat garis pada kolom atau baris, silahkan ikuti langkah-langkah di bawah ini. Jika anda belum membaca penjelasan saya di posting sebelumnya tentang Cara Menyisipkan Kolom atau Baris pada Microsoft Excel, Silahkan anda cari di blog ini. Tapi jika Anda sudah membacanya, mari kita lanjutkan ke topik kita sekarang.

1. Pertama–tama seleksilah terlebih dahulu sel yang akan diberi garis, misalnya sel A1:C7.

2. Berikutnya tekanlah segitiga kecil pada icon borders yang ada di dalam Tab Home bagian Font.

Read more »