26 Tips Excel untuk Menjadi Spreadsheet Pro

Katakanlah Anda mengubah tidak hanya pembungkus dalam sel, tetapi juga seluruh tampilan—font, warna, apa pun. Dan Anda ingin menerapkannya ke banyak sel lainnya. Caranya adalah dengan Format Painter tool yang ada di tab Home yang bentuknya seperti kuas cat.

Pilih penjualan yang Anda suka, klik ikon , lalu klik sel lain untuk melukis dalam format—mereka akan cocok dalam tampilan, bukan konten. Ingin menerapkannya ke banyak tab? Klik dua kali ikon kuas cat, lalu klik beberapa sel.

Mengetik ke dalam sel spreadsheet dapat membuat frustasi, karena default untuk teks yang Anda ketik adalah untuk melanjutkan selamanya, tanpa menutup kembali ke baris baru. Anda dapat mengubah itu. Buat baris baru dengan mengetik Alt+Enter (menekan Enter saja akan mengeluarkan Anda dari sel). Atau, klik opsi Bungkus Teks di bawah tab Beranda di bagian atas layar, yang berarti semua teks terbungkus tepat di tepi sel tempat Anda berada. Ubah ukuran baris/kolom dan bungkus ulang teks agar pas.

Jika Anda memiliki beberapa sel yang memiliki teks berlebih, pilih semuanya sebelum Anda mengklik Bungkus Teks. Atau, pilih semua sel bahkan sebelum Anda mengetiknya dan klik Bungkus Teks. Maka apa pun yang Anda ketik akan dibungkus di masa depan.

Ini adalah no-brainer, tapi begitu mudah diabaikan. Anda mulai mengetik serangkaian hal yang berulang seperti tanggal (1/1/20, 1/2/20, 1/3/20, dll.) dan Anda tahu bahwa Anda akan menjalani hari yang panjang. Sebagai gantinya, mulailah rangkaian dan pindahkan kursor pada layar ke bagian kanan bawah sel terakhir—pegangan isian. Ketika berubah menjadi tanda plus ( + ), klik dan seret ke bawah untuk memilih semua sel yang perlu Anda isi. Mereka akan secara ajaib mengisi menggunakan pola yang Anda mulai. Itu juga bisa naik kolom, atau kiri atau kanan pada satu baris.

Lebih baik lagi—Anda dapat Mengisi Otomatis tanpa banyak pola. Sekali lagi, pilih sel atau sel, pindah ke gagang isian, klik kanan, dan seret. Anda akan mendapatkan menu pilihan. Semakin banyak data yang Anda masukkan pada awalnya, semakin baik opsi Fill Series untuk membuat opsi AutoFill Anda. Lihat tutorial Microsoft ini.

Flash Fill akan dengan cerdas mengisi kolom berdasarkan pola data yang dilihatnya di kolom pertama (akan membantu jika baris atas adalah baris header yang unik). Misalnya, jika kolom pertama adalah semua nomor telepon yang diformat seperti "2125034111" dan Anda ingin semuanya terlihat seperti "(212)-503-4111," mulailah mengetik. Di sel kedua, Excel harus mengenali pola dan menampilkan apa yang menurut Anda diinginkan. Tekan saja enter untuk menggunakannya.

Ini berfungsi dengan angka, nama, tanggal, dll. Jika sel kedua tidak memberi Anda rentang yang akurat, ketik lagi—polanya mungkin sulit dikenali. Lalu pergi ke tab Data dan klik tombol Flash Fill. Lihat tutorial Microsoft ini untuk lebih lanjut.

Ada cara yang jauh lebih cepat untuk memilih kumpulan data daripada menggunakan mouse dan menyeret kursor, terutama dalam spreadsheet yang dapat berisi ratusan ribu baris atau kolom. Klik di sel pertama yang ingin Anda pilih dan tahan Ctrl+Shift , lalu tekan panah bawah untuk mendapatkan semua data di kolom di bawah, panah atas untuk mendapatkan semua data di atas, atau panah kiri atau kanan untuk memasukkan semuanya baris (ke kiri atau kanan, tentu saja). Gabungkan arahnya, dan Anda bisa mendapatkan seluruh kolom serta semua yang ada di baris di kiri atau kanan. Itu hanya akan memilih sel dengan data (bahkan data yang tidak terlihat).

