This tutorial describes three different ways to hide lines in a worksheet. It also explains how to show hidden lines in Excel and how to copy visible lines.

If you want to prevent users from paying attention to parts of the worksheet, you do not want to see them, so hide such lines from your view. This technique is often used to hide sensitive data or formulas, but you may also want to hide unused or unimportant areas to focus your users on relevant information.

On the other hand, when updating your personal worksheet or browsing workbooks, you definitely want to see all the rows and columns to view all the data and understand the dependencies. This article will teach you both.

• How to hide lines in Excel
• How to show hidden rows in Excel
• Show hidden rows using ribbon
• Show hidden rows via right-click menu
• Shortcut to show hidden rows
• Show hidden lines by double-clicking
• How to display all lines in Excel
• How to display multiple lines in Excel
• How to display the initial lines
• Tips and tricks to hide and show lines
• Hide rows containing empty cells
• Hide rows by cell size
• Hide unused rows (blank)
• Find all hidden lines on a page
• How to copy visible lines in Excel
• Do not display rows in Excel – solutions to common problems

How to hide lines in Excel

Like almost everything in Excel, there is more than one way to hide rows: using the ribbon button, right-click menu, and keyboard shortcut.

By the way, you start by selecting the lines you want to hide:

• Click a title to select a row.
• Drag the row title using the mouse to select multiple consecutive lines. Or select the first row and hold down the Shift key when selecting the last row.
• To select discontinuous rows, click on the title of the first row and hold down the Ctrl key to select the titles of the other rows you want.

Select one of the following options by selecting rows.

Hide rows using ribbon

If you enjoy working with ribbons, you can hide the lines this way.

1. Go to the Home> Cells tab and click the Format button.

2. Under Visibility, go to Hide & Unhide and then select Hide Rows.

Alternatively, you can go to> Home> Format> Row Height and type 0 in the Row Height box.

In either case, the selected rows are immediately hidden from view.

Hide rows using the right-click menu

You can access the Hide command from the right-click menu: right-click on the selected rows and then click Hide.

Excel shortcut to hide rows

If you prefer not to take your hand off the keyboard, you can quickly hide the selected row (s) by pressing this shortcut: Ctrl + 9

How to show hidden rows in Excel

Microsoft Excel has different ways of displaying lines, such as hiding them. Use any of your personal preferences. What makes the difference is the area you choose to instruct Excel to display all hidden rows, special rows, or the first row in a tab.

Show hidden rows using ribbon

On the Home tab, in the Cells group, click the Format button, select Hide & Unhide below Visibility, and then click Unhide Rows.

Show hidden rows via right-click menu

You select a group of rows, including the top and bottom rows of the row (s) you want to display, right-click on them, and select Unhide in the list that appears. This method is used to show a hidden line as well as several lines.

For example, to show all the hidden rows between rows 1 and 8, select this group of rows as shown below, right-click and click Unhide:

Shortcut to show hidden rows

Here is the Excel Unhide Rows shortcut: Ctrl + Shift + 9

Pressing this key combination (3 keys simultaneously) shows each row hidden in the selection range.

Show hidden lines by double-clicking

In many cases, the fastest way to display rows in Excel is to double-click on them. The good thing about this method is that you do not need to choose anything. Simply hover your mouse over the hidden row titles and double-click when the mouse pointer turns into a split double arrow.

How to display all lines in Excel

To display all rows on a page, you must select all rows. To do this, you can either:

• Click the Select All button (a small triangle in the upper left corner of a tab, at the intersection of row and column titles):

• Press the Select All shortcut key: Ctrl + A

Please note that in Microsoft Excel, this shortcut behaves differently in different situations. If the cursor is empty in a cell, the entire leaf job is selected. But if the cursor is in one of the cells connected to the data, only that group of cells are selected. Press Ctrl + A again to select all cells.

After selecting the entire page, you can display all the rows by doing one of the following:

• Press Ctrl + Shift + 9 (fastest method).
• Select Unhide from the right-click menu (the easiest way to remember nothing).
• On the Home tab, click Format> Unhide Rows.

How to display all lines in Excel

To display all rows and columns, select the entire page as described above, then press Ctrl + Shift + 9 to show hidden rows and Ctrl + Shift + 0 to show hidden columns. Give.

How to display specific rows in Excel

Depending on which rows you want to display, select them as shown below and then apply one of the unhide options discussed above.

To show one or more adjacent rows, select the top row and bottom row (s) you want to display.

To display multiple non-adjacent rows, select all rows between the first and last visible row in the group.

For example, to display rows 3, 7, and 9, select rows 2-10, and then use the ribbon, right-click menu, or keyboard shortcut to display them.

How to display the initial lines

Hiding the first line in Excel is easy, you hide it just like any other line on the page. But when one or more of the above lines are hidden, how can you show them again, given that there is nothing else to choose from?

The clue is cell selection A1. All you have to do is type A1 in the Name box and press Enter.

