This week's Office challenge takes a look at a mysterious Excel sort behavior and offers solutions for counting blank cells in Excel.
Sorting a range of values in a sheet is as simple as a couple of clicks. First, click in the column by which you want Excel to sort. We’ll call this column the sort column. Then, click one of the Sort icons on the Standard toolbar. Excel will extend the selection from the sort column to include all contiguous columns, automatically.
Then one day, the sort doesn’t work as expected. It sorts the sort column and one or maybe even a few contiguous columns, but not the entire spreadsheet. Several columns fail to sort. What happened?
Last week we asked…
How do you count the number of blank cells in an Excel spreadsheet? Kevaburg was the first to respond with the most efficient solution: Use Excel’s CountBlank() function.
This function uses the following syntax:
where rangeis a valid range. This function counts blank cells and cells with formulas that return an empty string. CountBlank() doesn't count cells that contain 0. Amasa and Topgun also mentioned CountBlank().
A few readers prefer the older solution of using CountIf() in the following form:
This function is the same as CountBlank() and is actually a few characters shorter than using CountBlank() — although technically, that’s not a good reason for choosing a function! AtCollege and Aaabdel suggested CountIf().
A special thanks to John, who offered a more complex solution, but one that does not count formulas that return an empty string. That’s a fundamental difference, so don’t throw this one out because it’s more complex. Someday, you might need it.
Thanks everyone for another great Office challenge!