Jika Anda menggunakan Ctrl+Shift+End , kursor akan melompat ke sel sebelah kanan terendah dengan data, memilih semua yang ada di antaranya, bahkan sel kosong. Jadi jika kursor berada di sel kiri atas (A1), itu saja.

Ctrl+Shift+* (tanda bintang) mungkin lebih cepat, karena akan memilih seluruh kumpulan data sel yang berdekatan, tetapi akan berhenti di sel yang kosong.

Katakanlah Anda memiliki kolom yang penuh dengan nama, pertama di samping yang terakhir, tetapi Anda ingin dua kolom yang memisahkannya. Pilih datanya, lalu pada tab Data (di bagian atas) klik Text to Columns. Pilih untuk memisahkannya dengan pembatas (berdasarkan spasi atau koma—cocok untuk nilai data CSV) atau dengan lebar tetap. Lebar tetap digunakan ketika semua data dijejalkan ke dalam kolom pertama, tetapi dipisahkan oleh sejumlah spasi atau titik yang tetap. Sisanya seperti sulap, dengan opsi tambahan untuk nomor tertentu.

Anda punya banyak baris. Anda ingin mereka menjadi kolom. Atau sebaliknya. Anda akan menjadi gila memindahkan hal-hal sel demi sel. Salin data itu, pilih Tempel Spesial , centang kotak Transpose , dan klik OK untuk menempelkan ke orientasi yang berbeda. Kolom menjadi baris, baris menjadi kolom.

Untuk beberapa alasan, Anda mungkin harus menulis hal yang sama berulang-ulang dalam sel di lembar kerja. Itu menyiksa. Cukup klik seluruh rangkaian sel, baik dengan menyeret kursor Anda, atau dengan menahan tombol Ctrl saat Anda mengklik masing-masing sel. Ketik di sel terakhir, lalu tekan Ctrl+Enter (bukan Enter saja)—apa yang Anda ketik masuk ke setiap sel yang dipilih.

Ini juga berfungsi dengan rumus, dan akan mengubah referensi sel agar berfungsi dengan baris/kolom apa pun tempat sel lain berada.

Katakanlah Anda memiliki sejumlah besar angka dalam format desimal yang ingin Anda tampilkan sebagai persentase. Masalahnya, angka 1 seharusnya tidak 100%, tetapi itulah yang diberikan Excel jika Anda cukup mengklik tombol Percent Style (atau tekan Ctrl-Shift-% ).

Anda ingin bahwa 1 menjadi 1%. Jadi Anda harus membaginya dengan 100. Di situlah Paste Special masuk.

Pertama, ketik 100 dalam sel dan salin. Kemudian, pilih semua nomor yang ingin Anda format ulang, pilih Tempel Spesial , klik tombol radio " Bagi ", dan boom berbunyi : Anda telah mengubah angka menjadi persentase. Ini juga berfungsi untuk menambah, mengurangi, atau mengalikan angka secara instan, tentu saja.

Anda dapat menempatkan grafik di setiap elemen bagan Excel. elemen apa pun . Setiap batang, sepotong kue, dll., dapat mendukung citranya sendiri. Misalnya, di atas, ada bendera negara bagian South Dakota pada diagram lingkaran (ditempatkan dengan memilih irisan, menggunakan menu fly-out Opsi Seri , dan memilih " Isi gambar atau tekstur "), ditambah logo PCMag yang disematkan (diletakkan dengan Sisipkan tombol Gambar tab ). Anda bahkan dapat menggunakan " tanpa isi " sama sekali, yang menyebabkan potongan yang hilang itu.

Clip art dapat dipotong dan ditempelkan ke suatu elemen—uang kertas untuk menunjukkan pengeluaran uang, tetesan air untuk biaya pipa, hal-hal semacam itu. Mencampur dan mencocokkan terlalu banyak elemen grafis membuatnya tidak mungkin untuk dibaca, tetapi opsi yang Anda miliki layak untuk diutak-atik digital. Biarkan desainer grafis residen Anda memeriksanya sebelum Anda menggunakannya.

