Hiding rows and columns is a great skill. Often we are required to create spreadsheet elements that are necessary for proper operation, but we don’t want them to appear on the screen or the printout.
The downside to hiding things is that they can interfere with other users who are not aware of their existence.
Take the following worksheet as an example.
We have a list of sales, and we want to get a quick total at the bottom of column C.
The user writes a quick SUM formula that targets all the cells above their current location.
When they hit ENTER, they receive an answer, but they don’t realize that the answer is incorrect.
But why is the answer wrong?
It’s subtle but notice that rows 20-30 have been hidden.
Unhiding rows 20-30 reveals values that were created to service the chart in column F.
Most users aren’t going to notice the skipped row numbers; and honestly, why should you expect them to?
A way to hide the rows and draw attention to their hidden state is to use the GROUP feature.
By selecting rows 20-30, then pressing
You can also activate the GROUP feature by clicking Data (tab) -> Outline (group) -> Group / Ungroup.
NOTE: You can ungroup rows and columns using the UNGROUP button on the Data tab or press
The dots and lines next to the grouped rows that lead to the big “minus” button indicate that the rows have been grouped.
Clicking the “minus” to hide the rows transforms the button into a “plus” button.
This makes it a bit more obvious that rows have been hidden.
PRO TIP: This leads back to the previous topic about separating workbook elements into different sheets. Calculations that feed charts should be stored on a sheet independent of the raw data sheet. This would make this entire scenario moot. But if you must store your data in this manner, you can at least make it more obvious using grouped rows.