Author Archives: Dan

Most useful spreadsheet ever? Excel-based year calendar

I created an Excel year calendar recently. Here's a link to it. It's completely dynamic, allowing you to show the calendar for any year between 1900 and 9999. The only user interaction is to enter the year. The rest of … Continue reading →

LEFT and RIGHT default to 1 character

The LEFT formula takes two arguments: the thing you're taking the left-hand side of, and the number of characters you want to take. So the following formula takes the first five characters of cell A1. =LEFT(A1,5) But if you only … Continue reading →

MIN

Summary MIN brings back the minimum value from a range of cells. As with other similar functions, it ignores any cells containing text or logical values As with SUM, it only applies to cells that are formatted as numeric values … Continue reading →

F4: repeat last action

For many things, F4 repeats the last thing you did, whether it's put a border on a cell, change its colour, insert a row, right-align etc. It's a bit hit and miss—it doesn't, for example, repeat data entry. But it's … 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 →

Editing cells

The easiest way to edit the content of a cell, be it a formula or a value, is to navigate to the cell and hit F2. Instead of moving around the cells in a sheet, the left and right cursor … Continue reading →

Conditions don’t care about case

If you're writing a formula that involves comparing two strings of text, beware. Two identical strings of text, one in uppercase, the other in lowercase, will be considered to be the same. Use the EXACT function if you want to … 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 →

In-cell carriage returns

Excel allows you to wrap text within a cell or within a merged set of cells. But it also allows you to force line-breaks within cells or merged cells. If you want to create the equivalent of a carriage return … Continue reading →