19 Tip, Trik, & Pintasan Excel Mudah

Terkadang, Excel tampak terlalu bagus untuk menjadi kenyataan. Yang harus saya lakukan adalah memasukkan formula, dan hampir semua hal yang perlu saya lakukan secara manual dapat dilakukan secara otomatis.

Perlu menggabungkan dua lembar dengan data serupa? Excel bisa melakukannya.

Perlu melakukan matematika sederhana? Excel bisa melakukannya.

Perlu menggabungkan informasi dalam beberapa sel? Excel bisa melakukannya.

Dalam posting ini, saya akan membahas tip, trik, dan pintasan terbaik yang dapat Anda gunakan sekarang untuk membawa game Excel Anda ke level berikutnya. Tidak diperlukan pengetahuan Excel tingkat lanjut.

Apa itu Excel?

Microsoft Excel adalah perangkat lunak visualisasi dan analisis data yang kuat, yang menggunakan spreadsheet untuk menyimpan, mengatur, dan melacak kumpulan data dengan rumus dan fungsi. Excel digunakan oleh pemasar, akuntan, analis data, dan profesional lainnya. Ini adalah bagian dari rangkaian produk Microsoft Office. Alternatif termasuk Google Spreadsheet dan Numbers.

Temukan lebih banyak alternatif Excel di sini.

Excel terutama digunakan untuk membuat dokumen keuangan karena kekuatan komputasinya yang kuat. Anda akan sering menemukan perangkat lunak di kantor dan tim akuntansi karena memungkinkan akuntan untuk melihat jumlah, rata-rata, dan total secara otomatis. Dengan Excel, mereka dapat dengan mudah memahami data bisnis mereka.

Meskipun Excel terutama dikenal sebagai alat akuntansi, para profesional di bidang apa pun dapat menggunakan fitur dan rumusnya — terutama pemasar — ​​karena dapat digunakan untuk melacak semua jenis data. Ini menghilangkan kebutuhan untuk menghabiskan berjam-jam menghitung sel atau menyalin dan menempelkan angka kinerja. Excel biasanya memiliki pintasan atau perbaikan cepat yang mempercepat proses.

Anda juga dapat mengunduh template Excel di bawah ini untuk semua kebutuhan pemasaran Anda.

Setelah Anda mengunduh template, saatnya untuk mulai menggunakan perangkat lunak. Mari kita bahas dasar-dasarnya terlebih dahulu.

Dasar-dasar Excel

Jika Anda baru memulai dengan Excel, ada beberapa perintah dasar yang kami sarankan agar Anda terbiasa. Ini adalah hal-hal seperti:

• Membuat spreadsheet baru dari awal.
• Melakukan perhitungan dasar seperti penjumlahan, pengurangan, perkalian, dan pembagian.
• Menulis dan memformat teks kolom dan judul.
• Menggunakan fitur pengisian otomatis Excel.
• Menambah atau menghapus satu kolom, baris, dan spreadsheet. (Di bawah ini, kita akan membahas cara menambahkan hal-hal seperti beberapa kolom dan baris.)
• Menjaga judul kolom dan baris tetap terlihat saat Anda menggulir melewatinya dalam spreadsheet, sehingga Anda tahu data apa yang Anda isi saat Anda bergerak lebih jauh ke bawah dokumen.
• Menyortir data Anda dalam urutan abjad.

Mari kita jelajahi beberapa di antaranya secara lebih mendalam.

Misalnya, mengapa pengisian otomatis penting?

Jika Anda memiliki pengetahuan dasar Excel, kemungkinan Anda sudah mengetahui trik cepat ini. Tetapi untuk menutupi basis kami, izinkan saya menunjukkan kepada Anda kemuliaan pengisian otomatis. Ini memungkinkan Anda dengan cepat mengisi sel yang berdekatan dengan beberapa tipe data, termasuk nilai, seri, dan rumus.

Ada beberapa cara untuk menerapkan fitur ini, tetapi gagang isian adalah salah satu yang termudah. Pilih sel yang ingin Anda jadikan sumber, temukan gagang isian di sudut kanan bawah sel, dan seret gagang isian untuk menutupi sel yang ingin Anda isi atau cukup klik dua kali:

Demikian pula, pengurutan adalah fitur penting yang ingin Anda ketahui saat mengatur data Anda di Excel.

Terkadang Anda mungkin memiliki daftar data yang tidak memiliki organisasi apa pun. Mungkin Anda mengekspor daftar kontak pemasaran atau posting blog Anda. Apapun masalahnya, Fitur pengurutan Excel akan membantu Anda menyusun abjad daftar apa pun.

