How to hide part of a table in excel. Possible ways to hide columns in Excel.

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 of this function is greater than 100, then it ignores the data in hidden lines 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 the 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 opened context menu select team 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 to both sides of the hidden column, then choose 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.

When working with Excel tables 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.

Surely everyone active user At least once in my life a PC has encountered the fact that when working in Microsoft program Office Excel There was a need 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 meanings, remove them from view intermediate results, the function of hiding unnecessary cells will come in handy. 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. Just because a value disappears from view does not mean it disappears from the table, so you can rest assured that your formulas will work as expected. 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 specified function greater than 100, then it will ignore all data that, as a result of structuring or filtering, ended up in hidden rows or columns. By the way, after the introduction of the new “Aggregation” function, Excel users now have the opportunity to set a special parameter that will allow them to 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. The Excel developers foresaw this scenario, and therefore made possible display previously hidden from view 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 solve 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 an Excel tool is very useful, since it provides us with the opportunity to adjust our document at any time and print a document containing only those indicators that are really needed for viewing, without deleting them from 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.

When you do large tables, for the convenience of perceiving information and working with it, you can make invisible minor this moment rows and columns. There are several ways to hide data.

Let's look at each of the ways to hide rows in Excel in detail.

Hiding rows and columns

This method is the easiest for a beginner Excel user. All that is needed:

Select the rows or columns of interest.
Right click mouse click on the row or column heading.
Select “Hide” in the context menu.

To make hidden information visible again, select adjacent rows or columns and click “Show” in the context menu.

Grouping data

Grouping data is very convenient in order to trace the hierarchy of importance of information and at any time it is easy to hide or show rows and columns.

Select the rows or columns you want to hide at the same time.
Go to the menu, to the “Data” section.
Then go to the "Group and Structure" subsection and select "Group".
Instead of points 2 and 3, you can use the keyboard shortcut CTRL+SHIFT+→ for grouping (to ungroup you need to use the button with the arrow in the opposite direction).

In total, you can create up to 8 levels of hierarchy and then use the “+” and “-” icons to display and hide grouped information.

Also, for easier and more understandable perception of information in tables, we recommend using line numbers. How to do this is described in the article.

Good afternoon, dear visitor!

In this article I would like to tell you about how to hide lines in Excel or how to hide columns in Excel . First, I would like to explain to you why this is needed, and there may be several reasons:

1. You just need to hide the data from the user;

2. Hide data that there is no need to visualize, but this is important for calculations, such as, for example, the value for which you will draw from the ranges you have hidden.

3. To minimize visual data, when there is a lot of data, hiding the ranges, focusing on the total values.

These are the main reasons for the need hide or collapse rows and columns. Each method is needed in its own way and suits one or another need, which you yourself determine.

So, for example, hide rows or columns is necessary in the long term, you enter the data necessary for subtotals and hide it. There is no need to regularly open ranges hidden by you, sometimes, unless for correction or addition you can use shamanism, especially when you work with values for.

And here collapsing rows or columns, this is already a solution operational tasks And the main goal is convenience in visualizing the final data, You just hide the ones you don’t really need, which are easy to get confused in if they are voluminous, leaving the final rows and columns containing the specific calculations you need.

I often used this option when preparing information for the board of directors. For example, I took an accounting report downloaded from 1C, made the necessary calculations and calculations in it, added a couple of lines with total and calculated values, and collapsed the primary table and received a convenient and readable document. No matter how simple this tool may look, you need to know about it and use it, this will allow you to receive excellent document

, which looks great and contains all the data you need. A tool that allows hiding rows and columns is very easy to find

And here it is available in the context menu and with one click of the mouse, the selected ranges disappear from view and also appear on their own at the click of a button, there is also an option, but that’s another story. access to the collapse tool is not so easy and V standard panel he's missing until Excel versions 2007, more earlier versions

, you need to use the “Settings” command in the program control panel and in the corresponding menu, drag out the icons of these operations onto your panel. Another useful one, that is, in an already collapsed range, you can collapse the data again, for example, there are data for months, quarters, half-years, 9 months and years, you can collapse months, leaving quarterly and annual result, we’ll roll up the quarter one more time and get a half-year with annual results, we’ll roll it up again and we’ll only have annual results. And it is very convenient, I recommend using it.

You can use any of the options to solve your problems, or better yet, use and combine them all. The simpler the display of data that people see, the more convenient it is for them to work with it, go for simplicity and convenience and your work will be appreciated. It is easier for people to navigate a table with several total figures than with big table intermediate results. As the saying goes " Brevity is sister talent” and keep your data short and clear.

In the example you will clearly see not only Howhide rows in Excel or Howhide columns in excel, but even then how to show hidden columns in Excel, which is also important process seizure of data, especially if you were not the one hiding it. By the way, in addition to everything listed above, there is generally.

“If there is a lot of money, don’t be happy, if there’s little, don’t be sad.”
"
Aesop, fabulist