Alternatively, go to Home> Editing, click Find & Select, and then click To Go To. The Go To dialog box appears, type A1 in the Reference box and click OK.

By selecting cell A1, you can display the first hidden row in the usual way, by clicking Format> Unhide Rows on the ribbon, or selecting Unhide from the right-click menu, or pressing the shortcut Ctrl + Shift + 9.

Aside from this common approach, there is another (and faster!) Way to display the first line in Excel. Simply move to the hidden row title and double-click when the mouse cursor turns into a split double arrow:

Tips and tricks to hide and show lines in Excel

As you can see recently, hiding and showing rows in Excel is fast and straightforward. However, in some cases even a simple task can become a challenge. Below are simple solutions to some complex problems.

The Hide rows containing empty cells

Follow these steps to hide rows that contain empty cells:

1. Select the domain containing the empty cells that you want to hide.

2. On the Home tab, in the Editing group, click Find & Select> Go To Special.

3. In the Go To Special dialog box, select the Blanks button and click OK. This selects all empty cells in the range.

4. Press Ctrl + 9 to hide the corresponding rows.

This method works well when you want to hide all rows that have at least one empty cell, as shown in the image below:

If you want to hide empty rows in Excel, ie rows where all cells are empty, use the COUNTBLANK formula.

Hide rows by cell size

Use the Excel Filter feature to hide and display rows based on the number of cells in one or more columns. This feature gives you a handful of predefined filters for text, numbers and dates, as well as the ability to configure a custom filter with the desired criteria.

To display filtered rows, remove the filter from a specific column or all filters on a page.

The Hide unused rows (blank)

If there is a small workspace on the page and a lot of unnecessary empty rows and columns, you can hide unused rows this way:

1. Select the row below the last row with the data (to select the entire row, click on the row tab).

2. Press Ctrl + Shift + Down arrow to expand the selection at the bottom of the tab.

3. Press Ctrl + 9 to hide selected rows.

Hide unused columns in a similar way:

1. Select the empty column after the last column with the data.

2. Press Ctrl + Shift + Right arrow to select other unused columns at the bottom of the tab.

3- To hide the selected columns, press Ctrl + 0. Done!

If you decide to display all cells later, select the full screen, then press Ctrl + Shift + 9 to hold all rows and Ctrl + Shift + 0 to display all columns.

Find all hidden lines on a page

If your worksheet contains hundreds or thousands of lines, it is difficult to identify hidden items. The following trick makes the job easier.

On the Home tab, in the Edit group, select Find & Select> Go To Special. Or press Ctrl + G to open the Go To dialog box, then click Special.

In the Go To Special window, select Visible cells only and click OK.

Select all visible cells and mark the rows adjacent to the hidden rows with a white border:

How to copy visible lines in Excel

Suppose you have hidden several discontinuous lines and now you want to copy the continuous data to another tab or workbook. How can you do this? Select visible lines with the mouse and press Ctrl + C to copy them? But it also copies hidden lines!

To copy visible lines in Excel, you must work differently:

1. Select the visible lines using the mouse.

2. Go to Home> Editing, and select Find & Select> Go To Special.

3. In the Go To Special window, select only the visible cells and click OK. This will select only visible rows.

4. Press Ctrl + C to copy the selected rows.

5. Press Ctrl + V to paste visible rows.

Do not display rows in Excel

If you have trouble displaying rows in your worksheets, it is most likely due to one of the following:

1- The worksheet is protected

Whenever Hide and Unhide features are disabled in Excel, the first thing to check is worksheet protection.

To do this, go to Review> Changes and see if the Unprotect Sheet button is there (this button only appears on protected tabs; in an unprotected worksheet, there is a Protect Sheet button instead). So, if you see the Unprotect Sheet button, click on it.

If you want to keep the user page protected but do not allow the lines to be hidden and visible, click the Protect Sheet button on the Review tab, select Format rows and click OK.

2. The row height is low, but not zero

If the worksheet is not protected, but certain rows still cannot be displayed, check the height of those rows. The point is, if the row height is set slightly, between 0.08 and 1, the row may appear to be hidden, but this is not the case. Such lines cannot be displayed in the usual way. You must change the row height to return them.

To do this, follow these steps:

1. Select a group of lines, including one line above and one line below the problematic line (s).

2. Right-click on them and select Row Height from the menu.

3. Type the desired value in the Row Height box (for example 15) and click OK.

This will make all hidden lines visible again.

If the row height is set to 0.07 or less, such rows usually cannot be displayed without the above manipulations.

3- The problem of not displaying the first line in Excel

If someone has hidden the first row in a tab, you may have trouble returning it because you cannot select the row before it. In this case, select cell A1 as described in the initial rows and then display the row as usual, for example by pressing Ctrl + Shift + 9.

If none of the above works for you, it is possible that the hidden lines are the result of a filter. In this case, clear the filters.