Klik pada data di kolom yang ingin Anda urutkan. Kemudian klik pada tab "Data" di bilah alat Anda dan cari opsi "Urutkan" di sebelah kiri. Jika "A" ada di atas "Z", Anda cukup mengklik tombol itu sekali. Jika "Z" ada di atas "A", klik tombol dua kali. Ketika "A" berada di atas "Z", itu berarti daftar Anda akan diurutkan dalam urutan abjad. Namun, ketika "Z" berada di atas "A", itu berarti daftar Anda akan diurutkan dalam urutan abjad terbalik.

Mari kita jelajahi lebih lanjut tentang dasar-dasar Excel (bersama dengan fitur-fitur canggih) selanjutnya.

Cara Menggunakan Excel

Untuk menggunakan Excel, Anda hanya perlu memasukkan data ke dalam baris dan kolom. Kemudian Anda akan menggunakan rumus dan fungsi untuk mengubah data tersebut menjadi wawasan.

Kami akan membahas formula dan fungsi terbaik yang perlu Anda ketahui. Tetapi pertama-tama, mari kita lihat jenis dokumen yang dapat Anda buat menggunakan perangkat lunak. Dengan begitu, Anda memiliki pemahaman menyeluruh tentang bagaimana Anda bisa menggunakan Excel dalam keseharian Anda.

Dokumen yang Dapat Anda Buat di Excel

Tidak yakin bagaimana Anda benar-benar dapat menggunakan Excel di tim Anda? Berikut adalah daftar dokumen yang dapat Anda buat:

• Laporan Laba Rugi: Anda dapat menggunakan spreadsheet Excel untuk melacak aktivitas penjualan dan kesehatan keuangan perusahaan.
• Neraca: Neraca adalah salah satu jenis dokumen paling umum yang dapat Anda buat dengan Excel. Hal ini memungkinkan Anda untuk mendapatkan pandangan holistik tentang posisi keuangan perusahaan.
• Kalender: Anda dapat dengan mudah membuat kalender bulanan spreadsheet untuk melacak acara atau informasi sensitif tanggal lainnya.

Berikut adalah beberapa dokumen yang dapat Anda buat khusus untuk pemasar.

Ini hanya contoh kecil dari jenis dokumen pemasaran dan bisnis yang dapat Anda buat di Excel. Kami telah membuat daftar lengkap templat Excel yang dapat Anda gunakan sekarang untuk pemasaran, pembuatan faktur, manajemen proyek, penganggaran, dan banyak lagi.

Dengan semangat bekerja lebih efisien dan menghindari pekerjaan manual yang membosankan, berikut adalah beberapa rumus dan fungsi Excel yang perlu Anda ketahui.

Rumus Excel

Sangat mudah untuk kewalahan dengan berbagai rumus Excel yang bisa Anda gunakan untuk memahami data Anda. Jika Anda baru mulai menggunakan Excel, Anda dapat mengandalkan rumus berikut untuk menjalankan beberapa fungsi kompleks — tanpa menambah kerumitan jalur pembelajaran Anda.

• Tanda sama dengan: Sebelum membuat rumus apa pun, Anda harus menulis tanda sama dengan (=) di sel tempat Anda ingin hasilnya muncul.
• Penambahan : Untuk menambahkan nilai dari dua sel atau lebih, gunakan tanda + . Contoh: =C5+D3 .
• Pengurangan : Untuk mengurangi nilai dari dua sel atau lebih, gunakan tanda – . Contoh: =C5-D3 .
• Perkalian : Untuk mengalikan nilai dari dua sel atau lebih, gunakan tanda * . Contoh: =C5*D3 .
• Pembagian : Untuk membagi nilai dari dua sel atau lebih, gunakan tanda / . Contoh: =C5/D3 .

Menempatkan semua ini bersama-sama, Anda dapat membuat rumus yang menambahkan, mengurangi, mengalikan, dan membagi semua dalam satu sel. Contoh: =(C5-D3)/((A5+B6)*3) .

Untuk rumus yang lebih kompleks, Anda harus menggunakan tanda kurung di sekitar ekspresi untuk menghindari penggunaan urutan operasi PEMDAS secara tidak sengaja. Ingatlah bahwa Anda dapat menggunakan angka biasa dalam rumus Anda.

Fungsi Excel

Fungsi Excel mengotomatiskan beberapa tugas yang akan Anda gunakan dalam rumus umum. Misalnya, alih-alih menggunakan tanda + untuk menambahkan rentang sel, Anda akan menggunakan fungsi SUM. Mari kita lihat beberapa fungsi lagi yang akan membantu mengotomatiskan perhitungan dan tugas.