Excel memiliki lebih banyak jenis bagan daripada yang dimiliki Jimmy Carter, tetapi hampir tidak mungkin menemukan bagan default yang sempurna untuk presentasi Anda. Untungnya, kemampuan Excel untuk menyesuaikan semua grafik patut dicontoh. Tetapi ketika Anda harus membuatnya kembali , itu menyakitkan. Tidak harus begitu. Simpan bagan asli Anda sebagai templat.

Setelah bagan disempurnakan, klik kanan di atasnya. Pilih Simpan sebagai Template . Simpan file dengan ekstensi CRTX di folder Microsoft Excel Templates default Anda. Setelah selesai, menerapkan template adalah kue. Pilih data yang ingin Anda bagan, buka tab Sisipkan , klik Bagan yang Direkomendasikan , lalu tab Semua Bagan , dan folder Templat . Di kotak Template Saya, pilih salah satu yang akan diterapkan, lalu klik OK .

Beberapa elemen, seperti teks sebenarnya dalam legenda dan judul, tidak akan diterjemahkan kecuali jika mereka adalah bagian dari data yang dipilih. Anda akan mendapatkan semua pilihan font dan warna, grafik yang disematkan, bahkan opsi seri (seperti bayangan jatuh atau cahaya di sekitar elemen bagan).

Yang ini, disebut 3D Sum, berfungsi saat Anda memiliki beberapa lembar dalam buku kerja yang semuanya memiliki tata letak dasar yang sama, katakanlah laporan triwulanan atau tahunan. Misalnya, di sel B3, Anda selalu memiliki jumlah dolar untuk minggu yang sama dari waktu ke waktu.

Pada lembar kerja baru di buku kerja, masuk ke sel dan ketik rumus seperti =sum('Y1:Y10'!B3). Itu menunjukkan formula SUM (menjumlahkan semuanya) untuk semua lembar yang berjudul Y1 hingga Y10 (bernilai 10 tahun), dan melihat sel B3 di masing-masing lembar. Hasilnya akan menjadi jumlah dari semua 10 tahun. Ini adalah cara yang baik untuk membuat spreadsheet master yang merujuk kembali ke data yang selalu berubah.

Sangat mudah untuk menyembunyikan baris atau kolom—cukup pilih semuanya dengan mengklik kepala huruf atau angka, klik kanan, dan pilih "Sembunyikan." (Anda dapat memperlihatkannya dengan memilih kolom di kedua sisi sekaligus, mengklik kanan, dan memilih "Tampilkan"). Tetapi bagaimana jika Anda hanya memiliki sedikit bagian dari data yang ditempatkan secara tidak nyaman yang ingin Anda sembunyikan, tetapi Anda masih ingin dapat bekerja dengannya? Mudah. Sorot sel, klik kanan, dan pilih Format Sel . Pada tab Angka di bagian atas, buka Kategori dan pilih " Kustom ." Ketik tiga titik koma ( ;;; ) di bidang Jenis:. Klik Oke. Sekarang angkanya tidak terlihat, tetapi Anda masih bisa menggunakannya dalam rumus.

Buku kerja Excel biasa Anda—file yang sedang Anda kerjakan—bisa dimuat dengan banyak lembar kerja (setiap lembar ditunjukkan oleh tab di bagian bawah, yang bisa Anda beri nama). Sembunyikan lembar jika Anda mau, daripada menghapusnya, membuat datanya tetap tersedia tidak hanya untuk referensi, tetapi juga tersedia untuk rumus di lembar lain di buku kerja. Klik kanan tab lembar bawah dan pilih Sembunyikan . Saat Anda perlu menemukannya lagi, Anda harus membuka tab Lihat di bagian atas, klik Perlihatkan , dan pilih nama lembar dari daftar yang muncul.

Ada juga tombol Sembunyikan pada menu tab Lihat di bagian atas. Apa yang terjadi ketika Anda mengklik itu? Ini menyembunyikan seluruh buku kerja yang Anda gunakan. Sepertinya Anda menutup file, tetapi Excel terus berjalan. Saat Anda menutup program, itu akan menanyakan apakah Anda ingin menyimpan perubahan ke buku kerja tersembunyi. Saat Anda membuka file, Excel memberi Anda apa yang tampak seperti buku kerja kosong—sampai Anda mengklik Perlihatkan lagi.

Saat Anda memperlihatkan seluruh buku kerja, Anda mungkin akan melihat buku kerja terdaftar yang tidak Anda sembunyikan: file Personal.XLSB. Ini sebenarnya adalah buku kerja pribadi Excel yang dibuat untuk Anda; itu dibuka sebagai buku kerja tersembunyi setiap kali Excel dimulai. Alasan untuk menggunakannya? Makro .

Saat Anda membuat makro, makro tidak berfungsi di setiap lembar bentang yang Anda buat secara default (seperti yang dilakukan di Microsoft Word)—makro terikat ke buku kerja tempat ia dibuat. Namun, jika Anda menyimpan makro di Pribadi. XLSB, itu akan tersedia sepanjang waktu, di semua file spreadsheet Anda.

Caranya adalah, saat Anda merekam makro, di bidang "Simpan makro di", pilih "Buku Kerja Makro Pribadi". (Rekam makro dengan mengaktifkan tab Pengembang—buka tab File , pilih Opsi , klik Sesuaikan Pita , lalu di kotak Tab Utama , centang Pengembang , klik OK .)

Seluruh buku telah dikhususkan untuk PivotTabels. Itu adalah ringkasan dari kumpulan data raksasa Anda yang membuatnya lebih mudah untuk mengurai info berdasarkan titik referensi Anda. Misalnya, jika Anda memiliki seluruh rangkaian nilai untuk semua siswa Anda di semua tes sepanjang tahun, PivotTable dapat membantu Anda mempersempit beberapa hal menjadi satu siswa selama satu bulan. Siapa pun yang memiliki data besar harus bermain dengannya (buat salinan data asli untuk dimainkan terlebih dahulu).

Untuk membuatnya, periksa apakah semua kolom dan baris diberi judul sebagaimana mestinya, lalu pilih PivotTable pada tab Sisipkan . Lebih baik lagi, coba opsi PivotTable yang Direkomendasikan untuk melihat apakah Excel dapat memilih jenis yang tepat untuk Anda. Atau coba PivotChart , yang membuat PivotTable dengan grafik yang disertakan agar lebih mudah dipahami.

Melihat sejumlah besar data dan bertanya-tanya di mana sorotannya? Siapa yang memiliki skor tertinggi (atau terendah), lima besar, dll.? Pemformatan Bersyarat Excel akan melakukan segalanya mulai dari meletakkan batas di sekitar sorotan hingga mengkode warna seluruh tabel. Itu bahkan akan membuat grafik ke dalam setiap sel sehingga Anda dapat memvisualisasikan bagian atas dan bawah rentang angka secara sekilas. (Di atas, angka tertinggi dalam warna hijau cepat, terendah dalam warna merah berhenti, dengan spektrum di antaranya.) Gunakan sub-menu Aturan Sel yang Disorot untuk membuat lebih banyak aturan untuk mencari sesuatu, seperti teks yang berisi string tertentu kata, tanggal berulang, nilai duplikat, dll. Bahkan ada opsi lebih dari/kurang dari sehingga Anda dapat membandingkan perubahan angka.

Membuat spreadsheet untuk digunakan orang lain? Jika Anda ingin membuat menu drop-down pilihan untuk digunakan dalam sel tertentu (sehingga mereka tidak dapat mengacaukannya!), itu mudah. Sorot sel, buka tab Data , dan klik Validasi Data . Di bawah "Izinkan:" pilih " Daftar ." Kemudian di bidang "Sumber:", ketikkan daftar , dengan koma di antara opsi. Atau, klik tombol di sebelah bidang Sumber dan kembali ke lembar yang sama untuk memilih seri data—ini adalah cara terbaik untuk menangani daftar besar. Anda dapat menyembunyikan data itu nanti, itu akan tetap berfungsi. Validasi Data juga merupakan cara yang baik untuk membatasi data yang dimasukkan—misalnya, memberikan rentang tanggal, dan orang tidak dapat memasukkan tanggal apa pun sebelum atau sesudah yang Anda tentukan. Anda bahkan dapat membuat pesan kesalahan yang akan mereka lihat.

Excel membuatnya sangat mudah untuk mengambil tangkapan layar dari program terbuka lainnya di desktop Anda dan memasukkannya ke dalam lembar kerja. Cukup buka tab Insert , pilih Screenshot , dan Anda akan mendapatkan menu drop-down yang menampilkan thumbnail dari semua program yang terbuka. Pilih satu untuk menyisipkan gambar berukuran penuh. Ubah ukurannya sesuai keinginan.

Tiga puluh lima tahun yang lalu, pemikiran untuk menempatkan data dari Excel ke Word atau PowerPoint sangat mengejutkan di dunia Office Suites. Hari ini, tidak ada apa-apa. Baik Anda mengambil sel data atau bagan grafis lengkap, salin dan tempel ke program lain. Hal yang harus diperhatikan adalah, ini adalah proses link-and- embed —jika Anda mengubah data di spreadsheet, itu juga akan berubah di Word DOC atau PowerPoint PPT. Jika Anda tidak menginginkannya, tempel sebagai grafik. Gunakan alat Khusus Tempel milik Word untuk itu. Atau, saat mengambilnya dari Excel, buka tab Beranda di bagian atas, pilih menu Salin , dan gunakan opsi Salin sebagai Gambar . Kemudian Anda dapat menempelkan grafik ke program apa pun.

Saat Anda menulis rumus, Anda mereferensikan sel berdasarkan posisinya, seperti A1. Jika Anda menyalin rumus dan menempelkannya di sel berikutnya ke bawah, Excel akan menggeser sel yang direferensikan itu, sehingga akan tertulis A2. Untuk mencegah pergeseran, gunakan tanda dolar ($). Ketik $A1 dan potong dan tempel ke sel baru, misalnya, yang mencegah pergeseran di kolom (A); A$1 mencegah pergeseran pada baris (1), dan $A$1 mencegah perubahan pergeseran ke segala arah saat menyalin rumus.

Ini berguna ketika Anda memiliki satu sel untuk digunakan dalam sejumlah besar rumus. Katakanlah Anda ingin membagi semuanya dengan 100. Anda dapat membuat rumus seperti =(A1/100), tetapi itu berarti Anda tidak dapat mengubah 100 dengan mudah secara keseluruhan. Masukkan 100 ke dalam sel B1 dan gunakan =(A1/B1)—tetapi kemudian ketika Anda memotong dan menempelkannya, hasilnya menjadi =(A2/B2), lalu =(A3/B3), dll. $ memperbaiki bahwa: =(A1/$B$1) dapat dipotong dan ditempelkan ke bawah satu baris, tetapi referensi $B$1 tidak pernah berubah. Kemudian Anda dapat mengubah nilai 100 dalam sel sesuai kebutuhan untuk bereksperimen dengan perubahan lainnya.

Jika Anda tidak tahu persis info apa yang ingin Anda terapkan ke data di Excel, coba menu Analisis Cepat untuk menjalankan opsi dengan cepat. Pilih data dan klik pada kotak Analisis Cepat yang muncul di kanan bawah. Anda akan mendapatkan menu yang muncul dengan opsi untuk menerapkan pemformatan bersyarat dengan cepat, membuat bagan, menangani total, menampilkan grafik mini, dan banyak lagi.

Excel, seperti perangkat lunak hebat lainnya, memiliki banyak pintasan keyboard yang luar biasa. Berikut adalah beberapa yang terbaik.

