Excel Tidbits: Creating Column Header Rows for Sorting

Well, sad to say this one has stumped me for a long time. I work in Numbers from Apple’s iWorks Suite about as much as I do in Excel. Data sets above about 1000 rows, assuming use of processor-heavy formulas like vlookups, really slow Numbers down. From a visual and formula perspective, Numbers is much easier to use. One of my favorite aspects is the ability to create header rows so that the last row becomes the column headers and the references in formulas. That makes modifying data quicker and improves one’s ability to keep track of a greater variety of segments.

However, when you’re dealing with tens of thousands of records/rows and multiple sheets, Excel excels (wink, wink) high above Numbers. Unfortunately, it is much harder to make things look professional in the program.

When I create dashboards, I usually want data summaries and pivot tables above the data, but it’s a pain when sorting the data below since typically you have to select the data you want to sort. Luckily the sorting options are saved even if your data selection changes. But it would still be much easier if I could just click a cell in a column and have Excel already know the data I want to sort and what I do not.

The solution is adding column filters (see image from the Mac Excel below).

1. Select the column header row.
2. Then under the “Data” menu or in the “Data” toolbar, select “Filter”.
3. You don’t even need to use the filters. In fact, just remove them after adding them. From then on, Excel will know which row is the column headers when sorting. Brilliant!
4. Now test the sort. Select a cell, or click a filter arrow on a column header to select a new sort for the column.
5. Tip: If you remove the filters on the header row, be sure NOT to try sorting above that column header row. Doing so will retrain Excel to ignore the row and you’ll have to reassign the filters again.

No more selecting the cells you want to sort before so doing–saving mental energy; allowing that energy to focus on the task and not loose track of a formula you’re creating or a use-case scenario you’re trying to answer.

Want to see it in action? Download an example filter column header row spreadsheet to give it a try.

Best wishes!