MS.Office 2007, 2010, 2013 & 2016: 139 TRIK CERDAS EXCEL & RUMUS FUNGSI

Lihat Daftar Isi

Daftar Isi

Kata Pengantar........................................................................................................ v

Daftar Isi................................................................................................................. vii

BAB 1 TRIK PENGGUNAAN MENU HOME........................................................ 1

1.1 Group Clipboard................................................................................................... 2

1.1.1 Trik 1. Copy - Paste........................................................................................... 2

1.1.2 Trik 2. Copy Formulas...................................................................................... 3

1.1.3 Trik 3. Paste - No Border.................................................................................. 4

1.1.4 Trik 4. Paste - Keep Source Column Widths.................................................. 5

1.1.5 Trik 5. Paste - Transpose.................................................................................. 6

1.1.6 Trik 6. Paste - Formatting................................................................................. 6

1.1.7 Trik 7 Penggunaan Paste - Picture................................................................... 7

1.1.8 Trik 8. Penggunaan PAste - Linked Picture.................................................... 8

1.1.9 Trik 9. Penggunaan Copy - Paste Link........................................................... 9

1.1.10 Trik 10 Menggunakan Copy As Picture...................................................... 10

1.2 Group FONT........................................................................................................ 12

1.2.1 Trik 11 Pengaturan Font.................................................................................. 12

1.2.2 Trik 12. Pengaturan Font Size......................................................................... 12

1.2.3 Trik 13. Pengaturan All Border....................................................................... 13

1.2.4 Trik 14. Penggunaan Bold............................................................................... 14

1.2.5 Trik 15. Penggunaan Italic............................................................................... 15

1.2.6 Trik 16. Penggunaan Double Underline......................................................... 16

1.2.7 Trik 17. Penggunaan Fill Color....................................................................... 17

1.2.8 Trik 18. Penggunaan Font Color..................................................................... 18

1.3 Group Alignment................................................................................................. 19

1.3.1 Trik 19. Penggunaan Top Align...................................................................... 19

1.3.2 Trik 20. Penggunaan Middle Align................................................................. 21

1.3.3 Trik 21. Bottom Align dan Align Text Left.................................................... 22

1.3.4 Trik 22. Penggunaan Center............................................................................ 23

1.3.5 Trik 23. Penggunaan Align Text Right.......................................................... 24

1.3.6 Trik 24. Penggunaan Orientation.................................................................... 25

1.3.7 Trik 25. Penggunaan Wrap Text..................................................................... 26

1.3.8 Trik 26. Penggunaan Merge & Center............................................................ 27

1.4 Group Styles................................................28

1.4.1 Trik 27. Menggunakan Less Than.................................................................. 28

1.4.2 Trik 28. Menggunakan Greater Than............................................................ 31

1.4.3 Trik 29. Penggunaan Between......................................................................... 33

1.4.4 Trik 30. Menggunakan Equal To.................................................................... 35

1.4.5 Trik 31. Text That Contains............................................................................ 37

1.4.6 Trik 32. Duplicate Values................................................................................. 39

1.4.7 Trik 33. Top 10 Items....................................................................................... 41

1.4.8 Trik 34. Bottom 10 Items................................................................................. 43

1.4.9 Trik 35. Top 10%.............................................................................................. 46

1.4.10 Trik 36. Menggunakan Bottom 10%............................................................ 48

1.4.11 Trik 37. Menggunakan Above Average........................................................ 50

1.4.12 Trik 38. Menggunakan Below Average........................................................ 52

1.4.13 Trik 39. Data Bars.......................................................................................... 54

1.4.14 Trik 40. Color Scales....................................................................................... 56

1.4.15 Trik 41. Icon Sets............................................................................................ 58

1.4.16 Trik 42. Clear Ruler........................................................................................ 60

1.5 Group Cells.......................................................................................................... 61

1.5.1 Trik 43. Insert Cells.......................................................................................... 61

1.5.2 Trik 44. Delete................................................................................................... 63

1.5.3 Trik 45. Format................................................................................................. 65

