Author Archives: Dan

Breaking rank—differentiating between values that are equal

Challenge: On a dashboard report, show the top five values from a table of data showing the number of respondents. And against these values, provide other data from the same records. At first glance, the above challenge seems rather simple. … Continue reading →

Hiding a cell’s contents

If you want to hide the contents of a cell, instead of matching its font colour with its background, give it a custom number format of ";;;" (without the quotes). It's more elegant and won't suddenly appear if the background … Continue reading →

Charting shortcut

If you select a range of data and hit F11, Excel will create a default chart on a new sheet based on that data. It's often useful as a throw-away action just to visualise how your data trends. Or you … Continue reading →

Formatting shortcut

CTRL+1 is a very useful shortcut to bring up the Format Cells dialog box.

Creating conditional dropdowns

Challenge: Create two dropdowns, the second one only showing values relevant to what has been selected in the first one. This question has come up a lot in my Excel career. A good example is where you have two-tiered data. … Continue reading →

Formatting percentages

Time and again, I receive spreadsheets from people multiplying numbers by 100 to show them as percentages. Here's a brief overview of how percentages work, both generally and specifically in Excel. A percentage is merely an expression of a ratio. … Continue reading →

Correct syntax for formulae

I think way back when, some highly-read source must have mistakenly advised people that to create a basic arithmetic formula, you needed the SUM, irrespective of the calculation being performed. So I often see formulae such as: =SUM(C7-C6) If you're … Continue reading →

1900 was not a leap year, despite what Excel might have you think

If you enter 29 February 1900 into Excel, it will recognise it as a date and format it such. But if you can find a calendar dating back that far, you'll discover that 1900 was not a leap year. This … Continue reading →

Killer Sudoku Pro: spreadsheet helper

Challenge: There is a variant of Sudoku called Killer Sudoku in which areas of the grid, either square, rectangular or some other irregular shape, are caged off. A small number in the corner indicates the value to which the numbers … 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 →