How can I hide and display a column? What if show hidden rows in Excel doesn't work

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 the question of how to display hidden elements becomes relevant. Let's find out how to solve this problem.

It must be said right away that the choice of the option to enable the display of hidden elements primarily depends on how they were hidden. Often these methods use completely different technologies. 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, where they are collected into separate groups and then hidden. 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.

From this guide you will learn and be able to learn how to hide columns in Excel 2010-2013. You'll see how standard Excel functionality for hiding columns works, and you'll also learn how to group and ungroup columns using the " Grouping».

Knowing how to hide columns in Excel is very useful. There may be many reasons not to display some part of the table (sheet):

• Two or more columns need to be compared, but they are separated by several other columns. For example, you would like to compare columns A And Y, and for this it is more convenient to place them side by side. By the way, in addition to this topic, you may be interested in the article How to Freeze Regions in Excel.
• There are several helper columns with intermediate calculations or formulas that may confuse other users.
• You would like to hide from prying eyes or protect from editing some important formulas or personal information.

Read on to learn how Excel makes it quick and easy to hide unwanted columns. In addition, in this article you will learn an interesting way to hide columns using the " Grouping", which allows you to hide and show hidden columns in one action.

Hiding selected columns in Excel

Do you want to hide one or more columns in a table? Is there an easy way to do this:

1. Open an Excel sheet and select the columns you want to hide.

Clue: To select non-adjacent columns, select them by left-clicking while holding down the key Ctrl.

Clue: For those who love shortcut keys. You can hide selected columns by clicking Ctrl+0.

Clue: You can find the team Hide(Hide) on the Menu Ribbon home > Cells > Format > Hide and show(Home > Cells > Format > Hide & UnHide).

Voila! Now you can easily leave only the necessary data for viewing, and hide the unneeded ones so that they do not distract from the current task.

Use the Grouping tool to hide or show columns in one click

Those who work a lot with tables often use the ability to hide and show columns. There is another tool that copes with this task perfectly - you will appreciate it! This tool is " Grouping" It happens that on one sheet there are several non-adjacent groups of columns that sometimes need to be hidden or displayed - and do this again and again. In such a situation, grouping greatly simplifies the task.

When you group columns, a horizontal bar appears above them to indicate which columns are selected for grouping and can be hidden. Next to the line you will see small icons that allow you to hide and show hidden data in just one click. Seeing such icons on the sheet, you will immediately understand where the hidden columns are and which columns can be hidden. How it's done:

Clue: Another way to the same dialog box: Data > Group > Group(Data > Group > Group).

Clue: To ungroup, select the range containing the grouped columns and click Shift+Alt+Left Arrow.

Comment: Only adjacent columns can be combined into a group. If you want to hide non-adjacent columns, you will have to create separate groups.

That's all! You have learned how to use the tool to hide columns in Excel. In addition, you learned how to group and ungroup columns. We hope that knowing these tricks will help you make your usual work in Excel much easier.

When working in Excel, sometimes you need to hide columns. After this, the specified elements no longer appear on the sheet. But what to do when you need to turn them on again? Let's look into this issue.

Before you enable the display of hidden pillars, you need to figure out where they are located. This is quite easy to do. All columns in Excel are marked with letters of the Latin alphabet, arranged in order. In the place where this order is violated, which is expressed in the absence of a letter, the hidden element is located.

The specific methods for resuming the display of hidden cells depend on which option was used to hide them.

Method 1: Manually moving borders

If you have hidden cells by moving the borders, you can try to show the row by moving them to their original location. To do this, you need to stand on the border and wait for the characteristic double-sided arrow to appear. Then press the left mouse button and drag the arrow to the side.

After completing this procedure, the cells will be displayed in expanded form, as they were before.

True, it is necessary to take into account that if, when hiding, the boundaries were moved very tightly, then it will be quite difficult, if not impossible, to “catch onto” them in this way. Therefore, many users prefer to resolve this issue using other options.

Method 2: Context Menu

The method of enabling the display of hidden elements through the context menu is universal and is suitable in all cases, no matter what option they were used to hide.

1. On the horizontal coordinate panel, select adjacent sectors with letters, between which there is a hidden column.
2. Right-click on the selected elements. In the context menu, select the item "Show".

Now the hidden columns will start to appear again.

Method 3: Ribbon Button

Using the button "Format" on the tape, like the previous option, is suitable for all cases of solving the problem.

As you can see, there are several ways to enable the display of hidden columns. At the same time, it should be noted that the first option with manually moving the borders is suitable only if the cells were hidden in the same way, and their borders were not shifted too tightly. Although, this method is the most obvious for an untrained user. But the other two options using the context menu and a button on the ribbon are suitable for solving this problem in almost any situation, that is, they are universal.

But also hide them if the user does not temporarily need them. However, the data in such hidden columns and rows remains in place, and the formulas that use them continue to work as usual (with some exceptions).

Now we will look at three ways to hide columns and rows in Excel, as well as two ways to display this hidden data again. The article will only be relevant for new versions of Excel, such as Excel 2007, 2010, 2013 and 2016.

The easiest way to hide a column or row in Excel is to select it, right-click on it and select “Hide” from the menu that opens.

In addition, to hide columns or rows, you can use the “Format” button, which is located on the “Home” tab. In this case, you need to select the cell that will be included in the desired column or row, then you need to click on the “Format” button and go to the “Hide or Show” menu. There will be several options available here: Hide Rows, Hide Columns and Hide Sheet. Select one of the options and you'll hide the row, column, or sheet that includes the previously selected cell.

If you like to use key combinations more than the mouse, then you will find combinations such as CTRL-9 and CTRL-0 (9 and 0 on the main part of the keyboard, not on the right) useful.

To hide a column or row using these key combinations, you must first select the cell and then press CTRL-9 or CTRL-0. The CTRL-9 key combination hides the row in which the selected cell is located, and the CTRL-0 key combination hides the column.

How to show hidden rows and columns in Excel

If you need to show columns or rows that were previously hidden, there are several ways to do this as well. The easiest way is to select rows or columns on both sides of the hidden column or row, then right-click and select “Show” from the menu that opens.

To make it clearer, let's demonstrate it with an example. Suppose we have a table in which column “C” is hidden. In order to display this hidden column, you need to select columns from “B” to “D”. Then you need to right-click on them and select “Show”. Hidden rows can be displayed in a similar way.

Additionally, you can show hidden rows or columns using the Format button on the Home tab. To do this, as in the previous case, you first need to select the columns or rows that are on either side of the hidden column or row. After selection, you need to click on the “Format” button and go to the “Hide or Show” menu.

Here you will have several options: “Show Rows”, “Show Columns” and “Show Sheet”. Simply select the appropriate option and the hidden columns or rows will appear again.

When working with Microsoft Office Excel, sometimes you 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 of the software have a question: “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. In order to achieve hiding one or more columns, you must perform one of the following four algorithms of 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 right-click. In the context menu that opens, 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 Quick Access Toolbar, select the "Home" item, then find the "Cells" toolbar and 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 hotkeys 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 the same actions as when hiding them, selecting the “Show columns” command at the end. 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.