• SUM: Fungsi SUM secara otomatis menambahkan rentang sel atau angka. Untuk menyelesaikan penjumlahan, Anda akan memasukkan sel awal dan sel akhir dengan titik dua di antaranya. Berikut tampilannya: SUM(Cell1:Cell2) . Contoh: =SUM(C5:C30) .
• RATA-RATA: Fungsi AVERAGE merata-ratakan nilai rentang sel. Sintaksnya sama dengan fungsi SUM: AVERAGE(Cell1:Cell2). Contoh: =AVERAGE(C5:C30) .
• JIKA: Fungsi IF memungkinkan Anda untuk mengembalikan nilai berdasarkan tes logis. Sintaksnya adalah sebagai berikut: IF(logical_test, value_if_true, [value_if_false]) . Contoh: =IF(A2>B2,”Over Budget”,”OK”) .
• VLOOKUP: Fungsi VLOOKUP membantu Anda mencari apa pun di baris lembar Anda. Sintaksnya adalah: VLOOKUP(nilai pencarian, larik tabel, nomor kolom, Kecocokan perkiraan (TRUE) atau Pencocokan tepat (FALSE)) . Contoh: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
• INDEX: Fungsi INDEX mengembalikan nilai dari dalam suatu rentang. Sintaksnya adalah sebagai berikut: INDEX(array, row_num, [column_num]) .
• MATCH: Fungsi MATCH mencari item tertentu dalam rentang sel dan mengembalikan posisi item tersebut. Ini dapat digunakan bersama-sama dengan fungsi INDEX. Sintaksnya adalah: MATCH(lookup_value, lookup_array, [match_type]) .
• COUNTIF: Fungsi COUNTIF mengembalikan jumlah sel yang memenuhi kriteria tertentu atau memiliki nilai tertentu. Sintaksnya adalah: COUNTIF(rentang, kriteria). Contoh: =COUNTIF(A2:A5,”London”).

Oke, siap untuk masuk ke seluk beluknya? Mari kita lakukan. (Dan untuk semua penggemar Harry Potter di luar sana ... terima kasih sebelumnya.)

Catatan : GIF dan visual berasal dari versi Excel sebelumnya. Jika berlaku, salinan telah diperbarui untuk memberikan instruksi bagi pengguna versi Excel yang lebih baru dan lebih lama.

1. Gunakan tabel Pivot untuk mengenali dan memahami data.

Tabel pivot digunakan untuk mengatur ulang data dalam spreadsheet. Mereka tidak akan mengubah data yang Anda miliki, tetapi mereka dapat menjumlahkan nilai dan membandingkan berbagai informasi di spreadsheet Anda, bergantung pada apa yang Anda ingin mereka lakukan.

Mari kita lihat sebuah contoh. Katakanlah saya ingin melihat berapa banyak orang di setiap rumah di Hogwarts. Anda mungkin berpikir bahwa saya tidak memiliki terlalu banyak data, tetapi untuk kumpulan data yang lebih panjang, ini akan berguna.

Untuk membuat Tabel Pivot, saya pergi ke Data > Tabel Pivot . Jika Anda menggunakan versi Excel terbaru, buka Sisipkan > Tabel Pivot . Excel akan secara otomatis mengisi Tabel Pivot Anda, tetapi Anda selalu dapat mengubah urutan datanya. Kemudian, Anda memiliki empat opsi untuk dipilih.

• Filter Laporan : Ini memungkinkan Anda untuk hanya melihat baris tertentu dalam kumpulan data Anda. Misalnya, jika saya ingin membuat filter menurut rumah, saya dapat memilih untuk hanya menyertakan siswa di Gryffindor daripada semua siswa.
• Label Kolom : Ini akan menjadi tajuk Anda di kumpulan data.
• Label Baris : Ini bisa menjadi baris Anda di kumpulan data. Label Baris dan Kolom dapat berisi data dari kolom Anda (mis. Nama Depan dapat diseret ke label Baris atau Kolom — itu hanya tergantung pada cara Anda ingin melihat datanya.)
• Nilai : Bagian ini memungkinkan Anda untuk melihat data Anda secara berbeda. Alih-alih hanya menarik nilai numerik apa pun, Anda dapat menjumlahkan, menghitung, rata-rata, maks, min, menghitung angka, atau melakukan beberapa manipulasi lain dengan data Anda. Bahkan, secara default, saat Anda menyeret bidang ke Nilai, itu selalu diperhitungkan.

Karena saya ingin menghitung jumlah siswa di setiap rumah, saya akan pergi ke pembuat tabel Pivot dan menyeret kolom Rumah ke Label Baris dan Nilai. Ini akan menjumlahkan jumlah siswa yang terkait dengan setiap rumah.

2. Tambahkan lebih dari satu baris atau kolom.

Saat Anda bermain-main dengan data Anda, Anda mungkin menemukan bahwa Anda terus-menerus perlu menambahkan lebih banyak baris dan kolom. Terkadang, Anda bahkan mungkin perlu menambahkan ratusan baris. Melakukan ini satu per satu akan sangat membosankan. Untungnya, selalu ada cara yang lebih mudah.

Untuk menambahkan beberapa baris atau kolom dalam spreadsheet, sorot jumlah baris atau kolom yang sudah ada sebelumnya yang ingin Anda tambahkan. Kemudian, klik kanan dan pilih "Sisipkan."