1.5.4 Trik 46. Column Widht.................................................................................... 68

1.5.5 Trik 47. Row Height......................................................................................... 70

1.5.6 Trik 48. Hide Rows dan Unhide Rows........................................................... 72

1.5.7 Trik 49 Hide Column & Unhide Column....................................................... 74

1.5.8 Trik 50. Rename Sheet..................................................................................... 77

1.5.9 Trik 51. Move or copy Sheet............................................................................ 78

1.6 Group Number..................................................................................................... 79

1.6.1 Trik 52. Penggunaan Custom.......................................................................... 79

1.6.2 Trik 53. Menggunakan lambang Rupiah....................................................... 84

1.6.3 Trik 54. Comma Style....................................................................................... 85

1.6.4 Trik 55. Increase Decimal................................................................................. 87

1.6.5 Trik 56. Decrease Decimal................................................................................ 88

1.6.6 Trik 57. Menggunakan Special........................................................................ 89

1.6.7 Trik 58. Penggunaan Percent Style................................................................. 90

1.7 Group Editing..................................................................................................... 92

1.7.1 Trik 59. Editing SUM....................................................................................... 92

1.7.2 Trik 60. Editing Average.................................................................................. 95

1.7.3 Trik 61. Editing Count Number...................................................................... 97

1.7.4 Trik 62. Editing MAX.................................................................................... 100

1.7.5 Trik 63. Editing MIN...................................................................................... 101

1.7.6 Trik 64. Penggunaan Down........................................................................... 102

1.7.7 Trik 65. Clear All............................................................................................ 103

1.7.8 Trik 66. Clear Formats................................................................................... 104

1.7.9 Trik 67. Clear Contens................................................................................... 106

1.7.10 Trik 68. Clear Comment.............................................................................. 107

1.7.11 Trik 69. Sort & Filter.................................................................................... 108

1.7.12 Trik 70. Find $ Select.................................................................................... 110

BAB 2 TRIK PENGGUNAAN MENU INSERT................................................... 113

2.1 Group Chart....................................................................................................... 113

2.1.1 Trik 71. Chart Column.................................................................................. 113

2.1.2 Trik 72. Chart Line......................................................................................... 115

2.1.3 Trik 73. Chart Pie........................................................................................... 116

2.1.4 Trik 74. Chart Bar.......................................................................................... 118

2.1.5 Trik 75. Chart Area........................................................................................ 119

2.1.6 Trik 76. Chart Scatter.................................................................................... 121

2.1.7 Trik 77. Other Charts..................................................................................... 122

2.2 Group Tables...................................................................................................... 124

2.2.1 Trik 78. Table................................................................................................. 124

2.2.2 Trik 79. Pivot Table........................................................................................ 126

2.2.3 Trik 80. Picture............................................................................................... 128

2.3 Group Illustration ............................................................................................. 128

2.3.1 Trik 81. ClipArt.............................................................................................. 130

2.3.2 Trik 82. Screenshot......................................................................................... 131

2.3.3 Trik 83. Shapes............................................................................................... 134

2.3.4 Trik 84. SmartArt........................................................................................... 136

2.4 Group Links ...................................................................................................... 138

2.4.1 Trik 85. Hyperlink.......................................................................................... 138

2.5 Group Text......................................................................................................... 139

2.5.1 Trik 86. Tex Box.............................................................................................. 139

2.5.2 Trik 87. Header & Footer............................................................................... 140

2.5.3 Trik 88. WordArt............................................................................................ 142

2.5.4 Trik 89. Object................................................................................................ 143

2.6 Group Symbols.................................................................................................. 146

2.6.1 Trik 90. Equation............................................................................................ 146

2.6.2 Trik 91. Symbol............................................................................................... 148

BAB 3. TRIK PENGGUNAAN MENU PAGE LAYOUT.................................... 149

3.1 Group Themes.................................................................................................. 149

3.1.1 Trik 92. Themes............................................................................................. 149

3.2. Group Page Setup............................................................................................ 150

3.2.1 Trik 93. Print Area......................................................................................... 150

