Cara Menggunakan Excel Pro: 18 Tips, Trik, & Shortcuts 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 tips, trik, dan pintasan terbaik yang dapat Anda gunakan sekarang untuk membawa game Excel Anda ke level berikutnya. Tidak diperlukan pengetahuan Excel tingkat lanjut.

Unduh 9 Template Excel untuk Pemasar [Free Kit]

Apa itu Excel?

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

Excel digunakan untuk apa?

Excel digunakan untuk mengatur, memfilter, dan memvisualisasikan data dalam jumlah besar. Ini paling sering digunakan dalam akuntansi, tetapi dapat digunakan oleh hampir semua profesional untuk mengelola kumpulan data yang panjang dan berat. Contoh aplikasi Excel termasuk neraca, anggaran, kalender editorial, dan kalkulator data.

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 secara otomatis melihat jumlah, rata-rata, dan total. 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.

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 masuk ke cara menambahkan hal-hal seperti beberapa kolom dan baris.
• Menjaga judul kolom dan baris terlihat saat Anda menggulir melewatinya dalam spreadsheet, sehingga Anda tahu data apa yang Anda isi saat Anda bergerak lebih jauh ke bawah dokumen.

Excel Tips

1. Gunakan tabel Pivot untuk mengenali dan memahami data.
2. Tambahkan lebih dari satu baris atau kolom.
3. Gunakan filter untuk menyederhanakan data Anda.
4. Hapus titik atau kumpulan data duplikat.
5. Transpos baris menjadi kolom.
6. Pisahkan informasi teks antar kolom.
7. Gunakan rumus ini untuk perhitungan sederhana.
8. Dapatkan rata-rata angka di sel Anda.
9. Gunakan pemformatan bersyarat untuk membuat sel secara otomatis berubah warna berdasarkan data.
10. Gunakan rumus IF Excel untuk mengotomatisasi fungsi Excel tertentu.
11. Gunakan tanda dolar untuk menjaga formula satu sel tetap sama di mana pun ia bergerak.
12. Gunakan fungsi VLOOKUP untuk menarik data dari satu area lembar ke area lain.
13. Gunakan rumus INDEX dan MATCH untuk menarik data dari kolom horizontal.
14. Gunakan fungsi COUNTIF untuk membuat Excel menghitung kata atau angka dalam rentang sel apa pun.
15. Gabungkan sel menggunakan ampersand.
16. Tambahkan kotak centang.
17. Hyperlink sel ke situs web.
18. Tambahkan menu tarik-turun.

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 data. 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 berdasarkan 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 berupa 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 bagaimana 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 dihitung.

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 mengurangi data untuk 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 mengeklik 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 berada 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 dataset 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). Dibutuhkan 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 "Dibatasi" 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. Dalam 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 sebuah 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 kami 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.

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 lainnya, 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) .

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 orang-orang itu untuk muncul di satu tempat.

Saya harus banyak mengombinasikan 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 di Sheet 2, yaitu kolom di Sheet 2 tempat data yang identik dengan nilai pencarian kami (email) di 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 kami 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 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 di 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 ada 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. Berikut adalah 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 penggemar non-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 ( lookup_value, lookup_array))

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.

Alih-alih 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 keduanya 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 kumpulan data.

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.

Jika Anda menggunakan lembar Excel untuk melacak data pelanggan dan ingin mengawasi sesuatu yang tidak dapat diukur, Anda dapat menyisipkan kotak centang ke dalam kolom.

Misalnya, jika Anda menggunakan lembar Excel untuk mengelola prospek penjualan Anda dan ingin melacak apakah Anda menelepon mereka di kuartal terakhir, Anda dapat memiliki "Dipanggil kuartal ini?" kolom dan centang sel di dalamnya saat Anda memanggil klien masing-masing.

Berikut cara melakukannya.

Sorot sel yang ingin Anda tambahi kotak centang di spreadsheet Anda. Kemudian, klik PENGEMBANG. Kemudian, di bawah KONTROL FORMULIR, klik kotak centang atau lingkaran pilihan yang disorot pada gambar di bawah.

Setelah kotak muncul di sel, salin, sorot sel tempat Anda juga ingin menampilkannya, lalu tempel.

17. Hyperlink sel ke situs web.

Jika Anda menggunakan lembar untuk melacak metrik media sosial atau situs web, akan sangat membantu jika memiliki kolom referensi dengan tautan yang dilacak setiap baris. Jika Anda menambahkan URL langsung ke Excel, itu akan otomatis dapat diklik. Tapi, jika Anda harus membuat hyperlink kata-kata, seperti judul halaman atau judul posting yang Anda lacak, begini caranya.

Sorot kata-kata yang ingin Anda hyperlink, lalu tekan Shift K. Dari sana akan muncul kotak yang memungkinkan Anda menempatkan URL hyperlink. Salin dan tempel URL ke dalam kotak ini dan tekan atau klik Enter.

Jika pintasan tombol tidak berfungsi karena alasan apa pun, Anda juga dapat melakukannya secara manual dengan menyorot sel dan mengklik Sisipkan > Hyperlink .

18. Tambahkan menu tarik-turun.

Terkadang, Anda akan menggunakan spreadsheet untuk melacak proses atau hal kualitatif lainnya. Daripada menulis kata-kata ke lembar Anda berulang-ulang, seperti "Ya", "Tidak", "Tahap Pelanggan", "Prospek Penjualan", atau "Prospek", Anda dapat menggunakan menu tarik-turun untuk menandai hal-hal deskriptif dengan cepat tentang kontak Anda atau apa pun yang Anda inginkan. sedang melacak.

Berikut cara menambahkan drop-down ke sel Anda.

Sorot sel yang Anda inginkan dari drop-down, lalu klik menu Data di navigasi atas dan tekan Validasi.

Dari sana, Anda akan melihat kotak Pengaturan Validasi Data terbuka. Lihat opsi Allow, lalu klik Lists dan pilih Drop-down List. Centang tombol tarik-turun Dalam Sel, lalu tekan OK.

Gunakan Excel untuk Mengotomatiskan Proses di Tim Anda

Bahkan jika Anda bukan seorang akuntan, Anda masih bisa menggunakan Excel untuk mengotomatisasi tugas dan proses di tim Anda. Dengan tips dan trik yang kami bagikan dalam posting ini, Anda pasti akan menggunakan Excel sepenuhnya dan mendapatkan hasil maksimal dari perangkat lunak untuk mengembangkan bisnis Anda.

Catatan Editor: Posting ini awalnya diterbitkan pada Agustus 2017 tetapi telah diperbarui untuk kelengkapan.