Pada contoh di bawah ini, saya ingin menambahkan tiga baris tambahan. Dengan menyorot tiga baris dan kemudian mengklik sisipkan, saya dapat menambahkan tiga baris kosong tambahan ke dalam spreadsheet saya dengan cepat dan mudah.

3. Gunakan filter untuk menyederhanakan data Anda.

Saat Anda melihat kumpulan data yang sangat besar, Anda biasanya tidak perlu melihat setiap baris secara bersamaan. Terkadang, Anda hanya ingin melihat data yang sesuai dengan kriteria tertentu.

Di situlah filter masuk.

Filter memungkinkan Anda untuk mengurangi data agar hanya melihat baris tertentu pada satu waktu. Di Excel, filter dapat ditambahkan ke setiap kolom dalam data Anda — dan dari sana, Anda dapat memilih sel mana yang ingin Anda lihat sekaligus.

Mari kita lihat contoh di bawah ini. Tambahkan filter dengan mengklik tab Data dan memilih “Filter.” Mengklik panah di samping tajuk kolom dan Anda akan dapat memilih apakah Anda ingin data Anda diatur dalam urutan menaik atau menurun, serta baris spesifik mana yang ingin Anda tampilkan.

Dalam contoh Harry Potter saya, katakanlah saya hanya ingin melihat siswa di Gryffindor. Dengan memilih filter Gryffindor, baris lainnya menghilang.

Kiat Pro : Salin dan tempel nilai di spreadsheet saat Filter aktif untuk melakukan analisis tambahan di spreadsheet lain.

4. Hapus titik atau set data duplikat.

Kumpulan data yang lebih besar cenderung memiliki konten duplikat. Anda mungkin memiliki daftar beberapa kontak dalam satu perusahaan dan hanya ingin melihat jumlah perusahaan yang Anda miliki. Dalam situasi seperti ini, menghapus duplikat sangat berguna.

Untuk menghapus duplikat Anda, sorot baris atau kolom yang ingin Anda hapus duplikatnya. Lalu, buka tab Data dan pilih "Hapus Duplikat" (yang ada di bawah subjudul Alat di versi Excel yang lebih lama). Sebuah pop-up akan muncul untuk mengkonfirmasi data mana yang ingin Anda kerjakan. Pilih "Hapus Duplikat," dan Anda siap melakukannya.

Anda juga dapat menggunakan fitur ini untuk menghapus seluruh baris berdasarkan nilai kolom duplikat. Jadi jika Anda memiliki tiga baris dengan informasi Harry Potter dan Anda hanya perlu melihat satu, maka Anda dapat memilih seluruh kumpulan data dan kemudian menghapus duplikat berdasarkan email. Daftar hasil Anda hanya akan memiliki nama unik tanpa duplikat.

5. Transpose baris menjadi kolom.

Bila Anda memiliki baris data dalam spreadsheet, Anda mungkin memutuskan bahwa Anda benar-benar ingin mengubah item di salah satu baris tersebut menjadi kolom (atau sebaliknya). Ini akan memakan banyak waktu untuk menyalin dan menempelkan setiap tajuk — tetapi fitur transpos yang memungkinkan Anda lakukan hanyalah memindahkan data baris Anda ke dalam kolom, atau sebaliknya.

Mulailah dengan menyorot kolom yang ingin Anda ubah menjadi baris. Klik kanan, lalu pilih "Salin." Selanjutnya, pilih sel pada spreadsheet Anda di mana Anda ingin baris atau kolom pertama Anda dimulai. Klik kanan pada sel, lalu pilih "Tempel Spesial." Sebuah modul akan muncul — di bagian bawah, Anda akan melihat opsi untuk mengubah posisi. Centang kotak itu dan pilih OK. Kolom Anda sekarang akan ditransfer ke baris atau sebaliknya.

Pada versi Excel yang lebih baru, drop-down akan muncul alih-alih pop-up.

6. Pisahkan informasi teks antar kolom.

Bagaimana jika Anda ingin membagi informasi yang ada dalam satu sel menjadi dua sel yang berbeda? Misalnya, mungkin Anda ingin mengeluarkan nama perusahaan seseorang melalui alamat emailnya. Atau mungkin Anda ingin memisahkan nama lengkap seseorang menjadi nama depan dan belakang untuk template pemasaran email Anda.

Berkat Excel, keduanya dimungkinkan. Pertama, sorot kolom yang ingin Anda pisahkan. Selanjutnya, buka tab Data dan pilih "Teks ke Kolom." Sebuah modul akan muncul dengan informasi tambahan.

Pertama, Anda harus memilih "Dibatasi" atau "Lebar Tetap."

