Author Archives: Dan

Excel wordsearch creator

I needed to create some wordsearches recently. So I turned to Excel. I created two identically-sized grids: a blank one in which I could type the words I wanted to appear in their rightful positions; and another one next to … Continue reading →

Times tables spreadsheet

My daughter's in the midst of learning her times tables. There are various apps out there that can help, but I was after something that addressed a specific need. I wanted to be able to specify the times tables that … Continue reading →

Convert to columns: Carriage-return delimiter

I found out the other day that the carriage return can be used as a delimiter when converting text to columns. This is useful if you have in-cell carriage returns that you want to get rid of. (To do an … Continue reading →

Looking for search substrings in a list of products

Reently, Sam asked me to help with a problem. I am trying to create a function whereby users of my spreadsheet can type in 1–3 words to search a range of parts descriptions (text entries) and pull up their associated … Continue reading →

How to sort your columns

Usually, the requirement is to be able to sort rows in a certain order. But once in a while, I have a need to sort the columns in a dataset. For example, I may want to order my columns in … Continue reading →

How to forecast based on a sales pipeline

I work with a company in one guise at the moment, but I don't work with their Sales area. While I have no influence here, I am aware that their sales forecasting is broken. Having worked in a number of companies … Continue reading →

Where there’s a MIN there’s a MAX

It's an odd one, but wherever you need to apply a minimum in Excel, you'll often find yourself using the MAX formula. Let's say you need to charge for transactions (stored in cell A1) at a rate of £20 each, … Continue reading →

Always validate the business requirements

George got in touch the other day, in responding to an earlier post on date highlighting. He asked whether there was a way for Excel to automatically insert a row on the left-hand side of a range for each day … Continue reading →

Using the text of a formula

I was asked an interesting question by a follower called Frederick recently. He had a column that contained the syntax behind some formulae, but the column of information was stored as text. All of his entries were of the form A*B. … Continue reading →

Linking spreadsheets: don’t do it

Spreadsheets are linked when formulae in one spreadsheet reference cells in another. There are two circumstances in which spreadsheets become linked: consciously and unconsciously. First, let's deal with conscious linking. My general rule of thumb on this subject is that … Continue reading →