Tutorial Microsoft Excel untuk Menengah: Panduan Lengkap dan Praktis
Setelah memahami dasar-dasar Microsoft Excel seperti membuat tabel, menggunakan rumus sederhana, melakukan penjumlahan, filter, dan sorting, langkah berikutnya adalah mempelajari fitur Excel tingkat menengah.
Pada tahap ini, kita mulai menggunakan rumus yang lebih kompleks, pencarian data, pengolahan tanggal, conditional formatting, PivotTable, grafik, hingga pengolahan data otomatis.
Tutorial ini cocok untuk Anda yang sudah memahami dasar Excel tetapi ingin meningkatkan kemampuan agar pekerjaan menjadi lebih cepat dan profesional.
1. Memahami Referensi Cell
Salah satu kemampuan penting pada Excel tingkat menengah adalah memahami referensi cell.
Ada tiga jenis referensi:
Relative Reference
Contoh:
=A2*B2Ketika rumus disalin ke bawah, referensinya akan berubah menjadi:
=A3*B3
=A4*B4
=A5*B5Absolute Reference
Absolute reference menggunakan tanda $.
Contoh:
=A2*$F$1Jika rumus disalin ke bawah, A2 akan berubah menjadi A3, tetapi $F$1 tetap.
Fitur ini sangat berguna ketika kita mempunyai nilai tetap, misalnya:
Pajak
Diskon
Kurs
Persentase komisi
Target penjualan
Mixed Reference
Contoh:
=$A2*B$1Sebagian referensi dikunci dan sebagian dapat berubah.
2. Fungsi IF yang Lebih Kompleks
Pada tingkat menengah, IF dapat digunakan untuk membuat beberapa kondisi.
Contoh:
=IF(B2>=90,"Sangat Baik",IF(B2>=75,"Baik",IF(B2>=60,"Cukup","Kurang")))Hasilnya:
| Nilai | Keterangan |
|---|---|
| 95 | Sangat Baik |
| 80 | Baik |
| 65 | Cukup |
| 50 | Kurang |
Namun jika kondisi semakin banyak, penggunaan IF bertingkat bisa menjadi sulit dibaca.
Untuk kondisi tertentu, kita dapat menggunakan IFS.
Contoh:
=IFS(B2>=90,"Sangat Baik",B2>=75,"Baik",B2>=60,"Cukup",B2<60,"Kurang")3. Menggunakan AND dan OR
Fungsi AND digunakan ketika beberapa kondisi harus terpenuhi.
Contoh:
=IF(AND(B2>=70,C2>=70),"Lulus","Tidak Lulus")Artinya siswa harus mendapatkan nilai minimal 70 pada kedua kolom.
Sedangkan OR digunakan jika cukup salah satu kondisi yang terpenuhi.
=IF(OR(B2>=70,C2>=70),"Lulus","Tidak Lulus")4. SUMIF dan SUMIFS
Jika SUM digunakan untuk menjumlahkan semua data, SUMIF digunakan untuk menjumlahkan data berdasarkan kondisi tertentu.
Contoh data:
| Produk | Kategori | Penjualan |
|---|---|---|
| Buku | ATK | 100000 |
| Pulpen | ATK | 50000 |
| Sepatu | Fashion | 500000 |
| Baju | Fashion | 300000 |
Untuk menghitung total penjualan kategori ATK:
=SUMIF(B2:B5,"ATK",C2:C5)Hasil:
150000SUMIFS
Jika mempunyai beberapa kondisi, gunakan SUMIFS.
Contoh:
=SUMIFS(D2:D100,B2:B100,"Jakarta",C2:C100,"Lunas")Rumus tersebut menjumlahkan nilai pada kolom D dengan dua kondisi:
Kota = Jakarta
Status = Lunas
5. COUNTIF dan COUNTIFS
COUNTIF digunakan untuk menghitung jumlah data berdasarkan kondisi.
Contoh:
=COUNTIF(B2:B100,"Lunas")Untuk menghitung berapa transaksi yang berstatus Lunas.
Sedangkan:
=COUNTIFS(B2:B100,"Jakarta",C2:C100,"Lunas")digunakan untuk menghitung data dengan dua kondisi sekaligus.
6. VLOOKUP
VLOOKUP merupakan salah satu fungsi Excel yang sangat populer untuk mencari data.
Misalnya kita mempunyai tabel:
| Kode | Produk | Harga |
|---|---|---|
| P001 | Buku | 10000 |
| P002 | Pulpen | 5000 |
| P003 | Pensil | 3000 |
Jika cell A10 berisi P002, kita dapat mencari nama produknya:
=VLOOKUP(A10,A2:C4,2,FALSE)Hasilnya:
PulpenAngka 2 menunjukkan bahwa Excel mengambil data dari kolom kedua.
7. XLOOKUP
Jika versi Excel yang digunakan mendukung XLOOKUP, fungsi ini biasanya lebih fleksibel daripada VLOOKUP.
Contoh:
=XLOOKUP(A10,A2:A4,B2:B4,"Tidak ditemukan")Excel akan mencari kode pada A10 di kolom A dan mengambil nama produk dari kolom B.
Jika data tidak ditemukan, hasilnya:
Tidak ditemukan8. INDEX dan MATCH
Kombinasi INDEX dan MATCH merupakan teknik pencarian data yang sangat berguna.
Contoh:
=INDEX(B2:B10,MATCH(E2,A2:A10,0))MATCH mencari posisi data, sedangkan INDEX mengambil nilai dari posisi tersebut.
Teknik ini berguna terutama ketika struktur tabel tidak cocok untuk VLOOKUP.
9. IFERROR
Terkadang rumus menghasilkan error seperti:
#N/A
#VALUE!
#DIV/0!Kita dapat menggunakan IFERROR agar hasilnya lebih rapi.
Contoh:
=IFERROR(VLOOKUP(A2,D2:F100,2,FALSE),"Data tidak ditemukan")Jika data tidak ditemukan, Excel tidak menampilkan #N/A, tetapi:
Data tidak ditemukan10. Mengolah Data Tanggal
Excel dapat digunakan untuk melakukan berbagai perhitungan tanggal.
Contoh:
=TODAY()Untuk menampilkan tanggal hari ini.
=NOW()Untuk menampilkan tanggal dan waktu saat ini.
Untuk menghitung selisih hari:
=B2-A2Misalnya:
| Tanggal Mulai | Tanggal Selesai |
|---|---|
| 01/10/2026 | 10/10/2026 |
Hasil:
911. Menggunakan DATEDIF
DATEDIF dapat digunakan untuk menghitung selisih tanggal dalam tahun, bulan, atau hari.
Contoh:
=DATEDIF(A2,B2,"Y")Menghitung selisih dalam tahun.
=DATEDIF(A2,B2,"M")Menghitung selisih dalam bulan.
=DATEDIF(A2,B2,"D")Menghitung selisih dalam hari.
12. Menggunakan Conditional Formatting
Conditional Formatting digunakan untuk memberikan format otomatis berdasarkan kondisi.
Contohnya kita ingin memberikan warna pada nilai di bawah 70.
Caranya:
Blok data nilai.
Pilih Home.
Pilih Conditional Formatting.
Pilih Highlight Cells Rules.
Pilih Less Than.
Masukkan
70.Pilih format yang diinginkan.
Excel akan otomatis menandai nilai yang kurang dari 70.
Conditional Formatting juga dapat digunakan untuk:
Menandai penjualan rendah.
Menandai stok hampir habis.
Menandai tanggal jatuh tempo.
Menandai nilai tertinggi.
Membuat indikator performa.
13. Data Validation
Data Validation digunakan untuk membatasi data yang boleh dimasukkan.
Contohnya kita ingin membuat pilihan:
Lunas
Belum LunasCaranya:
Pilih cell.
Klik Data.
Pilih Data Validation.
Pilih List.
Masukkan:
Lunas,Belum LunasSekarang cell tersebut memiliki menu dropdown.
Fitur ini sangat berguna untuk membuat formulir Excel yang rapi dan mengurangi kesalahan input.
14. Membuat Dropdown Dinamis
Dropdown dapat digunakan untuk memilih:
Nama karyawan
Nama produk
Kota
Status transaksi
Departemen
Kategori
Dengan menggunakan sumber data yang terstruktur, dropdown dapat dibuat lebih mudah diperbarui ketika daftar bertambah.
15. PivotTable
PivotTable adalah salah satu fitur terpenting pada Excel tingkat menengah.
PivotTable digunakan untuk menganalisis data dalam jumlah besar dengan cepat.
Misalnya kita memiliki ribuan transaksi:
| Tanggal | Sales | Produk | Kota | Penjualan |
|---|---|---|---|---|
| 01/10/2026 | Andi | Buku | Jakarta | 100000 |
| 01/10/2026 | Budi | Pulpen | Bandung | 50000 |
| 02/10/2026 | Andi | Buku | Jakarta | 150000 |
Dengan PivotTable kita dapat mengetahui:
Total penjualan per sales.
Total penjualan per kota.
Total penjualan per produk.
Penjualan per bulan.
Cara membuatnya:
Klik salah satu cell pada data.
Pilih Insert.
Klik PivotTable.
Pilih lokasi PivotTable.
Klik OK.
Kemudian susun field ke bagian:
Rows
Columns
Values
Filters
16. Membuat Grafik dari Data
Grafik membantu membuat data lebih mudah dipahami.
Jenis grafik yang sering digunakan:
Column Chart
Cocok untuk membandingkan penjualan.
Line Chart
Cocok untuk melihat perkembangan dari waktu ke waktu.
Pie Chart
Cocok untuk melihat komposisi atau persentase.
Bar Chart
Cocok untuk membandingkan banyak kategori.
Cara membuat grafik:
Blok data.
Pilih Insert.
Pilih jenis Chart.
Atur judul dan tampilan grafik.
17. Membuat Dashboard Sederhana
Dengan menggabungkan beberapa fitur Excel, kita dapat membuat dashboard.
Contohnya dashboard penjualan:
TOTAL PENJUALAN
Rp125.000.000
TOTAL TRANSAKSI
1.250
PRODUK TERLARIS
Produk A
SALES TERBAIK
Andi
Dashboard dapat dilengkapi dengan:
PivotTable
PivotChart
Slicer
Conditional Formatting
Rumus
Dropdown
Dengan dashboard, laporan yang kompleks dapat ditampilkan dalam bentuk yang lebih mudah dipahami.
18. Menggunakan Slicer
Slicer digunakan untuk melakukan filter data secara visual, terutama pada PivotTable.
Misalnya kita memiliki laporan penjualan dan ingin melihat:
Jakarta
Bandung
Semarang
Dengan Slicer, pengguna cukup mengklik kota yang diinginkan tanpa harus membuka menu filter.
Slicer sangat berguna untuk membuat dashboard interaktif.
19. Mengubah Data Menjadi Excel Table
Tekan:
Ctrl + Tuntuk mengubah data menjadi Table.
Keuntungan menggunakan Table:
Filter otomatis.
Format otomatis.
Rumus dapat mengikuti penambahan data.
Referensi data lebih mudah.
PivotTable lebih mudah diperbarui.
Contohnya:
=SUM(Table1[Penjualan])Rumus tersebut menjumlahkan seluruh kolom Penjualan dalam Table.
20. Menggunakan Named Range
Named Range memungkinkan kita memberikan nama pada range.
Misalnya:
A2:A100diberi nama:
DaftarProdukKemudian kita dapat menggunakan:
=COUNTA(DaftarProduk)Named Range dapat membuat rumus lebih mudah dibaca dan dikelola.
21. Menghapus Data Duplikat
Jika terdapat data yang sama berulang kali, gunakan fitur Remove Duplicates.
Caranya:
Blok data.
Pilih Data.
Pilih Remove Duplicates.
Pilih kolom yang ingin diperiksa.
Klik OK.
Excel akan menghapus data yang terduplikasi berdasarkan kolom yang dipilih.
22. Text to Columns
Fitur ini digunakan untuk memisahkan data dalam satu kolom menjadi beberapa kolom.
Contoh:
Andi,Jakarta,SalesDapat dipisahkan menjadi:
| Nama | Kota | Jabatan |
|---|---|---|
| Andi | Jakarta | Sales |
Caranya:
Data → Text to Columns
Kemudian pilih pemisah seperti:
Comma
Space
Tab
Semicolon
23. Fungsi LEFT, RIGHT, dan MID
Fungsi ini digunakan untuk mengambil sebagian teks.
LEFT
Mengambil karakter dari sebelah kiri.
=LEFT(A2,3)RIGHT
Mengambil karakter dari sebelah kanan.
=RIGHT(A2,3)MID
Mengambil karakter dari bagian tengah.
=MID(A2,2,5)Fungsi ini sangat berguna untuk mengolah kode produk, nomor dokumen, dan data administrasi.
24. Menggabungkan Teks
Untuk menggabungkan beberapa teks, kita dapat menggunakan operator &.
Contoh:
=A2&" - "&B2Jika A2 berisi:
Andidan B2:
Saleshasilnya:
Andi - SalesKita juga dapat menggunakan TEXTJOIN:
=TEXTJOIN(" - ",TRUE,A2:B2)25. Fungsi TRIM
Kadang data hasil copy dari sistem lain memiliki spasi berlebihan.
Contoh:
" Andi "Gunakan:
=TRIM(A2)Hasil:
AndiIni sangat berguna ketika melakukan pencarian data karena spasi tambahan dapat menyebabkan data terlihat sama tetapi tidak dianggap sama oleh Excel.
26. Mengunci Cell dan Worksheet
Jika file Excel digunakan oleh banyak orang, kita dapat melindungi worksheet.
Caranya:
Review → Protect Sheet
Kita dapat memberikan password agar pengguna tidak sembarangan mengubah rumus atau struktur laporan.
27. Freeze Panes
Jika tabel memiliki banyak baris, kita dapat mengunci baris judul agar tetap terlihat ketika melakukan scroll.
Caranya:
View → Freeze Panes
Fitur ini sangat berguna untuk tabel dengan ratusan atau ribuan baris.
28. Shortcut Excel Tingkat Menengah
Beberapa shortcut yang sebaiknya dikuasai:
| Shortcut | Fungsi |
|---|---|
| Ctrl + T | Membuat Table |
| Ctrl + Shift + L | Filter |
| Ctrl + Arrow | Lompat ke ujung data |
| Ctrl + Shift + Arrow | Memblok data |
| Ctrl + 1 | Format Cells |
| F4 | Mengunci referensi $ saat mengetik rumus |
| Alt + = | AutoSum |
| Ctrl + ; | Memasukkan tanggal |
| Ctrl + Shift + : | Memasukkan waktu |
| Ctrl + Page Up | Pindah worksheet sebelumnya |
| Ctrl + Page Down | Pindah worksheet berikutnya |
29. Contoh Proyek Excel Tingkat Menengah
Untuk menguji kemampuan, buatlah sebuah Laporan Penjualan.
Buat kolom:
| Tanggal | Sales | Produk | Kota | Harga | Qty | Total | Status |
|---|
Kemudian gunakan:
Total transaksi
=E2*F2Status transaksi
=IF(G2>=500000,"Besar","Kecil")Total seluruh penjualan
=SUM(G2:G100)Total penjualan Jakarta
=SUMIF(D2:D100,"Jakarta",G2:G100)Jumlah transaksi besar
=COUNTIF(H2:H100,"Besar")Kemudian buat:
PivotTable
Grafik penjualan
Slicer kota
Dashboard sederhana
Dengan satu proyek tersebut, banyak kemampuan Excel tingkat menengah dapat langsung dipraktikkan.
30. Setelah Menguasai Excel Menengah, Belajar Apa?
Jika sudah menguasai materi di atas, Anda dapat melanjutkan ke Excel tingkat lanjut.
Materi berikutnya antara lain:
XLOOKUP tingkat lanjut
INDEX + MATCH
Dynamic Array
FILTER
SORT
UNIQUE
LET
SUMPRODUCT
PivotTable tingkat lanjut
Power Query
Power Pivot
Data Model
Dashboard profesional
Macro VBA
Automasi pekerjaan Excel
Kesimpulan
Excel tingkat menengah bukan lagi sekadar memasukkan data dan menghitung angka. Pada tahap ini, Excel mulai digunakan sebagai alat analisis dan pembuatan laporan.
Kemampuan seperti SUMIFS, COUNTIFS, XLOOKUP, IFERROR, PivotTable, Conditional Formatting, Data Validation, Slicer, dan Dashboard akan sangat membantu pekerjaan administrasi, keuangan, penjualan, gudang, maupun pengolahan data.
Yang paling penting adalah jangan hanya menghafalkan rumus. Pahami kapan dan mengapa sebuah fungsi digunakan.
Semakin sering mengerjakan data nyata, semakin cepat kemampuan Excel meningkat.
0 Comments:
Posting Komentar