≡ Menu

Excel

Annual Growth rates from Linear Trendlines

Annual Growth rates from Linear Trendlines Thumbnail

In this post I’ll demonstrate how to generate annual growth rates based on historic linear trends. It effectively adds the % annual growth rate to a linear trendline produced on an excel chart. I’ve previously published a post sharing a model I used to produce linear forecasts from historic activity data, so I could generate [...]

{ 2 comments }

Macro to wrap IFERROR() around formula in cell or range

This is a fantastic little macro that I picked up from The Spreadsheet Guru I seem to use the GETPIVOTDATA function on a near daily basis to generate fixed format reports, it is such a flexible tool for creating dynamic reports that are formatted just as you require - without the limitations of the original [...]

{ 1 comment }

Using Custom Cell Styles

Using Custom Cell Styles Thumbnail

How many times have you had to set up a custom format to show a negative cell value in red font with brackets? (£105,000) or if I’m being fancy (£105k) I think I do it once every work day and I bet I am not the only one. No more though, because today I have [...]

{ 0 comments }

Pasting Formulas without Changing Cell References

Pasting Formulas without Changing Cell References Thumbnail

This tip enables you to make an exact copy of a range of formulas without changing cell references or links to other workbooks. When you copy a formula, Excel rather cleverly shifts the cells that the formula references.This is normally what you want it to do. So for example, if I have a table with [...]

{ 0 comments }

Using Formulas to create a Dynamic Chart Title

Using Formulas to create a Dynamic Chart Title Thumbnail

The ability to use formulas in Excel chart titles is very limited and so most people tend to hard code their chart titles. The only formula you can use in the chart title is one that references another cell, for example =$C$1 That does enable you to create a dynamic chart title though - just [...]

{ 1 comment }
Using Ramp-Up Profiles for QIPP Savings and Expenditure Thumbnail

You can use this ramp up trick to apply a phasing profile to planned expenditure but I am illustrating it here to model the monthly delivery of planned savings. In the NHS we tend to refer to savings as either QIPP (Quality, Innovation, Productivity and Prevention) or CIP (Cost Improvement Program) depending on whether you [...]

{ 2 comments }

Forecasting using Excel’s Linear Trendline

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

{ 2 comments }

The Double Bridge – An Excel Waterfall Template

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

{ 0 comments }

Excel Pivot Table or Crosstab to Flat List

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

{ 17 comments }

Excel Waterfall Charts with Ease

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

{ 6 comments }