Excel tips

Users of versions of Excel before Excel 2007 may have been sent Excel workbooks in the new Excel 2007 file format, which they have found they cannot read. It is possible to download the (free) Microsoft Office Compatibility Pack from this Microsoft web page which allows these workbooks to be read in earlier versions of Excel. It also provides compatibility for Word and Powerpoint files.

Workbooks are much easier to maintain if the input, calculation and output are kept separate - ideally on separate worksheets. By grouping all the input together, anyone subsequently adding data will know where to do this and is less likely to accidentally overtype calculations or results. By grouping all calculations together (ideally running top-to-bottom and left-to-right) it becomes easy for someone else to follow the logic of the workbook, rather than having to trace calculations that jump all over the workbook. This may take slightly longer to set up, but is well worthwhile in the long run.

The simplest layout for data is as a table, with one header row containing one-word descriptions of each field. The table continues as far down as needed with no blank rows and with nothing placed beneath it. This is as close as is possible in Excel to a database, and provides the maximum flexibility for maintaining the data and for using it in subsequent calculation steps. Multiple tables can either be placed alongside each other or (better) on separate worksheets.

If the data contains one field which depends on another (e.g. a custmer code and the customer's name), it is best not to include the dependent field in the table. Instead, the relationship can be stored in a separate table (as would be done in a database). Otherwise not only is the data more laborious to maintain, but it is all too easy to end up with inconsistent data (e.g. two rows with the same customer code but different names). The logic of the worksheet can be used to bring the information together when needed.

Workbooks are much easier to follow if names are used in formulae rather than direct cell references. Excel provides a number of ways in which names can be given to cell ranges or to constants. If data is laid out in tables as described above, it is worthwhile to name both the whole table and the individual columns (normally using the descriptions in the top row). Names can even contain formulae, though this can make a workbook harder to understand.

When creating a formula, references can be absolute (preceded witha $ sign) or relative. When copying the formula, absolute references stay the same while relative references change - so by considering how a formula might be copied, the right sort of references can be chosen. It is also possible to have mixed references where (say) the column is absolute but the row is relative. Toggling the F4 key when selecting a reference toggles between the possibilities.

For example, when totalling a range, it is worthwhile to include blank cells above or below in the range to be totalled. Otherwise it is possible that someone subsequentally adds another row, but this is not then included in the total. It is best to anticipate where data might be added, and to ensure that this will always be included automatically in any revevant formulae.

The SUBTOTAL function ignores any existing subtotals it finds in the range to be totalled, so is very useful for creating totals - an entire column can be selected as the range to be totalled without having to worry about excluding existing subtotals.

Reading complicated formulae with multiple brackets can be difficult. To take a relatively simple example, what is this doing?

=IF(NOT(ISNUMBER(B2)),"Text",IF(B2>100,"large",IF(B2<50,"small","medium")))

It is easier to follow as

=IF(NOT(ISNUMBER(B2)),"Text",
     IF(B2>100,"large",
         IF(B2<50,"small","medium")
     )
 )

Typing Alt/Enter in the text of a formula puts in a line break.

This very powerful feature is little used. For example, suppose one wants to test if range A1:A4 contains the same values as range B1:B4. The formula =AND(A1:A4=B1:B4) looks like it should do this, but entered as a normal formula it just retuns a #VALUE error. To make it work on the whole range, it is entered by pressing Control/Shift/Enter simultaneously, and it then appears with curly braces { } round it (which are not typed directly) to indicate that it is an array formula.

Not so simple this one. Regular Expressions were invented as part of the Perl language for validating that text fits a given pattern, which can be very difficult to do in any other way. They can be used from Excel via a macro such as is shown below. It is also necessary to set a reference (via Tools References in VBA) to Microsoft VBScript Regular Expressions 5.5. This example is to validate an email address.

Function CheckRegex(Value, Pattern) As Boolean
    Dim Regex As New RegExp
    Regex.Pattern = Pattern
    Regex.Global = False
    Regex.MultiLine = False
    Regex.IgnoreCase = True
    CheckRegex = Regex.Test(Value)
End Function

The test can then be performed in Excel by writing

=CheckRegex("testperson@testisp.com","^.+@[^\.].*\.[a-z]{2,}$")

which returns the value TRUE. The second parameter is the test pattern, and these can be as simple or complex as desired. Microsoft give a description of how to construct these at this page