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
Watch on YouTube & Subscribe to our Channel
Downloads
Download the file by clicking the link below:
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! 🙂
Really simple & nice skill, I just know it till read your post.
simple and clear
what about when the first row of merged cells is hidden, the top border disappears, why the next visible cell in the merged cells doesn’t have a top border
Hi Amr,
Great question! The same basic principle applies here. If you select the cell above the merged cell and apply a BOTTOM border, then you should see the border when you hide the top row of the merged cell. I hope that makes sense. Let me know if you have any questions. Thanks!
appreciate your reply, but what about if that above cell is part of a merged range and the row contain this above cell is also hidden?
I am having the same problem – I have a report with grouped/headed columns, and I am hiding the 0-value columns. This causes issues with borders, even on the un-merged cells. Since SubTotal(103,..) only excludes Vertically hidden cells and not Horizontally hidden ones, I have 4 tables of calculations and 2 conditional formats to work out “should have left border” and “should have right border” – but it doesn’t work on the Merged cells!
(As an example: 37 columns, 5 rows. Top row has a blank cell, then 12 merged into “2014”, 12 merged into “2015”, and twelve merged into “2016”. Second row is a blank cell plus the months of the year thrice. Third and fourth rows are a title, with 36 values, and the fifth rows is the month total. If a total is 0, hide the column.)
You might be surprised to discover that when you paste the table into a Word doc or email, the cell borders that are missing in Excel actually show up. The problem appears to be with how Excel displays the table in Excel, which probably means you won’t be able to get the table you’re looking for without moving it outside of Excel, sadly.
The critical thing to remember about merged cells is that ONLY the top left cell of the original merging cells exists (so to speak) to Excel from that point forward. It doesn’t even just leave data in the other cells, though never to be seen again while merged, but rather wipes it all out when doing the merge. And then it never considers anything for display except that top, left cell.
So hide that top left cell and ALL the formatting is now gone from view: that of the not-top-left cells was completely suppressed, and that never changes, and now you’ve hidden the one cell in the batch that Excel actually was displaying content and formatting for. So there’s nothing left so far as Excel is concerned.
Or said a slightly different way, merging causes Excel to never look to the not-top-left cells again for anything, at all, so their formatting has now been removed from consideration, no matter what gets done (short of unmerging the cells). Hide the one cell that it IS looking to for formatting and there’s nothing left in the ENTIRE merged block that Excel will take notice of.
So no borders can now be displayed. You told it to ignore all the other cells and it is doing so. And now you’ve told it to ignore the one cell it was willing to look to. And it is doing so. You’ve told it to ignore the entire block of cells.
And it is doing so.
I’ve suddenly started getting slashes in some, but no all open cells directly adjacent to hidden cells. If the hidden cells are on the left, I get a far-left-justified forward slash. If I then delete the contents in the non-hidden cell to the left (which is directly left of the hidden cells), then a back-slash shows up directly adjacent to the right cell border (essentially making a “V”, with one slash on either side of the hidden content.
This is happening on a document that’s shared between different users, and possibly different versions of Excel, although the two primary users are both using Office 2013.
Any clues???
I have not heard of that one. If possible, can you share the file? Thanks!
hello, how do you make it show the top part of the sheet shows you which rows are hidden?
Thank you the info on why borders disappear was helpful.
How do I get rid of the 123/ABC gray margins/header after working with hide/unhide?
Great video. I knew that this was happening and didn’t know why. This has explained it clearly. I had an “a-ha” moment when watching it.
EXCELLENT vid! Thank you
Thanks for the tips!
Hi Jon,
When I set Print Title, the next page print preview have lost cells border at Title repeat.
Can you explain for me, please?