Semua formula & shortcut yang dah dikongsi, setiap satu ada contoh guna kat mana. Rujuk bila-bila, ikut turutan atau lompat terus ke bahagian yang perlu.
Guna bila: nak gabung nama, extract sebahagian text, format nombor telefon — tanpa formula.
Ctrl + E
Taip satu contoh dulu, Excel sambung baki sendiri berdasarkan pattern tu.
Ctrl + Shift + L on/off filter
Ctrl + ; masukkan tarikh hari ini
Ctrl + D copy formula/value ke bawah
Alt + = auto-jumlah (AutoSum)
Guna bila: data ada beribu baris, nak lompat cepat tanpa scroll.
Ctrl + End pergi ke cell terakhir yang ada data
Ctrl + Arrow lompat ke hujung block data
Ctrl + Page Up/Down tukar sheet
F5 pergi ke cell tertentu terus
Ctrl + Home balik ke cell pertama sheet
Laptop dengan keyboard function key digabung mungkin perlu tambah Fn (contoh Fn+Ctrl+Home).
Guna bila: nak jumlah, purata, atau cari nilai tertinggi/terendah dalam satu senarai.
=SUM(range) jumlahkan nombor
=AVERAGE(range) kira purata
=MAX(range) nilai tertinggi
=MIN(range) nilai terendah
Guna bila: nak kira/jumlah, tapi hanya untuk data yang match SATU syarat.
=COUNTIF(A:A,"Kedah")Kira berapa banyak baris yang match syarat.
=SUMIF(A:A,"Kedah",B:B)Jumlahkan value column B, tapi hanya untuk baris yang match syarat.
Guna bila: nak Excel buat keputusan automatik antara dua pilihan.
=IF(markah>=50,"Lulus","Gagal")Excel check syarat, letak result pertama kalau betul, result kedua kalau tak.
Guna bila: keputusan bergantung pada DUA benda yang kena betul sekali.
=IF(AND(markah>=50,kehadiran>=80),"Lulus","Gagal")Dua-dua syarat kena betul untuk dapat result pertama. Kalau salah satu je cukup dah memadai, ganti AND dengan OR.
Guna bila: cukup satu daripada beberapa syarat betul untuk dapat keputusan.
=IF(OR(B2<5,C2<5),"Tambah stok","OK")Contoh: stok kedai A di B, stok kedai B di C. Kalau mana-mana kurang 5, tanda "Tambah stok".
Cell kosong dikira sebagai 0. Pastikan semua cell yang disemak ada nombor.
Guna bila: nak buat grade/kategori dengan lebih dari dua pilihan.
=IFS(markah>=80,"A",markah>=60,"B",markah>=40,"C",TRUE,"D")Excel cek dari tinggi ke rendah, sampai jumpa syarat yang betul. TRUE di hujung tu macam kata "yang lain-lain semua".
Guna bila: ada senarai panjang, nak cari satu maklumat tanpa scroll manual.
=VLOOKUP("Ahmad", A:B, 2, FALSE)1. "Ahmad" — nama yang nak dicari
2. A:B — jadual data (column A = nama, column B = markah)
3. 2 — ambil result dari column ke-2 dalam range tu
4. FALSE — cari padanan tepat
Keluar #N/A biasanya sebab nama tak wujud, atau ada space extra yang tak nampak. Besar/kecil huruf tak jadi masalah.
Had: VLOOKUP cuma boleh cari dari column PALING KIRI dalam range yang dipilih.
Guna bila: nak cari maklumat, tapi data susunan tak "kiri ke kanan" macam VLOOKUP perlukan. (Perlu Excel 365/2021 ke atas.)
=XLOOKUP("Ahmad", A:A, B:B)Boleh cari ke kiri ATAU kanan. Tak payah kira column number macam VLOOKUP.
=XLOOKUP("Ahmad", A:A, B:B, "-")Boleh terus letak apa nak keluar kalau tak jumpa — tak perlu formula tambahan.
Guna bila: Excel anda tiada XLOOKUP, atau data yang dicari berada di sebelah kiri kunci carian.
=INDEX(A2:A6,MATCH(F1,B2:B6,0))Contoh: nama produk di A, kod di B, kod yang dicari di F1. MATCH cari kedudukan kod dalam B. INDEX ambil nama pada kedudukan sama dari A. Tukar A2:A6 kepada C2:C6 untuk dapat harga.
Angka 0 wajib, supaya Excel cari padanan tepat.
Guna bila: SUMIF punya satu syarat tak cukup, perlu combine dua atau lebih.
=SUMIFS(C2:C7, A2:A7, "Terengganu", B2:B7, "Mac")C2:C7 = column nak dijumlah. Pasangan seterusnya (column + syarat) boleh tambah ikut keperluan.
Guna bila: nak kira BILANGAN (bukan jumlah RM) yang match beberapa syarat sekali.
=COUNTIFS(A2:A7, "Terengganu", C2:C7, ">1000")Rule simple: satu syarat guna SUMIF/COUNTIF. Dua syarat atau lebih, tambah "S" — SUMIFS/COUNTIFS.
Guna bila: nak nampak terus data penting tanpa check manual satu-satu.
Block data → Home tab → Conditional Formatting → pilih syarat & warna.
Set dua rule serentak: bawah target jadi merah, atas target jadi hijau. Guna "New Rule" dua kali — jangan lupa klik tab "Fill" untuk pilih warna.
Home tab → Conditional Formatting → Icon Sets → pilih style (traffic light, panah, dsb). Excel letak icon automatik ikut tinggi-rendah value.
Guna bila: buat invois, kira harga, atau laporan kewangan — elak nombor panjang tak kemas.
=ROUND(A2,2)Bundarkan kepada 2 decimal (contoh: 45.6789999 jadi 45.68).
Guna bila: perlukan sentiasa bundar naik ATAU sentiasa bundar turun, bukan ikut rule biasa.
=ROUNDUP(A2,0)Sentiasa bundar naik — berguna bila keperluan tak boleh pecahan (bilangan kotak, bilangan orang).
=ROUNDDOWN(A2,0)Sentiasa bundar turun — untuk situasi yang perlukan estimate conservative.
Guna bila: nak extract tarikh lahir dari No. IC, atau apa-apa yang berada di depan.
=LEFT(A2,6)Contoh: dari "990101-14-5566", ambil 6 aksara pertama → "990101".
Guna bila: nak ambil beberapa digit terakhir (contoh: nombor telefon untuk verify/mask).
=RIGHT(A2,4)Contoh: dari "012-3456789", ambil 4 aksara terakhir → "6789".
Guna bila: maklumat yang nak ambil berada di tengah-tengah text (contoh: kod negeri dalam No. IC).
=MID(A2,8,2)Mula dari posisi ke-8, ambil 2 aksara. Contoh: dari "990101-14-5566" → "14".
Guna bila: data tak konsisten — ada huruf besar, kecil, campur — nak seragamkan.
=UPPER(A2)Tukar semua jadi huruf besar.
=LOWER(A2)Tukar semua jadi huruf kecil.
=PROPER(A2)Huruf besar di awal setiap perkataan (contoh: "ahmad zulkifli" jadi "Ahmad Zulkifli").
Guna bila: ada space extra yang tak nampak dalam data — selalu punca #N/A dalam VLOOKUP/XLOOKUP.
=TRIM(A2)Buang semua space berlebihan (depan, belakang, atau berulang di tengah) secara automatik — data jadi kemas & konsisten untuk carian formula lain.
Guna bila: ada beratus/beribu baris data, nak ringkaskan jadi jadual summary tanpa formula panjang.
1. Klik mana-mana cell dalam data
2. Insert tab → PivotTable → OK
3. Dalam panel "PivotTable Fields" (sebelah kanan), drag field ke:
Rows — kategori nak breakdown (contoh: Negeri)
Values — nombor nak dikira (contoh: Jualan)
Boleh tambah lebih dari satu field ke Rows (contoh: Negeri + Produk) untuk breakdown lebih detail. Klik simbol (-)/(+) di sebelah kategori untuk collapse/expand.
Guna bila: bukan setakat nak jumlah, tapi nak bilangan atau purata.
Klik kanan pada angka dalam Pivot Table → "Value Field Settings" → pilih ikut nak (Sum, Count, Average).
Guna bila: nak bandingkan sumbangan setiap kategori berbanding jumlah keseluruhan (contoh: "Kedah = 16% dari total jualan").
Klik kanan angka → Value Field Settings → Show Values As → % of Grand Total.
Guna bila: dah tambah data baru, tapi angka dalam Pivot Table tak berubah.
Pivot Table memang tak automatik "sedar" data baru — bukan rosak. Klik kanan dalam Pivot Table → Refresh. Kalau data baru di luar range asal, guna "Change Data Source" pula untuk update range.
Guna bila: nombor telefon, harga atau No. IC ada tanda yang mengganggu.
=SUBSTITUTE(A2,"-","")Contoh: "012-3456789" jadi "0123456789". Maksudnya: tukar "-" kepada kosong.
=VALUE(SUBSTITUTE(A2,"RM",""))Buang "RM" dari harga supaya jadi nombor yang boleh dijumlah. "RM25" jadi 25.
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))Kira berapa kali huruf "a" muncul. "banana" keluar 3.
Guna bila: SUM keluar 0 walaupun nombor jelas ada.
=VALUE(A2)Tanda nombor jadi teks: ia rapat ke kiri dalam cell. Selalu berlaku bila data dicopy dari web, sistem atau WhatsApp. VALUE tukar teks kepada nombor sebenar.
Guna bila: formula keluar #N/A atau #DIV/0! dan nak tukar kepada teks yang lebih mesra.
=IFERROR(VLOOKUP("Ahmad",A:B,2,FALSE),"Tiada")Kalau formula berjaya, hasil asal keluar. Kalau error, perkataan "Tiada" keluar.
Guna bila: senarai nama atau tempahan ada baris yang sama.
Pilih data → Data tab → Remove Duplicates → pilih column yang mahu disemak → OK.
Simpan salinan data asal dahulu. Baris yang dibuang tidak boleh dikembalikan selepas fail ditutup.
Guna bila: nama penuh, alamat atau data bercampur dalam satu cell, dipisahkan koma atau ruang.
Pilih column → Data tab → Text to Columns → Delimited → Next → pilih pemisah (Comma, Space atau Other) → Finish.
Pastikan column di sebelah kanan kosong, kerana hasilnya mengisi column itu.
Guna bila: nak simpan hasil formula sebagai nombor tetap, atau hantar fail tanpa formula.
Copy → klik kanan di tempat tampal → Paste Options → ikon "Values" (123).
Guna bila: nak elak salah taip, contohnya status bayaran hanya "Sudah Bayar" atau "Belum Bayar".
Pilih cell → Data tab → Data Validation → Allow: List → Source: taip pilihan dipisahkan koma → OK.
Guna bila: formula betul di baris pertama, tapi salah bila di-copy ke bawah.
=A2*(1+$D$1)Harga modal di A2, kadar untung di D1. Tanda $ kunci D1 supaya tidak bergerak bila formula ditarik ke bawah. Tekan F4 selepas klik D1 dalam formula (laptop tertentu Fn+F4).
Guna bila: nak kira baris yang mengandungi perkataan tertentu, walaupun ayat penuh berbeza.
=COUNTIF(A:A,"*Panadol*")Tanda * bermaksud apa-apa pun. "Vitamin*" kira yang bermula dengan Vitamin. SUMIF pun boleh guna cara sama.
Guna bila: nak tahu senarai nama atau kategori unik daripada data yang berulang. (Perlu Excel 365/2021 ke atas.)
=UNIQUE(A2:A100)Keluar #SPILL! kalau cell di bawah formula tidak kosong. Kosongkan cell itu.
Guna bila: nak senarai berasingan, contohnya yang belum bayar, tanpa kacau data asal. (Perlu Excel 365/2021 ke atas.)
=FILTER(A2:B10,B2:B10="Belum Bayar","Tiada")Senarai ikut berubah sendiri bila status berubah. Perkataan "Tiada" keluar bila tiada baris yang padan.
Guna bila: nak tarikh akhir bulan untuk tarikh akhir bayaran atau laporan bulanan.
=EOMONTH(A2,0)Tarikh 15/10/2026 di A2 beri 31/10/2026. Tukar 0 kepada 1 untuk hujung bulan depan. Kalau keluar nombor besar seperti 46326, tukar format cell kepada Date.
Guna bila: nak tarikh sama bulan depan, contohnya tarikh bayaran ansuran atau tarikh sambung langganan.
=EDATE(A2,1)Tarikh 15/10/2026 beri 15/11/2026. Taip -1 untuk undur sebulan. Tukar format cell kepada Date.
Guna bila: nak tahu umur, tempoh bekerja atau tempoh sewa dalam tahun.
=DATEDIF(A2,B2,"Y")Mula 1/1/2020 di A2, tamat 15/10/2026 di B2 beri 6 tahun. Tukar "Y" kepada "M" untuk bulan atau "D" untuk hari.
Guna bila: nak ringkasan ikut bulan dalam laporan.
=TEXT(A2,"mmmm")Tarikh 15/10/2026 beri nama bulan penuh. Bahasa nama bulan ikut bahasa Excel kamu.
Guna bila: nak jumlah jualan tanpa buat lajur tambahan untuk setiap baris.
=SUMPRODUCT(A2:A4,B2:B4)Harga 10, 20, 5 di A2 hingga A4 dan kuantiti 2, 3, 4 di B2 hingga B4 beri 100 (20 + 60 + 20).
Guna bila: nak tahu jualan ketiga tertinggi atau kedudukan seseorang.
=LARGE(A2:A6,3)Markah 50, 82, 67, 91, 75 beri 75, iaitu yang ketiga terbesar.
=RANK(A2,$A$2:$A$6)Markah 50 di A2 beri kedudukan 5, iaitu yang paling bawah. Tanda $ kunci julat supaya tidak bergerak bila ditarik ke bawah.
Guna bila: nak purata jualan seorang sahaja daripada senarai bercampur.
=AVERAGEIF(A2:A5,"Ali",B2:B5)Ali jual 100 dan 300, Siti jual 200 dan 400. Hasil untuk Ali ialah 200.
Guna bila: harga mesti berakhir dengan 5 atau 10 sen, atau kuantiti dalam gandaan tertentu.
=MROUND(A2,5)Nombor 23 beri 25.
Guna bila: nombor 1, 2, 3 berterabur bila padam baris.
=ROW()-1Taip di A2, kemudian tarik ke bawah. Hasil 1, 2, 3 dan seterusnya. Padam satu baris, nombor di bawah kemas semula.
Guna bila: borang atau jadual diisi sedikit demi sedikit.
=IF(A2="","",A2*B2)Harga 5 dan kuantiti 10 beri 50. Kalau A2 kosong, C2 pun kosong, bukan 0.
Guna bila: baki hutang atau stok tak patut jadi negatif.
=MAX(0,A2-B2)Hutang 100 bayar 30 beri 70. Hutang 20 bayar 50 beri 0, bukan -30.
Guna bila: nak tahu setiap jualan sumbang berapa peratus daripada jumlah.
=A2/SUM($A$2:$A$4)Jualan 200, 300, 500 beri 0.2, 0.3, 0.5. Klik lajur dan tekan butang % di tab Home untuk jadi 20%, 30%, 50%.
Guna bila: nak satu cell berisi dua maklumat.
=D2&" - "&E2Nama Ali dan jabatan Akaun beri Ali - Akaun. Letak apa sahaja di antara tanda petik, contohnya koma atau ruang.
Nak practice sendiri? Muat turun dataset jualan (300 baris) yang digunakan sepanjang siri Pivot Table — cuba buat Pivot Table sendiri, ikut panduan di atas.
Muat Turun Dataset (.xlsx)