How to bring back hidden columns in excel. Hiding selected columns in Excel

When working with a program from Microsoft Office Excel Sometimes there is a need to hide some columns or rows. The reason for this may be to improve the readability of the document or simply to hide some calculations or data. In this regard, users software tool The question arises: “How to hide columns in Excel and how to return their visibility back?”

Ways to hide columns

To improve convenience, the program contains functions that allow you to hide a certain range of cells. It does not matter whether they contain any information or are empty. To hide one or more columns, you must do one of four things: following algorithms actions.

Having selected the cells of the columns that need to be hidden, hover the mouse cursor over the selected range - the column headings will turn darker, and click right click. In the opened context menu select the "Hide" command.
Having selected the entire required columns, press the “Menu” key on the keyboard, which will lead to a similar opening of the context menu, and then also select the “Hide” command.
Having selected one or more cells in the selected columns, follow the following commands. In the panel quick access select "Home", then find the "Cells" toolbar, in which click on the "Format" icon. In the drop-down list, find the “Visibility” heading, which contains the “Hide or Show” item, where you select the “Hide Rows” command.
To achieve the fastest results, Excel has a set of hot keys that can be used to hide columns by pressing two buttons on the keyboard. Having selected the column cells that you want to hide, you must press the key combination: “Ctrl” + “0”.

As you can see, if you need to understand how to hide columns in Excel, learning this is not difficult.

Restore visibility to columns

After hiding columns, the question often arises about how to open hidden columns in Excel. Returning hidden cells is very easy - by selecting a certain range of cells containing two columns, between which there are hidden ones, perform similar actions, as when hiding them, by selecting the “Show columns” command at the end point. In the case of using hot keys, just press the combination: “Ctrl” + “Shift” + “0”.

What else can you hide?

In addition to columns, Excel offers the user to hide rows, as well as entire sheets. In order to hide or show rows, you need to proceed similarly to how to hide and show hidden columns in Excel. The only difference is the choice at the end of the command "Hide Rows" and "Show Rows", and a slightly modified keyboard shortcut: "Ctrl" + "9" to hide and "Ctrl" + "Shift" + "9" to show rows.

When working with sheets, the process is a little different compared to how to hide columns in Excel. You can hide or display a sheet using the "Format" tab by selecting the "Hide Sheet" command from the "Hide or Show" menu while on the sheet you want to hide. To return visibility, just follow a similar procedure by selecting “Display Sheet” and selecting the required sheets in the window that opens. This can also be done by right-clicking on the panel containing the list of sheets, where you can select the option to hide or show.

Specifics of hidden cells

By understanding how to hide rows and how to hide columns in Excel, you can make your job a lot easier. The advantage of hidden cells is that they are not printed - this way you can exclude data that is unnecessary from being printed on paper, without editing the document itself. Another benefit is that the data is more readable - by hiding unnecessary columns, meaningful data can be placed side by side.

You don't have to be a genius to display hidden cells on an unprotected Excel sheet. That's what I thought until I came across 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 I 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.

When working in Excel program Quite often you can encounter a situation where a significant part of the sheet array is used simply for calculations and does not carry an information load for the user. Such data only takes up space and distracts attention. In addition, if the user accidentally violates their structure, this can disrupt the entire cycle of calculations in the document. Therefore, such lines or individual cells It's better to hide it altogether. In addition, you can hide those data that are simply temporarily not needed so that they do not interfere. Let's find out in what ways this can be done.

There are several completely different ways to hide cells in Excel. Let's look at each of them in detail so that the user can understand in what situation it will be more convenient for him to use a particular option.

Method 1: grouping

One of the most popular ways hiding elements is their grouping.

Method 2: dragging cells

The most intuitive in a clear way Hiding the contents of cells is probably done by dragging the row borders.

Method 3: group hiding cells by dragging

If you need to hide several elements at once using this method, you should first select them.

Method 4: Context Menu

Two previous methods, of course, are the most intuitive and easy to use, but they still cannot provide complete hiding of cells. There is always a small space left, by catching on which you can expand the cell back. It is possible to completely hide a line using the context menu.

