Excel

I’ve added a number of excel tutorials to the NHS Excel website and this will become the home for most of my excel related content. It’s been a busy month at work due to year end and the agreement of balances exercise but its been interesting from an excel point of view. We’ve had...
Read more »

Waterfall charts have become all the rage in the office of late. They are popping up all over. I suppose it is no surprise, it’s springtime and we are well into the planning process and a waterfall chart is the perfect way to demonstrate how a starting position either increases or decreases through a...
Read more »

If you work with spreadsheets that are regularly being updated and amended by multiple users then you will know something about version control. If you then add to this mix, the need to periodically distribute the updated spreadsheet for public viewing you will be more than well aware that Microsoft Excel isn’t really designed...
Read more »

Crossing the line at the end of the Great North run I felt elated, 10 mins later I was sick and feverish. Any fitness I had in September disappeared over the next 5 weeks as I feebly battled a chest infection. Now that I’ve more or less cleared my chest and started venturing back...
Read more »

Excel to CrossTabI often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads...
Read more »

The blog post sounds a little Darwinian but I’m afraid it’s just a dull selection of VBA code that enables you to sort one array on the basis of values from another. A double sorting array. I’ve amended array sorting code from John Walkenbach and Anthony’s VBA Tutorials to create something I can understand....
Read more »

Here’s another VBA snippet post. I find it handy to have a store of regularly used macros on the blog so I can access them at any time, not that I need them that often of course. These particular snippets are useful routines for sorting arrays and can be called from other procedures. I’ve...
Read more »

I regularly use simple macros to delete blank rows in my excel spreadsheets and there are loads of examples on internet forums. I’ve recently had to recreate these delete row macros to install on a new computer and thought it would be useful to keep them filed here on the blog. This first macro...
Read more »

I was hunting around for a RANKIF function yesterday so I could rank a load of hospitals in terms of their cost per procedure. Unfortunately RANKIF isn’t one of the available functions but I managed to achieve the same effect by utilising the SUMPRODUCT function. I wrote a SUMPRODUCT tutorial some time ago indicating...
Read more »

I had a query on my sumproduct tutorial that was effectively asking if I could work backwards from an excel crosstab or data table to get back to the simple flat list. Converting a table like this: Back to the data sheet like this: If you didn’t have to do this sort of task...
Read more »