OzGrid Excel and VBA Newsletter

Software Categories Search Software

Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software

COMPLETE EXCEL COURSE SPECIAL!

Newsletter Index

ARE YOUR SPREADSHEETS DULL? | EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS ARE YOUR SPREADSHEETS DULL?

Excel is the industry staple for collecting and sorting data in a spreadsheet. But what happens when you want to put that data on display in order to persuade or compel someone to take action? Staring at dull spreadsheets full of numbers is not the most inspiring way to communicate your point. This is where Xcelsius comes into play.

Xcelsius is the award winning Windows software that animates your Excel spreadsheets, turning them into interactive and compelling dashboards , graphs , charts , financial calculators , and more. It uses a simple "point-and-click" interface (so anyone can use it) and with the push of a button exports to PowerPoint, HTML, or Outlook (so anyone can see it).

To get Start ed, you first import the Excel spreadsheet you want to animate. With the push of a button you actually imbed your spreadsheet's data inside Xcelsius, removing any further need for Excel.

Next, you get to choose from over 42 components and 9 charts, including pie charts, line graphs, grids, and more, to represent your spreadsheet's data. You can visually represent anything on your Excel spreadsheet, including the titles of the chart, imbedded formulas, the x and y axis labels, multiple data ranges, and so on.

In order to make your data interactive, you need to add a slider, gauge, spinner, or other component to your model, and associate it with any dynamic variable that is supposed to change in your spreadsheet. For example, it could be the estimated revenue forecast , the amount of return merchandise in a retail store, or anything else you can imagine.

When you're ready, you can quickly preview your final product in Flash. If you're satisfied with the results, do a one-button export to PowerPoint, HTML, Flash, or email. The results are stunning, interactive presentations that make sure you never look at your Excel spreadsheets the same way again.

The Xcelsius web site includes detailed support, including a comprehensive Learning Center and several "Quick Start " guides that walk you through the steps of creating your first interactive presentation. Xcelsius is available in three different editions: Standard, Professional , and Enterprise Suite . Or, try Xcelsius FREE here .

EXCEL TIPS AND TRICKS

Named Ranges in Excel have been around a long time. Using them frequently is a very good habit to form. The advantages are;

1)Formulas are easier to read and understand.
2)VBA code is not adversely affected by moving cells.
3)Range reference can be changed globally in one place.

Naming any Excel range is very easy and can be done in seconds.

1)Select the range of cells to name
2)Click in the Name Box (left of formula bar) and enter a one word name (no spaces allowed) and push Enter.

Then you can replace a formula like: =SUM($A$1:A$100) with: =SUM(Yr2005Figures)

If you already have $A$1:A$100 in many cells throughout the Workbook and now wish to use a named range in its place, simply use Edit>Replace with the Options "Match case" and "Look in formulas" chosen. If you like more control on how and which cells are replaced, go to Insert>Name>Apply and use the built-in Apply Names feature. Just be aware that the feature will only work in the current active sheet.

The only way to delete an existing range name, or modify its cell reference, is via Insert>Name>Define.

Ok, let's now go a step further with Named Ranges and look at Named Constants. These are simply values that are given a meaningful name. For example, let's say you have a Workbook that has many formulas using the percentage of 20% and this represents some sort of 'special tax'. You can replace all those occurrences of 20% with a Named Constant like "SpecialTax". Here is how you do this.

1)Go to Insert>Name>Define and in the "Names in workbook" box type: SpecialTax
2)Now click in the "Refers to" box and type: =0.2 then click Add.

Now you can replace formulas like: =SUM(Yr2005Figures)*20% with: =SUM(Yr2005Figures)*SpecialTax

You can again either use Edit>Replace or Insert>Name>Apply if needed.

The best thing about Named Constants is that any constant can be changed to another value globally via Insert>Name>Define.

Now we have seen how easy Named Ranges and Named Constants are, we can look at a little known ability of Names where they can used to create custom formulas. For example, let's suppose we frequently use a formula like: =SUM(A1:A100)-SUM(B1:B100) and this resides in A101 and is copied across
many columns on row 101. It would be better in this case to create a custom formula that does this in each cell on row 101. Here is how;

1)Select cell A101 (this is vital).
2)Go to Insert>Name>Define and in the "Names in workbook" box type: SalesLessCosts
3)Now click in the "Refers to" box and type: =SUM(A1:A100)-SUM(B1:B100) then click Add.

Now you can replace the formula in cell A101 with: =SalesLessCosts You can also copy this across row 101 and it will change its relative references just as the formula =SUM(A1:A100)-SUM(B1:B100) would. The reason it does this is all down to the fact we selected A101 before going to Insert>Name>Define and used relative references in =SUM(A1:A100)-SUM(B1:B100) when we added it to the "Refers to" box.

You could force full Absolution simply by using: =SUM($A$1:$A$100)-SUM($B$1:$B$100) or relative row absolute column like: =SUM($A1:$A100)-SUM($B1:$B100) or, of course any mix of absolute/relative row/column you desire. This can be a bit confusing at first, but some trial and error along with being aware of the selected cell when going to Insert>Name>Define will help a lot!

