<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**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

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 IfEnd 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.**

Excel*Flat File database.***IS**a flat File Application and should be used as such.Try and ensure all related raw data is on 1 Worksheet and in 1 workbook. The number 1 issue with excel spreadsheets is users spreading raw data over many worksheets and/or workbooks.*Less Worksheets/Workbooks is more and makes analyzing/reporting a*__LOT easier.__- All Layouts SHOULD contain 1 single Worksheet in a table format
of
**RAW DATA**. From this**raw data**comes your**FINAL data**. . This will help Excel recognize them as headings when you use one of its functions eg;*When putting in headings bold the font***Sort**.This is because a lot of Excels built-in features will assume a blank row or column is the end of your data. It can also helps with a lot of Excels formulas. Having said this though, it is a good idea to leave*When putting data into the data area of your spreadsheet try to avoid blank rows and columns.***at least**4 blank rows above your headings (hide them if you wish), these can then be used for keeping Totals (much better than being at the bottom) and criteria for some of Excels built in features, eg Advanced Filters.. Excel is very rich in what is known as Lookup & Reference formulas and many of these may rely on your data being sorted in a logical order. It will also greatly speed the calculation process of many functions.*Have your data sorted whenever possible*. By this I mean if you want the names of the months as headings type them in as 1/1/2001 , 1/2/2001 , 1/3/2001 etc then format them as "*Use real dates for headings and format them appropriately***mmmm**". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road if you need to use them in formulas, if you don't nothing is lost.. Say you have the names of 100 people to put into your spreadsheet, don�t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily - the same cannot always be said for the reverse.*Don't put in one cell what could go in more than one cell*. Remember that Excel has far more rows than columns. Excel expects your data to be laid out this way.*Place your headings across columns and your data in rows directly underneath*

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, i**t 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 "

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.

- Array formulas
- Sumproduct used for multiple condition summing or counting
- UDFs (User-defined functions)
- Volatile functions
- Lookup Functions

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

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:

- Organize your worksheets vertically. Use only one or two screens of columns, but as many rows as possible. A strict vertical scheme promotes a clearer flow of calculation.
- When possible, a formula should refer only to the cells above it. As a result, your calculations should proceed strictly downward, from raw data at the top to final calculations at the bottom.
- If your formulas require a large amount of raw data, you might want to move the data to a separate worksheet and link the data to the sheet containing the formulas.
- Formulas should be as simple as possible to prevent any unnecessary calculations. If you use constants in a formula, calculate the constants before entering them into the formula, rather than having Microsoft Excel calculate them during each recalculation cycle.
- Reduce, or eliminate, the use of data tables in your spreadsheet or set data table calculation to manual.
- If you only need a few cells to be recalculated, replace the equal signs (=) of the cells you want to be recalculated. This is only an improvement if you are calculating a very small percentage of the formulas on your worksheet.
- When a certain group of formulas must be recalculated a great number of times, then it may be helpful to replace the equal sign (=) in the formulas that you do not need to recalculate with a unique string that does not appear elsewhere. The formulas without the equal signs will not be recalculated (they are no longer considered formulas). When Microsoft Excel has recalculated the formulas that still contain equal signs, search for the unique string and restore the =.

**
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...**