3.2.2 Trik 94. Breaks............................................................................................... 152

3.2.3 Trik 95. Background..................................................................................... 154

3.2.4 Trik 96. Print Titles........................................................................................ 156

3.2.5 Trik 97.Orientation........................................................................................ 158

3.3 Group Sheet Options......................................................................................... 160

3.3.1 Trik 98.Gridlines............................................................................................ 160

3.3.2 Trik 99.Headings............................................................................................ 161

3.4 Group Arrange.................................................................................................. 162

3.4.1 Trik 100. Rotate............................................................................................ 162

BAB 4 PENGGUNAAN MENU FORMULAS, MENU DATA,MENU REVIEW, & VIEW 164

4.1 Group Formulas................................................................................................ 164

4.1.1 Trik 101. Insert Function............................................................................... 164

4.1.2 Trik 102. Auto SUM....................................................................................... 167

4.1.3 Trik 103. Penggunaan Text............................................................................ 168

4.2 Menu Data......................................................................................................... 169

4.2.1 Trik 104.Text to Columns.............................................................................. 169

4.3 Group Review.................................................................................................... 173

4.3.1 Trik 105. New Comment................................................................................ 173

4.4 Group View........................................................................................................ 175

4.4.1 Trik 106. Freeze Panes.................................................................................... 175

4.5 Menu File............................................................................................................ 176

4.5.1 Trik 107 Print Out......................................................................................... 176

BAB 5 PENGGUNAAN FUNGSI EXCEL........................................................... 177

5.1 Rumus Fungsi................................................................................................... 177

5.1.1 Trik 108. Penggabungan Karakter.............................................................. 185

5.1.2 Trik 109.Penggabungan Fungsi IF untuk Aplikasi Terbilang.................. 178

5.1.3 Trik 110. ROUND......................................................................................... 181

5.1.4 Trik 111. Fungsi Gabungan IF, AND dan OR........................................... 183

5.1.5 Trik 112.Fungsi Gabungan IF, NOT, ISBLANK, dan VLOOKUP......... 184

5.1.6 Trik 113. Penggunaan ARITMATIKA....................................................... 185

5.1.7 Trik 114. Fungsi PRODUCT........................................................................ 186

5.1.8 Trik 115. Fungsi IF dengan 2 Kriteria ........................................................ 187

5.1.9 Trik 116. Fungsi IF dengan 3 Kriteria......................................................... 188

5.1.10 Trik 117. Fungsi IF dengan 4 Kriteria........................................................ 189

5.1.11 Trik 118. Fungsi IF dengan 5 Kriteria........................................................ 191

5.1.12 Trik 119. Fungsi IF dengan 6 Kriteria........................................................ 193

5.1.13 Trik 120. Fungsi LEN................................................................................... 195

5.1.14 Trik 121. Fungsi UPPER.............................................................................. 196

5.1.15 Trik 122. Fungsi LOWER............................................................................ 197

5.1.16 Trik 123. Fungsi COUNT............................................................................ 197

5.1.17 Trik 124. Penggunaan COS......................................................................... 199

5.1.18 Trik 125. Penggunaan SIN.......................................................................... 200

5.1.19 Trik 126. Penggunaan TAN......................................................................... 201

5.1.20 Trik 127. Penggunaan LOG......................................................................... 203

5.1.21 Trik 128. Menggunaan LN.......................................................................... 204

5.1.22 Trik 129. Penggunaan FACT...................................................................... 205

5.1.23 Trik 130. Menggunakan MODE................................................................. 206

5.1.24 Trik 131. MEDIA.......................................................................................... 208

5.1.25 Trik 132. QUARTILE.................................................................................. 209

5.1.26 Trik 133. Menggunakan SQRT................................................................... 210

5.1.27 Trik 134. Fungsi INT.................................................................................... 211

5.1.28 Trik 135. Fungsi POWER............................................................................ 212

5.1.29 Trik 136. Vlookup & Data Validation........................................................ 213

5.1.30 Trik 137. HLOOKUP.................................................................................... 220

5.1.31 Trik 138 Fungsi COUNTA......................................................................... 224