Let's say you wanted to have these results only on another sheet to where the $A$1:$A$100 and $B$1:$B$100 ranges are. For a standard formula you would use a formula like: =SUM(Sheet1!$A$1:$A$100)-SUM(Sheet1!$B$1:$B$100). However, we cannot use: =Sheet1!SalesLessCosts as Excel will assume you mean the Workbook name and change it accordingly. Without it Excel will sum the *relative* cells on the same Worksheet as you Enter: =SalesLessCosts

What we need to do is precede the name in the "Names in workbook" box of Insert Name dialog with: 'Sheet1'! The single apostrophes are not required when the sheet name has no space, but are when a space character is used as part of the Worksheet name, i.e. "Sheet 1". With this in mind, it pays to use them anyway as it won't matter.

1)Select cell A101 (again, this is vital).
2)Go to Insert>Name>Define and in the "Names in workbook" box replace: SalesLessCosts with 'Sheet1'!SalesLessCosts and click Add.

Now, in the required cell simply Enter: ='Sheet1'!SalesLessCosts

This same principle can be used in creating sheet level names. Normally when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!

Assume we have a Workbook with 3 Worksheets. These 3 Worksheets are simply named Sheet1, Sheet2 and Sheet3. We want to have a named range called MyRange (can be any legitimate name) that will refer to the range Sheet1 A1:A10 when on Sheet1, Sheet2 A1:A10 when on Sheet2 and Sheet3 A1:A10 when on Sheet3. Here is how;

1)Activate Sheet1
2)Select the range A1:A10
3)Click in the Name Box. This is to the left of the Formula Bar below File Edit on the Worksheet Menu Bar.
4)Type Sheet1!MyRange
5)Push Enter

Do the same for Sheet2 and 3 using Sheet2!MyRange and Sheet3!MyRange

Now activate any sheet and click the drop arrow on the Name Box. You should see only one occurrence of the name MyRange. Select this and you will be taken directly to the range A1:A10. Now activate any other sheet and do the same. You will always be taken to the range A1:A10 of the active sheet.

The reason we can do this is because we preceded the name with the sheet name followed by the ! (exclamation mark). If you go into Insert>Name>Define you will note that you only see one name and that name is the one that refers to the current active sheet.

If your Worksheet name includes spaces you cannot simply use Sheet1!MyRange. What you must use is 'Sheet 1'!MyRange. In fact you can use the single apostrophes with a Worksheet name with no spaces. It is a good idea to always use the single apostrophes when referring to Worksheet name as it covers all bases.

EXCEL VBA TIPS AND TRICKS

One of the most common problems encountered by those that write and use Excel VBA code is perhaps the finding of last cells in a range, or the entire Worksheet. The macros below cover most bases and should help anyone using VBA code to accurately identify the last cell. Many use the Select Method purely to help you see the cell. As you may know, selecting a cell is rarely needed in good VBA code.

Find the last used cell, before a blank in a Column:Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub

Find the very last used cell in a Column:Sub LastCellInColumn()
Range("A65536").End(xlup).Select
End Sub

Find the last cell, before a blank in a Row:

Sub LastCellBeforeBlankInRow()
Range("A1").End(xlToRight).Select
End Sub

**Find the last Row, Column or Cell**

You can use Edit>Go to-Special-Last cell to try and find the last cell in the active sheet, but it is not very reliable. The reasons are two-fold:

1. The last cell is only re-set when you save. This means if you enter any number or text in say, cell A10 and A20 of a new Worksheet, then delete the content of A20, the Edit>Go to-Special-Last cell will keep taking you to A20, until you save.

2. It picks up cell formatting. Let's say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. The Edit>Go to-Special-Last cell will still take you to A20. This is because entering a date in A20 has caused Excel to automatically format the cell from "General" to a Date format. To stop from going to A20 you will have to use Edit>Clear>All and then save.

IMPORTANT: Resist the use of Edit>Delete as this can cause the dreaded #REF! error when formulas are referencing the deleted rows/columns or cells.

So when using VBA you cannot rely on:
Range("A1").SpecialCells(xlCellTypeLastCell).Select

The same applies when using the UsedRange Property
ActiveSheet.UsedRange.Select

**Below are three methods that will find the "LastRow", "LastColumn" and the "LastCell"**

Find the last used Row on a Worksheet:Sub FindLastRow()
Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row        MsgBox LastRow
    End IfEnd Sub

Find the last used Column on a Worksheet:

Sub FindLastColumn()
Dim LastColumn As Integer
    If WorksheetFunction.CountA(Cells) > 0 Then        'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column        MsgBox LastColumn
    End IfEnd Sub

Find the last used Cell on a Worksheet:

Sub FindLastCell()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
    If WorksheetFunction.CountA(Cells) > 0 Then       'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row      'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column        MsgBox Cells(LastRow, LastColumn).Address
    End IfEnd Sub

Find the very last used cell in a Row:Sub LastCellInRow()
    Range("IV1").End(xlToLeft).Select
End Sub

Find the very last used cell on a Worksheet:Sub Demo()
    Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub

Until next month, keep Excelling!ADVERTISEMENTSArtificial neural network software for stock markets! EXCEL TEMPLATES SPECIALS DATABASE SOFTWARE