Concatenate function for sorting

As I said before, the concatenate function can be used in a great many useful ways, and one of those ways is to make it easier to sort.

There are two things which often annoy me when I’m doing frequent sorts. The first is building the sort, and the second is dealing with the limitation on the number of columns that can be used in the sort. It was the latter problem that made me consider the value of concatenation for sorting.

Then I saw how making a column of concatenated values could mean the end to rebuilding sorts. One annoying thing about sorting is that most spreadsheet programs can’t be relied on to remember them, especially if they’re complicated or you’ve sorted something else in the meantime. After the third or fourth repetition of “sort this column first, then the other, then this third one…” I generally get heartily sick of it. So if I’m going to be repeatedly sorting — especially if I might have multiple different ways I might want to sort — I make one “sort” column for each sort I frequently do.

To make a sort column, I just use concatenate, and for parameters I use the columns I want to sort. Say, for example, in my book inventory, I might have =concatenate(author,series,title). This would sort with author as the primary field, followed by series and the title. I could also have another sort column with =concatenate(publisher,author,series,title) if I wanted, if there’s a publisher that’s prone to having sales. That turns a multi-column sort into a single-column sort, which is much simpler.

Once I’ve set up the formula, usually in the second row (the first row being reserved for column names), I copy it, then navigate down to the bottom row in the list, then highlight everything up to the second row (which is the next populated cell, when you’re heading up from the bottom), and paste the formula. Unlike when I am initially creating the list I don’t usually copy the formula beyond the end of the list, because doing so would likely sort all the blank rows to the top.

An example is available as Project 6: Concatenated Sorts.

Freezing rows and columns: Basic how-to

Even if you do have column headers, once you scroll down more than a screen you can’t see them anymore. Unless, that is, you freeze them.

Freezing allows you to specify any number of currently showing rows and/or columns to always display. You could, in theory, tell it to freeze all but the bottom row, and all but the rightmost column, but if you did you could only move around one cell. Generally when working with a large detailed list I freeze between one and three rows at the top, and sometimes the leftmost column. For example, a list of student scores might freeze row 1, which contains the names of the assignments, and column A, which contains the student names.
Continue reading Freezing rows and columns: Basic how-to

Why to name columns

When you’re making a detailed list, column headers are extremely important. It’s possible to do without them, but after four columns or so I usually find myself studying the contents and saying “Now, WHAT did this column contain?

Likewise, when making a column of formulas it’s extremely helpful to have named the column(s) that the formula refers to. It can turn the formula =b1*c1*d1, which could be anything, into =height*width*depth, which is pretty obviously the formula for the volume of something. Plus, if you have a long and complicated formula, it makes it harder to make accidental mistakes. Plus, it’s a little bit harder to spot the problem in =(b1-c1)/a1 than it is in =(value2011-value2010)/value2009 when trying to calculate the annual percentage change in something. [The correct formula would be (value2011-value2010)/value2010, or (CurrentYear-PreviousYear)/PreviousYear.]

So, always remember to put in a column header, and name the column (preferably something similar to the header). It’s also very helpful to freeze the panes so that the header row stays visible.