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 »
Excel
NHS Excel website expands
Waterfall Charts and the X-Axis
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 »
Managing Version Control in Excel using Macros
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 »
Personlised FIRST Training Schedules – A Marathon in 3 days a Week
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 Pivot Table or CrossTab to Flat List
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 »
VBA Snippets – Sorting Arrays and Random Selection
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 »
VBA Snippets to Sort Arrays in VBA
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 »
VBA Snippets to Delete Rows in Excel
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 »
Conditional Ranking in Excel – RankIF using SumProduct
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 »
Excel CrossTab Table to Flat List
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 »



