Monthly Archives: January 2013
As we all know Excel has more built in functions than any one human could ever need. Personally I have never had to use the BESSELK function and I very much doubt I ever will….in fact I have no idea what it does.
But what if there is some sort of calculation you do on a regular basis for which there is no built in function? You may well have a calculation that is specific to your company or job role and you use it on a daily basis. The calculation itself might be quite complex and requires lots of brackets, sub-calculations etc making it prone to typing errors or simply takes you ages to put together each time you need it.
What if you could create your own function with all the calculations built in and all you had to do was enter the basic data? Well…you can, with the help of VBA.
First of plan your function on paper; what is the calculation going to do and how?
For each element within your calculation, how many variables do you have? This is where it gets a bit confusing for people not used to working with or even knowing what I am referring to as variables.
Let’s try and build a simple example to understand what a variable is and how we can build variables into our function.
I want to be able to calculate the cost of a car journey so I can work out costs more accurately rather than just apply a flat rate per mile.
So what would I need to know to work this out?
• The distance driven
• The cost of fuel
• The average fuel consumption of the car being used to make the journey
Of these three values I need, how many will vary each time I need to do the calculation?
• Each journey will be different so the mileage will vary each time
• The cost of fuel is always changing (usually upwards!)
• Unless you only have one pool car, chances are you will be using different cars, each with their own fuel consumption rate
These are VARIABLES. In each case, there is no fixed value whenever we come to do the calculation. Hopefully, that clears up what a variable is.
So our calculation might be;
(distance driven/fuel consumption) * (cost of fuel * 4.5)
The *4.5 bit is more of a UK thing….fuel prices are quoted as cost per litre but we base fuel consumption on miles per gallon, and one UK gallon is 4.5 litres.
If we were to put numbers into this we might have;
(200miles/35mpg)*(£1.41 per litre * 4.5 litres) = £36.26
i.e. the cost of fuel to do a 200 mile journey in a car that does 35 miles per gallon.
Now that we have planned our function on paper, we now need to translate it into VBA code. Make sure you have a blank workbook open.
Go to the DEVELOPER tab and click on VISUAL BASIC, or press CTRL + F11. If you use 2007 or 2010 it may not be visible. For 2007 users, go to the OFFICE button, select EXCEL OPTIONS and on the POPULAR tab tick SHOW DEVELOPER TAB IN THE RIBBON. If you are on 2010, right click in the ribbon and select CUSTOMIZE RIBBON and tick the DEVELOPER option.
Remember earlier on in this task we opened a blank workbook? In the project window you should see something like VBAProject(Book1). Right click on the name of the workbook (project in VBA terms), click on INSERT, then click on MODULE. A MODULE is simply a folder that stores code within the workbook.
You should now have a blank page to the right of the project explorer window…that’s the list of open workbooks on the left hand side.
Now I need to declare my variables by giving them a name and data type.
The data type you use is important when working with variables. Each variable can only store one type of information. The basic variable types you are likely to use are as follows:
There are others but if you know these it’s a good start.
All that’s left now is to create the function itself substituting the names of the variables into our formula. Start by using the name you have given to your function (make sure you do this or your function won’t work);
If you are not used to working with variables, this does seem a bit odd to start with, looking at a formula that contains words rather than numbers.
To test your new function start typing the name in a cell (in the same workbook that contains the code module) where you want to perform the calculation;
The new function appears in the pop up list of functions! If you cannot remember the order of the variables you need to enter to make the function work, click on the fx button next to the formula bar. This will show you the wizard for your function, showing each variable you need to add in.
Use the wizard as you would any normal function and hey presto…you have an answer without the need to type out all the brackets, constants, operators etc.
If you are happy with your new function and want to make it a permanent part of YOUR Excel you need to save the workbook containing the code as an ADD-IN.
Make sure the workbook is clear: no calculations or data in the workbook. Then just do a SAVE AS, selecting EXCEL ADD-IN(*.XLAM), name the workbook, ideally using the function name and click on SAVE.
To make the function available in Excel you need to activate the add-in. Go to the OFFICE button (2007) or FILE (2010), then click on EXCEL OPTIONS, and then ADD-INS.
At the bottom of the window make sure EXCEL ADD-INS is showing and click on GO.
Tick your newly saved function as an add-in, and click on OK. You are now free to use your function whenever you want in any workbook on your laptop/PC. So if you are some sort of budding quantum physicist or you just have a special way of calculating your home finances, you can now put your own functions into Excel to work on all your data.
When working with lots of data and formulas, understanding relative and absolute references is key to success in Excel.
First of all, does this look familiar?
Everything works fine……until you try to work out the VAT based on the value in cell A1.
So why does this happen?
By default, when you copy formulas in Excel the references are set to RELATIVE. By that I mean that when you copy a formula down 1 row, all the cells the formula is looking at also move the same distance. If you are not sure by what I mean, try this….
Select a handful of cells scattered across your screen and give them some colour so they stand out.
Now, enter a formula in a blank cell that adds up the cells you have just coloured in.
And press enter. The answer will be 0, but that doesn’t matter. Make sure your formula cell is selected, then click in the formula bar above the worksheet area.
When you do this each cell that is in the formula will get a coloured border that matches the colours of the cell references in the formula bar.
Now for the bit that will show us what happens when we copy a formula…
Click on the bottom right hand corner of the formula cell and drag it down 1 row. This will copy your formula down. Again, make sure that the new formula cell is selected and then click again in the formula bar. What do you see?
As you can see, the formula has moved down one row and so have all the formula reference cells. They have moved the same RELATIVE distance as the formula cell. You can repeat this in any direction you like to see what happens.
So now that you can see what happens when you copy a formula let’s go back to the original problem.
The first thing people do when they see errors appear is undo, delete, scream…or any combination of all of those. DON’T! Use the error messages to your advantage. If you delete the errors you have nothing to investigate or work with to try and find out what has gone wrong. Use the same steps as we have just used to see what happens when you copy a formula down or across. Click in the first cell where the formula works, then try the next cell and so on and so forth. Each time, click in the formula bar to see which cells the formula is looking at.
Now you can clearly see that the formula is looking correctly at £1.84, the net cost, but it has moved away from the 20% value in A1. You can then try out the cell that has the #VALUE error in it and see what that is looking at.
So remember: leave errors in place and use them to work out what has gone wrong.
So now to fix the problem.
We need to fix the position of the cell A1 so that when the formula is copied in does not away from it. You may have noticed in spreadsheets you receive from colleagues that some formulas contain cell references that look like $D$5. The dollar symbols represent ABSOLUTE referencing. In other words, the cell reference is fixed. A dollar symbol in front of the column letter means that the column is fixed, and the symbol in front of the row number means the row is also fixed.
You can type the ‘$’ symbols in if you like, but that is a bit of a faff, so the simplest way to do it is press the F4 key once you have selected the cell in your formula.
So in this example our formula should read;
So now when you copy your formula down, or in any direction for that matter, you will always be looking at the fixed cell location of A1.
Now…just to complicate matters you can also have PARTIAL ABSOLUTE references. When you press F4 is puts $ in front of both the column and row references. If you press F4 again it then displays A$1 which fixes the row number but turns the column into a relative reference. Press F4 again and then you get $A1, fixing the column reference but making the row relative. Press once more and you return to a fully relative cell reference.
What’s the difference between those then?
Let’s extend our example a bit. Now we have a total in GBP we now want to convert to USD and EUR, for which we have rates in cells G2 and H2 respectively.
I could write a formula in G4 fixing the Dollar conversion rate cell and then fixing the Euro rate in the next column. Might be OK to do for a couple of columns, but not if you have lots to do. So, we can create a formula that when copied across and down looks at the correct conversion rate every time whether we copy across and/or down.
In this example I want to be able to copy across so I cannot fix the column reference as I have a formula in column H too. However, when I copy the formula down I do not want to move away from row 2 in any of my columns as this is where the conversion value sits, so this needs to be fixed. This then gives us the formulas as shown below. This allows you to create a single formula in cell G4 which can be successfully copied across and down whilst still looking at the correct cell references.
If you want to practice using partial absolute references try this;
Create a grid to work out your times tables.
Enter a formula in the 1 x 1 cell. Then copy it across and then down to fill the whole table. Does it work? If not, try out various combinations of RELATIVE, ABSOLUTE and PARTIAL ABSOLUTE references in that single cell until you get the table correctly working out all the values. It may take some time, and you’ll probably think it would have been quicker to do it manually but trust me, spending some time getting this right will help you no end on understanding how to make complex formulas work – whether in a spreadsheet, in building complex conditional format rules etc etc.
If you are completely stuck then the answer is at the end of this blog. Good luck!
Now that you know how to create rules that can be applied directly to cell values and rules based on formulas looking to one or more cells, what happens if you want to change them?
Exactly the same as when we set up rules, we first need to highlight the cell or cells whose rules you want to change or add to.
Let’s go back to our earlier example. Instead of highlighting cells with values over 500 we now want to change to over 600. I could delete the existing rules and start again but no need.
Go to CONDITIONAL FORMATTING, then select MANAGE RULES.
We can now see the rules we initially set up. Through this window you can add, edit and delete any existing rules. Click on the rule you want to edit and click on EDIT RULE.
In this window you can change pretty much everything about the rule.
Make your choices and edit the value that will trigger the rule, then make any changes you want to the format and click on OK, then OK again.
However, there is one thing you need to be careful of – the order in which the rules are applied.
Let’s say I want the following rules applied to my table;
- Values greater than 0
- Values greater than 300
- Values greater than 600
Seems like a fair set of rules. BUT….computers being the logical things that they are, stop once a condition is met. So as it checks the cell value and finds it’s greater than 0, it formats it according to the rule. What it then does not do, is check its value against all the remaining rules ‘just in case’. Logically, a human would check further, but the computer is governed by different rules and once something is TRUE, it looks no further. So our table looks like this:
All the selected cells end up formatted the same way, as they are in fact all grater than 0. If you do this or find that your cells do not format as planned check the running order of your rules. Use the arrows to rearrange the order, click on APPLY and see if it works OK.
If your rules involve test on values LESS THAN, start with the lowest value first.
If you want to change the range of cells the rules apply to edit the range value sin the REFERS TO box.
Now that you know how to go in and edit a rule, how about editing the rules for DATA BARS, COLOR SCALES and ICON SETS?
If you remember from my earlier blog, the DATA BARS etc have a simple preset way of calculating how they work. Three picture ICON sets for instance work on rules of thirds by default. By going through our MANAGE RULES we can edit these to suit our own particular requirements.
Using an ICON set as an example, you can change the operator (>/>=), set your own specific cut off value and decide whether you want to base this on percentage of values, a number, a formula or a percentile. You can also change the icon set, although you have to work out what they are from a description rather than an image.
By default it is assumed that a high value is ’good’ and therefore green, but this may not be the case. You therefore have the option to REVERSE ICON ORDER. The final option you have here is SHOW ICON ONLY. This is quite neat as it allows you to show an icon in a cell by itself rather than have both the data and the icon which can look a little messy or crowded. Problem is you cannot use icons etc when applying a formula to your conditional format.
To get around this minor inconvenience, you can add a new column and create an IF statement that puts a value in the cell and you can then apply your icon set based on the values generated by the IF statement. Simple!
Let’s add a new column to our table called ‘Status’ and add a formula;
In this example I only want to highlight totals that are less than 1000 so if the statement returns a FALSE value it displays nothing. Copy this down to all rows in your table.
Now apply a conditional format so that if the value is 1, display a red ‘X’ icon only.
Voilà! By using this method, you avoid a lot of clutter in your spreadsheet and draw the viewer’s attention only to those values that require a closer look.
Try playing with the settings of the DATA BARS etc. Follow the same steps as above, apply a variety of values and see what effect it has on the appearance of the formatting.
And that is CONDITIONAL FORMATTING!
In the previous blog I showed you how to use the basics of CONDITIONAL FORMATTING to automatically change the appearance of cells or their contents based on criteria. The rules were applied directly to the cell and their contents. However, there are times when you want cells to change format based on something happening elsewhere or perhaps a combination of criteria which trigger the format change.
In order to achieve this we have to create formulas. It’s not that difficult, but you have to think a bit about what you are trying to do, and key to the formulas succeeding is the correct use of relative and absolute references in the formulas. If you are unsure about relative and absolute referencing check my blog (Relative vs. Absolute Referencing) first.
Let’s take a simple table tracking invoice payments.
On the far right is the payment status for each invoice. Let’s imagine for a moment that this column is over on the far right of the spreadsheet and every time you want to check the status of an invoice you have to scroll over to see it. But what if you could get the Order No to show up in red if an invoice is unpaid instead?
Well guess what? Using a formula in conditional formatting will do the trick.
Go to CONDITIONAL FORMATTING as before on your HOME tab, and click on NEW RULE.
This will open a new window with a fairly long list of options to choose from. Most of the options you can achieve using the buttons/icons I used in the previous blog. The one we are interested in this time is USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.
Previously we created conditions or criteria based on the actual cell contents. This time I want an order number to change appearance but the order number itself will never change, so I can’t create a rule based on the contents of the order number cells, so I have to look elsewhere for changes to cause some sort of trigger.
First select all the cells you want to apply your conditional format to. In this example it will be cells B3 to B12. Make sure you select cells correctly: top to bottom or left to right. I’ll explain why a bit later…
Click on CONDITIONAL FORMATTING, then select NEW RULE, and finally click on USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.
Click into the box under FORMAT VALUES WHERE THIS FORMULA IS TRUE and enter the following formula;
Note that any text values you are looking for must be in double speech marks in your formula.
Select a format by clicking on the FORMAT button, and in this example go to the FILL tab and pick one of the purples, and then click on OK. Note that when using formulas you don’t have the option to apply data bars etc as a format.
Trying to add some meaning to this formula…we are saying “if F3 has the word ‘No’ in it, then make the cell I am applying the format to (i.e. B3) change to purple”. We don’t mention the cell B3 in the formula, because by highlighting the cell(s) it is implicit that the formula relates to the selected cell(s).
Problem! If you click on the reference cell, in this example F3, Excel automatically turns this into an absolute reference ($F$3). If you leave it like this, it means that EVERY cell in that column you are applying the rule to will look to cell F3 and nowhere else.
So why does this happen? When you select a group of cells and apply a conditional format rule, you are effectively writing a formula for a single cell i.e. the first one in the selection. In this case it is B3. Because you have selected a block of cells, when you confirm your new rule, Excel copies the formula down (or across depending on the cells selected). So just like a standard formula you might write in a cell, the copied formula will modify its reference points depending on whether the formula cell reference is relative or absolute. This is why you have to select your cells in the correct order: left to right, top to bottom. If you do it the other way round Excel will look in completely the wrong place for its trigger.
So applying our new rule would give us this;
Then, if we update our data at any point it will automatically change according to the rules we have set up;
What if I want the whole line to change format rather than just the first column?
This is where we need to be a little more careful with our relative/absolute references.
So now, rather than select only the cells in column B, select the entire table. Follow all the same steps as above but you will need to make one small change to the formula. Remember that as formulas are copied down or across if the references are relative, the cells the formula looks to shift the same distance as the formula cell as it is copied. We therefore need to make sure that the formula always looks to column F which contains our “Yes/No” words that form part of our conditional statement.
Our formula should now look like this;
Our table will now be formatted properly with the entire row coloured in;
Now we still have one issue with this table and its formatting. If today is the 16th of January, for example, the last invoice is unpaid but it is not actually late. If you want to highlight invoices that are simply unpaid then this formatting is fine, but would it not be better to show up late payments?
For that we need a slightly more complex formula that performs a couple of tests:
- Is the invoice unpaid?
- Is today’s date beyond the due date?
If both these conditions are true then we want to highlight these in red so they stand out so we can chase up our customers!
So we can add a second rule to our table:
- First rule highlights any unpaid invoices
- Second rule flags any that are unpaid AND late
Our formula for this would therefore be;
By using the TODAY() function the formula will automatically adjust to the current date so you don’t have to!
Our finished table would now give us:
So there you have conditional formatting using rules. Of course, formulas can be as simple or as complex as your Excel skills allow. You just need to stop and think about what you are trying to achieve, which cells you are looking at and most importantly, setting the correct relative and absolute references in your formula to guarantee that it works properly.
My final blog on CONDITIONAL FORMATTING will look at editing existing rules and setting up formulas to take advantage of icons etc. rather than applying the rules to the cell contents.
You have a spreadsheet full of data that is regularly, if not continuously being updated and rather than have to scan all the records manually every day, you’d prefer to have some sort of flag or identifier that tells you certain records or cells are above/below a target figure, or perhaps payment of an invoice is late. If you only have a handful of lines to check you could get away with doing it manually. And let’s be honest here, who hasn’t manually coloured in cells to represent some status or other. But, of course this is unbelievably tedious when you have hundreds of records to update and a likely consequence of this is errors and ultimately loss of control over your data. The more records you have, the more likely you are to miss something – the worse it is likely to get over time.
So how can you get all this to happen automatically?
This is where CONDITIONAL FORMATTING comes to the rescue.
Conditional formatting is a set of rules you can create to automatically highlight cells by changing their colour based on their values or rules looking at other cells , or if you are using 2007 or later, colour bars and icons!
Let’s start with the easier of the 2 rules – applying them directly to the cells and their contents.
If you are using 2003 or earlier, unfortunately you are limited to 3 rules per cell. With later versions you pretty much have no limit although setting up lots of rules may get a bit confusing.
I’ll be using the 2007/2010 format as this gives you many more options to work with and is generally easier when applying rules directly to cells.
Let’s take a simple example….
We have a number of values and might want cells to be highlighted if certain conditions are met;
- Cells with values over 500
- Cells with values under 100
First of all highlight all the cells you want to apply a conditional format to. You could apply it to one cell to start with and then try and copy the format across, but personally I find it better to apply rules to a block in one go. At least like that I know that the rule has been applied to everything from the start rather than try to fill gaps later on.
Click on Conditional Formatting on the HOME tab.
You are then faced with an array of choices…..
This is less daunting than it looks. In fact they have simplified the whole process quite well for basic conditional formatting…just trust me on that one.
So the first rule we wanted to apply was to highlight cells with a value over 500.
Click on HIGHLIGHT CELL RULES, and then click on GREATER THAN.
Excel tries to be helpful and ‘suggests’ a value. Simply overwrite it with whatever value you want, or in this example – 500.
Pick a format from the left hand drop down list. There are a few presets you can use, or go to custom format and pick your own format style. Click OK.
And hey presto! There you have all cells with a value over 500 now shown in light red with dark red text. Simples!
Now apply the second rule. Do not click anywhere or you will lose your selected cells.
Go back to conditional formatting, but this time select LESS THAN. Enter the lower value, then make sure you pick a different format to the first rule…sounds obvious but you’d be surprised, so that you can clearly see the different conditions in your table.
Now, if any of the values change in the cells, depending on the condition they meet, the colours will automatically change to reflect that condition.
To remove any rules, go back to conditional formatting and click on CLEAR RULES, then select whether you want to clear rules on the selected cells only, or the entire sheet.
All the other rules under HIGHLIGHT CELL RULES are pretty self explanatory. Each one when selected has its own set of options e.g. A DATE OCCURING allows you to select from Yesterday/Today/Next Week etc.
One brilliant option that was added from 2007 is the DUPLICATE VALUES option. Previously you had to write a formula to identify duplicates, now you can choose to highlight duplicates or unique values. Simply pick a format and Excel does the rest.
Again, does what it says on the tin. You can pick out your top/bottom 10, top 5, top/bottom however many values you want, base your search on percentages or pick out values over or under the average of the selected cells. Couldn’t be simpler!
Also new since 2007 is the introduction of DATA BARS, COLOR SCALES (apologies for the spelling) or ICON SETS. Please note that if you use any of these and save the file in an earlier format you will lose all this formatting as it is not compatible with earlier versions of Excel. These are colourful, some would say distracting, some may be even more disparaging….however, your manager will love them. Pretty colours and icons that tell a thousand words and save anyone having to actually look at details (perish the thought!). Cynicism aside, they do stand out and can serve a purpose if used sensibly i.e. apply them to a single row or column rather than an entire spreadsheet with 1000s of cells.
All it does is look at the highest and lowest values in the data set and shades the cell based on where the cell’s value falls between those to points. Choose from a variety of preset colours.
Not my favourite as it is not as distinct as some of the other formats. Although it is a traffic light system of sorts it has multiple shades within each colour…but it’s up to you If you want to use it or not. Again, you have a number of colours to choose from.
This is the one that will probably make you your manager’s best buddy. Not particularly scientific how it works out which icon to apply – rule of thirds. Top third is green, middle third is amber and the bottom third is red (or whatever equivalent icon is used in the set). Where there are four icons in the set, each one represents 25% of the value range.
All settings for the data bars/colour scales/icons can be edited but I will show you how in another blog. For now, let’s just stick to some standard rules/settings.
So now you have the tools to make your spreadsheets spring to life and alert you automatically as data is changed. So if you have been manually colouring cells until now…..no need. Head straight for the CONDITIONAL FORMATTING button and set up those rules!
On any given day of the week, there are at least a couple of pleas on Twitter for help on how to create charts in Excel. So to assist all of you who have asked, and no doubt for the many who will follow, here are the basics of creating charts in Excel.
First of all make sure your data table is workable. By that I mean make sure that it is one continuous table of data; NO subtotals, NO blank rows/columns within the data, leave a gap of at least 1 row and 1 column around your table, NO double row headings and ideally leave the top left hand corner cell EMPTY. By leaving this cell empty you will avoid a host of potential problems. For basic trouble free charting a simple, basic layout is best.
You can have pretty much as many rows and columns as you like but bear in mind that too much data makes it very hard to read the chart.
So limit the data you want to convert to a chart…
Far more sensible and actually easy to view and understand.
Once you have a usable table click somewhere in it. You don’t have to highlight the entire table. As long as you have clicked in the table Excel will work out how many rows and columns make up the data table. Basically it looks around the cell you have clicked in until it finds empty rows and columns which tells Excel it has found the outer boundaries of the data table. That’s why you leave at least 1 row and 1 column between your data table and any other data in your worksheet to avoid including data that is not part of the table you want to base your chart on.
That’s the hard bit out of the way!
You have 2 keyboard shortcuts available to you to instantly create a chart;
- F11 creates a chart in a new chart sheet
- Alt + F1 creates a chart on the same worksheet as your data table (v2007 onwards)
There you are…one chart done and dusted. Using these shortcuts will use whichever chart type is set as the default on your PC. You may well find that the default is a column chart. In fact this is a very good default type to have. Plain, but it works, and usually shows the data in an easy to understand layout.
If you want to change the chart type, make sure you have clicked on the chart to activate it. Once selected you will see 3 new tabs top right hand corner of Excel; DESIGN, LAYOUT and FORMAT.
Make sure you are on the Design tab and over on the far left you will see CHANGE CHART TYPE. Click on that and you will get the full list of available char types. Not all of them are recommended (for a variety of reasons). If you are interested in which charts you should or should not use, check out this blog by Jon Peltier http://peltiertech.com/WordPress/excel-chart-types/
If you want to have a little more input into how your chart is created then start off exactly the same way as above i.e. click somewhere in your data table. Then click on the INSERT tab (if you are using 2003 or earlier, you have the chart wizard to help you. Just click on the chart wizard icon and follow the prompts), and from the Charts group click on one of the chart options, then choose from one of the sub-options that appear. To see the full range of charts click on the launch button in the bottom right hand corner of the group icons.
Now it is simply down to editing the overall appearance of your chart. Once again make sure the chart is activated by clicking on it, then check out the three CHART TOOLS tabs;
- Design: change chart type, pick from a range of preset colour schemes, switch the rows and columns over
- Layout (this is where you’ll do most stuff): add titles, axis labels, position your legend, add series labels, show/hide data/axes/gridlines
- Format: change shape and text styles. Chances are you won’t do much here.
The DESIGN ribbon;
The LAYOUT ribbon;
The FORMAT ribbon;
The only other thing you might want to do is change the appearance of the series and the chart background etc. All you have to do is RIGHT CLICK on any part of the chart and you will get the option to format whichever part of the chart you have clicked on. Choose from the multitude of options available to you. I could go through each one but it’s more fun trying things out for yourself. If it all goes pear shaped just do Ctrl + Z to undo your last change.
And there you have basic charting.
If you want to learn more about charting techniques try out these great websites:
Follow me https://twitter.com/excelmate on Twitter
Or check out other articles/blogs on http://excelmate.tumblr.com/