Contents
When working with different tables in Excel format, sooner or later it will be necessary to temporarily hide some data or hide intermediate calculations and formulas. At the same time, deletion is unacceptable, since it is possible that editing of hidden data will be required to get the formulas to work correctly. To temporarily hide this or that information, there is such a function as hiding cells.
How to hide cells in Excel?
There are several ways to hide cells in Excel documents:
- changing the boundaries of a column or row;
- using the toolbar;
- using the quick menu;
- grouping;
- enable filters;
- hiding information and values in cells.
Each of these methods has its own characteristics:
- For example, hiding cells by changing their borders is the easiest. To do this, just move the cursor to the bottom border of the line in the numbering field and drag it up until the borders touch.
- In order for hidden cells to be marked with “+”, you need to use the “Grouping”, which can be found in the “Data” menu tab. Hidden cells will thus be marked with a scale and a “-” sign, when clicked, the cells are hidden and a “+” sign appears.
Important! Using the “Grouping” option, you can hide an unlimited number of columns and rows in the table
- If necessary, you can also hide the selected area through the pop-up menu when you press the right mouse button. Here we select the item “Hide”. As a result, the cells disappear.
- You can hide several columns or rows through the “Home” tab. To do this, go to the “Format” parameter and select the “Hide or show” category. Another menu will appear, in which we select the necessary action:
- hide columns;
- hide lines;
- hide sheet.
- Using the filtering method, you can hide information in several rows or columns at the same time. On the “Main” tab, select the “Sort and Filter” category. Now in the menu that appears, activate the “Filter” button. A checkbox with an arrow pointing down should appear in the selected cell. When you click on this arrow in the drop-down menu, uncheck the boxes next to the values uXNUMXbuXNUMXbthat you want to hide.
- In Excel, it is possible to hide cells without values, but at the same time not violate the structure of calculations. To do this, use the “Cell Format” setting. To quickly call up this menu, just press the combination “Ctrl + 1”. On the left side of the window, go to the “(all formats)” category, and in the “Type” field, go down to the last value, that is, “;;;”. After clicking on the “OK” button, the value in the cell will disappear. This method allows you to hide some values, but all formulas will work properly.
If several users are working on a document, then you should know how to detect the presence of hidden cells in an Excel file. To only find hidden columns and rows, but not display them, you will have to check the sequence of all column and row headings. A missing letter or number indicates hidden cells.
If the table is too large, then this method is extremely inconvenient. To simplify the process of finding hidden cells in a document, you need to go to the “Editing” command set in the “Home” menu. In the “Find and select” category, select the “Select a group of cells …” command.
In the window that opens, check the “Only visible cells” category. After that, within the table, you can see not only the selected area of cells, but also thickened lines, which indicate the presence of hidden rows or columns.
Just like that, opening cells hidden from prying eyes will not work. First you need to understand the methods that were used to hide them. After all, the choice of their display will depend on this. For example, these could be:
- displacement of cell borders;
- ungrouping of cells;
- turning off the filter;
- formatting certain cells.
Let us consider each of them in more detail.
Method 1: Shift Cell Borders
If the method of physically shifting the boundaries of a column or line was used to hide cells, then to display it is enough to return the boundaries to their original position using a computer mouse. But you should carefully control every movement of the cursor. And in the case of a large number of hidden cells, their display can take quite a long time. But even this task can be done in a matter of seconds:
- It is necessary to select two adjacent cells, and there must be a hidden cell between the cells. Then in the “Cells” toolbox in the “Home” menu we find the “Format” parameter.
- When you activate this button in the pop-up menu, go to the “Hide or show” category. Next, select one of the functions – “Display Rows” or “Display Columns”. The choice depends on which cells are hidden. At this point, the hidden cells will be displayed immediately.
Advice! In fact, this rather simple method can be further simplified, and most importantly, accelerated. To begin with, we select not only adjacent cells, but adjacent rows or columns, between which By clicking on the right button of the computer mouse, a pop-up menu will appear, in which we select the “Show” parameter. Hidden cells will appear in their place and will be editable.
These two methods will help to reveal and display hidden data only in case of manual hiding of cells in the Excel spreadsheet.
Method 2: Ungroup Cells
An Excel tool called grouping allows you to hide a specific area of cells by grouping them together. Hidden data can be shown and hidden again using special keyboard shortcuts.
- First, we check the Excel sheet for hidden information cells. If there are any, then a plus sign will appear to the left of the line or above the column. When you click on the “+” all grouped cells will open.
- You can reveal hidden areas of a file in another way. In the same area where the “+” is, there are also numbers. Here you should select the maximum value. The cells will be displayed when you click on the number with the left mouse button.
- In addition to temporary measures for displaying cells, grouping can be turned off completely. We select a specific group of rows or columns. Next, in the tab called “Data” in the “Structure” tool block, select the “Ungroup” category.
- To quickly remove a grouping, use the keyboard shortcut Alt+Shift+Left Arrow.
Method 3: Turn off the filter
One powerful way to find and organize large amounts of information is to filter table values. It should be borne in mind that when using this method, some columns in the file table go into hidden mode. Let’s get acquainted with the display of hidden cells in this way step by step:
- Select a column filtered by a specific parameter. If the filter is active, then it is indicated by a funnel label, which is located next to the arrow in the top cell of the column.
- When you click on the “funnel” of the filter, a window with available filter settings will appear. To display hidden data, tick each value or you can activate the “Select All” option. Click “OK” to complete all settings.
- When filtering is cancelled, all hidden areas in the Excel spreadsheet will be displayed.
Pay attention! If filtering will no longer be used, then go to the “Sort and filter” section in the “Data” menu and click “Filter”, deactivating the function.
Method 4: Cell Formatting
In some cases, you want to hide values in individual cells. To do this, Excel provides a special formatting function. When using this method, the value in the cell is displayed in the format “;;;”, that is, three semicolons. How to identify such cells and then make them available for viewing, that is, display their values?
- In an Excel file, cells with hidden values appear as blank. But if you transfer the cell to the active mode, then the data written in it will be displayed in the function line.
- To make hidden values in cells available, select the desired area and press the right mouse button. In the pop-up menu window, select the line “Format Cells …”.
- The Excel cell formatting settings will appear in the window. In the “Number” tab, in the left column “Number formats”, go to the “(all formats)” category, all available types will appear on the right, including “;;;”.
- Sometimes the cell format can be chosen incorrectly – this leads to incorrect display of values. To eliminate this error, try choosing the “General” format. If you know exactly what value is contained in the cell – text, date, number – then it is better to choose the appropriate format.
- After changing the cell format, the values in the selected columns and rows became readable. But in case of repeated incorrect display, you should experiment with different formats – one of them will definitely work.
There are some pretty useful videos that will help you figure out how to hide cells in an Excel file and show them.
So, in order to learn how to hide cells, we recommend watching the video below, where the author of the video clearly shows several ways to hide some rows or columns, as well as the information in them:
We also recommend that you familiarize yourself with other materials on the topic:
After carefully watching just a few videos on this topic, any user will be able to cope with such a task as showing or hiding a cell with information in Excel tables.
Conclusion
If you need to display hidden cells, you should determine by what method the columns and rows were hidden. Depending on the selected method of hiding cells, a decision will be made on how they are displayed. So, if the cells were hidden by closing the borders, then no matter how the user tries to open them using the Ungroup or Filter tool, the document will not be restored.
If the document was created by one user, and another is forced to edit, then you will have to try several methods until all columns, rows and individual cells with all the necessary information are revealed.