5.1.32 Trik 139 Fungsi COUNTBLANK............................................................... 225

BAB 2. LATIHAN TRIK EXCEL & RUMUS FUNGSI...................................... 227

Trik 1. Sort & Filter................................................................................................. 227

Trik 2. Protect Sheet................................................................................................ 229

Trik 3. Insert Function............................................................................................ 230

Trik 4. Chart............................................................................................................ 232

Trik 5. Above Average............................................................................................. 233

Trik 6. SmartArt...................................................................................................... 234

Trik 7. Fungsi COUNT & COUNTIF................................................................... 236

Trik 8. Fungsi Logika IF......................................................................................... 238

Trik 9. Menggunakan SUM, MAX, MIN, & AVERAGE................................... 239

Trik 10. Data Validation, IF, VLOOKUP, SUM & ISBLANK........................... 240

Tentang Penulis..................................................................................................... 244

Sinopsis

Buku ini menjelaskan trik-trik cerdas terbaru bagaimana cara menguasai Excel dan rumus fungsi dengan baik. Saya yakin setelah anda mempelajari buku ini, anda akan menguasai materi di Excel 2010 maupun 2007, karena buku ini sangat mudah untuk dipelajari yaitu di dalamnya termuat petunjuk penggunaan dengan metode step by step, gambaran ilustrasi yang dibantu dengan panah penunjuk, agar kita mudah memahami materi dan seperti biasa setiap akhir bab terdapat latihan agar dapat melatih kita mengingat trik yang telah dipelajari sebelumnya.

Setiap trik Excel & Rumus Fungsi dijelaskan secara gamblang dan jelas sehingga kita mampu memahami maksud dari petunjuk penggunaan setiap ikon. Dengan membaca, menganalisa dan mempraktekkan kita akan mahir dalam hitungan hari dengan mudah.

sample buku

mengaktifkan sebagian Cell dan Mengunci Sheet

Agar Cell yang kita pilih dapat aktif untuk melakukan edit data dan yang lainnya terkunci dapat kita lakukan sebagai berikut. Sorot atau Blok Cell yang berisi data à klik mouse kanan pada area blokan à kemudian klik Format Cells, maka akan muncul kotak dialog Format Cells. Untuk lebih jelasnya perhatikan gambaran ilustrasi di bawah ini.

Pada kotak dialog Format Cells pilih dan kliklah Protectionà hilangkan tanda Ceklis pada Locked dengan cara mengklik à apabila kita ceklis maka cell tersebut tidak aktif dan apabila kita hilangkan tanda ceklisnya hal tersebut berarti Cells aktif dan dapat kita gunakan dalam pengeditan à klik OK. Agar lebih jelasnya perhatikan gambaran ilustrasi di bawah ini.

Langkah selanjutnya kita akan mengunci Sheet dengan cara klik sub Menu Review à klik Protect Sheet à pada kotak dialog Protect Sheet ketiklah Pasword yang kita inginkan dan pasword ini harus kita ingat agar kita dapat membukanya sewaktu-waktu dibutuhkan untuk pengeditan à selanjutnya klik OK dan kotak dialog Confirm Password akan tampil. Agar lebih jelasnya perhatikan gambar di bawah ini.

Pada kotak dialog Confirm Password yaitu pada bagian Reenter Password to Procced ketik ulanglah password tadi à klik OK.

Untuk membuka sheet yang telah dikunci dapat dilakukan dengan cara klik Menu Review à klik tab Unprotect Sheet à pada kotak dialog Unprotect Sheet ketik password yang tadi dan apabila terjadi kesalahan pengetikan password, maka sheet tidak akan terbuka.. agar lebih jelasnya perhatikan gambar di bawah ini.

Apabila terjadi kesalahan pengetikan password maka akan muncul kotak dialog seperti gambar berikut ini.

Untuk mengedit data dapat kita lakukan pada Cells yang tidak terkunci dan pada cells yang terkunci tidak dapat kita ubah atau kita edit. Untuk lebih jelasnya perhatikan gambar di bawah ini.