Rumus Excel Lengkap dengan Sheet yang Bisa Diedit
Rumus dan fungsi Excel dijelaskan di sheet yang bisa kamu edit: VLOOKUP, XLOOKUP, IF, SUMIF, COUNTIF, tanggal, teks, dan dynamic array. Ubah angka atau rumus, dan sheet langsung menghitung ulang di browser-mu.
Mulai kursusDasar Rumus
- SUMKetik =SUM(B2:B6) di bawah sebuah kolom angka untuk menjumlahkannya, atau tekan Alt+= supaya AutoSum menuliskan rumusnya. Jumlahkan baris, sel yang terpisah dan sheet lain, di sheet interaktif yang bisa kamu edit.
- PenguranganExcel tidak punya fungsi SUBTRACT: ketik =B2-C2 untuk mengurangi satu sel dengan sel lain. Kurangi satu kolom penuh, beberapa sel sekaligus, persentase atau tanggal, di sheet interaktif yang bisa kamu edit.
- Perkalian dan pembagianUntuk mengalikan di Excel, pakai tanda bintang, =B2*C2, dan untuk membagi, pakai garis miring, =B2/C2. Kalikan satu kolom dengan satu angka, pakai PRODUCT, dan cegah error #DIV/0!, di sheet interaktif yang bisa kamu edit.
- AVERAGE=AVERAGE(B2:B7) menjumlahkan angka-angka di B2:B7 lalu membaginya dengan banyaknya angka. Pelajari bagaimana sel kosong dan nol mengubah hasilnya, cara mengabaikan nol, dan cara menghitung rata-rata 3 nilai tertinggi.
- COUNT dan COUNTA=COUNT(B2:B8) menghitung sel yang berisi angka, =COUNTA(B2:B8) menghitung setiap sel yang tidak kosong, dan =COUNTBLANK(B2:B8) menghitung sel yang kosong. Lihat ketiganya di lembar kerja yang bisa kamu edit.
- Referensi AbsolutReferensi absolut seperti $E$1 tidak berubah saat kamu menyalin rumus, sedangkan referensi relatif seperti E1 ikut bergeser. Tekan F4 untuk menambahkan tanda dolar. Lihat bedanya di lembar kerja yang bisa kamu edit.
- PersentaseRumus persentase di Excel adalah =bagian/total, misalnya =B2/C2, dengan sel berformat persentase. Persentase dari total, persentase dari sebuah angka, menambah atau mengurangi persen, di sheet interaktif.
- Perubahan persenRumus perubahan persen di Excel adalah =(new-old)/old, misalnya =(C2-B2)/B2, dengan format persentase. Hasil negatif berarti penurunan. Sheet interaktif membahas perubahan bulan ke bulan, nilai awal nol dan poin persentase.
Logika
- IF=IF(B2>=50,"Pass","Fail") mengecek apakah B2 bernilai 50 atau lebih, lalu menghasilkan Pass kalau benar dan Fail kalau tidak. Pelajari sintaks IF, IF dengan teks, IF dengan perhitungan, IF untuk sel kosong, dan kesalahan yang membuat IF memberikan hasil yang salah.
- IF bertingkat=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) menaruh satu IF di dalam IF lain untuk memilih di antara lebih dari dua hasil. Pelajari cara membaca IF bertingkat, kenapa urutan kondisinya penting, dan kapan IFS atau tabel lookup lebih tepat.
- IFS=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") menguji setiap kondisi secara berurutan dan menghasilkan nilai yang dipasangkan dengan kondisi pertama yang bernilai TRUE. Pelajari sintaks IFS, TRUE sebagai nilai bawaan, kenapa IFS menghasilkan #N/A, dan perbandingannya dengan IF bertingkat.
- AND, OR, NOT=AND(B2>=10,B2<=20) menghasilkan TRUE hanya jika semua kondisi benar, dan =OR(B2="North",B2="South") menghasilkan TRUE jika minimal satu kondisi benar. Pelajari AND, OR, NOT dan XOR, baik sendiri maupun di dalam IF, cara mengecek apakah angka ada di antara dua nilai, dan cara menulis AND dan OR di rumus array.
- IFERROR=IFERROR(B2/C2,0) menghasilkan B2/C2, atau 0 kalau pembagiannya menghasilkan error. Pelajari IFERROR dengan VLOOKUP, cara menghasilkan sel kosong sebagai ganti error, kenapa IFNA lebih tepat untuk pencarian data, dan kenapa menyembunyikan semua error bisa menyembunyikan kesalahan yang sebenarnya.
- SWITCH=SWITCH(B2,"N","North","S","South","Unknown") membandingkan B2 dengan setiap nilai secara berurutan dan mengembalikan hasil yang dipasangkan dengan kecocokan persis pertama, atau Unknown jika tidak ada yang cocok. Pelajari sintaks SWITCH, nilai default, pola SWITCH(TRUE,...) dan kapan sebaiknya memakai IFS atau IF bertingkat.
- ISBLANK, ISNUMBER=ISBLANK(B2) menghasilkan TRUE saat B2 kosong, dan =ISNUMBER(B2) menghasilkan TRUE saat B2 berisi angka. Pelajari ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, ISEVEN, dan ISODD, kenapa rumus yang menghasilkan "" tidak dianggap kosong, dan bagaimana ISNUMBER(SEARCH()) mengecek apakah sebuah sel mengandung teks.
Pencarian Data
- VLOOKUP=VLOOKUP(F2,A2:D6,3,FALSE) mencari F2 di kolom pertama A2:D6 dan mengembalikan nilai dari kolom ketiga di baris yang sama. Pencocokan persis dan perkiraan, cara mengatasi #N/A, beda sheet, dua kriteria.
- XLOOKUP=XLOOKUP(F2,A2:A6,C2:C6) mencari F2 di A2:A6 dan mengembalikan nilai di baris yang sama dari C2:C6. Teks jika tidak ditemukan, beberapa kolom sekaligus, pencarian ke kiri, kecocokan terakhir, pencocokan perkiraan dan wildcard.
- INDEX MATCH=INDEX(C2:C6,MATCH(F2,A2:A6,0)) mencari baris F2 di kolom A lalu menghasilkan nilai dari baris itu di kolom C. Rumus ini bisa mencari ke kiri, melakukan pencarian dua arah, dan berfungsi di semua versi Excel.
- INDEX=INDEX(A2:C6,3,2) menghasilkan nilai di baris ketiga dan kolom kedua dari A2:C6. Pakai fungsi ini untuk item ke-n sebuah daftar, satu baris atau kolom penuh, dan nilai di posisi yang ditemukan MATCH.
- MATCH=MATCH(E2,A2:A6,0) mengembalikan posisi E2 di A2:A6: 4 kalau nilai itu item keempat. Tipe pencocokan 0, 1 dan -1, wildcard, pencocokan yang membedakan huruf besar kecil, dan cara mengecek apakah sebuah nilai ada di daftar.
- HLOOKUP=HLOOKUP("Mar",A1:E3,2,FALSE) mencari Mar di baris pertama A1:E3 lalu menghasilkan nilai dari baris kedua di kolom yang sama. Pencocokan persis dan perkiraan, serta kapan XLOOKUP menjadi pilihan yang lebih baik.
- XMATCH=XMATCH(E2,A2:A6) mengembalikan posisi E2 di A2:A6, dengan pencocokan persis sebagai default. XMATCH juga bisa menemukan nilai lebih kecil atau lebih besar berikutnya tanpa pengurutan, mencari dari bawah, dan memakai wildcard.
- Lookup banyak kriteria=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) mengembalikan nilai dari baris tempat kolom A cocok dengan E2 dan kolom B cocok dengan F2. Ada juga versi INDEX MATCH, kolom bantu untuk VLOOKUP, dan FILTER untuk semua hasil yang cocok.
- VLOOKUP vs XLOOKUPXLOOKUP bisa melakukan semua yang dilakukan VLOOKUP, dengan pencocokan persis sebagai default, tanpa nomor kolom, bisa mencari ke kiri dan punya argumen untuk data yang tidak ditemukan. VLOOKUP tetap jadi pilihan jika file-nya harus bisa dibuka di Excel 2019 atau yang lebih lama.
- INDIRECT=INDIRECT("C"&E2) membaca sel yang alamatnya disusun sebagai teks: kolom C, baris E2. Pakai fungsi ini untuk memilih sheet berdasarkan nama di sebuah sel, membuat rentang dari angka, dan membuat drop down list bertingkat.
- OFFSET=OFFSET(A1,3,2) mengembalikan sel yang berjarak 3 baris ke bawah dan 2 kolom ke kanan dari A1. Dengan tinggi, rumus ini mengembalikan seluruh rentang, dan begitulah cara menjumlahkan N baris terakhir atau membuat rata-rata bergerak.
- CHOOSE=CHOOSE(B2,"Low","Medium","High") menghasilkan Low jika B2 bernilai 1, Medium jika 2, dan High jika 3. Ubah angka menjadi nama, pilih rentang yang akan dijumlahkan, ganti IF bertingkat, dan pilih kolom dengan CHOOSECOLS.
Menghitung dan Menjumlah dengan Kondisi
- COUNTIF=COUNTIF(B2:B7,"North") menghitung sel di B2:B7 yang berisi North. Hitung berdasarkan teks, angka, wildcard, sel kosong, dan tanggal, serta temukan duplikat, di lembar kerja yang bisa kamu edit.
- COUNTIFS=COUNTIFS(A2:A7,"North",C2:C7,">50") menghitung baris yang wilayahnya North dan penjualannya di atas 50. Hitung di antara dua angka atau dua tanggal, dengan logika OR, dan dengan sel kosong, di lembar kerja interaktif.
- SUMIF=SUMIF(A2:A7,"North",C2:C7) menjumlahkan nilai di C2:C7 pada baris yang kolom A-nya berisi North. SUMIF lebih dari, jika teks mengandung, berdasarkan tanggal dan dari sheet lain, di sheet interaktif yang bisa kamu edit.
- SUMIFS=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") menjumlahkan penjualan di C2:C7 yang wilayahnya North dan produknya Apple. Rentang tanggal, logika OR dan filter opsional, di sheet interaktif.
- AVERAGEIF=AVERAGEIF(A2:A7,"North",C2:C7) menghitung rata-rata nilai di C2:C7 pada baris yang kolom A-nya North. AVERAGEIFS untuk beberapa kondisi, rata-rata yang mengabaikan nol, cara mengatasi #DIV/0!, serta MAXIFS dan MINIFS.
- Menghitung Sel Berisi Teks=COUNTIF(A2:A8,"*") menghitung sel di A2:A8 yang berisi teks, tanpa menghitung angka, tanggal, dan sel kosong. Hitung sel yang mengandung kata tertentu, dan hasilkan sebuah nilai jika sel berisi teks.
- COUNTIF Tidak Kosong=COUNTIF(B2:B8,"<>") menghitung sel di B2:B8 yang tidak kosong, sama seperti COUNTA. Tambahkan kondisi lain dengan COUNTIFS, dan tangani sel yang hanya terlihat kosong.
- Menghitung Nilai Unik=COUNTA(UNIQUE(A2:A9)) menghitung berapa banyak nilai berbeda di A2:A9. Untuk Excel versi lama, pakai =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)). Hitung nilai yang muncul sekali, hitung dengan kondisi, dan lewati sel kosong.
- SUMPRODUCT=SUMPRODUCT(B2:B6,C2:C6) mengalikan setiap jumlah barang dengan harganya lalu menjumlahkan hasilnya. Dengan kondisi seperti (A2:A7="North")*C2:C7, rumus ini menjumlahkan dan menghitung di tempat yang tidak bisa dijangkau SUMIFS: per bulan, kolom dengan kolom, dengan OR.
- SUBTOTAL=SUBTOTAL(9,C2:C8) menjumlahkan C2:C8 seperti SUM, tapi mengabaikan baris SUBTOTAL lain di dalam rentang dan baris yang disembunyikan filter. Nomor fungsi 9 dan 109, menghitung baris yang terlihat, dan AGGREGATE untuk error.
- Rata-rata Tertimbang=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) adalah rata-rata tertimbang: setiap nilai dikalikan dengan bobotnya, hasil kalinya dijumlahkan, lalu totalnya dibagi dengan jumlah bobot. Nilai rapor, IPK berdasarkan SKS dan harga berdasarkan jumlah barang.
Teks
- Concatenate`=A2&" "&B2` menggabungkan teks di A2 dan B2 dengan spasi di antaranya. CONCATENATE dan CONCAT melakukan pekerjaan yang sama; TEXT menjaga angka dan tanggal tetap mudah dibaca saat digabungkan.
- TEXTJOIN`=TEXTJOIN(", ",TRUE,A2:A6)` menggabungkan setiap sel di A2:A6 menjadi satu teks, dengan koma dan spasi di antara item dan sel kosong dilewati. Tambahkan FILTER untuk menggabungkan hanya baris yang cocok dengan sebuah kondisi.
- Memisahkan teks`=TEXTBEFORE(A2," ")` mengembalikan nama depan dari `Ana Silva` dan `=TEXTAFTER(A2," ")` nama belakangnya. TEXTSPLIT memisahkan satu sel menjadi beberapa kolom sekaligus; LEFT, MID dan FIND melakukan hal yang sama di Excel versi lama.
- LEFT, RIGHT, MID`=LEFT(A2,3)` mengembalikan 3 karakter pertama A2, `=RIGHT(A2,2)` 2 karakter terakhir, dan `=MID(A2,5,4)` 4 karakter mulai dari karakter ke-5. Gabungkan dengan FIND dan LEN kalau panjangnya berubah-ubah.
- FIND dan SEARCH`=SEARCH("apple",A2)` menghasilkan posisi awal `apple` di A2, tanpa membedakan huruf besar kecil. FIND melakukan hal yang sama tetapi membedakan huruf besar kecil. Keduanya menghasilkan #VALUE! kalau teksnya tidak ada, dan ISNUMBER mengubahnya menjadi uji "jika sel berisi".
- SUBSTITUTE, REPLACE`=SUBSTITUTE(A2,"-","")` menghapus setiap tanda hubung dari A2: SUBSTITUTE mengganti teks dengan mencocokkan isinya. REPLACE mengganti berdasarkan posisi: `=REPLACE(A2,1,3,"XYZ")` menimpa 3 karakter pertama.
- TRIM`=TRIM(A2)` menghapus spasi sebelum dan sesudah teks di A2 dan mengubah deretan spasi di antara kata menjadi satu. SUBSTITUTE menghapus semua spasi atau spasi tak terputus yang terlewat oleh TRIM.
- UPPER, LOWER, PROPER`=UPPER(A2)` mengubah semua huruf di A2 menjadi huruf besar, `=LOWER(A2)` menjadikan semuanya huruf kecil, dan `=PROPER(A2)` mengubah huruf pertama setiap kata menjadi huruf besar. Untuk huruf pertama teks saja, gabungkan UPPER, LEFT dan MID.
- LEN`=LEN(A2)` mengembalikan jumlah karakter di A2, termasuk spasi dan tanda baca. Dengan TRIM dan SUBSTITUTE, LEN juga bisa menghitung kata, dan dengan SUM bisa menghitung karakter di seluruh rentang.
- TEXT`=TEXT(A2,"mmm d, yyyy")` mengubah tanggal di A2 menjadi teks seperti `Mar 15, 2026`, dan `=TEXT(B2,"$#,##0.00")` mengubah 1250.5 menjadi `$1,250.50`. Hasilnya berupa teks, jadi pakai untuk label, bukan untuk perhitungan lanjutan.
- Teks ke Angka`=VALUE(A2)` mengubah angka yang tersimpan sebagai teks, misalnya `'120`, menjadi angka 120. Dua tanda minus, `=--A2`, melakukan hal yang sama, NUMBERVALUE menangani koma sebagai pemisah desimal, dan Convert to Number memperbaiki selnya langsung.
- Baris baru di selTekan Alt+Enter saat mengetik di sel untuk membuat baris baru di dalamnya (Control+Option+Return di Mac). Di rumus, `CHAR(10)` adalah pemisah baris: `=A2&CHAR(10)&B2` menaruh B2 di baris kedua, yang terlihat setelah Wrap Text aktif.
- Angka 0 di depanExcel membuang angka 0 di depan karena `00742` dibaca sebagai angka 742. Pertahankan dengan format angka kustom seperti `00000`, apostrof (`'00742`) atau format Text, atau tambahkan dengan `=TEXT(A2,"00000")`.
- WildcardDi kriteria Excel, `*` mewakili berapa pun jumlah karakter dan `?` mewakili tepat satu karakter: `=COUNTIF(A2:A7,"*apple*")` menghitung sel yang mengandung `apple`. `~` mengubah wildcard kembali menjadi karakter biasa.
Tanggal dan Waktu
- Menghitung Umur=DATEDIF(B2,TODAY(),"Y") menghasilkan umur dalam tahun penuh untuk seseorang yang lahir pada tanggal di B2. Hitung umur pada tanggal tertentu, dalam tahun, bulan dan hari, juga tanpa DATEDIF.
- DATEDIF=DATEDIF(A2,B2,"M") menghitung jumlah bulan penuh di antara tanggal awal di A2 dan tanggal akhir di B2. Satuan Y, M, D, YM, MD dan YD, kenapa DATEDIF tidak ada di daftar fungsi, dan error #NUM!.
- Jumlah Hari Antara Tanggal=B2-A2 menghasilkan jumlah hari di antara tanggal di A2 dan tanggal yang lebih lambat di B2. Hitung hari dengan DAYS, sertakan kedua tanggal, atau hitung minggu, bulan, tahun, dan hari kerja.
- Nama Hari=TEXT(A2,"dddd") mengembalikan nama hari dari tanggal di A2, misalnya Monday, dan =WEEKDAY(A2) mengembalikannya sebagai angka. Nama hari singkat, return type WEEKDAY dan cara memeriksa akhir pekan.
- TODAY dan NOW=TODAY() mengembalikan tanggal hari ini dan =NOW() mengembalikan tanggal dan jam saat ini, dan keduanya diperbarui setiap kali sheet menghitung ulang. Hitung sisa hari sampai sebuah tanggal, dan masukkan tanggal yang tidak pernah berubah dengan Ctrl+;.
- Menambah Hari dan Bulan=A2+30 menghasilkan tanggal 30 hari setelah A2. Untuk menambah bulan pakai =EDATE(A2,3), untuk akhir bulan =EOMONTH(A2,0), dan untuk tahun pakai EDATE dengan 12 bulan per tahun.
- NETWORKDAYS dan WORKDAY=NETWORKDAYS(A2,B2) menghitung hari kerja (Senin sampai Jumat) dari A2 sampai B2, kedua tanggal ikut dihitung. =WORKDAY(A2,10) mengembalikan tanggal 10 hari kerja setelah A2. Keduanya bisa melewati daftar hari libur.
- DATE, YEAR, MONTH, DAY=DATE(2026,3,15) menghasilkan tanggal 15 Maret 2026 dari tahun, bulan, dan hari. YEAR, MONTH, dan DAY memecah tanggal, dan DATE menggulirkan bulan ke-13 ke tahun berikutnya.
- Menghitung Waktu=B2-A2 mengembalikan selisih antara waktu mulai di A2 dan waktu selesai di B2: format sebagai h:mm untuk melihat 8:30, atau kalikan dengan 24 untuk mendapatkan 8,5 jam. Shift yang melewati tengah malam, total di atas 24 jam dan gaji dari jam kerja.
- Nomor Minggu=WEEKNUM(A2) mengembalikan nomor minggu dari tanggal di A2, dengan minggu yang dimulai pada hari Minggu. =ISOWEEKNUM(A2) mengembalikan minggu ISO yang dipakai di Eropa, dengan minggu yang dimulai pada hari Senin. Tanggal awal sebuah minggu dan tanggal dari nomor minggu.
Matematika dan Statistik
- ROUND=ROUND(A2,2) membulatkan angka di A2 menjadi dua angka desimal, dan =ROUND(A2,0) ke bilangan bulat terdekat. Jumlah digit negatif membulatkan ke puluhan, ratusan dan ribuan; MROUND membulatkan ke kelipatan apa pun.
- ROUNDUP / ROUNDDOWN=ROUNDUP(A2,0) selalu membulatkan menjauhi nol, jadi 2.1 menjadi 3, dan =ROUNDDOWN(A2,0) selalu membulatkan ke arah nol, jadi 2.9 menjadi 2. CEILING dan FLOOR membulatkan ke atas atau ke bawah ke sebuah kelipatan, sedangkan INT dan TRUNC membuang desimal.
- Standar deviasi=STDEV.S(B2:B9) memberi standar deviasi sebuah sampel dan =STDEV.P(B2:B9) standar deviasi seluruh populasi. Pakai STDEV.S kecuali datamu berisi semua nilai yang ada. VAR.S dan VAR.P memberi variansnya.
- RANK=RANK.EQ(B2,$B$2:$B$7) memberi posisi B2 di antara nilai-nilai di B2:B7, dengan nilai terbesar di peringkat 1. Tambahkan 1 sebagai argumen ketiga untuk memberi peringkat 1 ke nilai terkecil. Nilai kembar berbagi peringkat; COUNTIFS membuat peringkat di dalam kelompok.
- Angka acak=RANDBETWEEN(1,100) mengembalikan bilangan bulat acak dari 1 sampai 100, dan =RAND() desimal acak dari 0 sampai kurang dari 1. RANDARRAY mengisi seluruh rentang, INDEX dengan RANDBETWEEN memilih item acak, dan Paste Special > Values membekukan hasilnya.
- MOD dan ABS=MOD(A2,B2) mengembalikan sisa pembagian A2 dengan B2, jadi =MOD(17,5) hasilnya 2. =ABS(A2) mengembalikan angka tanpa tandanya, jadi =ABS(B2-C2) adalah selisih dua nilai, mana pun yang lebih besar.
- PMT=PMT(B2/12,B3*12,-B1) mengembalikan cicilan bulanan pinjaman sebesar B1 dengan bunga tahunan di B2 selama B3 tahun. Bagi bunganya dengan 12, kalikan tahunnya dengan 12, dan taruh tanda minus sebelum jumlah pinjaman supaya cicilannya positif.
- NPV dan IRR=NPV(E2,B3:B5)+B2 mendiskontokan arus kas masa depan dengan tingkat bunga di E2 lalu menambahkan investasi awal di B2, yang tidak boleh didiskontokan oleh NPV. =IRR(B2:B5) mengembalikan tingkat bunga saat NPV itu bernilai nol. XNPV dan XIRR memakai tanggal sebenarnya.
- CAGR=(B2/A2)^(1/C2)-1 menghasilkan tingkat pertumbuhan tahunan majemuk dari nilai awal di A2 ke nilai akhir di B2 selama C2 tahun. =RRI(C2,A2,B2) menghasilkan tingkat yang sama. Format selnya sebagai persentase.
Array Dinamis
- FILTER=FILTER(A2:C7,B2:B7="North") menghasilkan setiap baris A2:C7 yang wilayahnya North, dan hasilnya ikut diperbarui saat datanya berubah. Pelajari banyak kriteria dengan * dan +, if_empty, #CALC!, dan cara mengurutkan hasilnya.
- UNIQUE=UNIQUE(B2:B8) mengembalikan setiap nilai di B2:B8 satu kali, sesuai urutan kemunculan pertamanya, dan ikut diperbarui saat daftarnya berubah. Pelajari baris unik, exactly_once, daftar unik yang terurut, menghitung nilai unik dan memakai hasilnya sebagai sumber drop-down.
- SORT dan SORTBY=SORT(A2:C7,3,-1) mengembalikan tabel A2:C7 yang diurutkan berdasarkan kolom ketiganya, dari yang terbesar, dan terus mengurutkan ulang saat datanya berubah. SORTBY mengurutkan berdasarkan rentang apa pun, termasuk beberapa kolom dan urutan khusus.
- SEQUENCE=SEQUENCE(5) mengembalikan angka 1 sampai 5 ke bawah sebuah kolom, dan =SEQUENCE(3,4) mengisi 3 baris kali 4 kolom. Tambahkan nilai awal dan langkah untuk deret apa pun, termasuk tanggal, nomor baris yang bertambah mengikuti daftar, dan kalender bulanan.
- TRANSPOSE=TRANSPOSE(A1:D3) mengubah baris-baris A1:D3 menjadi kolom dan tetap terhubung ke sumbernya. Untuk salinan sekali pakai, pakai Paste Special > Transpose. TOCOL menumpuk seluruh tabel menjadi satu kolom.
- LET=LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) menghitung jumlahnya sekali, menamainya total, lalu memakai nama itu dua kali. LET membuat rumus panjang lebih pendek, lebih mudah dibaca dan lebih cepat, karena setiap bagian bernama hanya dihitung sekali.
- LAMBDA=LAMBDA(price,price*1.2)(B2) membuat fungsi kecil dengan satu input, price, lalu memanggilnya pada B2. Simpan LAMBDA di Name Manager agar bisa dipakai seperti fungsi bawaan, atau berikan ke MAP, BYROW, SCAN dan REDUCE.
Error dan Pemecahan Masalah
- Error #SPILL!#SPILL! berarti rumus yang mengembalikan beberapa nilai tidak punya tempat untuk menaruhnya: ada sel di rentang spill-nya yang tidak kosong. Kosongkan sel yang menghalangi, maka hasilnya muncul.
- Error #VALUE!#VALUE! berarti sebuah rumus menerima jenis nilai yang salah, paling sering teks di tempat yang butuh angka: =B2+C2 gagal jika C2 berisi "n/a" atau sebuah spasi. SUM mengabaikan teks, jadi =SUM(B2:C2) tetap jalan.
- Error #NAME?#NAME? berarti Excel tidak mengenali sebuah kata di rumus: nama fungsi yang salah ketik seperti =SUMM(B2:B6), teks tanpa tanda kutip, rentang tanpa titik dua, nama yang belum didefinisikan, atau fungsi yang tidak ada di versi Excel-mu.
- Error #REF!#REF! berarti rumus merujuk ke sel yang sudah tidak ada, biasanya karena baris, kolom atau sheet yang dipakainya dihapus: =B2*C2 menjadi =B2*#REF!. Error ini juga muncul ketika VLOOKUP atau INDEX meminta kolom atau baris di luar rentangnya.
- Error #N/A#N/A berarti lookup tidak menemukan nilai yang dicarinya. Periksa salah ketik, spasi berlebih, dan rentang tabel yang bergeser saat rumus disalin ke bawah, lalu pakai IFNA untuk menampilkan pesan bagi nilai yang memang tidak ada.
- Error #DIV/0!#DIV/0! muncul saat rumus membagi dengan nol atau dengan sel kosong, seperti =B2/C2 dengan C2 kosong. =IF(C2=0,"",B2/C2) menampilkan sel kosong sebagai gantinya, dan AVERAGE dari rentang tanpa angka juga menghasilkan error ini.
- Circular ReferenceCircular reference adalah rumus yang merujuk ke selnya sendiri, langsung atau lewat rumus lain, misalnya =SUM(B2:B7) yang diketik di B7. Excel memberi peringatan, menampilkan 0, dan mencantumkan selnya di Formulas > Error Checking > Circular References.
- Rumus Tidak MenghitungKalau Excel menampilkan rumus, bukan hasilnya, berarti selnya berformat Text, rumusnya diawali apostrof atau spasi, atau Show Formulas sedang menyala. Kalau hasilnya tidak diperbarui, perhitungannya diatur ke Manual: Formulas > Calculation Options > Automatic.
Alat Data
- Hapus duplikatPilih datanya lalu klik Data > Remove Duplicates untuk menghapus baris yang berulang langsung di tempatnya, atau pakai =UNIQUE(A2:A9) untuk mendapat salinan yang bersih tanpa mengubah data asli. Temukan, tandai dan hitung duplikat, lalu hapus berdasarkan dua kolom.
- Menandai DuplikatPilih selnya lalu pilih Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Untuk satu baris penuh, hanya salinan kedua, atau kecocokan di dua kolom, pakai aturan rumus seperti =COUNTIF($A$2:$A$9,A2)>1.
- Conditional FormattingConditional formatting mewarnai sel saat sebuah kondisi bernilai benar. Pakai Home > Conditional Formatting untuk aturan siap pakai, atau New Rule > Use a formula dengan aturan seperti =$C2>100 untuk mewarnai satu baris penuh, tanggal yang lewat jatuh tempo, dan teks yang cocok.
- Drop Down ListPilih selnya, buka Data > Data Validation, pilih List, lalu ketik item-itemnya (North,South,East) atau pilih sebuah rentang sebagai sumbernya. Setelah itu, buat daftarnya dinamis dengan UNIQUE, bergantung pada daftar lain, dan cari nilai dari item yang dipilih.
- Membandingkan Dua KolomUntuk membandingkan dua kolom baris per baris, pakai =A2=B2 (atau EXACT jika huruf besar kecil penting). Untuk menemukan nilai di satu kolom yang tidak ada di kolom lain, pakai COUNTIF, MATCH atau XLOOKUP, lalu sorot perbedaannya dengan conditional formatting.
- Pivot tablePivot table mengelompokkan baris tabel berdasarkan sebuah kategori dan menjumlahkan sebuah angka untuk setiap kategori, tanpa rumus: Insert > PivotTable, lalu seret field ke Rows dan Values. Berikut langkahnya, penjelasan keempat areanya, dan ringkasan yang sama dibuat dengan rumus.
