Tag: excel training london

Categories
Excel Training

Excel macro training: To change text to upper or proper case

On a recent Excel VBA Intermediate course everyone thought a macro for converting text to upper or proper case would be helpful. It’s a good one to put into your Personal Macro Workbook then assign to a button on your Quick Access Toolbar. Here’s how to do it:

1. Starting with a blank Workbook record a macro called ConvertCase in the Personal Macro Workbook. With Excel 2007 or Excel 2010 click View, Macros, Record Macro…

2. Press OK  then straight away click Macros, Stop Recording.

3. Press Alt+F11 to view the VBA Editor and open the module and code window for the blank ConvertCase macro.

4. Now add the following code:

Sub ConvertCase()
‘Macro to start frmConvertCase
frmConvertCase.Show
End Sub

Sub Uppercase()
For Each rng In Selection
    rng.Value = UCase(rng.Value)
Next rng
End Sub

Sub Propercase()
For Each rng In Selection
    rng.Value = Application.WorksheetFunction.Proper(rng.Value)
Next rng
End Sub

5. Insert a User Form and name it frmConvertCase using the Properties window adding a caption ‘Convert Case’.

6. Add two Option buttons and two Command buttons to your form:

 7. Name the controls Upper, Proper, OK and Cancel adding captions as shown below.

 

 8. Double click the OK button and add the following code:

Private Sub OK_Click()
Dim rng As Range
If UPPER.Value = True Then
Call uppercase
ElseIf Proper.Value = True Then
Call propercase
Else
MsgBox “Please choose either upper or proper case”
End If
End Sub

9. Close the code window the double click the Cancel button adding the following code:

Private Sub Cancel_Click()
Unload frmConvertCase
End Sub

10. Finally close the VBA Editor and customise your Quick Access Toolbar.
Select More Commands, Choose commands from Macros and select your ConvertCase macro, Add, OK.

That’s it. You now have a permenant button to change case of any selected text!

Categories
Excel Training

Adding a Calculated Item to a Pivot Table in Excel 2010

Above is an example of a standard pivot table in Microsoft Excel 2010.  It is set up with financial quaters as column headers and products as Row labels.  I’m interested in seeing the results for the combined sales for the first half and the second half of the year. As you can see I have colour coded these two halves and now I am going to add two “calculated items” showing a total for Q1+Q2 and Q3+Q4.

TAKE ACTION: 

  1. Ensure your cursor is placed onto the Q4 column header as in the image.
  2. Select the “PivotTable tools” tab and click on “options”
  3. In the “calculations” box” select “fields, items, & sets” and then “calculated items”

When this box appears follow these instructions:

  1. Click into the “name” field and enter the new name Qtr1+Qtr2.
  2. Click into “formula” field, remove the 0, double click on Qtr1 in the “Items” field, add + then double click on the Qtr2 from the “Items” field.  Here you are entering a formula which is Qtr1 + Qtr2.
  3. Click the “Add” button and then OK

You will now see that this new column has been added to your PivotTable in Microsoft Excel 2010.

Repeat this process for Qtr3 + Qtr4 and adjust the background colours to match those already on the pivot table.  All going well you should have a pivot table that resembles the one I have pasted below:

You now have a pivot chart showing you the totals for both halves of the year.  Take note that your grand total includes your two new columns so its best to remove that. To learn how to remove the total column in Microsoft Excel 2010, well that’s for the next blog.

Good luck!