Tag Archives: Tutorial

Excel VBA macros: How to record a VBA macro

It is a good idea to automatise time consuming routine tasks in Excel. You don’t need to know how to implement VBA macros, because you can just record your tasks. In following example I record a macro that assumes that I have people’s whole names listed in column A and want to split names (first name and last name) into columns A anB. This is good example when simple recorded macro can save you from doing this routine task all over again.

Here is a video that demonstrates the VBA macro recording and shows how to run the macro:

How to import text and use Excel’s Text to Columns -feature

Normally, when you paste text to Excel, the whole text will appear in one column. Luckily Excel has nice feature called Text to Columns. With that feature you can spread your text data to columns nice and easy. In my example, I have text where items are delimited with commas and colons.

Following video demonstrates how to import that text to Excel’s columns easily:

How to use Excel’s Goal Seek analysis

Excel provides many ways to analyse data. One powerful tool is Goal Seek -tool. Following demo presents a simple example to find out body weight when target Body Mass Index (BMI) is known by using Goal Seek analysis. All we need to do is to know the formula of BMI and then define target BMI. We try to solve out the body weight that is needed to achieve our target BMI.

The formula for BMI is:

BMI = Weight[kg] / (Height[m] x Height[m])

The following video demonstrates this:

Excel: How to show number in custom format

You are able to format Excel’s cell items as you like. You can e.g. add custom texts to be shown with numbers. Typical case is to show numbers with units:

  1. Select the cell/cells you want to format:
    Context menu: Select Format Cells
    Shortcut: Windows ctrl + 1, Mac cmd + 1
  2. Select Number and Custom from the Category list.
  3. If you want to one decimal and unit, type following format code
    0.0″ kg” or
    0.0″ lb”
Following video demonstrates this in action:

How to create and use Excel’s drop-down lists

Excel has a great feature to improve productivity by using drop-down list. If you have specific list of allowed entries to a cell, use drop-down list! In my example video below my list contains car brands. Creating list is easy:

  1. Type down your list items to Excel worksheet. It might be a good idea to put list to different worksheet than your main data.
  2. Activate cell where you want to use the list
  3. Select Data-tab from the Excel’s Ribbon menu.
  4. Select Validate
  5. From the Allow drop-down list, select List
  6. Click the painting tool on the right hand sight of the Source box
  7. Select list items you typed in the first phase
  8. Press Enter
  9. Now you have drop-down list for selecting items in the cell you activated
Following video demonstrates this:

How to use Excel’s absolute reference ($) and why

The normal way to reference Excel’s cell is like “A1”. This is called relative reference. When copying cell containing formula with relative reference, the reference will change depending on where to paste it. If you for example paste formula containing “A1” reference to one cell above, the new reference will be “B1”. If you want to fix column and/or row of the reference so that it won’t change when pasting, you can use so called absolute references. All you need to do is to add $ to the reference:

  • $A$1 means that both column and row are fixed
  • $A1 means that column is fixed but row reference will change when pasting formula
  • A$1 means that row is fixed but column reference will change when pasting formula
Following video demonstrates this:

How to use Auto Filter -feature

An easy way to analyse data of large tables is to use Excel’s Auto Filter -feature. With this feature you can define filtering criteria for each column of the table to show only the information that is most relevant to you.

Following video demonstrates the Auto Filter -feature

How to colour Excel chart according to source data using VBA

I will demonstrate you the power of Excel VBA macros with a nice simple macro that goes through all your chart objects and set the colours of those according to their source cells’ colour. If you are unfamiliar with Excel VBA, then I suggest that you first check my previous post Excel Tips and Tricks: How to write simple VBA function.

Here is the code that does the trick (I don’t guarantee that it works in every cases, but it should give you the idea):
Sub ColorCharts()
    For Each ch In ActiveSheet.ChartObjects
        For Each ser In ch.Chart.SeriesCollection
            s = Split(ser.Formula, ",")
            For i = 1 To UBound(ser.Values)
                ser.Points(i).Interior.Color = Range(s(2)).Cells(i).Interior.Color
            Next i
        Next ser
    Next ch
End Sub
Following video demonstrates this:

 

How to write Excel VBA function

If there is no proper function for your purposes, you can always write one by yourself. All you need is a basic understanding of Basic-programming. It is not hard!

In this demonstration we implement macro function for reversing text (e.g. “help”–>”pleh”). Following steps are needed:

  • Add Developer tab to Excel Ribbon bar:
    Excel 2011 for Mac: Select click gear in the Ribbon’s right edge and select Ribbon Preferences…
    Excel 2010 for Windows: Right clickon the Ribbon and select Customize Ribbon…

    –> Make sure that the Developer-Tab is selected in the Tab list
  • Select Developer-Tab
  • Start Excel’s Visual Basic -editor
  • Add new module by opening context menu from VBAProject and selecting Insert –> Module
  • Write following code to Module1
    Function TransposeText(txt)
        Dim returntxt
        returntxt = ""
        For i = Len(txt) To 1 Step -1
            returntxt = returntxt + Mid(txt, i, 1)
        Next i
        TransposeText = returntxt
    End Function
  • Now your function is ready to use
  • Go back to the worksheet and test the new function
Here is the video demonstrating this using Excel 2011 for Mac:

How to transpose Excel table using Paste Special -feature

There is a handy way to transpose entire table:

  1. Select the table you want to transpose
  2. Select Copy
  3. Select Paste Special (e.g. from the context menu)
  4. Mark checkmark to Transpose
    –> And vóila, you have transposed table!
Following video demonstrates this: