NHS Excel website expands Thumbnail

NHS Excel website expands

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 to [...]

Waterfall Charts and the X-Axis Thumbnail

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 series [...]

Managing Version Control in Excel using Macros Thumbnail

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 to [...]

Excel Pivot Table or CrossTab to Flat List Thumbnail

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 out [...]

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. The [...]

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 taken [...]

VBA Snippets to Delete Rows in Excel Thumbnail

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 will [...]

Conditional Ranking in Excel – RankIF using SumProduct Thumbnail

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 how [...]

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 very [...]

Macros to Consolidate Worksheets

I’ve got a spreadsheet with tabs for each individual directorate. Within the tabs are tables with the same layout but different sizes (number of rows). I want to group together all the used data on each tab and create a single consolidated sheet of continuous data. Obviously I need a macro to copy and paste [...]