≡ Menu

Forecasting using Excel’s Linear Trendline

Charts Excel

Forecasting using Excel’s Linear Trendline Thumbnail

Excel offers the option to insert a series of trendlines on your charts. I have been using the linear trendline feature to represent the historic trends in SUS activity data and I wanted to use the associated formula to generate forecast data based on this trendline. If you think back to your algebra days you [...]

Charts Excel

{ 2 comments }

Converting T-SQL for MS Access SQL Queries

Access SQL Server

I’m moving to pastures new and will be leaving behind a well-loved SQL Server database, complete with a rich array of patient level data. At my new job I will likely have to make do with an Access database and so I’ve been doing a bit of preparation, building a quick cheat sheet so I [...]

Access SQL Server

{ 1 comment }

The Double Bridge – An Excel Waterfall Template

Charts Excel

The Double Bridge – An Excel Waterfall Template Thumbnail

It seems that each NHS planning round inspires me to produce yet another Excel Bridge template.The powers that be seem to love the visual appeal of a colourful waterfall and I find that pulling the data together for a detailed bridge makes the pain of completing the Source and Apps, TRU64 templates, marginally less painful.This [...]

Charts Excel

{ 0 comments }
Importing CSV files to an SQL Server Database Thumbnail

I regularly need to set up new tables in my SQL Server Database from data held externally as either text or csv. It sounds like an incredibly straightforward task but always sends me scurrying off to google for advice on How to Import CSV to SQL Server. It doesn’t help that I don’t have access [...]

SQL Server

{ 0 comments }

Excel Pivot Table or Crosstab to Flat List

Excel Macro

Excel Pivot Table or Crosstab to Flat List Thumbnail

I 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 across the [...]

Excel Macro

{ 17 comments }

What to do with the Personal Macro Workbook

Macro

What to do with the Personal Macro Workbook Thumbnail

When you first start to dabble with macros and VBA, one of the first challenges you will face is fathoming out what to do with the personal macro workbook. The Personal Macro Workbook called personal.xlsb does not exist until you create it and even then it tends to remain hidden from general view. This personal [...]

Macro

{ 2 comments }

Excel Waterfall Charts with Ease

Charts Excel

Excel Waterfall Charts with Ease Thumbnail

It's Operating Plan time at work again, and that means long term financial models and bridge diagrams to show the causes of movement between years. I've previosuly written about Excel waterfall charts and I had to refer to my own waterfall chart tutorial to help me with this months submission. I've been researching the topic [...]

Charts Excel

{ 6 comments }

Excel’s Missing RANKIF Function

Excel Function

Excel’s Missing RANKIF Function Thumbnail

Excel offers a number of conditional IF functions such as COUNTIF and SUMIF which allow you to count or sum a range based on the values in another column. If you have Excel 2007 or later you’ll have the really useful COUNTIFS and SUMIFS functions which allow you to perform multi-condition actions based on the [...]

Excel Function

{ 3 comments }
Revealing Hidden Cells

Chandoo published a useful tip recently for revealing the contents of hidden cells. Anyone who works in the NHS and ever has to deal with excel spreadsheets that originate for the DH will be very familiar with the frustration of working with workbooks that have been protected to the max. I’ve been working on the [...]

Excel Tips and Tricks

{ 0 comments }
Excel Histogram Charts from a Pivot Table Thumbnail

Today I am processing data from a patient level costing system (PLiCS) and trying to assess the cost distribution by the patients diagnosis. Frequency distribution charts can be a bit painful to produce in excel so I have fallen back on my favourite tool – the pivot table which enables me to produce dynamic excel [...]

Charts Pivot Tables PLiCS

{ 3 comments }
Page 2 of 4
1 2 3 4