 |
|
|
Questions? Excel Help
Excel is without doubt a very powerful spreadsheet application and arguably the best in the world. However, many people often design their spreadsheets with no foresight at all. This means most spreadsheets have poor foundations and have limited life spans. Perhaps the number one rule when designing a spreadsheet is, we should start with the end in mind and never assume you will not need to add more data or formulae to your spreadsheet, because, the chances are that you will. A good spreadsheet should have about 80% planning and 20% implementation. While this can seem extremely inefficient in the short run, I can assure you that the long term gain will far outweigh the short-term pain. Remember that spreadsheets are about giving correct information to the user, not possible erroneous information that looks good. Let's look at how a spreadsheet should be set up efficiently.
The very 1st thing you SHOULD know about Excel is that ALL versions contain far more rows than the Application can ever hope to handle. Why Microsoft continue to mislead its users by supplying far more rows than it can handle remains a mystery. My best guess is that, like most car speedometers, the upper range exceeds the cars capabilities.
Even with newer versions, the expectations far exceed the Applications capabilities. In fact, the problems (misleading of its users) gets worse. For Example, Excel 2007 now has 1 million rows and if you fill just 1 column using all rows, save a close, you will very likely never be able to open the Workbook again. BUYER BEWARE AND DON'T FORGET ABOUT MS ACCESS!
While a spreadsheet should be easy to read and follow, this should rarely be at the expense of efficiency. I myself am a big believer in 'keep it simple stupid' (K.I.S.S). Far too many people spend about 30%, or more, of their time formatting their spreadsheets. This time (although they don't realize it) often comes at the expense of efficiency and accuracy. Often the overuse of formatting adds size to your Workbook and while it may look like a work of art to you, it may look terrible to another. Some very good universal colors are black, white and grey.
Only ever use the cell format of Text if really necessary. This is because all data entered into the cell becomes text and spreadsheets are all about numbers. Worse still, is any cell housing a formula, that is referencing a Text formatted cell, will also become formatted as Text. Believe me, you do not want formula cells to be formatted as Text! This format is VERY rarely needed, yet frequently used.
If you apply a number format to specific cells try to not apply the format to the entire column. If you do, Excel will assume you are using these cells. This can give unexpected results when locating the last used cell. With 65536 (1 Million in 2007) rows in Excel it wont take too long before you had told Excel that you are using millions of cells, when in reality the number is only in the thousands! Having said not to format entire columns & rows, it should be noted that intermittent & sporadic formatting to individual cells can increase file size.
If you want your formatting to automatically apply to new data, either use the "Format Painter" (paint brush icon on the formatting toolbar). Or go to Tools>Options | Edit and check "Extend list formats and formulas" (checked by default) this will format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.
See also: Excel Ranges
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.
So when using VBA you cannot rely on:
Range("A1").SpecialCells(xlCellTypeLastCell).Select
Below is a method that will find the REAL last used row
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 If End Sub
Another common mistake with cell formatting is the changing of alignment of cell data. By default, numbers are right aligned and text is left aligned, leave it this way! If you Start changing this, you will not be able to tell at a glance if the contents of a cell is text or numeric. It is very common for people to reference cells, that look like numbers, when in reality they are text. If you have altered the default alignment, you will be left scratching your head. Perhaps the exception to this rule is headings.
Merged cells are (in my opinion) to be avoided. The Microsoft Knowledge base is full of frequently encountered problems with merged cells. As a good alternative use "Center across selection" found on the Alignment tab of the Format cells dialog under "Horizontal".
When setting out you data, follow these golden rules as closely as possible.
If you can stick to these guidelines you will thank yourself later as you will then easily be able to use many of Excels powerful built in features, which often need the data laid out this way.
See also: Excel Formula Errors.
This is the biggest part of any spreadsheet! Without them you really only have a document. Excel has over 300 built in Functions (with all add-ins installed), but chances are you will only use a handful of these.
The number one mistake made in regards to formulae in Excel is the referencing of entire columns, this is a big mistake! This forces Excel to look through potentially thousands, if not millions, of cells which it need not be concerned with at all. Although later versions of Excel are getting smarter at detecting 'dirty cells'. Assume, for example you have a table of data ranging from cell A1 down to H1000. You may decide that you want to use one or more of Excel's look-up formulas to extract the required information. As your table may be growing, as new data is added, it is common to reference the entire table incorporating all rows. In other words, your reference may look something like "A:H", or possibly A1:H65536. The reason this is done is so that when new data is added to the table it will automatically be referenced in the formulas. This is a very bad habit to form and should be avoided in most cases. This obviously poses the problem that if you do not do this, you will be constantly needing to update your formula references to incorporate the new data as it is added to the table.
Possibly one of the very best ways to overcome this is to familiarize yourself with the use of dynamic named ranges. If you are not familiar with these, check them out here: Dynamic Ranges Please note that for the purpose of being totally generic, the examples of dynamic ranges reference entire columns, there is most likely no need for this in your own spreadsheet. I often play it safe and reference about 300 more rows than I think I will need.
Calculate Message Remains in Status Bar If 65,536 Formula References
Microsoft Excel calculates during different events like column auto-resizing,
when entering anything in any cell, and so forth. However, Excel can only track
65,536 dependencies to unique references for automatic calculation.
After the workbook has passed this limit, Excel no longer attempts to recalculate
only changed cells. Instead, it recalculates all cells after each change.
This behaviour is by design of Microsoft Excel.
Microsoft Excel will correctly calculate all formulas. However, the "Calculate"
message in the status bar indicates that Microsoft Excel can no longer track the
formula dependencies and is calculating every formula in the workbook after each
change. If you are unsure when the last calculation event took place and you want
to be sure your formulas are up to date, simply Start a calculation manually. To
do this, press CTRL+ALT+F9 and wait until "Calculating Cells:
n%" disappears and "Calculate" returns to the status bar.
A common problem with poorly designed spreadsheets is that they become painfully slow in recalculating. Many people will suggest that a solution to this problem is putting a calculation into Manual via Tools>Options>Calculations. This is generally very poor advice and fraught with potential disasters. A spreadsheet is all about formulas and calculations and the results that they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information off your spreadsheet which will not have been updated. In other words your formulas may well be returning old values and not the updated values. This is because when you go into manual calculation you must force Excel to recalculate by pushing the F9 key. As you can imagine, it is very easy to overlook doing this. I liken it to a case where your car brakes are rubbing and slowing down you car. Rather than fix the problem you disconnect the brakes and rely on the handbrake. Most of us wouldn't dream of doing this, but most don't hesitate to put their spreadsheet into manual calculation. The bottom line here is, if you need to run your spreadsheet in manual calculation, you have a design problem. Address it properly and do not apply a 'band-aid' approach.
Below is a list of what is often the worst offenders for slowing down recalculations of spreadsheets.
Let's look at each of these now in turn, and see what alternatives we can have for them.
A possible alternative for array formulas, are Excel's database functions . The Excel help has some very good examples on how these formulas can be used on large tables of data and are able to return results based on multiple criteria. Another alternative which is too often overlooked is the use of Excel's Pivot table feature. While these may seem very daunting when first encountered, I highly recommend that you familiarize yourself with this powerful Excel feature as once you master them, you will wonder how you survived without them!
Perhaps the biggest problem with array formulae is that they look efficient, but when compared to an alternative, nothing could be further from the truth! An array formulae must follow rules that Excels built in Functions do not have to, that is they must loop through each and every cell they reference (one at a time) and check them off against a criteria. For this reason arrays are best suited to being used on single cells or referencing only small ranges.
UDFs for those of you that are not aware, are User-defined Functions that are written with Excel VBA which can then be used in a Worksheet in the same way as any one of Excel's built-in worksheet functions. Unfortunately, no matter how good the person is at VBA who has written the UDF, it is very unlikely that it will perform at the same speed as one of Excel's built-in functions, even if it would be necessary to use several nested functions to get the same result. Often a well written and efficient UDF function will incorporate the use of Excel's worksheet functions. By this, I mean you may decide to write a UDF to replace a function which otherwise would require you to use several worksheet functions nested within each other. The most efficient way to replace the use of several nested worksheet functions (if you must) would be to write a UDF that would incorporate the use of all of the worksheet functions necessary. This way instead of having to repeatedly nest the functions on the Worksheet to get the desired result, you could nest the functions once within VBA (by accessing the WorksheetFunction Object) which would then allow you to use this function in the interface, without the need to repeat the nesting. Most of Excels Worksheet Functions can be used in VBA simply by using: WorksheetFunction.<Function Name> or Application.<Function Name> if the one you need is not available, there will be VB equivalent.
Volatile functions are simple functions that will recalculate each time a change of data occurs in any cell on any worksheet. Most functions (non Volatile) will only recalculate if a cell that they are referencing has changed. Some of the most common volatile functions used are undoubtedly the NOW() and TODAY() functions, there are also OFFSET(), CELL(), INDIRECT(), ROWS(), COLUMNS() to name a few more. If you are going to be using the result of these functions frequently throughout your spreadsheet, avoid the temptation of nesting these functions within other functions to get your desired result (especially array formulae and UDF's). Instead, simply type the volatile function into a single cell on your spreadsheet and reference that cell from within other functions. This alone can potentially cut down on the amount of volatile functions by hundreds, if not thousands.
Excel is very rich in Lookup & Reference Formulae, with the most popular probably being VLOOKUP. These functions are all very generic and can be used to extract data from just about any table of data. The biggest mistake made by most, is the forcing of Excel to look in thousands, if not millions of cells superfluously, see: Formulae . As shown by the Formulae link, one of the best ways to overcome this is via the use of Dynamic Ranges .
The other mistake is that the lookup functions (in the case of VLOOKUP, HLOOKUP and MATCH) are told to find an exact match. That is, the optional fourth argument Range_lookup is set to False in both VLOOKUP and HLOOKUP. In the case of MATCH the last optional argument (Match_type) is set to 0 (zero). This means that Excel will need to check all cells until it finds an exact match. If possible, always use True (or omitted) for VLOOKUP and HLOOKUP, or 1 (sort ascending) or -1 (sort descending) in the case of Match. So, whenever possible, sort your data appropriately. Sorting the lookup columns is the single best way to speed up lookup functions.
Another very bad mistake is the double use of the Lookup Function nested within one of Excels Information functions. see example below
=IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))
This is used to prevent the #N/A when no match can be found. The big problem with this is, it forces Excel to use the VLOOKUP twice! As you can imagine, this doubles the number of Lookup functions used. The best approach (if possible) is to live with the #N/A, or hide it via Conditional Formatting . Or, if this is not an option, place the Lookup in a 'out-the-way' spot on the same Worksheet (eg IV1) and then use:IV1=VLOOKUP(100,MyRange,2,False) Result Cell = IF(ISNA(IV1),"",IV1). This halves the number of Lookup functions needed!
One other common problem is storing the Lookup Function on another sheet to the Table. While the effect of this is not too bad on approximate matches, it can be dramatic on exact matches. Consider placing the Lookup functions on the same Worksheet as the Table, then create a simply reference (eg =Sheet1!IV1) to the cell(s) to get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use:
IF(ISNA(Sheet1!IV1),"",Sheet1!IV1)
Last, but far from least, learn how to use Excels Database functions . They are very easy to use and are often much faster than their Lookup & Reference counterpart.
In Microsoft Excel, recalculation performance is affected by the way data and formulas are arranged on the worksheet. The following list contains tips for optimizing your worksheet to improve recalculation speed:
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.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.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel
| MSSQL Migration
Toolkit |
Monte Carlo Add-in |
Excel
Costing Templates