Show hidden rows in excel. Enable Hidden Columns to Be Shown in Microsoft Excel

When working with Excel tables Sometimes you need to hide certain areas of the sheet. Quite often this is done if, for example, they contain formulas. Let's find out how you can hide columns in this program.

There are several options to do this this procedure. Let's find out what they are.

Method 1: Shift Cells

The most intuitive option with which you can achieve desired result, is the cell shift. In order to carry out this procedure, we move the cursor over the horizontal coordinate bar at the place where the border is located. A characteristic arrow directed in both directions appears. We left-click and drag the boundaries of one column to the boundaries of another, as far as this can be done.

After this, one element will actually be hidden behind the other.

Method 2: Using the context menu

It is much more convenient to use the context menu for these purposes. Firstly, it is easier than moving the borders, and secondly, in this way, it is possible to achieve complete hiding of the cells, unlike the previous option.

Right click on horizontal panel coordinates in the area Latin letter, which indicates the column to be hidden.
In the appeared context menu click on the button "Hide".

After this, the specified column will be completely hidden. To make sure of this, take a look at how the columns are labeled. As you can see, one letter is missing in the sequential order.

Advantages this method before the previous one is that it can be used to hide several consecutive columns at the same time. To do this, you need to select them, and in the context menu that appears, click on the item "Hide". If you want to carry out this procedure with elements that are not located next to each other, but are scattered across the sheet, then the selection must be carried out with the button held down Ctrl on keyboard.

Method 3: Using Tools on the Ribbon

In addition, you can perform this procedure using one of the buttons on the ribbon in the tool block "Cells".

As in the previous case, this way you can hide several elements at once by selecting them as described above.

As you can see, there are several ways to hide columns in Excel. The most intuitive way is to shift cells. However, it is still recommended to use one of the following two options (context menu or button on the ribbon), as they guarantee that the cells will be completely hidden. In addition, elements hidden in this way will then be easier to display back if necessary.

When working with Excel spreadsheets, sometimes you need to hide formulas or temporarily unnecessary data so that they do not interfere. But sooner or later there comes a time when the formula needs to be adjusted, or the user suddenly needs the information contained in hidden cells. That's when it becomes topical issue, how to show hidden elements. Let's find out how to solve this problem.

It must be said right away that choosing the option to enable display hidden elements primarily depends on how they were hidden. Often these methods are used completely different technology. There are the following options to hide the contents of a sheet:

shifting the boundaries of columns or rows, including through the context menu or button on the ribbon;
data grouping;
filtration;
hiding cell contents.

Now let’s try to figure out how to display the contents of elements hidden using the above methods.

Method 1: opening boundaries

Most often, users hide columns and rows by closing their borders. If the borders have been moved very tightly, then it is difficult to grab onto the edge to push them back. Let's find out how this can be done easily and quickly.

There is another option that can be used to display elements hidden by shifting the borders of elements.

These two options can be used not only if the cell borders have been moved manually, but also if they have been hidden using tools on the ribbon or the context menu.

Method 2: Ungrouping

Rows and columns can also be hidden using grouping when they are collected into separate groups and then hide. Let's see how to display them on the screen again.

The groups will be deleted.

Method 3: Removing the filter

In order to hide temporarily unnecessary data, filtering is often used. But, when the need arises to return to working with this information, the filter must be removed.

Method 4: Formatting

In order to hide the contents of individual cells, formatting is applied by entering the expression “;;;” in the format type field. To reveal hidden content, you need to return these elements to their original format.

As you can see, after this the hidden values are again displayed on the sheet. If you think that the display of information is incorrect and, for example, instead of a date you see a regular set of numbers, then try changing the format again.

When solving the problem of displaying hidden elements the main task is to determine with what technology they were hidden. Then, based on this, use one of the four methods described above. You need to understand that if, for example, the content was hidden by closing the borders, then ungrouping or removing the filter will not help display the data.

Excel allows you to hide rows and columns. This may be necessary when printing a final report in which you do not need to show all available data, or if you need to hide empty lines and columns to focus only on the area of the sheet being used.

Formulas that reference data in hidden columns or rows will continue to function normally. The exception is the function SUBTOTALS. If the first argument to this function is greater than 100, then it ignores data in hidden rows or columns as a result of filtering or structuring. Besides, in new feature UNIT There is an option to ignore hidden data in rows, even if the rows are manually hidden. You can learn more about these features at Help- in fact, they can do much more than just add numbers.

Hiding Columns or Rows

To hide one or more columns, use any of following methods.

Select the cell in the column (or cells in columns) you want to hide. Then select the command Home Cells Format Hide or Show Hide Columns.
Select the columns and right-click on the headings of these columns, and then in the context menu that opens, select the command Hide.
Select the cell in the column (or cells in columns) you want to hide and click Ctrl+0.

To hide one or more rows, use any of the following methods.

Select the cell in the row (or cells in rows) you want to hide. Then select the command Home Cells Format Hide or Show Hide Rows.
Select the rows and right-click on the headers of these rows, and then in the context menu that opens, select the command Hide.
Select the cell in the row (or cells in rows) you want to hide and click Ctrl+9.

Bringing hidden columns or rows back to the screen

What was hidden can be shown again. To show one or more hidden columns, use any of the following methods. Note: bringing a hidden column or row back to the screen is not as simple as hiding it, since you cannot select a column or row that is hidden.

Select the cells adjacent on both sides to hidden column, then select a command Home Cells Format Hide or Show Show Columns;
Select the columns adjacent to both sides of the hidden column, then right-click on the headers of these columns and select the command in the context menu that opens Show.

To display one or more hidden rows, use any of the following methods.

Select the cells adjacent to the top and bottom of the hidden row. Then select the command Home Cells Format Hide or Show Show Rows.
Select the lines adjacent to both sides of the hidden line, then right-click on the headers of these lines and select from the context menu that appears Show.
Select the cells adjacent to the top and bottom of the hidden row and click Ctrt+Shift+9.

You don't have to be a genius to display hidden cells on an unprotected Excel sheet. That's what I thought until I encountered a strange file where I couldn't show the lines with traditional tools.

Below is a print screen of the file in which lines 2:15 are hidden. This is quite obvious!

Next, I tried to display the lines in normal ways that he knew. But they didn't work.
I selected lines 1 through 16, right-clicked, and selected Show from the drop-down menu. Lines 2:15 remained invisible to me.

The worksheet was not protected.
I also tried moving from cell A1 to the cell below using the keyboard to see the changes in the Name window. It changed from A1 to A16, which meant lines 2:15 were hidden.

Then why can't I display them?

To test if the Hide and Show commands worked, I hid rows 18:19 and then showed the entire sheet:

Strange situation, lines 18 and 19 were displayed, lines 2:15 continued to be hidden. I have not encountered a more strange situation since the beginning using Excel. However, the answer came unexpectedly.
It's all about the height of the cells!
When I tried to change the height of the cells to 15, all the hidden cells showed up. To do this, select lines from 1 to 16, right-click on the line numbers, and select Row Height from the drop-down menu. In the dialog box that appears, specify 15 (this is the default height on my computer, yours may be different).

This interesting observation led me to do several tests with line height and this is what came out:

For rows with height<=0.07, строка считается скрытой и мы можем отобразить из с помощью обычных инструментов Excel
For rows with heights between 0.08 and 0.67, the row appears to be hidden, but in fact it is not. We will not be able to display such rows in a normal way by just changing the row height.
For rows with a height >=0.68, we can notice small rows, which means they are not hidden.

How did all this come to light? Only through experiments.

This method can be in a great way data protection from prying eyes, of course, provided that the person using the file with such protection has not read this article.