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.
New Excel Charting Lessons (currently 50% off)
New List Manager Add-ins (currently at 50% off)
Free Custom Functions Add-in (Free only for 1 month more and 3 new functions added)
Spark-Lines, Tiny Chart, Graphs and Trends
Excel Trader Package (price slashed)
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
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
Use as few Workbooks and Worksheets as possible. 1, normally.
Separate the data from the final result(s).
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