Category Archives: How to

Convert to columns: Carriage-return delimiter

I found out the other day that the carriage return can be used as a delimiter when converting text to columns. This is useful if you have in-cell carriage returns that you want to get rid of. (To do an … Continue reading →

How to sort your columns

Usually, the requirement is to be able to sort rows in a certain order. But once in a while, I have a need to sort the columns in a dataset. For example, I may want to order my columns in … Continue reading →

MODEIF and MEDIANIF (conditional mode and median)

There's not an inbuilt function to cater for the conditional mode or median. So we need to use an array function. =MODE(IF($A:$A=D1,$B:$B)) If you have supplier names in column A and their delivery lead times in column B, the above … Continue reading →

Highlighting today’s date: a lovely little trick

I came up with a neat trick the other day. With each column indicating a sequential date, it was a way of highlighting the current date. There are two ways of doing this. You can either use conditional formatting to … Continue reading →

Conditional Formatting: How to

First: what is Conditional Formatting? Conditional Formatting is a feature that allows you to change how a cell looks based on the contents of your workbook. You may have a cube of random numbers, and want to shade everything over … Continue reading →

Ever right-clicked and dragged? Start now

There are some things that we do intrinsically. And there are some that grate. When left-clicking your mouse, you're happy to drag. You might want to select a block of cells, highlight a range of text or group select a … Continue reading →

Saving a fixed-width import layout

Excel has an in-built ability to import fixed-width text files. If you try to open a text file, a wizard will appear asking whether it's fixed-width or delimited. Delimited files are easily processed. With fixed-width files, Excel has a go … 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 →

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 →