≡ Menu

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

Excel Macro Tips and Tricks

{ 0 comments }

Using Custom Cell Styles

Excel Tips and Tricks

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

Excel Tips and Tricks

{ 0 comments }

Macro to convert filtered list to multiple pdf

Macro

Macro to convert filtered list to multiple pdf Thumbnail

We generate reports that hold details for multiple practices all in one sheet, which is nice and convenient for me as I can run a pivot table off it and analyse any number of practices and services as I see fit. Unfortunately this report also needs to go out to the individual practices and without [...]

Macro

{ 4 comments }
How to amend a calculated field in a pivot table Thumbnail

Until fairly recently I didn't realise you could edit user-defined calculated fields in pivot tables. Well I was wrong! All this time I've been taking extra care when creating a calculated field because I didn't think I would get a second opportunity to correct it. Yesterday I was doing some preliminary work for some contracts [...]

Pivot Tables

{ 0 comments }
How to Break Resistant External Links in Excel Thumbnail

Usually when you want to break or amend a link in Excel you use the Edit Links feature under the data ribbon. You can either re-point the link or choose to break it, at which point the last updated values are left intact. Sometimes these external links prove to be very resistant to breaking or changing [...]

Tips and Tricks

{ 1 comment }
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 [...]

Excel Tips and Tricks

{ 0 comments }

Using Formulas to create a Dynamic Chart Title

Charts Excel

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

Charts Excel

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

Charts Excel Models

{ 2 comments }

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 }
Page 1 of 3
1 2 3