Good afternoon, dear visitor!
In this article, I would like to tell you about how to hide strings in Excel or how to hide the columns in Excel . First, I would like to explain to you why this is necessary, and there may be several such reasons:
1. You just need to hide data from the user;
2. There is no need to hide the data that you do not need to visualize, but for calculations it is important, 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 ranges, focusing on the final values.
Here they are, the main reasons for the need hide or collapse rows and columns. Each method is needed in its own way and is suitable for a particular need that you yourself will determine.
So, for example, hide rows or columns there is a need for a long term, you enter the data necessary for subtotals and hide it. There is no need to regularly open your hidden ranges, sometimes, except for correction or additions, you can shamanize, especially when you work with values \u200b\u200bfor.
But folding rows or columns, this is already for solving operational problems and the main goal is the convenience in visualizing the final datayou quite simply hide not very necessary for you, in which it is easy to get confused, if they are voluminous, leaving the final rows and columns containing already specific and necessary calculations. I often used this option when I was preparing information for the board of directors. For example, I took the accounting report uploaded from 1C, did the necessary calculations and calculations in it, added a couple of lines with the final and calculated values, and collapsed the primary table and got a convenient and readable document.
No matter how simple this tool may look, but you need to know about it and use it, this will allow you to get an excellent document that looks great and contains all the data you need.
The tool allows hiding rows and columns is very easy to find it is available in the context menu and with one click of the mouse the selected ranges disappear from the field of view and also appear at the click of a button, there is still the possibility, but this is another story.
But access to the folding tool is not so simple and in the standard panel, it is absent before the version of Excel 2007, in earlier versions, it is necessary through the “Settings” command in the program control panel and in the corresponding menu you pull out the icons of these operations on the panel.
Another useful the ability to collapse data is a multiple investment, that is, in the already collapsed range, you can again collapse the data, for example, there are data of the month, quarters, semester, 9 months and years, you can collapse the months leaving the quarter and annual result, we’ll even roll up the quarter and get a half-year with annual totals, we’ll roll up another time and we will only have annual results. And it is very convenient, I advise you to use it.
You can apply any of the options to solve your problems, but it is better to use and combine everything. The easier it is to display the data that people see, the more convenient it is for them to work with them, go to simplicity and convenience, and your work will be appreciated. It’s easier for people to navigate in a table with several totals than with a large table of subtotals. As the saying goes " Brevity - Sister talent ”and let your data be concise and clear.
In the example, you will clearly see not only that ashide rows in Excel or ashide columns in Excelbut also how to display hidden columns in Excel, which is also an important process of data extraction, especially if you did not hide them. By the way, besides all of the above, there is generally.
"If a lot of money - do not rejoice, if not enough - do not grieve
"
Aesop, fabulist
Surely every active PC user at least once in his life was faced with the fact that when working in Microsoft Office Excel, there was a need to hide part of the entered data without deleting it from the table. You can do this by simply hiding individual columns or rows. Such a feature of the program will be very useful when you need to preview the document that should be sent for printing.
I think many of you know how difficult it is to work with tables in which numbers creep endlessly. In order to focus exclusively on the main values, to remove intermediate results from the eyes, the function of hiding unnecessary cells is useful to you. By the way, if during the process of checking the document you find flaws 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 must say that not a single formula that calculates data from hidden cells will be cleared after you hide the cell with the original data. The fact that the value disappears from view does not mean at all that it disappears from the table, and therefore your formulas, be sure, will function in normal mode. The only exception to this rule is an Excel function such as Subtotals. The thing is that if the value of the first argument of the specified function is more than 100, then it will ignore all the data that, as a result of structuring or filtering, were in hidden rows or columns. By the way, after introducing the new “Aggregate” function, Excel users have the opportunity to set a special parameter with which it will be possible to ignore the data of the 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 display hidden data?
It is possible that soon after you hide the data you do not need, you will need it again. Excel developers have provided such an option for the development of events, and therefore made it possible to display previously hidden cells from the eyes. In order to return hidden data to the visibility zone again, you can use the following algorithms:
- Select all the columns and rows you have hidden by activating the Select All feature. To do this, you will need the Ctrl + A key combination or click on the empty rectangle located on the left side of column “A”, immediately above row “1”. After that, you can safely proceed to the already familiar sequence of actions. Click on the “Home” tab and select “Cells” in the list that appears. Here we are not interested in anything except the “Format” item, so you 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" position. Activate the function you need - "Display columns" or "Display rows", respectively.
- Activate all zones adjacent to the hidden data area. Right-click on the empty area that 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 key combination “Ctrl + A”, which will select the entire Excel workspace.
In the context menu, select "Show"
By the way, if you want to make column “A” available for viewing, we advise you to activate it using the caption “A1” in the “Name Field”, located immediately after the line with the formulas.
It happens that even the performance of all the above actions does not have the necessary effect and does not return hidden information in the field of view. The reason for this may be the following: the width of the columns is equal to the mark “0” or a value close to this value. To solve this problem, simply increase the width of the columns. This can be done by banal transfer of the right edge of the column to the distance you need to the right.
In general, it should be recognized that such an Excel tool is very useful, because it gives us the opportunity at any time to adjust our document and send to print a document containing only those indicators that are really needed for viewing, without deleting them from the workspace. Monitoring the display of the content of cells is the ability to filter the main and intermediate data, the ability to concentrate on priority problems.
You do not need to be seven spans in your forehead to display hidden cells on an unprotected Excel sheet. So I thought until I came across a strange file in which I could not show strings with traditional tools.
Below is a printscreen of a file in which lines 2:15 are torn. This is quite obvious!
Next, I tried to display the lines in the normal ways I knew. But they did not work.
I highlighted lines 1 to 16, right-clicked the soap, from the drop-down menu, select Show. 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 that lines 2:15 were hidden.
Then why can't I display them?
To check if the Hide and Show commands work, I hid the 18:19 lines and then displayed the whole sheet:
A 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 moment I started using Excel. Nevertheless, the answer came unexpectedly.
It's all about cell height!
When I tried to change the cell height by 15, all hidden cells were displayed. To do this, select lines 1 to 16, right-click on line numbers, select Row Height from the drop-down menu. In the dialog box that appears, specify 15 (this is the default height on my computer, it may be different for you).
This interesting observation led me to do some tests with line height and this is what happened:
- For strings tall<=0.07, строка считается скрытой и мы можем отобразить из с помощью обычных инструментов Excel
- For strings between 0.08 and 0.67 in height, the string appears to be hidden, but in fact it is not. We will not be able to display such lines in the normal way, only changing the height of the line.
- For strings with height\u003e \u003d 0.68, we can notice small strings, which mean that they are not hidden.
How did all this come to be? Only through experimentation.
This method can be a great way to protect data from prying eyes, of course, provided that the person using the file with such protection did not read this article.
When working in Excel, quite often you can meet a situation where a significant part of the sheet array is used simply for calculation 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 lead to disruption of the entire cycle of calculations in the document. Therefore, it is better to hide such rows or individual cells altogether. In addition, you can hide data that is simply temporarily not needed so that it does 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 us dwell on each of them, so that the user himself can understand in what situation it will be more convenient for him to use a specific option.
Method 1: Grouping
One of the most popular ways to hide items is to group them.
Method 2: dragging cells
The most intuitive way to hide the contents of cells is probably dragging the borders of the rows.
Method 3: group cells by dragging and dropping cells
If you need to hide several elements at once using this method, then you should select them first.
Method 4: the context menu
The two previous methods, of course, are the most intuitive and easy to use, but they still cannot ensure that cells are completely hidden. There is always a small space, catching on which you can expand the cell back. You can completely hide the line using the context menu.
Method 5: tool tape
You can also hide lines by using the button on the toolbar.
Method 6: filtering
In order to hide content that will not be needed in the near future so that it does not interfere, you can apply filtering.
Method 7: hide 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 still there is a way, if not completely hide the elements themselves, then hide their contents.
As you can see, there are several different ways that you can hide lines in Excel. Moreover, most of them use completely different technologies: filtering, grouping, shifting cell borders. Therefore, the user has a very wide selection of tools for solving the task. He can apply the option that he considers more appropriate in a particular situation, as well as more convenient and simple for himself. In addition, using formatting, it is possible to hide the contents of individual cells.
When working in Excel, sometimes you want to hide columns. After that, the indicated elements cease to be displayed on the sheet. But what to do when you need to turn on their display again? Let's look at this issue.
Before you enable the display of hidden pillars, you need to figure out where they are located. This is pretty simple. All columns in Excel are marked with letters of the Latin alphabet in order. In the place where this order is violated, which is expressed in the absence of a letter, and a hidden element is located.
Specific methods for resuming display of hidden cells depend on which option was used to hide them.
Method 1: manually move borders
If you hide cells by moving the borders, then you can try to show the row by moving them to their original location. To do this, you need to get to the border and wait for the appearance of a characteristic two-way arrow. Then click the left mouse button and drag the arrow to the side.
After this procedure, the cells will be displayed in expanded form, as it was before.
True, it must be borne in mind that if the borders were moved very tightly when hiding, then “hooking” on them in this way would be quite difficult, if not impossible. Therefore, many users prefer to resolve this issue by applying other options.
Method 2: the context menu
The way to enable the display of hidden elements through the context menu is universal and is suitable in all cases, no matter by which option they were hidden.
- Select adjacent sectors with letters on the horizontal coordinate panel, between which a hidden column is located.
- Right-click on the selected items. In the context menu, select Show.
Now the hidden columns will begin to display again.
Method 3: Ribbon Button
Using button "Format" on the tape, like the previous version, 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 manual movement of borders is only suitable if the cells were hidden in the same way, and their borders were not moved too tightly. Although, this method is the most obvious for an unprepared user. But the other two options using the context menu and buttons on the ribbon are suitable for solving this problem in almost any situation, that is, they are universal.
