Four tips for using Microsoft Excel

Originally published in GP magazine .

Written for GP magazine around 2002. This is the version I submitted - the published article may have been edited.

The following are some of the tips I have picked up over the last two years of using Excel in combination with EMIS.

Learn the keyboard shortcuts as they will save you hours. My favourite is filling a highlighted column with one value by pressing Ctrl-Enter.

Always export a field like SEX into your Excel table. This will only ever have one value per patient, unlike some fields, e.g. drugs. You can then use the Autofilter tool to select the blanks and block delete those rows, leaving one row per patient so that your counts are correct.

Export as many items of information as possible and then use the hide columns facility to make things look clearer. There is nothing worse than having spent ages tidying up a sheet to realise you will have to do it all again to add an extra bit of information.

Sorting one column in a list rather than the whole thing can be a disaster. A trick to easily spot when this happens is to apply “stripy” formatting so you can see that the rows are staying intact. The fastest way to do this is to fill the first row with one colour, then the next with another. Then select the two rows and hold down the right mouse button and drag on the Autofill handle that you would normally use to copy a formula. Choose the option to fill formatting only. Take care - some colour combinations can make you ill!