Category Archives: How to

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 →

Converting a column of data into a row

If you have a column of data and want to instead display this across a single row, you can do so via the Paste Special tool. First, copy your column (CTRL+C or via the Copy button). Then select the leftmost … Continue reading →

Number to text converter: most useless spreadsheet ever

I can’t remember why, but I set myself a personal challenge some time back to create a spreadsheet that could convert numbers to text. Here it is. Type in a number between 1 and 999,999 in the red cell and … Continue reading →

Check which entries of one list appear in another list

Challenge: find out which entries in one list appear in another list. This problem comes up time and time again in Excel. In fact, it's probably the most common challenge that people come to me to solve. Here's how to … Continue reading →

Navigation: Zipping around the worksheet

When I take over someone's machine to help them out with Excel, whether remotely or in person, the first thing that wows them is the speed at which my cursor moves around the screen to accomplish the task at hand. … Continue reading →

Using a single column for percentages and currency amounts

This morning I received a slightly quirky requirement. The client had a spreadsheet containing people's salaries—a row per person—and wanted to be able to input either a percentage increase or an uplifted salary, and for a separate column to show … Continue reading →

Alternate row shading

Alternate row shading is quite useful if you’ve got particularly wide data (lots of columns) and when readers need to scan across individual lines. Bug listings, contact lists etc. I usually use a light yellow shading to help with this, … Continue reading →