• “Dibatasi” berarti Anda ingin memecah kolom berdasarkan karakter seperti koma, spasi, atau tab.
• “Fixed Width” berarti Anda ingin memilih lokasi yang tepat pada semua kolom yang Anda inginkan agar pemisahan terjadi.

Pada contoh kasus di bawah ini, mari kita pilih “Delimited” sehingga kita dapat memisahkan nama lengkap menjadi nama depan dan nama belakang.

Kemudian, saatnya untuk memilih Pembatas. Ini bisa berupa tab, titik koma, koma, spasi, atau yang lainnya. (“Sesuatu yang lain” bisa menjadi tanda “@” yang digunakan dalam alamat email, misalnya.) Dalam contoh kita, mari kita pilih spasi. Excel kemudian akan menampilkan pratinjau seperti apa kolom baru Anda nantinya.

Saat Anda senang dengan pratinjau, tekan "Berikutnya." Halaman ini akan memungkinkan Anda untuk memilih Format Lanjutan jika Anda mau. Setelah selesai, klik "Selesai."

7. Gunakan rumus untuk perhitungan sederhana.

Selain melakukan perhitungan yang cukup rumit, Excel dapat membantu Anda melakukan aritmatika sederhana seperti menambahkan, mengurangi, mengalikan, atau membagi data Anda.

• Untuk menambahkan, gunakan tanda +.
• Untuk mengurangi, gunakan tanda –.
• Untuk mengalikan, gunakan tanda *.
• Untuk membagi, gunakan tanda /.

Anda juga dapat menggunakan tanda kurung untuk memastikan perhitungan tertentu dilakukan terlebih dahulu. Pada contoh di bawah (10+10*10), 10 kedua dan ketiga dikalikan sebelum menambahkan 10 tambahan. Namun, jika kita membuatnya (10+10)*10, 10 pertama dan kedua akan dijumlahkan terlebih dahulu .

8. Dapatkan rata-rata angka di sel Anda.

Jika Anda menginginkan rata-rata dari sekumpulan angka, Anda dapat menggunakan rumus =AVERAGE(Cell1:Cell2) . Jika Anda ingin menjumlahkan kolom angka, Anda dapat menggunakan rumus =SUM(Cell1:Cell2) .

9. Gunakan pemformatan bersyarat untuk membuat sel secara otomatis berubah warna berdasarkan data.

Pemformatan bersyarat memungkinkan Anda mengubah warna sel berdasarkan informasi di dalam sel. Misalnya, jika Anda ingin menandai nomor tertentu yang berada di atas rata-rata atau di 10% teratas dari data di spreadsheet, Anda dapat melakukannya. Jika Anda ingin kesamaan kode warna antara baris yang berbeda di Excel, Anda bisa melakukannya. Ini akan membantu Anda dengan cepat melihat informasi yang penting bagi Anda.

Untuk memulai, sorot grup sel tempat Anda ingin menggunakan pemformatan bersyarat. Kemudian, pilih "Pemformatan Bersyarat" dari menu Beranda dan pilih logika Anda dari tarik-turun. (Anda juga dapat membuat aturan Anda sendiri jika Anda menginginkan sesuatu yang berbeda.) Sebuah jendela akan muncul yang meminta Anda untuk memberikan informasi lebih lanjut tentang aturan pemformatan Anda. Pilih "OK" setelah selesai, dan Anda akan melihat hasil Anda muncul secara otomatis.

10. Gunakan rumus IF Excel untuk mengotomatisasi fungsi Excel tertentu.

Terkadang, kita tidak ingin menghitung berapa kali suatu nilai muncul. Sebagai gantinya, kami ingin memasukkan informasi yang berbeda ke dalam sel jika ada sel yang sesuai dengan informasi itu.

Misalnya, dalam situasi di bawah ini, saya ingin memberikan sepuluh poin kepada semua orang yang termasuk dalam keluarga Gryffindor. Daripada mengetik 10 secara manual di sebelah nama setiap siswa Gryffindor, saya dapat menggunakan rumus IF Excel untuk mengatakan bahwa jika siswa tersebut di Gryffindor, maka mereka harus mendapatkan sepuluh poin.

Rumusnya adalah: IF(logical_test, value_if_true, [value_if_false])

Contoh Ditunjukkan Di Bawah: =IF(D2=”Gryffindor”,”10″,”0″)

Secara umum, rumusnya adalah IF(Logical Test, value of true, value of false). Mari kita gali masing-masing variabel ini.

