≡ Menu

Tips and Tricks

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


How to Break Resistant External Links in Excel

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

{ 1 comment }

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


Revealing Formulas in Hidden and Protected Cells

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