## Creating relative named ranges

I’ve posted many times about named ranges in various guises.  However, one thing I have not covered is relative named ranges.  Relative named ranges don’t sound too exciting, but once you’ve read this post I’m sure there will be plenty of examples where you could have used them and now wish you had. Creating named Read More

## Variable width column charts and histograms in Excel

In Excel, the standard column chart will display all columns with the same width at regular intervals.  However, in some circumstances, it would be better for the width of each column to be different.  For example, where each column represents different ranges of data.  This same principle of column width also applies to histograms. Excel Read More

Happy Spreadsheet Day! What are your feelings about allocating days to celebration or awareness of certain things?  Like International Talk Like a Pirate Day (19th September), Oatmeal Nut Waffles Day (11th March) or Bathtub Day (7th October)?  Well, the 17 October is International Spreadsheet day.  Normally, I would think these days are pointless, but I Read More

## Excel formula to check if a list is sorted

I love using the double VLOOKUP trick to get some lighting fast calculation times in Excel.  The drawback is that the list being looked up has to be sorted to prevent errors.  If a list were not sorted, it would be great to see a warning.  Then the user can take action to sort the Read More

## Excel Off the Grid’s 1st Birthday

Excel Off the Grid has reached its first birthday. 12 months ago, when I started the site, I had no idea what I was doing (to be honest, I still don’t), but it’s been fun. There have been over 100,000 words, across 120 posts all about Excel (and sometimes PowerPoint or Word). So, it’s certainly Read More

## 3 ways to change an image based on a cell value

From stock parts to holiday villas, from employees to logos, there are many reasons you may want to automatically change an image based on a cell value.  In this post, we’ll explore 3 different methods to achieve this task: Named range + INDEX/MATCH + linked picture Chart fill + #N/A VBA User-Defined Function Each solution Read More

## Reverse engineering a standard Excel Ribbon

In July 2017, I released the Calc Buddy Add-in.  It’s a tool which monitors Excel’s calculation mode and shows a warning whenever it is different to the user’s preferred setting.  As I was getting the Add-in ready for release, I decided I wanted the button to be within the Calculation section of the Formula Ribbon.  Read More

## Understanding basic array formulas

Have you heard of the mysterious “array formulas”?  Do you use them?  Or are you like most Excel users, you’ve placed them in the “too hard” pile, never to be seen again.  They always seem to crop up in forums as solutions to some tricky formula questions. In this post, I want to introduce to Read More

## Using Custom Lists in Excel

Do you ever find yourself typing out the same standard lists over and over again, such as lists of products, cost centers, projects or companies?  I do.  It is essential these lists don’t exclude any items and are presented in the same order each time.  Lists of 4 or 5 things are easy to remember, Read More