Shortcut To Delete and Insert Row / Column

Untuk melakukan insert row , dengan cara biasa adalah sbb :
1. Block Row
2. Masuk ke Insert
3. Pilih Row

Untuk melakukan delete row , dengan cara biasa adalah sbb :

1. Block Row

2. Klik Kanan Mouse
3. Pilih Delete

Begitu juga dengan column....

Untuk mempercepat pekerjaan anda, biasanya kita bisa menggunakan short cut
Short cut untuk menambah baris :
1. Shift + Space --> Untuk block row
2. Alt + i + e
atau bisa juga dengan :
1. Shift + Space
2. Ctrl Shift + (Control Shift Plus)
 
Short cut untuk menghapus baris :
1. Shift + Space
2. Ctrl - (Control Minus)

Untuk block column ganti Shift + Space menjadi Ctrl + Space

Semoga membantu

Convert Number To Time

Terkadang kita memerlukan waktu cukup lama untuk mengentry data dalam format time, misal 09:45. Salah satu solusi untuk mempercepatnya adalah mengentry dengan menggunakan format number, sehingga entry datanya adalah 9.45.
Tetapi perlu diingat bahwa 9.45 tidak sama dengan 9:45, nah bagaimana supaya entry dalam format number tersebut bisa menampilkan nilai yang sama dalam bentuk format time.

Perlu diketahui bahwa setiap angka 1 dalam format number adalah sama dengan 1/24 format time, dan angka 0.1 dalam format number sama dengan 0.1/24/6*10

Sebagai contoh , misal anda ketikkan 6.25 , maka untuk mengubah ke dalam format time 6:25 anda harus mengkonversi nilai 6.25 tersebut menjadi (6/24)+(0.25/24/6*10).

Untuk formulanya adalah sbb :
Misal nilai 6.25 berada di cell D5, dan hasil yang diinginkan ada di cell D6
=((D5-ROUND(MOD(D5,1),2))+(ROUND(MOD(D5,1),2)/6*10))/24

Jangan lupa untuk mengubah format cell D6 dengan format time

Semoga membantu

Max Column and Row for Excel 2007

Just share saja, berdasarkan informasi yang ada, untuk maksimal baris dan kolom untuk microsoft excel 2007 adalah 16 ribu kolom dan lebih dari 1 juta baris...
Jadi gak perlu pusing lagi jika data anda sudah mencapai lebih dari 65 ribu baris...langsung ganti aja excel anda dengan versi 2007...

Sumproduct Include Non Number Cell Value

Jika anda mempunyai table dengan format seperti pada contoh dibawah


 
Bisa anda lihat bahwa pada baris ke 7 adalah header table


Nah jika anda cukup "iseng", dan ingin melakukan sumproduct berkondisi pada table tersebut (data beserta headernya), maka :
misal: Name= ANDY
=SUMPRODUCT((C7:C11="ANDY")*E7:E11*F7:F11)
Hasilnya adalah #VALUE

Nah bagaimana cara untuk menghindari #VALUE ???
Caranya adalah sbb :
=SUMPRODUCT(--(C7:C11="ANDY"),(E7:E11),(F7:F11))
Jangan gunakan '*' sebagai operator AND, gunakan koma (,)
Perhatikan sebelum (C7:C11="ANDY") ada tanda minus dua kali "--"

Artinya "--" apa ya ?
Tanda tersebut digunakan untuk "memaksa" hasil boolean (TRUE atau FALSE) menjadi numeric (1 atau 0)

Bisa juga dengan menggunakan fungsi berikut :
=SUMPRODUCT((C7:C11="ANDY")*1,(E7:E11),(F7:F11))
=SUMPRODUCT((C8:C12="ANDY")+0,(E8:E12),(F8:F12))

Thanks

Sumproduct with (multi) condition

Jika anda mempunyai format data seperti pada contoh , dan ingin melakukan perhitungan sumproduct sesuai dengan nama masing masing :



Bisa saja anda lakukan secara manual, misal untuk ANDY

