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:
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:
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.
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:
Type down your list items to Excel worksheet. It might be a good idea to put list to different worksheet than your main data.
Activate cell where you want to use the list
Select Data-tab from the Excel’s Ribbon menu.
From the Allow drop-down list, select List
Click the painting tool on the right hand sight of the Source box
Select list items you typed in the first phase
Now you have drop-down list for selecting items in the cell you activated
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
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
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):
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
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
Start Excel’s Visual Basic -editor
Add new module by opening context menu from VBAProject and selecting Insert –> Module
Write following code to Module1
returntxt = ""
For i = Len(txt) To 1 Step -1
returntxt = returntxt + Mid(txt, i, 1)
TransposeText = returntxt
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: