HomeWinBuzzer TipsHow to Hide and Unhide Rows and Columns in Excel

How to Hide and Unhide Rows and Columns in Excel

We show you how to hide and unhide rows and columns in Excel using the mouse, hotkeys, the toolbar and how you can deal with hidden rows on the top of your table.

-

The ability to hide and unhide rows and columns in Excel is particularly useful for managing large datasets, protecting sensitive information, and maintaining a clean, focused workspace. This tutorial aims to guide you through the simple yet effective techniques of hiding and unhiding rows and columns in Excel, ensuring that you can control the visibility of your data with ease.

Why Hide Rows and Columns in Excel?

Hiding rows and columns in Excel is a common practice for several reasons. Firstly, it allows users to conceal sensitive data or complex formulas that are crucial for calculations but not necessarily needed for display. This is particularly important in shared workbooks where you want to prevent accidental modifications. Secondly, hiding irrelevant or less important data can help in focusing on the key information, making data analysis and presentation more efficient. Lastly, for large with extensive data, hiding unused rows and columns can significantly declutter the interface, making it easier to navigate and work with the visible data.

Unhiding: Revealing the Concealed Data

While hiding data is crucial for various reasons, the ability to unhide rows and columns is equally important. Whether you're revisiting a section of your spreadsheet for review or need to update hidden data, Excel provides straightforward methods to unhide rows and columns. This functionality is essential for maintaining the integrity and completeness of your data, especially when dealing with complex spreadsheets where hidden data plays a key role in calculations and outcomes.

How to Hide Rows and Columns in Excel

The process for hiding rows and columns in Excel is essentially the same, with the only difference being the initial selection of either rows or columns. We'll guide you through various methods using rows as the example, including keyboard shortcuts and menu options, to efficiently hide the data you don't need to display.

  1. Select the Excel rows or columns you want to hide

    Using Mouse Drag:

    Rows: Click on the row number of the first row you want to select. Then, hold down the left mouse button and drag up or down to extend the selection to other rows.

    Columns: Click on the column letter of the first column you want to select. Then, drag left or right while holding down the left mouse button to extend the selection to other columns.

    Using Shift Key:

    Rows: Click on the row number of the first row you want to select. Then, hold down the Shift key and click on the row number of the last row you want to include in your selection.

    Columns: Click on the column letter of the first column you want to select. Hold down the Shift key and click on the column letter of the last column you want to include.

    Selecting Non-Contiguous Rows or Columns:

    Using Ctrl Key (Cmd on Mac):

    Rows: Click on the row number of the first row you want to select. Then, hold down the Ctrl key (Cmd key on Mac) and click on the row numbers of any other rows you want to include in your selection.

    Columns: Click on the column letter of the first column you want to select. Hold down the Ctrl key (Cmd key on Mac) and click on the column letters of any other columns you want to include.

    Windows 11 - Excel - Select Rows

  2. Method 1: Click “Home”, then “Cells” and “Format”
     
    Windows 11 - Excel - Select Rows - Home - Cells - Format

  3. Then select “Hide Rows” or “Hide Columns” under “Visibility”
     
    Windows 11 - Excel - Select Rows - Home - Cells - Format - Visibility - Hide Rows

  4. Method 2: Right-click the selected rows or columns and select “Hide”
     
    Windows 11 - Excel - Select Rows - Context Menu - Hide

  5. Method 3: Use a hotkey
     
    To Hide Excel Rows:
    Press Ctrl + 9
     
    To Hide Excel Columns: Press Ctrl + 0 (zero)

    Windows 11 - Excel - Select Rows - Keyboard - Hide Rows Ctrl+9

How to Unhide Rows and Columns in Excel

This section will demonstrate how to make hidden rows and columns visible again. The procedure for unhiding is similar to hiding, with a slight variation in the steps involved. We'll cover different approaches to unhide rows and columns, ensuring that you can easily access your complete data whenever necessary.

  1. Method 1: Click “Home”, then “Cells” and “Format”
     
    Windows 11 - Excel - Home - Cells - Format
  2. Then select “Unhide Rows” or “Unhide Columns” under “Visibility”
     
    Windows 11 - Excel - Home - Cells - Format - Visibility - Unhide Rows
  3. Method 2: Select the rows or columns before and after the hidden rows/columns
     
    Windows 11 - Excel - Select Rows
  4. Right-click the selected rows or columns and select “Hide”
     
    Windows 11 - Excel - Select Rows - Context Menu - Unhide
  5. Method 3: Select the rows or columns before and after the hidden rows and use a hotkey
     
    To Unhide Excel Rows:
    Press Ctrl + 9
     
    To Unhide Excel Columns: Press Ctrl + 0 (zero)

    Windows 11 - Excel - Keyboard - Unhide Rows Ctrl+Shift+9

  6. Method 4: Use a double click to unhide rows or columns
     
    Hover over the rows or columns before and after the hidden rows/columns until the cursor shows a separator and double-click.
     
    Windows 11 - Excel - DoubleClick to Unhide

How to Unhide All Rows and Columns in Excel at Once

This part of the tutorial will teach you how to unhide all rows and columns simultaneously in Excel. This method is particularly useful when dealing with extensive data sets where multiple rows and columns are hidden, and individual unhiding would be time-consuming. We'll explore the most efficient ways to restore full visibility to your entire spreadsheet with just a few clicks.

  1. Click on the button in the upper left corner to select the whole table
     
    Windows 11 - Excel - Select All
  2. Use the hotkey to unhide rows or columns

    To Unhide all Hidden Rows: Press Ctrl + 9

    To Unhide all Hidden Columns: Press Ctrl + 0 (zero)

    Windows 11 - Excel - Select All - Keyboard - Unhide Columns Ctrl+Sift+0

How to Unhide Top Rows in Excel

Unhiding the top rows in Excel can sometimes be tricky, especially if they are the very first rows of the spreadsheet. This section is dedicated to showing you how to effectively unhide the top rows in your Excel worksheet. This method is particularly useful when the standard unhiding techniques don't seem to work, as is often the case with the first few rows.

  1. Option 1 for selecting: Type “A1” in the Cell Name Box and press Enter
     
    This action will move the selection to the specified cell, even if it's in a hidden row.
     
    If other top rows are hidden (say Row 2) type A2 (or the corresponding cell in the hidden row like A3, A4, etc.), to jump to the respective cell.

    Windows 11 - Excel - Select Cell A1

  2. Option 2 for selecting: Click “Home”, “Editing”, “Find & Select” and then “Go To”
     
    Windows 11 - Excel - Home - Editing - Find & Select - Go To
  3. Put the cell name as “Reference” and click “OK”
     
    Windows 11 - Excel - Home - Editing - Find & Select - Go To - A1 Reference - Accept

  4. Use one of the methods described previously to unhide the row
     
    Using the “hover and double-click method” before the first visible row number is generally a quick solution.

    Windows 11 - Excel - Select Cell A1 - Unhide first Row

Extra: How to Add Shading to Alternating Rows in Excel

It's an old trick at this point, but applying shading (zebra stripes) to alternative rows in Excel makes your sheet easier to read. The effect, also known as banded row, allows your eyes to keep their place more easily when you're scanning a spreadsheet. The difficulty, then comes in knowing where to look and how to format cells as a table in the first place. In our other guide, we show you how to apply and customize table formatting to form alternating rows in Excel.
 
Featured - How to Apply Shading to Alternate Rows in Excel

Extra: How to Alphabetize Data in Excel Columns or Rows

One of the most common types of sorting in Excel is alphabetical sorting. Whether it's a list of names, businesses, or mail addresses, sorting helps to organize and keep track of what you're doing. In our other guide, we are showing you how to alphabetize in Excel for both rows and columns.
 
Featured - How to alphabetize in Excel

Extra: How to Remove Table Formatting in Excel

In Excel, you can apply predefined table styles, making your data presentation-ready. However, there are instances where you might want to strip away the table formatting without losing the underlying data. In our other guide we show you various techniques to remove table formatting in Excel.
 
Featured - how to remove table formatting in excel

Extra: How to Structure Collected Data in Excel

To make the most of Excel's features, you need to structure your data properly. Data structure refers to how you organize your data in a spreadsheet. A good data structure makes it easy to perform calculations, filter, and sort data, create charts and pivot tables, and apply formulas and functions. In our other guide, we show you how to structure collected data in Excel using some best practices and tips.
 
Featured How to Structure Collected Data in Excel

Markus Kasanmascheff
Markus Kasanmascheff
Markus is the founder of WinBuzzer and has been playing with Windows and technology for more than 25 years. He is holding a Master´s degree in International Economics and previously worked as Lead Windows Expert for Softonic.com.

Recent News