At first glance, hiding rows and columns in spreadsheets seems like a very helpful feature. You have some table or analysis with extraneous data and so you just hide all of the columns and rows that you don’t want. Seems straightforward, right? Wrong.
Hiding rows and columns in a spreadsheet is a very manual task. As a result, only the person who hid the column or row knows that there are hidden cells. Yes, if you look at the axis letters and numbers you can technically see that a row or letter has been skipped, but you still don’t know what has been hidden. Not to mention, forcing every new user of a spreadsheet to be cognizant of whether or not there is a hidden row based on if a number or letter has been skipped is a horrible user experience that can easily be overlooked.
If you receive a spreadsheet with a bunch of hidden rows and columns, in addition to not knowing what has been hidden, you also don’t know the reason or pattern behind why certain rows or columns are hidden without unhiding everything to check. Even then, you may have to use your own reasoning or intuition to figure out the pattern. In the worst case, you may never figure out why the original creator hid those rows or columns.
If you’ve used Excel or Google Sheets long enough, you’ve probably run into a situation where you sum up an entire table and the sum total looks off to you. After too much time of trying to debug, you realize that it’s because there are rows that have been hidden. Since hidden rows are still included in totals, that’s why your total is off.
The example below is a simplification of this issue. You can quickly see that this table does not make sense: 1+1+1 does not equal 5, but that’s because it appears that there are two rows that are hidden between row 1 and 4. A few questions might arise right away: Why were those two rows hidden? Should they be included in the total or not? What are the numbers in those two rows; are they 1 and 1, 0 and 2, or 3 and -1?
So, whats the solution? Should we boycott the hiding columns and rows feature altogether? There is admittedly no great solution in existing spreadsheet software, but here are the principals I follow anytime I need to hide rows or columns:
1. Always maintain a version of the unedited source data
- 1. In other words, don’t hide the original data, but duplicate it and then hide data in the duplicated version.
2. Have good documentation
- 1. For example, if you duplicate data and then hide rows or columns in the duplicated data, you should make it clear in the title of the new table or spreadsheet and try to provide an explanation where possible.
3. When possible, try to automate instead of manually hiding rows or columns
- 1. You can get creative with a combination of functions such as VLOOKUP, XLOOKUP, INDEX, MATCH and other formulas to automatically generate a grid that does not have hidden data, but just excludes certain data automatically.
