Getting more out of Excel

Originally published in GP magazine .

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

Excel is currently the best known and most used spreadsheet. Most probably only use it for simple adding up of figures or for drawing pretty graphs. However, it is capable of so much more, so here is my list of useful functions that you may not have come across that might make life easier.

Autofilter. Excel can be used as a mini database. If you give titles to the columns by putting text in the top row you are effectively creating a simple flat database. By using the autofilter command you can manipulate that data. You can sort columns, you can filter for one type of entry, you can even use arguments like show all >5. I use this more than any other feature of Excel.

Pivot tables. These are often seen as complicated and difficult to use but are so amazing that you need to understand what they can do for you. I run a search from our clinical system of every referral, which comes out by patient. Each row lists the patient’s details, e.g. age, and information like date of referral, to whom, by whom, urgency. Working out how many referrals each doctor has done is long-winded manually - you have to go to the right column, sort it, then count each person’s. With a pivot table it will give you that information instantly on the right selection. You can even break it down so that you can look at referrals by referrer to which unit on which date.

Macros. Do you find yourself doing the same thing over and over in Excel? Well, you need macros. Don’t be afraid - you can record what you do then have a look at the code generated. Often it’s really easy to understand and you can adapt it or change it to do what you want.

VBA. If you are feeling more adventurous you can get into Visual Basic for Applications. This is a full programming language that allows you to completely customise and drive Excel. It will even interact with other Office documents. I once wrote a program that analysed some data, created some graphs and pasted those into a Word document without much training. OK, you may not want to do this, but knowing someone can do it might help you solve a problem.

Add-ons. Although you can customise Excel with your own macros and even programming, often someone has done it already for you. Many companies sell add-ons that enhance the functionality of the software.

There are loads of amazing websites with tips and tricks for Excel. There are tutorials and sample sheets to download. There are even forums where you can ask questions - but be careful, they like you to make sure no one else has asked the same first.