Author Archives: Dan

Ever right-clicked and dragged? Start now

There are some things that we do intrinsically. And there are some that grate. When left-clicking your mouse, you're happy to drag. You might want to select a block of cells, highlight a range of text or group select a … 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 →

Saving a fixed-width import layout

Excel has an in-built ability to import fixed-width text files. If you try to open a text file, a wizard will appear asking whether it's fixed-width or delimited. Delimited files are easily processed. With fixed-width files, Excel has a go … Continue reading →

Danger: copying and cutting in a filtered range

If you're cutting or copying content from a filtered range, beware. Here are some behaviours that may catch you out. First of all, copying. If you copy from a filtered range, Excel will only copy those cells on display. Which … Continue reading →

CLEAN

Summary The CLEAN function in Excel is a little-known function. As its name suggests, it cleans up text. Or more specifically, it removes all non-printable characters from text. So wherever you see one of those ugly characters that appear as … Continue reading →

How to think about sorting across multiple fields

Some people struggle to get their heads around sorting when more than one field is involved. Below is the best way to think about sorting in these circumstances. First, group everything by this field Within the records with the same … Continue reading →

Cross-sheet data validation

One of the annoying limitations of Excel is that Data Validation does not work from one sheet to another. If you have a list of items that you want to use as a pick list for a specific cell, the … Continue reading →