Comparison Charts With Excel

---

Today we are going to learn how to make a chart which compares two or more data in a table. It is very useful when we want to compare between two values very easily within no time. As you will agree that comparing data with a graph is far more better than digging into a lot of data in a table. So lets get started. this is what we are going to create.

Follow these easy step by step method to create the chart as in the above image. Lets say we want to compare a list of items A to E for number of units in stock, purchased and sold.

Step 1

Create a sample data as below.

Step 2

Go to Insert > Charts > Column and then select Clustered Column chart from 2D Column.

Step 3

Select Layout 5 from Chart Layouts in Chart tools in Design tab.

Step 4

Right click on chart area and click on Select Data.

Step 5

In the Select Data source window click on Add.

Select the title "In Stock" for Series Name and Data below In Stock title for Series Values and click OK.

Notice a series In Stock is added in the Legend Entries (Series) list box. Now click on Edit button.

Select Item names A to E for Axis label range and click OK.

Similarly Add Two more series Purchased and Sold  as illustrated above for Purchase and Sold units.

Step 6

After completing above five steps you should get a graph as shown in the image.

Right click on Axis Title and choose Edit Text. Delete the text and write "Number of Units". Select the text and change the font size to 12 and the result is. . . 

Leave me a comment if you like this post.

Read More

Comparison Charts With Excel

---

Today we are going to learn how to make a chart which compares two or more data in a table. It is very useful when we want to compare between two values very easily within no time. As you will agree that comparing data with a graph is far more better than digging into a lot of data in a table. So lets get started. this is what we are going to create.

Follow these easy step by step method to create the chart as in the above image. Lets say we want to compare a list of items A to E for number of units in stock, purchased and sold.

Step 1

Create a sample data as below.

Step 2

Go to Insert > Charts > Column and then select Clustered Column chart from 2D Column.

Step 3

Select Layout 5 from Chart Layouts in Chart tools in Design tab.

Step 4

Right click on chart area and click on Select Data.

Step 5

In the Select Data source window click on Add.

Select the title "In Stock" for Series Name and Data below In Stock title for Series Values and click OK.

Notice a series In Stock is added in the Legend Entries (Series) list box. Now click on Edit button.

Select Item names A to E for Axis label range and click OK.

Similarly Add Two more series Purchased and Sold  as illustrated above for Purchase and Sold units.

Step 6

After completing above five steps you should get a graph as shown in the image.

Right click on Axis Title and choose Edit Text. Delete the text and write "Number of Units". Select the text and change the font size to 12 and the result is. . . 

Leave me a comment if you like this post.

Read More

tintin di bulan

Read More

Dynamic Chart

---

Today we are going to make our charts dynamic in nature. By dynamic I mean that the chart will update automatically on adding new data to the table. As I always say, just follow these simple steps to make your charts update as you enter new data. You can use this tutorial to create resource chart also.

Step1:- First of all open excel  and create sample data as in the image below.

 Step 2:- Go to Formula tab in excel and click on Define Names. A pop-up window opens up.

              In Name write "Day".
              In Refer to write " =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)".
              Click OK.

              Again click on Define Names.
              In Name write "Resource".
              In Refer to write "=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)".
              Now save your book. I saved it as "Dynamic.xls".

Step 3:- Now go to Insert > Charts > Column Chart and select Clustered ColumnType

 

Step 4:-  Go to Chart Layouts and Select Layout 8 from the layouts.  

Step 5:- Create the chart as in my previous tutorial Create a Simple Chart in Excel.

              While Selecting data do follow these steps

              In the series values write "=Dynamic.xls!Resoures". 

              Remember to replace Dynamic.xls with your own file name.

              In the Axis Label Range write "=Dynamic.xls!Day"

Note:- Confusion regarding file extension as pointed out by my friend.

For Excel 2003 "Your_File_Name.xls".

For Excel 2007 "Your_File_Name.xlsx".

Above example is as per 2003 format. 

Step 6:- Change Chart style to Style 16 and it should look like this.

Now try adding new data to your table, you will notice that it automatically updates in your graph. Leave a comment if it works.

Referred Links

Dynamic Charts (Peltier Tech Blog)

Read More

Dynamic Chart

---

Today we are going to make our charts dynamic in nature. By dynamic I mean that the chart will update automatically on adding new data to the table. As I always say, just follow these simple steps to make your charts update as you enter new data. You can use this tutorial to create resource chart also.