Method 5: Tool Tape

You can also hide rows using the button on the tool ribbon.

Method 6: Filtering

In order to hide content from the sheet that is not needed in the near future, so that it does not interfere, you can apply filtering.

Method 7: Hiding Cells

Now let's talk about how to hide individual cells. Naturally, they cannot be completely removed, like lines or columns, since this will destroy the structure of the document, but there is still a way, if not to completely hide the elements themselves, then to hide their contents.

As you can see, there are several different ways, with which you can hide rows in Excel. Moreover, most of them use completely different technologies: filtering, grouping, shifting cell borders. Therefore the user has very wide choice tools to solve the problem. He can apply the option that he considers more appropriate in specific situation, as well as more convenient and simpler for yourself. In addition, you can use formatting to hide the contents of individual cells.

Surely everyone active user At least once in my life a PC has encountered the fact that when working in a program Microsoft Office Excel needed to hide some of the entered data without deleting it from the table. This can be done by simply hiding individual columns or rows. This feature of the program will come in very handy when you need to make preview document that needs to be sent for printing.

I think many of you know how difficult it is to work with tables in which numbers spread like an endless canvas. In order to focus attention exclusively on the main values and remove intermediate results from view, the function of hiding unnecessary cells will be useful to you. By the way, if during the process of checking a document you find shortcomings in it, you will have the opportunity to display the data hidden in the Excel sheet in order to correct them.

If necessary, you can hide unnecessary data without deleting it

I should note that no formula that was calculated using data from hidden cells will be cleared after you hide the cell with the original data. The fact that a value disappears from view does not mean that it disappears from the table, and therefore your formulas, rest assured, will function in normal mode. The only exception to this rule is this Excel function, as “Subtotals”. The thing is that if the value of the first argument of the specified function is greater than 100, then it will ignore all the data that, as a result of structuring or filtering, ended up in hidden rows or columns. By the way, after implementation new feature“Aggregation” Excel users now have the opportunity to install special parameter, with which you can ignore the data of cells that were hidden.

How to hide data in columns and rows?

If you are faced with the need to hide columns and/or rows, then the following methods will certainly come in handy:

How to show hidden data?

It is quite possible that soon after you hide the data you do not need, you will need it again. Excel developers foresaw this scenario, and therefore made it possible to display previously hidden cells. In order to bring hidden data back into visibility, you can use the following algorithms:

Select all the columns and rows you have hidden by activating the “Select All” function. To do this, you will need the key combination “Ctrl+A” or click on the empty rectangle located at left side from column "A", immediately above line "1". After this, you can safely proceed to the already familiar sequence of actions. Click on the "Home" tab and select "Cells" from the list that appears. Here we are not interested in anything except the “Format” item, so we need to click on it. If you did everything correctly, you should see a dialog box on your screen in which you need to find the “Hide or Show” option. Activate the function you need - “Show columns” or “Show rows”, respectively.
Make active all zones adjacent to the hidden data area. Right-click on the empty zone you want to display. A context menu will appear on your screen in which you need to activate the “Show” command. Activate it. If the sheet contains too much hidden data, then again it makes sense to use the “Ctrl+A” key combination, which will select the entire Excel workspace.

Select "Show" from the context menu

By the way, if you want to make column “A” available for viewing, we advise you to activate it using the spelling “A1” in the “Name Field” located immediately after the line with formulas.

It happens that even performing all of the above actions does not have the desired effect and does not bring you back into view hidden information. The reason for this may be the following: the width of the columns is equal to “0” or a value close to this value. To decide this problem, just increase the width of the columns. This can be done by simply moving the right edge of the column to the required distance to the right.

In general, it should be recognized that such Excel tool is very useful because it gives us the opportunity to adjust our document at any time and send a document for printing that contains only those indicators that are really needed for viewing, without removing them from the work area. Control over the display of cell contents is the ability to filter main and intermediate data, the ability to concentrate on priority problems.