Author Archives: Dan

Alphabetical ranking

Today I faced the challenge of having to find the rank of a text string in a range of text strings. Or put another way, in what position would the entry sit alphabetically? The RANK function only works on numeric … Continue reading →

RANK

Summary If you have a range of values, the RANK function tells you where in that range a specific number sits. It can either tell you it's the nth biggest number in the range. Or it can tell you it's … Continue reading →

PivotTables: the reverse. Creating raw data from a summary

I received an interesting if slightly odd conundrum from Kat the other day. The solution was quite artful, so I thought I'd share. She had some summary data, each row of which contained a count representing the number of records … Continue reading →

TRIM

Summary This is an oft forgotten function. Quite simply, it gets rid of unwanted spaces. It does so in the following ways. All leading spaces that appear at the start of the cell being trimmed are removed All trailing spaces … Continue reading →

Unable to insert columns [solved]

Sometimes you'll encounter a spreadsheet that doesn't allow you to insert a new column. When you try, it will give you the following error: To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select … Continue reading →

Comparing date/time values with dates

I received a tweet last week from @rcdl: How do you count how many date/time values match a given date? It was a Thursday night and I was out having dinner with a bunch of friends. But I felt compelled … Continue reading →

Shortcut: Copying unformatted text

Oftentimes, I need to copy a cell’s contents into another application, usually Word or my email client. And often, I simply want the contents, not the associated formatting. For many applications (Word included), you have the Paste Special… option. But … Continue reading →

Using financials in Mail Merges

Imagine you have a financial figure that you want to use in a mail merge (a salary, bonus, house prices etc.) If you use them as they stand, they'll likely look rubbish. They won't show the currency and there won't … Continue reading →

PivotTables: A comprehensive guide (part 2 of 2)

In the last post, we covered the fundamental principles that supported PivotTables, and reached the stage at which the frame of the PivotTable was built. This post will introduce you to the analysis itself. So now, we have the wireframe … Continue reading →

PivotTables: A comprehensive guide (part 1 of 2)

If you are anything more than a very basic Excel user, then this post is important to you. It introduces undoubtedly the most powerful single element of Excel: PivotTables. If you're new to them, this is one post I heavily … Continue reading →