Category Archives: How to

Olympic ticket allocation spreadsheet: explained

Yesterday, a friend and a Twitter follower (two separate people) asked me to put together a spreadsheet to figure out which Olympics tickets they might have been allocated based on the amount of money that would, they hoped, shortly be … Continue reading →

Text to columns: saving your file layout

The Text to columns function in Excel is useful. If you have a bunch of data in a single column, it can be used to split it into separate columns. Found on the Data ribbon, you can either specify delimiters (for … Continue reading →

Conditional minimums

A friend asked me the other day how to return the lowest positive value from a row of data in Excel. Given that there’s no MINIF function, at first I struggled. Then I re-phrased the question: how do you return … Continue reading →

Variable vlookup offsets

Challenge: I want to future-proof the column number returned in my VLOOKUP formula. VLOOKUP is a hugely useful formula in Excel. Apart from the basic mathematical operators and the IF statement, there’s nothing that touches it in terms of how … Continue reading →

Dynamic named ranges

Huge power can result from combining the OFFSET function with Named Ranges in Excel. Here's how. OFFSET itself is phenomenally powerful. It allows you to refer to a range of a chosen height and width that is a chosen number … Continue reading →

Hiding errors

Challenge: I want to hide error values. Error values are pretty ugly. #N/A, #VALUE, #REF etc. When the eye sees them, it triggers an ever so slight wince. They occur when something's awry: when lookups don't yield any results, when … Continue reading →

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 →

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 →