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

9 comments

Your email address will not be published. Required fields are marked *

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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly