Visit our homepage, you might be surprised at what is free.

Tuesday, 03 November 2009

OzGrid's Free Excel Newsletter

MicrosoftExcel and VBA for Excel Training Information (auto response)

OrderTraining Online

Over 200 Excel Add-ins and Business Software

Training Specials!

What's new at OzGrid.com

Hi all,

It has been a very busy month for us here at OzGrid, with an increase in training and consultancy work.  We are approaching the end of the Australian Financial year, so lots of our local clients have requested training or consultancy before 30 June 2002.  This has made our usually large workload huge as we are both needing to spend time away from the office fulfilling these commitments.  We are hoping for a quieter month in July!!!!

However, as we are superhuman, we have still found time to update and improve upon our website, and have finally put the finishing touches to an Add-In that was started some time ago.  Simply put, an Add-in (or Add-on) adds extra functionality to Excel.  Microsoft Excel already has a number of Add-ins that can be installed by opening Excel and going to Tools>Add-ins and simply selecting the Add-in you want. Once installed Excel will add a new menu option to your Toolbar. Possibly the best known Add-in is "AutoSave" which automatically saves your Workbook at set time intervals.

The OzGrid Plus Add-In encompasses all of the following:

Formula Report Generator

This allows you to very easily create a report showing formulas within your Workbook that are either external references, internalreferences or both. You can also nominate which Worksheet(s) to use to base your report on.

Dynamic Range Namer

This will allow you to very quickly and easily create any one of eight different types of dynamic ranges that will expand down rows or across columns. You can even nominate the column or row to use as the criteria. It can also expand automatically each day, month or week.

Real Calendar

This one will create a live calendar in an instant using real dates, so you can reference it if needed.

Sheet Creator and Sorter

This option will create x number of pre-named worksheets which can be FinancialYears (1901-2050), Weekdays (Monday-Sunday), Months (January-December), Years (1901-2050), Quarters (Quart1-Quart4),Days (Day1-Day1460) and Weeks (Week1-Week208). It also has an option that allows you to Sort (ascending or descending) any Worksheets in any Workbook.

A Random Number Generator

This can produce up to 1000 non repeating random numbers that meet a criteria you specify.

Conditional Row Deletion

This one will save many hours of repetitive work when you need to quickly and easily delete rows in a Worksheet that meet any criteria. There is an option that allows you to easily nominate any one of Excels six Error Types, or all of them. There is also a simple option that allows you to nominate the criteria.

Duplication Report

Will produce a statistical table of your data. Including a Count of occurrences of each item, the total number of duplicates and a total of all entries. It will also produce a list of your data containing only unique entries. Much like a Pivot Table in a single click.

Do On Time

This feature allows you to run a macro (recorded or written) at a set time, display a personal message or reminder at a specified time or even open a nominated Workbook at a set time.

Read all about this add-in and our other very popularAdd-ins Here.

NewInformation Added to Site

NewNewsletter on the Web

The Word Expert: http://www.thewordexpert.com/

Has recently started a FREEmonthly newsletter that looks very promising! Follow the above link to join, or clickhere and Send.

Note this is NOT a paid advertisement.I have corresponded with Dreamboat (The Word Expert) for a few years now. Wehave also posted answers to questions on many of the same Question and AnswerWeb sites. She is one of the few genuinely generous people out there! 

Microsoft Excel tips

I thought for this month's Excel Tips we would cover a very important part of Excel that can make the difference between a good spreadsheet and a poor one.  That is Named Ranges and Constants.

By default, Excel uses the A1 style reference for cell addresses.  However, we can override this default and apply meaningful names to our cell(s).  For those of you who are not familiar with naming ranges within Excel, it is a very simple process that allows us to change the way we read formulas.  Instead of having a formula read =$E$1*$E$2, where $E$1 may store a specific Tax Rate, and $E$2 an employees wage, we can use named ranges to make our formula look like =TaxRate*Wage.  Perhaps the biggest advantage to doing this is that our formulas become very easy to read and understand. 

