How to Hide Rows and Columns in Excel Like a Pro

The tutorial shows three different ways to hide rows in your worksheets. It also explains how to show hidden rows in Excel and how to copy only visible rows.

If you want to prevent users from wandering into parts of a worksheet you dont want them to see, then hide such rows from their view. This technique is often used to conceal sensitive data or formulas, but you may also wish to hide unused or unimportant areas to keep your users focused on relevant information.

On the other hand, when updating your own sheets or exploring inherited workbooks, you would certainly want to unhide all rows and columns to view all data and understand the dependencies. This article will teach you both options.

Microsoft Excel worksheets can span hundreds or even thousands of rows and columns, But viewing all that data at once can feel overwhelming

The ability to hide rows and columns allows you to declutter the worksheet and focus on only the most essential info.

In this comprehensive guide, I’ll explain how to:

Temporarily hide rows and columns
Unhide hidden rows and columns
Permanently delete hidden rows and columns

I’ll also share tips to hide rows and columns faster and mistakes to avoid.

So read on to master the skill of hiding data in Excel worksheets like a pro!

Why Hide Rows and Columns in Excel?

Here are some common scenarios where hiding rows and columns can be useful:

Hide irrelevant data: Hide unused rows that are just taking up space. Also hide columns with intermediate calculations you don’t need to see.
Hide confidential data: Secure sensitive information like salaries, SSN or credit card numbers by hiding their columns.
Reduce scrolling Hide columns between beginning and end of table to compress data for easier scrolling
Print selectively: Printing the full sheet wastes paper. Hide unwanted columns before printing.
Compare data: Hide alternate rows to compare data in adjacent rows more easily.
Focus attention: Hide surrounding data to zoom in on the numbers of interest.
Present summaries: Hide source data to only present the resulting charts and summaries.

The key is hiding rows and columns doesn’t remove them – it simply toggles their visibility. The data remains intact and can be unhidden anytime.

Now let’s see how to hide and unhide in Excel.

Hiding Rows and Columns in Excel

Hiding individual cells in Excel isn’t possible. But you can easily hide entire rows and columns.

Hiding a Single Row or Column

To hide a single row:

Select the row number to highlight the whole row.
Right click on the row number and choose Hide from the menu.

The row will be hidden from view. The hidden row numbers will be skipped in the sequence.

![Hiding a row in Excel]

Similarly, to hide a column:

Select the column letter to highlight the entire column.
Right click and choose Hide from the menu.

The column will be hidden and skipped in the sequence.

![Hiding a column in Excel]

Hiding Multiple Rows or Columns

To hide multiple contiguous rows or columns:

Select the rows or columns by clicking and dragging across their row or column headers.
Right click and select Hide.

To hide non-contiguous rows or columns:

Press Ctrl and click to select the specific row or column numbers.
Right click and choose Hide.

The hidden rows and columns will be replaced by a thick gray line.

![Hiding multiple rows and columns in Excel]

Unhiding Rows and Columns

To unhide hidden rows:

Select the rows before and after the hidden rows so the hidden ones are included in the selection.
Right click and choose Unhide.

To unhide columns:

Select the columns on either side of the hidden columns.
Right click and click on Unhide.

The hidden rows and columns will be visible again.

![Unhiding rows and columns in Excel]

That’s all there is to temporarily hiding and unhiding in Excel! Now let’s look at how to permanently delete hidden rows and columns.

Deleting Hidden Rows and Columns

If you want to permanently remove hidden Excel rows and columns rather than just hide them:

Select and right click on the rows or columns adjacent to the hidden ones.
Choose Delete from the menu.

This will delete the hidden rows or columns along with their data.

![Deleting hidden rows and columns in Excel]

A quick way to delete hidden columns is by double-clicking on the grey line where they’re hidden.

Important: Deleted rows and columns cannot be retrieved. The data will be gone forever. So exercise caution!

Now let me share some tips and tricks to work faster when hiding data in Excel.

Tips for Hiding Rows and Columns in Excel

Follow these tips to hide rows and columns swiftly:

Use keyboard shortcuts Ctrl + 9 to hide rows and Ctrl + 0 to hide columns rather than right clicking.
To hide multiple non-adjacent rows or columns, hold Ctrl and click to select them before hiding.
To unhide, double click on the grey lines separating the hidden rows or columns.
When hiding columns, ensure you don’t hide any row headers or numbering.
Be careful when deleting hidden rows or columns that you don’t remove data you actually need.
Add dividers or notes in long sheets to remember what’s hidden where.

Now that you’re adept at hiding Excel rows and columns, let’s round up with some key takeaways.

Key Takeaways

The critical points to remember when hiding rows and columns in Excel are:

Use the Hide command after selecting rows or columns to temporarily hide them from view.
Select and Unhide hidden rows and columns to restore their visibility.
Permanently remove hidden rows and columns with the Delete command – but this can’t be undone.
Work efficiently by using Ctrl to multi-select, keyboard shortcuts to hide, and double-clicking gray lines to unhide.
Avoid accidentally deleting important hidden data in long complex sheets.

Hiding irrelevant, confidential or intermediate data helps present only the most impactful information. Mastering Excel’s hide and unhide features will allow you to declutter and organize unwieldy worksheets with ease.

So put these handy tips to use and see your Excel skills soar to new heights!

hide rows and columns in excel

How to hide rows in Excel

As is the case with nearly all common tasks in Excel, there is more than one way to hide rows: by using the ribbon button, right-click menu, and keyboard shortcut.

Anyway, you begin with selecting the rows youd like to hide:

To select one row, click on its heading.
To select multiple contiguous rows, drag across the row headings using the mouse. Or select the first row and hold down the Shift key while selecting the last row.
To select non-contiguous rows, click the heading of the first row and hold down the Ctrl key while clicking the headings of other rows that you want to select.

With the rows selected, proceed with one of the following options.

How to locate all hidden rows on a sheet

If your worksheet contains hundreds or thousands of rows, it can be hard to detect hidden ones. The following trick makes the job easy.

On the Home tab, in the Editing group, click Find & Select > Go To Special. Or press Ctrl+G to open the Go To dialog box, and then click Special.
In the Go To Special window, select Visible cells only and click OK.

This will select all visible cells and mark the rows adjacent to hidden rows with a white border:

Hide and unhide columns in Microsoft Excel

How do I hide columns in Excel?

Hiding columns is a very similar process to hiding rows. Right-click on the column you want to hide, or select multiple column letters first and then right-click on the selected columns. Select “Hide” from the popup menu. The hidden column letters are skipped in the row number column and a double line displays in place of the hidden rows.

How do I hide rows in Excel?

For quickly hiding rows, you can use the Ctrl + 9 shortcut. To hide columns, use Ctrl + 0. To unhide, you’ll need to first select the rows/columns surrounding the hidden area, but there’s no direct keyboard shortcut for unhiding. You’ll need to use the right-click context menu or the Home tab options after making the correct selection.

How many rows/columns can I hide in Excel?

Excel does not impose a specific limit on the number of rows/columns you can hide. You are limited only by the total number of rows and columns supported by Excel in your version (for example, Excel 2016 and later versions support up to 1,048,576 rows and 16,384 columns per sheet).

How do I make hidden rows & columns visible again?

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. This section will demonstrate how to make hidden rows and columns visible again.