Step1:- First of all open excel  and create sample data as in the image below.

 Step 2:- Go to Formula tab in excel and click on Define Names. A pop-up window opens up.

              In Name write "Day".
              In Refer to write " =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)".
              Click OK.

              Again click on Define Names.
              In Name write "Resource".
              In Refer to write "=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)".
              Now save your book. I saved it as "Dynamic.xls".

Step 3:- Now go to Insert > Charts > Column Chart and select Clustered ColumnType

 

Step 4:-  Go to Chart Layouts and Select Layout 8 from the layouts.  

Step 5:- Create the chart as in my previous tutorial Create a Simple Chart in Excel.

              While Selecting data do follow these steps

              In the series values write "=Dynamic.xls!Resoures". 

              Remember to replace Dynamic.xls with your own file name.

              In the Axis Label Range write "=Dynamic.xls!Day"

Note:- Confusion regarding file extension as pointed out by my friend.

For Excel 2003 "Your_File_Name.xls".

For Excel 2007 "Your_File_Name.xlsx".

Above example is as per 2003 format. 

Step 6:- Change Chart style to Style 16 and it should look like this.

Now try adding new data to your table, you will notice that it automatically updates in your graph. Leave a comment if it works.

Referred Links

Dynamic Charts (Peltier Tech Blog)

Read More

Bedah Tunas Gadget: Change Case

Kali ini saya akan mencoba membedah source code dari salah satu fitur Tunas Gadget yaitu Change Case. Change Case bertujuan untuk merubah huruf besar dan kecil pada sel Excel menjadi HURUF BESAR (UPPERCASE), huruf kecil (lower case), Huruf kalimat (Sentense case) dan Huruf Judul (Title Case). Jika fitur ini mudah didapatkan di Microsoft Word, maka untuk di Microsoft Excel kita bisa menggunakan fitur ini.

Di sini saya menggunakan form sebagai Graphic User Interface (GUI) atau antar muka agar kita bisa lebih mudah menjalankan fungsinya dibanding kita menggunakan function-function yang terpisah. Karena dalam fitur Change Case ini terdapat 8 function sehingga akan lebih mudah jika kita menggunakan form daripada 8 function tersebut.

Kita dapat membuat sebuah file baru yang disimpan sebagai file Excel Add-in atau sebagai file excel biasa. Saran saya kita memilih sebagai file Excel Add-in.

Selanjutnya kita membuat form dengan menekan tombol Alt + F8 (Microsoft Visual Basic) lalu Insert > User Form sehingga muncul seperti gambar berikut:

Dengan menggunakan tombol-tombol di Toolbox, silahkan desain form kosong tersebut seperti form di bawah.

Perhatikan nomor-nomornya. Contoh nomor 1 adalah untuk Form, maka isi propertiesnya sesuai petunjuk gambar di bawah ini. Begitu juga untuk nomor 2 hingga 8, ikuti petunjuk propertiesnya.

1. Form

2. UPPERCASE

3. lower case

4. Sentence case

5. Title Case

6. Textbox

7. Run Macro

8. Exit

Setelah form sudah selesai didesain, selanjutnya kita masuk ke bagian source code/scriptnya. Untuk scriptnya pilih View > Code lalu gunakan script berikut:

1. Form
Private Sub UserForm_Activate()
    cmd_run.SetFocus
End Sub


Private Sub UserForm_QueryClose _
(cancel As Integer, CloseMode As Integer)
    
If CloseMode = vbFormControlMenu Then
    cancel = True
End If


End Sub

2. Tombol Radio UPPER CASE
Private Sub opt_upper_Click()
    Label2.Caption = "Text will be changed as " & vbCrLf & "UPPER CASE."
End Sub

3. Tombol Radio lower case
Private Sub opt_lower_Click()
    Label2.Caption = "Text will be changed as " & vbCrLf & "lower case."
End Sub

4. Tombol Radio Sentence case
Private Sub opt_sentence_Click()
    Label2.Caption = "Text will be changed as " & vbCrLf & "Sentence case."
End Sub

5. Tombol Radio Title Case
Private Sub opt_title_Click()
    Label2.Caption = "Text will be changed as " & vbCrLf & "Title Case."
End Sub


7. Tombol Run Macro
Private Sub cmd_run_Click()


On Error GoTo ErrorHandler:


If opt_upper = False And opt_lower = False And opt_sentence = False And opt_title = False Then
    MsgBox "Please choose a case how text should be changed", vbExclamation + vbOKOnly, "Choose Method"
