@ > Home > Contents > Worksheet design  Excel4+, SpreadCE, Ooo, Lotus

Names: Avoid short names up to 3 letters with appended numbers (like day1, day2 ...) since they interfere with cell addresses, or they can not be defined or ported. No numbers turn out ok. Remember: Excel 2007 expands the sheet to columns A:XFD. Day1 will be a problem then, being within. You might try to import thus alike to the 2007 format using a necessary assistant. You can also go one with the old format. - Names cause slower models, so use them carefully. Mass data should never be named each but only as a range, while named column heads or any parameter cells turn out ok.

Formulas: See also Philosophy. You gain about 10% speed per formula by "declaring" strings like TEXT() as --TEXT() when to be used as value. It prevents Excel from trial and error about data types itself. 

Formats: Don't be generous with them. The program may freeze. Formats as numbers, colors, frames, fonts and font sizes, orientation and protection rather multiply than add to each others counting. Format columns rather than cells. If using f.e. frames, do not use colors or fonts. Don't format data. Format reports only.

Worksheets: Normally their alphabetical order does not matter. In some constellations of some Excel versions it does (link shows a page of William Whooper, worth to be read, but no longer online). The model or parts of it might be calculated more than once. Large models showing a poor performance should be treated accordingly: Measure the speed on an alpha sorted worksheets copy of the file. Stay at the copy if it succeeds. Or rename a desired order alphabetically.

Tables: You remember early recommendations to refer to left or upper cells? Today one could give others: One table per sheet, first row: fieldnames, below: data, no empty columns inside. This is necessary for all auto sensing abilities of the programs, as auto filter or sorting, Pivot or summations. Do not use further tables: If they use same rows or columns you will be caught painfully when deleting such in one of the tables. If using a second table from AA100 after an ending first table at Z99, you have a ugly navigation problem. Be generous! Excel is it, as it has no sheets number limit. Read here: Improving Performance for Excel 2007

Human Property: Sheets, VBA modules and Add-Ins can be more or less easily recompiled even if protected. Use .XLLs, .DLLs or COM-Add-Ins instead.