• Logical_Test : Tes logis adalah bagian "JIKA" dari pernyataan. Dalam hal ini, logikanya adalah D2=”Gryffindor” karena kita ingin memastikan bahwa sel yang sesuai dengan siswa mengatakan “Gryffindor.” Pastikan untuk menempatkan Gryffindor dalam tanda kutip di sini.
• Value_if_True : Inilah yang kita inginkan agar sel ditampilkan jika nilainya benar. Dalam hal ini, kami ingin sel menunjukkan "10" untuk menunjukkan bahwa siswa tersebut diberikan 10 poin. Hanya gunakan tanda kutip jika Anda ingin hasilnya berupa teks, bukan angka.
• Value_if_False : Inilah yang kita inginkan agar sel ditampilkan jika nilainya salah. Dalam hal ini, untuk siswa mana pun yang tidak berada di Gryffindor, kami ingin sel menunjukkan "0". Hanya gunakan tanda kutip jika Anda ingin hasilnya berupa teks, bukan angka.

Catatan : Dalam contoh di atas, saya memberikan 10 poin kepada semua orang di Gryffindor. Jika nanti saya ingin menjumlahkan jumlah total poin, saya tidak akan dapat melakukannya karena 10 ada dalam tanda kutip, sehingga menjadikannya teks dan bukan angka yang dapat dijumlahkan oleh Excel.

Kekuatan sebenarnya dari fungsi IF muncul saat Anda merangkai beberapa pernyataan IF bersama, atau sarang mereka. Ini memungkinkan Anda untuk mengatur beberapa kondisi, mendapatkan hasil yang lebih spesifik, dan pada akhirnya mengatur data Anda menjadi bagian yang lebih mudah dikelola.

Rentang adalah salah satu cara untuk mengelompokkan data Anda untuk analisis yang lebih baik. Misalnya, Anda dapat mengategorikan data ke dalam nilai yang kurang dari 10, 11 hingga 50, atau 51 hingga 100. Berikut tampilannya dalam praktik:

=IF(B3<11,“10 atau kurang”,IF(B3<51,”11 hingga 50”,IF(B3<100,“51 hingga 100”)))

Dibutuhkan beberapa percobaan dan kesalahan, tetapi begitu Anda menguasainya, rumus JIKA akan menjadi teman terbaik Excel Anda yang baru.

11. Gunakan tanda dolar untuk menjaga rumus satu sel tetap sama di mana pun ia bergerak.

Pernahkah Anda melihat tanda dolar dalam rumus Excel? Ketika digunakan dalam formula, itu tidak mewakili dolar Amerika; sebagai gantinya, ini memastikan bahwa kolom dan baris yang tepat dipertahankan sama bahkan jika Anda menyalin rumus yang sama di baris yang berdekatan.

Anda lihat, referensi sel — ketika Anda merujuk ke sel A5 dari sel C5, misalnya — relatif secara default. Dalam hal ini, Anda sebenarnya merujuk ke sel yang lima kolom di sebelah kiri (C minus A) dan di baris yang sama (5). Ini disebut rumus relatif. Saat Anda menyalin rumus relatif dari satu sel ke sel lain, itu akan menyesuaikan nilai dalam rumus berdasarkan tempat pemindahannya. Namun terkadang, kami ingin nilai tersebut tetap sama tidak peduli apakah mereka dipindahkan atau tidak — dan kami dapat melakukannya dengan mengubah rumus menjadi rumus absolut.

Untuk mengubah rumus relatif (=A5+C5) menjadi rumus absolut, kita mendahului nilai baris dan kolom dengan tanda dolar, seperti ini: (=$A$5+$C$5) . (Pelajari lebih lanjut di halaman dukungan Microsoft Office di sini.)

12. Gunakan fungsi VLOOKUP untuk menarik data dari satu area lembar ke area lain.

Pernahkah Anda memiliki dua set data pada dua spreadsheet berbeda yang ingin Anda gabungkan menjadi satu spreadsheet?

Misalnya, Anda mungkin memiliki daftar nama orang di sebelah alamat email mereka di satu spreadsheet, dan daftar alamat email orang yang sama di sebelah nama perusahaan mereka di spreadsheet lainnya — tetapi Anda ingin nama, alamat email, dan nama perusahaan dari orang-orang itu untuk muncul di satu tempat.

Saya harus sering menggabungkan kumpulan data seperti ini — dan ketika saya melakukannya, VLOOKUP adalah formula masuk saya.

Namun, sebelum Anda menggunakan rumus, pastikan Anda memiliki setidaknya satu kolom yang muncul secara identik di kedua tempat. Telusuri kumpulan data Anda untuk memastikan kolom data yang Anda gunakan untuk menggabungkan informasi Anda sama persis, termasuk tidak ada spasi tambahan.

Rumusnya: =VLOOKUP(nilai pencarian, larik tabel, nomor kolom, Pencocokan perkiraan (TRUE) atau Pencocokan tepat (FALSE))

Rumus dengan variabel dari contoh kita di bawah ini: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

Dalam rumus ini, ada beberapa variabel. Berikut ini benar ketika Anda ingin menggabungkan informasi di Sheet 1 dan Sheet 2 ke Sheet 1.

• Nilai Pencarian : Ini adalah nilai identik yang Anda miliki di kedua spreadsheet. Pilih nilai pertama di spreadsheet pertama Anda. Dalam contoh berikut, ini berarti alamat email pertama dalam daftar, atau sel 2 (C2).
• Tabel Array : Array tabel adalah rentang kolom pada Lembar 2 yang akan Anda ambil datanya, termasuk kolom data yang identik dengan nilai pencarian Anda (dalam contoh kami, alamat email) di Lembar 1 serta kolom data yang Anda coba salin ke Sheet 1. Dalam contoh kami, ini adalah "Sheet2!A:B." “A” berarti Kolom A pada Sheet 2, yaitu kolom pada Sheet 2 dimana data yang identik dengan nilai pencarian kita (email) pada Sheet 1 terdaftar. "B" berarti Kolom B, yang berisi informasi yang hanya tersedia di Lembar 2 yang ingin Anda terjemahkan ke Lembar 1.
• Nomor Kolom : Ini memberi tahu Excel di kolom mana data baru yang ingin Anda salin ke Lembar 1 berada. Dalam contoh kami, ini akan menjadi kolom tempat "Rumah" berada. "Rumah" adalah kolom kedua dalam jangkauan kami kolom (tabel array), jadi nomor kolom kita adalah 2. [ Catatan : Jangkauan Anda bisa lebih dari dua kolom. Misalnya, jika ada tiga kolom di Sheet 2 — Email, Age, dan House — dan Anda masih ingin membawa House ke Sheet 1, Anda masih bisa menggunakan VLOOKUP. Anda hanya perlu mengubah “2” menjadi “3” sehingga akan menarik kembali nilai di kolom ketiga: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Pencocokan Perkiraan (TRUE) atau Pencocokan Persis (FALSE) : Gunakan FALSE untuk memastikan Anda hanya memasukkan nilai yang sama persis. Jika Anda menggunakan TRUE, fungsi akan menarik perkiraan kecocokan.

Pada contoh di bawah, Lembar 1 dan Lembar 2 berisi daftar yang menjelaskan informasi berbeda tentang orang yang sama, dan benang merah antara keduanya adalah alamat email mereka. Katakanlah kita ingin menggabungkan kedua kumpulan data sehingga semua informasi rumah dari Lembar 2 diterjemahkan ke Lembar 1.

Jadi ketika kita mengetikkan rumus =VLOOKUP(C2,Sheet2!A:B,2,FALSE) , kita membawa semua data rumah ke dalam Sheet 1.

Ingatlah bahwa VLOOKUP hanya akan menarik kembali nilai dari lembar kedua yang berada di sebelah kanan kolom yang berisi data identik Anda. Hal ini dapat menyebabkan beberapa keterbatasan, itulah sebabnya beberapa orang lebih memilih untuk menggunakan fungsi INDEX dan MATCH sebagai gantinya.

13. Gunakan rumus INDEX dan MATCH untuk menarik data dari kolom horizontal.

Seperti VLOOKUP, fungsi INDEX dan MATCH menarik data dari kumpulan data lain ke satu lokasi pusat. Inilah perbedaan utamanya:

• VLOOKUP adalah formula yang jauh lebih sederhana. Jika Anda bekerja dengan kumpulan data besar yang memerlukan ribuan pencarian, menggunakan fungsi INDEX dan MATCH akan secara signifikan mengurangi waktu buka di Excel.
• Rumus INDEX dan MATCH berfungsi dari kanan ke kiri, sedangkan rumus VLOOKUP hanya berfungsi sebagai pencarian kiri ke kanan. Dengan kata lain, jika Anda perlu melakukan pencarian yang memiliki kolom pencarian di sebelah kanan kolom hasil, maka Anda harus mengatur ulang kolom tersebut untuk melakukan VLOOKUP. Ini bisa membosankan dengan kumpulan data yang besar dan/atau menyebabkan kesalahan.

Jadi jika saya ingin menggabungkan informasi di Sheet 1 dan Sheet 2 ke Sheet 1, tetapi nilai kolom di Sheets 1 dan 2 tidak sama, maka untuk melakukan VLOOKUP, saya perlu mengganti kolom saya. Dalam hal ini, saya akan memilih untuk melakukan INDEX dan MATCH sebagai gantinya.

Mari kita lihat sebuah contoh. Katakanlah Lembar 1 berisi daftar nama orang dan alamat email Hogwarts mereka, dan Lembar 2 berisi daftar alamat email orang dan Patronus yang dimiliki setiap siswa. (Untuk non-penggemar Harry Potter di luar sana, setiap penyihir memiliki penjaga hewan yang disebut "Patronus" yang terkait dengannya.) Informasi yang ada di kedua lembar adalah kolom yang berisi alamat email, tetapi kolom alamat email ini dalam nomor kolom yang berbeda pada setiap lembar. Saya akan menggunakan rumus INDEX dan MATCH alih-alih VLOOKUP jadi saya tidak perlu mengganti kolom apa pun.

Jadi, apa rumusnya? Rumusnya sebenarnya adalah rumus MATCH yang bersarang di dalam rumus INDEX. Anda akan melihat saya membedakan rumus MATCH menggunakan warna yang berbeda di sini.

Rumus: =INDEX(array tabel, rumus MATCH)

Ini menjadi: =INDEX(array tabel, MATCH ( nilai_pencarian, array_pencarian))

Rumus dengan variabel dari contoh kita di bawah ini: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Berikut adalah variabelnya:

• Tabel Array : Rentang kolom pada Lembar 2 yang berisi data baru yang ingin Anda bawa ke Lembar 1. Dalam contoh kita, "A" berarti Kolom A, yang berisi informasi "Patronus" untuk setiap orang.
• Nilai Pencarian : Ini adalah kolom di Lembar 1 yang berisi nilai identik di kedua spreadsheet. Pada contoh berikut, ini berarti kolom “email” pada Sheet 1, yaitu Kolom C. Jadi: Sheet1!C:C.
• Lookup Array : Ini adalah kolom di Sheet 2 yang berisi nilai identik di kedua spreadsheet. Dalam contoh berikut, ini mengacu pada kolom “email” pada Lembar 2, yang kebetulan juga merupakan Kolom C. Jadi: Sheet2!C:C.

Setelah variabel Anda lurus, ketikkan rumus INDEX dan MATCH di sel paling atas kolom Patronus kosong pada Lembar 1, di mana Anda ingin informasi gabungan itu hidup.

14. Gunakan fungsi COUNTIF untuk membuat Excel menghitung kata atau angka dalam rentang sel apa pun.

Daripada menghitung secara manual seberapa sering nilai atau angka tertentu muncul, biarkan Excel yang bekerja untuk Anda. Dengan fungsi COUNTIF, Excel dapat menghitung berapa kali sebuah kata atau angka muncul dalam rentang sel apa pun.

Misalnya, katakanlah saya ingin menghitung berapa kali kata "Gryffindor" muncul di kumpulan data saya.

Rumusnya: =COUNTIF(rentang, kriteria)

Rumus dengan variabel dari contoh kita di bawah ini: =COUNTIF(D:D,”Gryffindor”)

Dalam rumus ini, ada beberapa variabel:

• Range : Rentang yang kita ingin rumus untuk menutupi. Dalam hal ini, karena kami hanya fokus pada satu kolom, kami menggunakan "D:D" untuk menunjukkan bahwa kolom pertama dan terakhir adalah D. Jika saya melihat kolom C dan D, saya akan menggunakan "C:D .”
• Kriteria : Berapa pun jumlah atau bagian teks yang Anda inginkan untuk dihitung oleh Excel. Hanya gunakan tanda kutip jika Anda ingin hasilnya berupa teks, bukan angka. Dalam contoh kita, kriterianya adalah "Gryffindor."

Cukup mengetikkan rumus COUNTIF di sel mana pun dan menekan "Enter" akan menunjukkan kepada saya berapa kali kata "Gryffindor" muncul di dataset.

15. Gabungkan sel menggunakan &.

Basis data cenderung membagi data untuk membuatnya seakurat mungkin. Misalnya, alih-alih memiliki kolom yang memperlihatkan nama lengkap seseorang, database mungkin memiliki data sebagai nama depan dan kemudian nama belakang di kolom terpisah. Atau, mungkin lokasi seseorang dipisahkan oleh kota, negara bagian, dan kode pos. Di Excel, Anda bisa menggabungkan sel dengan data yang berbeda menjadi satu sel dengan menggunakan tanda “&” di fungsi Anda.

Rumus dengan variabel dari contoh kita di bawah ini: =A2&” “&B2

Mari kita lihat rumusnya bersama-sama menggunakan sebuah contoh. Anggap saja kita ingin menggabungkan nama depan dan nama belakang menjadi nama lengkap dalam satu kolom. Untuk melakukan ini, pertama-tama kita meletakkan kursor kita di sel kosong di mana kita ingin nama lengkapnya muncul. Selanjutnya, kami akan menyorot satu sel yang berisi nama depan, ketik tanda “&”, lalu sorot sel dengan nama belakang yang sesuai.

Tapi Anda belum selesai — jika semua yang Anda ketik adalah =A2&B2, maka tidak akan ada spasi antara nama depan dan nama belakang orang tersebut. Untuk menambahkan ruang yang diperlukan, gunakan fungsi =A2&” “&B2 . Tanda kutip di sekitar ruang memberi tahu Excel untuk memberi spasi di antara nama depan dan belakang.

Untuk membuatnya benar untuk beberapa baris, cukup seret sudut sel pertama itu ke bawah seperti yang ditunjukkan pada contoh.

16. Tambahkan kotak centang.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

Berikut cara melakukannya.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.