=(E8*F8)+(E9*F9)+(E11*F11)

=(3*1)+(4*2)+(5*3)

Hasilnya adalah 26

Dengan menggunakan sumproduct

=SUMPRODUCT(E8:E9,F8:F9)+SUMPRODUCT(E11,F11)
atau
=SUMPRODUCT(E8:E9,F8:F9)+(E11*F11)

Ribet ???


Nah cara praktisnya adalah dengan menggunakan condition pada sumproduct
=SUMPRODUCT((C8:C11="ANDY")*E8:E11*F8:F11)
Tanda '*' disini bisa diartikan sebagai operator AND
(cara baca : lakukan sumproduct untuk baris C8 hingga C11 yang nilainya sama dengan "ANDY", maka selain "ANDY" akan dianggap sebagai 0)

Bagaimana jika anda ingin melakukan sumproduct lebih dari satu kondisi, misal Nama ANDY dengan Class A
=SUMPRODUCT((C8:C11="ANDY")*(D8:D11="A")*E8:E11*F8:F11)

Thanks

SUM From Multiple Sheet

Jika saya mempunyai 100 sheet (Sheet1 s/d Sheet100), bagaimana formula untuk menjumlahkan salah satu cell dari 100 sheet tersebut dalam sheet yang lain (Summary) ?

Caranya cukup mudah :
1. Ketikan =SUM(
2. Kemudian tahan tombol Shift
3. Klik sheet paling kiri (Sheet1)
4. Sambil tetap menahan Shift, klik sheet paling kanan (Sheet100)

Pada saat anda klik Sheet1 dan Sheet100, fokus sheet masih sheet Summary
5. Kemudian klik pada cell yang akan dijumlahkan (Misal :A1)
6. Formula anda akan terlihat sbb :
=SUM(Sheet1:Sheet100!A1)

Syarat yang harus diperhatikan adalah :
1. Pastikan semua nilai yang akan di jumlah berada pada cell yang sama, dalam contoh adalah A1.
2. Sheet yang akan dijumlahkan adalah Sheet yang berada di dalam range Sheet1 hingga Sheet100.
Jika sheet100 yang awalnya berada di posisi paling kanan anda geser (drag) ke arah kiri (misal sebelah kiri Sheet95, maka yang akan dijumlahkan adalah Sheet1 s/d Sheet94 ditambah Sheet100 itu sendiri.

Semoga membantu....

Penggunaan Array

Sebagai tambahan informasi pada tulisan saya sebelumnya yaitu tentang SumIf with multiple condition , caranya adalah dengan memanfaatkan array. Nah cara ini tidak hanya berlaku untuk SUM, tetapi anda juga bisa mengaplikasikan dengan fungsi-fungsi yang lain seperti MAX, MIN, Average, dll

Dan yang perlu diingat bahwa penggunaan array sebaiknya seminimal mungkin, karena terlalu banyak array dalam worksheet anda akan mempengaruhi performa excel anda. Jadi gunakan cara ini jika memang fungsi standard di excel sudah mencukupi kebutuhan anda....

SumIf with multiple condition

Sumif pada excel hanya bisa digunakan untuk 1 kondisi saja, misalnya ">2" atau "=0".
Syntax SumIf : "=sumif(range, criteria, [sum_range])"

Bagaimana jika kita ingin melakukan sumif tetapi dengan menggunakan lebih dari 1 kondisi ?
Solusinya adalah kita bisa memanfaatkan array, bagi yang belum mengerti tentang istilah array, artinya adalah sekumpulan/himpunan data.

Himpunan data bisa digambarkan sebagai berikut :
{1,2,3,4,5,6,7,8,9,0}

Syntac untuk menggunakan sumif dengan array sedikit berbeda dengan sumif pada umumnya.
Syntac untuk Sumif dengan multi kondisi adalah sbb :
=Sum(if(filter,if(filter,if(filter,[sum_range]))))
akhiri dengan menekan tombol Shift + Ctrl + Enter
Jika penekanan tombol sudah benar maka hasilnya akan tampak pada kolom formula sbb :
{=Sum(if(filter,if(filter,if(filter,[sum_range]))))}

contoh untuk pengisian filter adalah sbb:
$A1:$A10 > 5
(Cara baca : dalam cell A1 hingga A10 manakah yang nilainya lebih besar dari 5)
Jika kita lakukan evaluate formula maka hasilnya akan nampak sbb :
{False,False,False,False,False,False,True,True,True,True}

Note : Anda boleh saja mengartikan False = 0 dan True = 1

Dan sum_range yang akan masuk dalam perhitungan adalah posisi nilai , dimana semua filternya menghasilkan True, jika ada satu saja dari filter pada posisi nilai tersebut menghasilkan False, maka nilai tersebut tidak akan masuk dalam perhitungan.

Misal :
Kolom A1:A6 --> Data : {1,2,3,4,5,6}
Filter 1 : Data >3 --> Hasilnya adalah : {False,False,False,True,True,True}
Filter 2 : Data <6 --> Hasilnya adalah : {True,True,True,True,True,False}

Maka jika diformulakan : "=Sum(if(A1:A6>3,if(A1:A6<6, A1:A6)))"
Hasilnya adalah 9
Mengapa bisa 9 ? karena posisi array yang semuanya menghasilkan True ada pada posisi ke 4 dan 5

Semoga membantu
Thanks

Multi Condition for Conditional Formatting

Conditional Formatting adalah feature yang disediakan oleh excel untuk membantu kita dalam pembacaan data. Dengan conditional formatting ini, banyak hal yang bisa kita lakukan, misalnya mengubah warna cell/font jika nilai dalam cell tersebut sesuai dengan ketentuan
Untuk mengakses menu conditional Formating lihat pada gambar dibawah :

Akan muncul form berikut

Sebagai contoh, jika kita ingin mengetahui jumlah pengunjung yang lebih dari 400, lakukan :

1. Block kolom jumlah pengunjung

2. Masuk dalam menu conditional formatting

3. Pada Kolom 1 isi dengan Cell Value Is

4. Pada Kolom kedua isi dengan Greater Than

5. Pada kolom ketiga isikan angka 400

6. Kemudian tekan tombol Format

7. Pilih tab Patterns, kemudian pilih warna merah

8. Sebagai gambaran bisa dilihat pada image dibawah :

 

Hasil dari conditional formating bisa di lihat dibawah :

 

Nah, sekarang bagaimana caranya untuk membuat conditional formatting lebih dari 1 syarat ?

Misalnya yang diminta adalah tandai data Bulan July dengan Tahun 2010

Caranya adalah sbb:

1. Pilih Formula Is

2. Isikan formula seperti pada gambar

=AND($B74=$B$72,$C74=$C$72)

Membuat Data Validation List dari Sheet Lain

Seringkali kita membuat Validation List pada beberapa sheet dengan isi yang sama, tetapi pada Excel (2003) kita tidak bisa select source list pada sheet yang berbeda.

Ada 2 cara untuk membuat Data Validation List yang sourcenya berasal dari Sheet yang lain.
1. Menggunakan Define
a. Select Range yang akan dibuat List (Source)
b. Pilih Menu Insert -> Name -> Define
c. Isikan Names In Workbook, Misal : DataKu
d. Pindah ke Sheet Lain
e. Pilih Cell yang akan di buat List
f. Kemudian pilih Menu Data -> Validation
g. Pada Kolom Allow pilih List
h. Kemudian pada kolom Source isikan =DataKu

2. Menggunakan Fungsi INDIRECT
Jika kita asumsikan range A1:A10 adalah source yang terletak pada Sheet1. Maka untuk membuat Data Validation List pada Sheet2 adalah sebagai berikut :
a. Pilih Cell yang akan di buat List
b. Kemudian pilih Menu Data -> Validation
c. Pada Kolom Allow pilih List
d. Kemudian pada kolom Source isikan =INDIRECT("Sheet1!$A$1:$A$10")

Untuk Indirect terdapat kelemahan pada saat nama Sheet Source di ubah, sehingga harus mengubah Data Validation Source pada Cell yang di setting sebagai Data Validation List

Misal Nama Sheet awal adalah Sheet1 , fungsi indirectnya adalah =INDIRECT("Sheet1!$A$1:$A$10")

Pada saat nama sheet1 diubah menjadi Master , maka fungsinya harus diubah menjadi =INDIRECT("Master!$A$1:$A$10")

Semoga bermanfaat....

Fungsi INDIRECT

Fungsi INDIRCT berfungsi untuk mengubah text menjadi formula.
Sebagai contoh :
Cell A1 diisi dengan angka 5
kemudian cell A2 diisi dengan =INDIRECT("A1")
maka hasilnya adalah angka 5

Fungsi indirect juga bisa digunakan untuk lintas sheet
misal =INDIRECT("Sheet1!A1")

Kelemahan fungsi INDIRECT adalah jika digunakan cukup banyak dalam satu file excel, maka kinerja excel akan sangat lambat, hal ini disebabkan pada saat kita melakukan perhitungan di excel, maka akan dijalankan secara otomatis autocalculation.

Fungsi INDIRECT ini bisa juga disebut sebagai volatile function
Selain INDIRECT ada beberapa volatile function antara lain :
CELL() 
OFFSET()  
TODAY()  
NOW()  
INFO()  
RAND()

Untuk menghindari penurunan performance, sebaiknya seminimal mungkin menggunakan volatile function tersebut

Thanks
Ferry Setiawan

Disable Cut, Copy, Paste

Jika anda ingin data pada worksheet anda tidak bisa dicut, copy dan paste, caranya sangat mudah...
Tambahkan macro berikut pada worksheet anda :

Procedure yang pertama berguna untuk membatalkan perintah klik kanan pada mouse

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

Procedure yang kedua berguna untuk membatalkan perintah cut atau copy

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Mudah kan ?
Semoga bermanfaat...

Fungsi MATCH dalam VLOOKUP

Seringkali dalam membuat suatu perhitungan , kita menggunakan fungsi VLOOKUP untuk mendapatkan nilai sesuai dengan lookup_value yang kita kehendaki

Syntax : VLOOKUP(lookup_value, array_date,column_index,[range_lookup])

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data
Column_Index : Nomor kolom berdasarkan array_data
Range_Lookup :
-. Jika diisi dengan True, maka hasilnya akan sesuai dengan lookup_value, tetapi jika lookup_value tidak ditemukan dalam array_data maka hasilnya adalah nilai terbesar yang paling mendekati dengan lookup_value yang akan muncul
-. Jika diisi dengan False, maka hasilnya akan sesuai dengan lookup_value, tetapi jika lookup_value tidak ditemukan dalam array_data maka hasilnya adalah #N/A

Bagaimana jika dilakukan insert column pada column C ?

hasilnya adalah sebagai berikut :

Pada column Result hasilnya menjadi kacau, hal ini disebabkan karena Column_Index bersifat statis, tidak bisa mengikuti perubahan column.

Bagaimana solusinya ?

Solusinya kita bisa menggunakan Fungsi MATCH di dalam VLOOKUP

Setelah di lakukan insert Column C, hasilnya adalah sebagai berikut

Hasilnya sama dengan sebelum di lakukan insert column

Semoga bermanfaat...

Fungsi MATCH

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

Match Type :

-. Jika 1, hasilnya adalah posisi dimana Lookup_Value berada, atau posisi nilai terbesar yang paling mendekati dengan Lookup_Value, syaratnya adalah array_data harus berurutan (kecil ke besar)

-. Jika 0, hasilnya adalah posisi dimana lookup_value berada, dan datanya tidak harus berurutan

-. Jika -1, hasilnya adalah posisi dimana Lookup_Value berada, atau posisi nilai terkecil yang paling mendekati dengan Lookup_Value, syaratnya adalah array_data harus berurutan (besar ke kecil)

-. Jika tidak diisi maka secara default akan dianggap 1

Contoh

Thanks

Ferry Setiawan

Fungsi INDEX

Fungsi INDEX dalam excel dapat kita gunakan untuk mendapatkan suatu nilai dari baris dan kolom yang kita kehendaki.

ntax:

INDEX(array, row, column)












array : adalah range untuk data , dalam contoh ini adalah di column B
row : adalah baris data dari array
column : adalah kolom data dari array, dalam contoh dituliskan 1, karena array data hanya 1 kolom (perhitungan nomor kolom tidak dihitung dari kolom A, tetapi dimulai berdasarkan kolom dalam range data, dalam contoh adalah kolom B)

Mengubah Baris Menjadi Kolom

Bagaimana cara untuk mengubah tampilan row data menjadi column data ?

Ada beberapa cara :
1. Manual, yaitu dengan menggunakan copy & paste special ,aktifkan option transpose
2. Formula, misal data yang ada berada dalam range (A1:A5), kemudian akan di mengubah menjadi column data (misal range(B1:F1)), lakukan langkah langkah berikut :

• Block range(B1:F1)
• ketikkan formula "=transpose(B1:F1)"
• tahan Ctrl+Shift
• masih dalam keadaan menekan tombol Ctrl+Shift, tekan tombol enter

Set Error Formula Result To Zero Value

Untuk mengubah nilai error (#N/A, #DIV/0!, dll) pada suatu formula menjadi 0 (nol) , biasa kita menambahkan fungsi IF pada formula.
Contoh :
Diketahui : A1=10, B1=0
Formula : =A1/B1
Result : #DIV/0!

Untuk mengubah nilai error menjadi 0, maka Formulanya kita ubah menjadi :
=IF(B1=0,0,A1/B1)
Maka hasilnya adalah 0

Bayangkan jika formula anda cukup panjang, maka dengan ditambahkannya fungsi IF seperti pada contoh, panjang formula anda akan menjadi 2 kali lipat, dan ini tentunya akan mempersulit anda dalam pengecekan formula jika ada kesalahan.

Contoh Formula :
=IF(A49="",0,SUM(B60:C60)/VLOOKUP(Setting!$C$3,Setting!$B$25:$C$26,2,FALSE)*VLOOKUP($B$3,Setting!$B$6:$L$8,COLUMN()-3,FALSE))

Jika menggunakan IF akan menjadi :
=IF(VLOOKUP(Setting!$C$3,Setting!$B$25:$C$26,2,FALSE)*VLOOKUP($B$3,Setting!$B$6:$L$8,COLUMN()-3,FALSE))=0,0,IF(A49="",0,SUM(B60:C60)/VLOOKUP(Setting!$C$3,Setting!$B$25:$C$26,2,FALSE)*VLOOKUP($B$3,Setting!$B$6:$L$8,COLUMN()-3,FALSE)))

Bagaimana cara untuk mempermudah ?

Cara untuk mempermudah adalah dengan memanfaatkan macro

Buat satu module , kemudian isi dengan code berikut :

Function SetToZero(vString As Variant) As Double
On Error Resume Next

If IsEmpty(vString) Or Len(vString) = 0 Then
SetToZero = 0
GoTo akhir:
Else
SetToZero = vString
GoTo akhir:
End If

If vString = CVErr(xlErrDiv0) Or _
vString = CVErr(xlErrNA) Or _
vString = CVErr(xlErrName) Or _
vString = CVErr(xlErrNull) Or _
vString = CVErr(xlErrNum) Or _
vString = CVErr(xlErrRef) Or _
vString = CVErr(xlErrValue) Then
SetToZero = 0
GoTo akhir:
Else
SetToZero = vString
GoTo akhir:
End If
akhir:
End Function

Kemudian ubah formula anda menjadi :
=SetToZero(A1/B1)

Kemudian rasakan hasilnya....