Monday, October 29, 2018

Excel 2016 Pie/Pivot charts

If you have Gender column in your sheet which have values "Male", "Female" for every row - how to create a PIe Chart?
In Google sheets it just takes couple of clicks and quite intuitive.
Here is how to do it in Excel:

1. Select the column
2. Insert Tab - Click PivotChart - OK
3. In PivotChart Fields select and drag that column to all sections.
4. Now you have Clustered Column chart.
5. Right click the chart -> Change Chart Type -> Pie -> Ok

Excel 2016 Macros

Enabling Macros:
File -> Options -> Customize Ribbon -> Main Tabs -> Check Developer
Now Developer tab will be visible.

Inserting Macro:
Click: Developer Tab -> Insert ActiveX Controls -> Command Button
Now click anywhere on the sheet. You will see a Command Button. Right click the button and View Code.

There you can enter this code: (Give Score of 1 if Gender is Female). Column B is Gender. Column A is score.

Private Sub CommandButton1_Click()
Dim Gender
Dim Score

For i = 1 To 3
   Score = 0
   Gender = Range("B" & i).Value
   If Gender = "Female" Then
      Score = 1
   End If
   
   Range("A" & i).Value = Score
Next i
End Sub

Blog Archive