Bottom line: Learn why the cell borders disappear when hiding or collapsing rows and columns, and how to fix it. Plus video tutorial.
Skill level: Beginner
Video Tutorial on Disappearing Cell Borders
Double-click video to view full screen HD.
Why Do The Cell Borders Disappear?
It all depends on what cells the borders were applied to. Here are the general rules:
Rule #1: The Borders Are NOT Visible (Disappear) When Applied to Hidden Rows or Columns
If you apply the borders to cells that will be hidden, then the borders will NOT be visible when the rows or columns are hidden.
Even if the adjacent rows or columns are visible, the border will be hidden because it was applied to the cells that are hidden. So even though cells share borders, the cell the border was applied to matters.
Rule #2: The Borders Remain Visible When Applied to Visible Rows or Columns
If you apply the borders to the cells that will remain visible when adjacent rows or columns are hidden, then the borders will remain visible.
Important Note: You must clear the borders completely before applying borders based on the rules above.
Select the cells that contain the borders and right-click > Format Cells. The keyboard shortcut to open the Format Cells window is Ctrl+1. Go to the Border tab and clear the top/bottom borders for rows or the left/right borders for columns.
Then go back and select the cells/ranges you want to apply borders to based on the rules above.
Use These Border Rules To Make Nicer Looking Reports
These rules can be confusing at first, but they are actually very useful. As I show in the video above, you can use these techniques to create nicer looking reports when row or column groups are expanded or collapsed.
In the image above I applied a Right Border to column D, and a Left Border to column F. Since these columns are hidden when the column groups are collapsed, the borders will also be hidden. You can see that the borders are not visible when the column groups are collapsed (hidden).
This gives the report a cleaner look when the groups are collapsed and only a few columns are being displayed. When the groups are expanded, the borders are visible and this makes the quarter total columns stand out more.
Checkout the video above for more details and explanation on these techniques.
Make Your Borders Behave Better
I hope those tips help you use borders strategically to create nicer looking reports. Or at least alleviate some of the confusion that is caused by disappearing borders.
Please leave a comment below with any questions or suggestions. Thanks! 🙂