Naming Ranges - The Basics

To name a range in Excel it is very easy, but there are a few rules that must be adhered to:

1. The first character of a name must be a letter, or the underscore (_) character.
2. We cannot name a cell the same name as an existing cell reference, eg; B22 or R22C2.
3. Spaces are not allowed.  However, this can be overcome by the use of the underscore (_), period (.) or capitalising the first letter of each word (eg: TaxRate).
4. They cannot exceed 255 characters
5. If it contains more than 253 characters, you will not be able to select it from the Name Box.
6. Names are not case sensitive (taxrate is the same as TaxRate).
7. All named ranges are absolute by default eg; TaxRate will have a cell reference of $E$1.

To name a basic range in Excel, you simply select the cell or cells you wish to apply a name to, and click at the top of your screen immediately to the left of your formula bar in what is called your Name Box.  (This will highlight the active cell address that you will type over - If you haven't noticed before, this box always displays the address or name of the active cell).  Type the name that you wish to apply to your range selection.  Once you have done this, simply push Enter.

From now on, no matter where you are in your Workbook, you will be able to click the drop arrow to the right of the Name Box and you will see a list of all standard named ranges.  Click on the one you require and you will be taken to that location.  When you type a formula starting with an = sign, and then use your mouse pointer to select the cell you wish to reference, instead of Excel inserting the A1 style reference address, it will automatically insert the name you have given the cell.

If you have already set your workbook up with possibly thousands of formulas referencing only cell addresses, and you decide that you should name your ranges but cannot face the task of manually going through changing all cell references to your newly named ranges, don't despair because Excel has made this extremely easy to do.  The best way to see this is to follow this simply example.

1. In C1 type =A1*B1
2. Copy this relative reference formula down to cell C3.
3. Select cell A1 and name it aRange1
4. Select A2 and call it aRange2
5. Select A3 and call it aRange3
6. Repeat steps 3 - 5 starting from cell B1 and using the name bRange1 etc.

Imagine you have hundreds of formulas like this all referencing these cells.  Rather than go through and change each individually, you could perhaps use Excels Edit>Replace feature to replace all "A1" with "aRange1", but this is really tempting fate and could cause all sorts of problems if you are not fully aware of the consequences of what you are doing.  Here is the correct method for doing this.

1. Select the range of cells containing the formulas (that are currently using A1 style reference) you want to replace with named ranges, or only a single cell if you want this to affect the entire worksheet.
2. Go to Insert>Name>Apply
3. Select the name(s) you wish to use in place of references
4. Click OK.

If you now click back in any cell that was previously referencing a cell address, that you have replaced with it's name, you should see that Excel will have automatically replaced all cell references with the named range.  You will have noticed there are a few other options available when using this feature which are very easy to use, but as with most things, trial and error on an unimportant workbook is the best way.

Deleting/Modifying Named Ranges

To delete or modify a Named Range, you must go to Insert>Name>Define or (Ctrl+F3).  Once here you simply select the name you wish to delete and click Delete.  If you are wanting to modify where the Named Range refers to, simply select its name and then modify accordingly in the Refers to: box at the bottom, then click Add.

Creating Names Very Quickly

Let's look at how we can use existing column and row labels to name ranges in Excel.  For this example do the following:

1. Type the headings Head1 to Head5 in cells B1:F1. 
2. In cell A2:A6 type the row headings Row1 to Row5.
3. In the cells B2:F6 type any data you wish.
4. Select the range A1:F6
5. Go to Insert>Name>Create
6. Ensure that Top row and Left Column are the only options checked
7. Click OK.

If you now select the drop arrow to the right of your name box, you will see that by selecting one of the newly created names you will be taken directly to that range.  This can save a lot of time and mis-typing if you have a large table of data.  Don't be concerned that Excel may run into problems if you use too many named ranges, as I have frequently worked on Workbooks containing hundreds without any problems.

What we can now do is cross reference our table and very easily extract out information at the intersection of two named ranges.  For example, if we wanted to find out what resided in the intersecting cell of the named range Head3 and Row4 (eg; cell D5), all we simply need to do is type =Head3 Row4 and push Enter.  It is very important to note the space between the two named ranges.

As you can see this method can be used in place of some very deeply nested Look-up formulas.  The one that springs to mind immediately is the VLOOKUP and/or the HLOOKUP.

Naming Non-Contiguous Ranges

Our ranges that we name are usually continguous, in other words all cell boundaries are adjoining.  However, if there is a need to name non-contiguous ranges, we can do this simply by selecting our cell(s) and holding down our Control key.  Once we have our selection, we simply again go up to our Name Box and type in our name.  Just check you have incorporated the correct range, by selecting the name from the Name Box and seeing where Excel takes you.

Lets assume we have named our non-contiguous range MyRange and it incorporates many different cell references.  We can now replace a formula that may have looked like =SUM(A1:A5,D6:D10,Q50:Q57,BB30:CC43) with =SUM(MyRange).  Just be very careful when doing this as not all Excel's functions that take ranges as their arguments will exclude the cells between the non-contiguous named range.  As with all things, if uncertain, give it a thorough test first.

Naming Constants

It is often when developing a spreadsheet that you will be constantly referring to a specific figure for calculations, such as a Tax Rate, Superannuation Rate etc.  This often means either referring to a cell containing the value or simply typing the value in the cell itself.  What we can do is instead of naming a cell or range of cells, is name a constant value.  For example, you may be using calculations such as =(EmployeeRate*TaxRate)-8%, where the 8% represents the employers Superannuation contribution.  We can easily replace the constant value 8% (0.08) with a more meaningful name.  To do this:

1. Go to Insert>Name>Define
2. Type the name SuperCont in the Names in Workbook: box
3. Click in the Refers to: box and type in 0.08.
4. Click Add, then OK.

The two advantages of doing this are:

1. It makes our formulas easier to read at a later date.
2. We only need change the Superannuation Contribution rate at one central point for it to take effect thoughout our entire workbook.

Naming Formulas

As mentioned above Excel uses Absolute cell references for named ranges, this is normally what is required.  But there might be times when a Relative reference would be better. Assume you have a list of 300 employee names in Column A, Column B has their pay rate per hour, Column C the hours they have worked. We would normally use a formula like: =$B2*($C2*24). Note we must multiply the hours by 24 to get the correct result, otherwise a time value like 8:00 would have a real value of 0.333333333333333 times this by 24 and you get a true value of 8. Lets replace this formula with a named formula.  Note that the cell selection in this case only reflects a table set up as stated above.

1. Select cell D2 and go to Insert>Name>Define
2. In the Names in Workbook box, type in PayOwing
3. In the Refers to box type =$B2*($C2*24)
4. Click Add, then OK

In cell D2 type =PayOwing and copy this formula down and you will have the pay owing for each employee in the list.  The very important point to note here is that we had cell D2 selected before we went to Insert>Name>Define and we used a relative row reference in our formula.  This means that whenever we copy this named formula PayOwing, it will always multiply the cell two columns to the left on the same row by one column to the left *24.

Microsoft Excel VBA tips

For this months VBA tips lets look at how we can use a combination of worksheet cells and Excel's  formulas to greatly enhance and make our code more efficient.  Lets start off with something relatively simple and assume that you want to have some code that would automatically insert a sum function at the end of a range of numbers to give you a total.

SubSumUp()
Dim strAddress As String

strAddress = _
Range("A1", Range("A65536").End(xlUp)).Address

 If Range(strAddress).Cells.Count > 1 Then
    Range("A65536").End(xlUp).Offset(1, 0).Formula = _
                        "=Sum(" & strAddress & ")"
 End If
End Sub

Sub SumDown()
Dim strAddress As String

strAddress =Range("A2", Range("A65536").End(xlUp)).Address

 If Range(strAddress).Cells.Count > 1 Then
     Range("A1").Formula ="=Sum(" &strAddress & ")"
 End If
End Sub

Sub SumRight()
Dim strAddress As String

strAddress =Range("B1", Range("IV1").End(xlToLeft)).Address

 If Range(strAddress).Cells.Count > 1 Then
    Range("A1").Formula = "=Sum(" &strAddress & ")"
 End If
End Sub

Sub SumLeft()
Dim strAddress As String

strAddress =Range("A1", Range("IV1").End(xlToLeft)).Address
 If Range(strAddress).Cells.Count > 1 Then
      Range("IV1").End(xlToLeft).Offset(0,1).Formula = _
                            "=Sum(" & strAddress & ")"
 End If
End Sub

As you can see from the four examples above, the code needed to do this is fairly straight forward and simple.

Let us now use a slightly more advanced example of how we could go through a range of text and perhaps extract only the first word in each cell.  The most common method used for this would be a loop.  However, if you have thousands of cells, a loop can be tediously slow.  The method I commonly use for this and similar type objectives is by the use of Excel's excellent built in text functions.  The very first thing we would need to do if the text resided in column A is simply insert a nested text formula into cell B2 that would give us our correct result.  This could be:

 =LEFT(A2,FIND(" ",A2)-1)

This would extract the first word from the cell A2 and return the result in B2.  What we need to do now is discover how we would write this formula into a cell in Excel using relative references as we have here.

The easy way to do this is to simply select cell B2, record a macro pushing F2, followed by Enter.  Then stop recording.  This would generate the code as shown below.

ActiveCell.FormulaR1C1 = _
"=LEFT(RC[-1],FIND("" "",RC[-1])-1)"
Range("B3").Select

We will be using the code generated to extract the first word from the thousands of cells in column A, which will be on average about ten times faster than any loop!

Sub ExtractFirstWord()

Columns(2).EntireColumn.Insert

    With Range("A1", Range("A65536").End(xlUp))
            .Offset(0, 1).FormulaR1C1 = _
            "=LEFT(RC[-1],FIND("" "",RC[-1])-1)"
            .Value = .Offset(0, 1).Value
            .Offset(0, 1).Clear
    End With

End Sub

This one Procedure will out performany other method I know of and is very fast even on an entire Column. A loopworking on an entire Column extracting the first word only would be horriblyslooowww!

You may of course need toincorporate an IF Function in the above to prevent any #VALUE! errorsshould the cell on contain one word, eg:

=IF(ISERROR(FIND(" ",A2)),A2,LEFT(A2,FIND(" ",A2)-1))

Keep thatWorkbook size and speed optimised

Some Workbooks can contain manythousands of Formulae and this can cause the file size to increase and slow downrecalculation. If you have array formulae then the effect is compounded manytimes over! A very simple Procedure you can use to get around this problem isthis:

Sub CopyDownFormulae()

    With Sheet2
        .Range("A1:L1").AutoFill Destination:=.Range("A1:L5000")
        .Range("A2:L5000") = .Range("A2:L5000").Value
        .Range("A1:L1").Font.ColorIndex = 3
    End With

End Sub

What this code will do is copy theFormulae that reside in A1:L1 down 5000 rows that convert all except A1:L1to values only. Now, as this can potentially cause errors in the results(static formulae) it also changes the Font color of A1:L1 to Red, so youcan easily tell the results below row 1 are static. It also leaves all theformulae in A1:L1 so there is no need to retype any formulae. To updatethe results, simply run the Procedure again

The Procedure could also be set torun upon the Workbook opening by placing a Run Method in the Private Module ofthe Workbook Object. To do this, simply right click on the Excel icon, top leftnext to "File", select "View Code" and place in this:

Private Sub Workbook_Open()
    Run "CopyDownFormulae"
End Sub

The Procedure  CopyDownFormulaemust reside in a Standard module.

OK, that's it for this month - until next month - keep Excelling!

Kind regards

Dave Hawley

[email protected]

HelpfulInformation

OrderTraining Online

HireOzGrid For Excel/VBA