Else
If opt_upper = True Then
    Dim cell_upper As Range
    For Each cell_upper In Selection.Cells
    If cell_upper.HasFormula = False Then
    cell_upper = UCase(cell_upper)
    End If
    Next
Else
If opt_lower = True Then
    Dim cell_lower As Range
    For Each cell_lower In Selection.Cells
    If cell_lower.HasFormula = False Then
    cell_lower = LCase(cell_lower)
    End If
    Next
Else
If opt_sentence = True Then
    For Each cell In Selection.Cells
    s = cell.Value
    Start = True
    For i = 1 To Len(s)
    ch = Mid(s, i, 1)
          Select Case ch
          Case "."
          Start = True
          Case "?"
          Start = True
          Case "a" To "z"
          If Start Then ch = UCase(ch): Start = False
          Case "A" To "Z"
          If Start Then Start = False Else ch = LCase(ch)
          End Select
    Mid(s, i, 1) = ch
    Next
    cell.Value = s
    Next
Else
If opt_title = True Then
    Dim cell_title As Range
    For Each cell_title In Selection.Cells
    If cell_title.HasFormula = False Then
    cell_title = Application.Proper(cell_title)
    End If
    Next
End If
End If
End If
End If
End If


GoTo WrapUp:


ErrorHandler:


    MsgBox "Error Macro. Please contact doddy_151619@yahoo.com for further enhancement.", vbCritical + vbOKOnly, "Error Macro"
    
WrapUp:


    Application.Interactive = True
    Application.ScreenUpdating = True
    
End Sub

8. Tombol Exit
Private Sub cmd_cancel_Click()
    Unload Me
End Sub

Selanjutnya pilih ThisWorkbook pada window Project – VBA Project dan gunakan script berikut pada window sebelah kanan:

Sub Open_Form_Change_Case()
 With Form_Change_Case
     .Show vbModeless
 End With
End Sub

Untuk menjalankan macro ini silahkan balik ke window Microsoft Excel lalu tekan Alt + F8 lalu pilih Open_Form_Change_Case dan klik Run.

Untuk cara penggunaan macro ini, silahkan buka artikel Tunas Gadget: Change Case.

Selamat mencoba!

Read More

Statistik Blog Formula Excel dari Google Analytics

Setelah sekian lama tidak sempat “menengok” blog ini, ternyata jumlah kunjungan blog Formula Excel ini semakin meningkat. Saat saya menulis post ini terdapat sebanyak 16.939 kunjungan dan total halaman yang dilihat (pageviews) sebesar 46.942 sejak 1 Januari 2011 hingga 19 November 2011 yang berarti +/- 1539 kunjungan per bulan. Memang jumlah ini bukan angka yang besar karena hanya terdapat 2,77 halaman per kunjungan, namun bukankah semua harus disyukuri? :)

Di lihat dari grafiknya, kunjungan meningkat mulai bulan September hingga November 2011 ini. Berikut grafiknya.

Sebagian besar kunjungan ke blog Formula Excel melalui Search Engine (83,32%), kunjungan langsung (11,58%) dan link dari website lain (5,11%)

Keyword yang paling sering digunakan untuk menemukan blog ini adalah “formula excel” sebanyak 1.529 kunjungan. Berikut 10 keyword terbanyak:

Secara wilayah, Indonesia adalah negara yang paling banyak mengunjungi blog ini (ya iyalah… :D) sebanyak 15.668 kunjungan. Namun ternyata ada juga negara lain yang mengunjungi blog ini di antaranya Amerika (159 kunjungan), Malaysia (78 kunjungan), dll.

Untuk wilayah Indonesia, kunjungan dari Jakarta paling banyak yaitu 7.076 kunjungan dari total 37 kota. Berikut 10 kota terbanyak kunjungannya.

Belum sempat tambah artikel

Dikarenakan kesibukan saya di kantor baru sejak Juni 2011, tentunya saya harus fokus dulu ke pekerjaan baru saya yang membutuhkan tenaga ekstra. Di samping itu di kantor saya (demi keamanan katanya) banyak website yang diblock termasuk Blogspot ini sehingga saya tidak bisa “leluasa” update/tambah artikel baru.

Untuk itu saya mohon maaf kepada para pengunjung blog ini yang sudah mengirim email ke saya karena banyak email yang belum sempat saya jawab, begitu juga dengan Facebook Friend Request yang belum saya Approve.

Terima kasih telah mengunjungi blog saya ini :)

Read More