Why Cell Borders Disappear When Hiding Rows & Columns + Video - Excel Campus
7

Why Cell Borders Disappear When Hiding Rows & Columns + Video

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

Why Do Cell Borders Disappear When Hiding Rows and Columns in Excel

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

Cell Borders Not Visible (Disappear) When Applied to Hidden Rows Columns in Excel

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

Cell Borders Appear When Applied to Visible Rows Columns in Excel

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.

Clear All Existing Cell Borders Before Applying New Borders in Excel

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.

Cell Border Rules Can Be Used For Nicer Looking Reports to Hide Borders

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 7 comments
WJK - January 20, 2017

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???

Reply
Amr N - January 21, 2016

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

Reply
    Jon Acampora - January 22, 2016

    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!

    Reply
      Amr N - January 22, 2016

      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?

      Reply
        Chronocidal - September 9, 2016

        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.)

        Reply
Kiev - September 10, 2015

Really simple & nice skill, I just know it till read your post.

Reply

Leave a Reply: