Category Archives: General

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 →

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 TRUE in VLOOKUP

I've used the VLOOKUP function since the cows last came home. I can't imagine my Excel life without it. It's sublime, and while a relatively simple function, it caters for so many needs. (More on the VLOOKUP function here.) It … Continue reading →

PureText: the paste-as-text shortcut

Last year, I was looking for a tool that could shortcut the "paste as values" command. My requirement fell outside of Excel – I wanted it to paste into WordPress, MS Word, into Google Mail messages etc. I never even … Continue reading →

Don’t over-engineer when industrial strength is not warranted

I would estimate that 80–85% of my spreadsheets are throwaway. I create them for a specific purpose: to understand some data; to create an import file for one-off use; to prove someone wrong. (Ha!) After its creation date, I'll never … Continue reading →

Dating in the new year

Some people in the office are whinging about the fact that typing a December date without appending the year defaults to storing it as a 2012 date. They are of the view that Excel should know that they meant December … Continue reading →

The odd syntax of the COUNTIF function

My friend Sharon yesterday was trying to get her head around the COUNTIF function. Its syntax is odd, so I sympathise. Here's why. COUNTIF takes two arguments: a range and a condition. COUNTIF(Range,Condition) The range is the thing that you'll … Continue reading →

Excel is like life

Life is complex. Problems are complex. And it’s rare that an intricate problem can be solved by a simple solution. Or indeed that a single solution is the only one. Excel is a good analogy here. It has rows and … Continue reading →