Ctrl+; —Menyisipkan tanggal hari ini.
Ctrl+Shift+: —Menyisipkan waktu saat ini (titik dua adalah apa yang ada dalam pembacaan jam, seperti 12:00).
Ctrl+Shift+# —Mengubah format tanggal.
Ctrl+5 —Menerapkan coretan ke teks dalam sel.
Ctrl+0 — Menyembunyikan kolom saat ini.
Ctrl+9 —Menyembunyikan baris saat ini.
Ctrl+F6 —Beralih di antara buku kerja yang terbuka (yaitu, membuka file Excel di jendela yang berbeda).
Ctrl+` —Itu tanda aksen, naik dengan tombol 1. Kombo ini mengaktifkan tampilan di lembar untuk menampilkan semua rumus.
Ctrl+PageUp atau PageDown —Pergeseran cepat antar lembar dalam buku kerja yang sedang dibuka.
F2 —Mulai mengedit sel yang dipilih saat ini (jauh lebih cepat daripada mengklik dua kali).
Shift+F10 —Membuka menu klik kanan untuk sel tempat Anda berada.

Punya angka di spreadsheet yang ingin Anda hitung dengan cepat, tanpa perlu repot membuka sel baru dan membuat rumus SUM untuk pekerjaan itu? Excel sekarang menawarkan cara cepat untuk melakukannya. Klik sel pertama, tahan tombol Ctrl , dan klik sel kedua. Lihat bilah status di bagian bawah dan Anda akan melihat jumlah sel yang dihitung untuk Anda.

Pertahankan jari Anda di Ctrl dan klik sel sebanyak yang Anda suka, bilah status akan terus menampilkan jumlah untuk semua sel. (Klik sel dengan huruf/kata sebagai konten, itu akan diabaikan.) Lebih baik lagi, klik kanan bilah status untuk mendapatkan menu Customize Status Bar dan Anda dapat memilih untuk menambahkan elemen lain yang dapat dihitung cepat seperti ini, seperti seperti melihat rata-rata atau hitungan berapa banyak sel yang Anda klik (atau jumlah numerik, yaitu berapa banyak sel yang Anda klik yang benar-benar memiliki angka).

Bekerja dengan kumpulan data besar dalam spreadsheet bisa jadi sulit, terutama saat Anda menggulir ke atas/bawah atau kiri/kanan dan baris dan kolom mungkin sulit dilacak. Ada trik sederhana untuk itu jika Anda memiliki baris atau kolom header, di mana baris/kolom pertama memiliki deskriptor. Anda membekukannya sehingga saat Anda menggulir, baris dan/atau kolom tersebut (atau beberapa baris dan/atau kolom) tidak bergerak.

Buka tab View dan cari Freeze Panes . Anda dapat dengan mudah membekukan baris atas (pilih Freeze Top Row ) atau kolom pertama (pilih Freeze First Column ). Anda dapat melakukan keduanya sekaligus dengan mengklik sel di B2 dan memilih Freeze Panes . Di sinilah kesenangannya—pilih sel lain dan juga Bekukan semua panel di atas dan kirinya. Pilih sel C3 misalnya dan dua baris di atas dan dua kolom di sebelah kiri tidak akan bergulir. Anda dapat melihatnya pada tangkapan layar di atas, yang ditunjukkan oleh garis kisi yang digelapkan.

Saat Anda ingin menghilangkan pembekuan, Anda cukup memilih Unfreeze Panes dari menu.

Spreadsheet bisa sangat besar, dan Anda mungkin harus berinteraksi dengan berbagai area spreadsheet pada waktu yang tampaknya bersamaan, seperti memotong dan menempelkan info dari atas ke bawah berulang kali. Jika ratusan ribu sel, pengguliran yang diperlukan bisa membuat Anda mual. Atau, Anda bisa membuka jendela kedua di desktop dengan tampilan spreadsheet yang sama persis. Mudah. Di tab Lihat , klik Jendela Baru . Anda juga dapat mengeklik Atur Semua untuk mengurutkannya di layar dengan cara yang sesuai untuk Anda. Anda dapat melihatnya diatur secara horizontal di atas. Kemudian, ketik sesuatu ke dalam sel di satu jendela, Anda bisa melihatnya muncul di jendela lain. Trik ini sangat berguna jika Anda memiliki dua monitor.

Pikirkan Anda tahu segalanya tentang Microsoft Word? Berikut adalah beberapa trik tersembunyi dan peretasan hemat waktu yang perlu Anda ketahui tentang aplikasi pengolah kata Redmond.