Excel is what we do best
ALL YOUR EXCEL NEEDS
FREE Excel STUFF
Excel Newsletter
Advanced Search Search Excel Content
PRODUCTS
Development
Contact Us
Learn how to create Excel dashboards.

Ozgrid Excel Newsletter. Excel Newsletter Archives  

Excel Dashboard Reports & Excel Dashboard Charts 50% Off and/or Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $70.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

As this is the 1st paid newsletter, I wanted to offer all of you loyal subscribers some super specials.

EXCEL SPREADSHEET DESIGN AND LAYOUT

I'm actually quite excited about writing for a smaller audience. Seems more personal, somehow. I thought would start off the newsletter with a bit about my thoughts on how Excel Spreadsheets should be set up for efficiency. For full details, see Excel Best Partices

THE BIG GRID

Yes, that last word is the second half of my Company name, and by no accident. A Worksheet is simply one big grid with many more rows than columns, by no accident either. Excel has always been a flat file/line database. Trouble is, it is rarely used as such by most users. Instead, most simply let their 'taste' guide them. That is, what looks good visually is good enough. Sadly this is not true. Spreadsheets can look good and be useful, efficient, easy to use and, above all, give the correct information. It's called the 80-20 rule. Spend 80% of your time planning the Spreadsheet and 20% of your time implementing your plans.

3 GOLDEN RULES

  1. Use as few Workbooks and Worksheets as possible. 1, normally.

  2. Separate the data from the final result(s).

  3. Use more rows than columns.

Let's explain the 2nd golden rule. You should have 1 large continuous table containing ALL your data. This data will only been see by you, so no need to format cells beyond basic number, date and time displays. Bold row 1 cells, these are YOUR DESCRIPTIVE headings. No blank cells, repeat entries as needed. If there are blanks, hit F5, Special Cells, Blanks - OK now Enter a zero.

From this point, you'll probably want to create a few Dynamic Named Ranges. Here's a list of the most common functions you'll end up using.

What I would like to discuss at this point is how to keep lookup formulas to a minimum and how to set them up efficiently. I'll use the VLOOKUP () for example purposes.

LESS IS MORE

The first thing is the lookup value. Why not create a Validation List of all items in your left most column? This way, we can change the value to be found. This alone can save hundreds of excess Lookup Functions.

SORT THAT DATA

One way we can speed up lookup formulae is to sort the lookup column.

FORMULAE AND DATA ON THE SAME WORKSHEET

As you have a nice flat line database on a Worksheet, why not store the lookups on the same Worksheet? This way, you aren't giving Excel extra overhead by asking it to lookup data on another Worksheet. As I have said above though, "Separate the data from the final result(s)." So after placing the lookup functions on our database sheet, simply reference them where you want your results.

LOOKUP ONCE AND ONCE ONLY

Time and time again I see lookups nested within the ISNA Function FORCING Excel to double the amount of calculations to perform. The alternative is just so simple most never use it. That is, as we already have our lookups on the database Worksheet, we can simply reference them with: =IF(ISNA(Database!A1),0,Database!A1)

EXCEL 2007 ROW NUMBERS

Excel 2007 user beware! Excel now offers many more rows than any previous version. BUT, if you use all rows you may not ever be able to open the Workbook. Even in past versions, Excel offers more rows that it can handle, and Excel 2007 new row limits are wishful thinking on Microsofts part.

 

EXCEL VBA. FINDING THE LAST USED CELL ON A WORKSHEET OR COLUMN

Finding the last used cell on Worksheet, or in Column, is a vital need in Excel VBA. The SpecialCells Method includes any cells that have had ANY default formatting changed, as does the UsedRange. Here is a Custom Function I use often. Copy paste into a Module and Run "Sub ExampleProcedure"

Dim rLastCell As Range


Sub ExampleProcedure()
    Run "LastUsedCell", 1
        If Not rLastCell Is Nothing Then
            MsgBox rLastCell.Address
        Else
            MsgBox "The Sheet is empty"
        End If
End Sub

Function LastUsedCell(lWsIndex As Long, Optional strColumn As String) As Range

'''''''''''''Written by www.ozgrid.com''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Returns a Range Object of the last used cell on Worksheet or in a Column.
'Sheet index number (lWsIndex) is mandatory and Column letter (strColumn) is optional.
'Example call from Procedure in the same Module.
''''''''''''''''''''' Run "LastUsedCell", 1, "A" '''''''''''''''''''''''''''''''''''''''
'IMPORTANT. WILL FAIL IF SHEET INDEX USED IS NOT A WORKSHEET.


    If strColumn = vbNullString Then
        With Sheets(lWsIndex).UsedRange
            Set rLastCell = .Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
        End With
    Else
        With Sheets(lWsIndex)
            Set rLastCell = .Cells(.Rows.Count, strColumn).End(xlUp)
        End With
    End If
End Function

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft