Contents
Formulation of the problem
Suppose we have such a table that we have to “dance” with every day:
To whom the table seems small – mentally multiply it twenty times by area, adding a couple more blocks and two dozen large cities.
The task is to temporarily remove from the screen rows and columns that are currently unnecessary for work, i.e.,
- hide details by month, leaving only quarters
- hide totals by months and quarters, leaving only the total for half a year
- hide cities that are unnecessary at the moment (I work in Moscow – why should I see St. Petersburg?), etc.
In real life, there are a sea of examples of such tables.
Method 1: Hiding rows and columns
The method, frankly, is primitive and not very convenient, but two words can be said about it. Any previously selected rows or columns on a sheet can be hidden by right-clicking the column or row header and selecting the command from the context menu Hide (Hide):
For reverse display, select adjacent rows / columns and, by right-clicking, select from the menu, respectively, display (Unhide).
The problem is that you have to deal with each column and row individually, which is inconvenient.
Method 2. Grouping
If you select multiple rows or columns and then select from the menu Data – Group and Structure – Group (Data — Group and Outline — Group), then they will be enclosed in a square bracket (grouped). Moreover, groups can be nested one into another (up to 8 nesting levels are allowed):
A more convenient and faster way is to use a keyboard shortcut to group pre-selected rows or columns. Alt+Shift+Right Arrow, and for ungrouping Alt+Shift+Left Arrow, respectively.
This method of hiding unnecessary data is much more convenient – you can either click on the button with the “+” or “—“, or on the buttons with a numerical grouping level in the upper left corner of the sheet – then all groups of the desired level will be collapsed or expanded at once.
Also, if your table contains summary rows or columns with the function of summing neighboring cells, that is, a chance (not 100% true) that Excel he will create all the necessary groups in the table with one movement – through the menu Data – Group and Structure – Create Structure (Data — Group and Outline — Create Outline). Unfortunately, such a function works very unpredictably and sometimes does complete nonsense on complex tables. But you can try.
In Excel 2007 and newer, all these joys are on the tab Data (Date) in group Structure (Outline):
Method 3. Hiding marked rows/columns with a macro
This method is perhaps the most versatile. Let’s add an empty row and an empty column to the beginning of our sheet and mark with any icon those rows and columns that we want to hide:
Now let’s open the Visual Basic Editor (ALT + F11), insert a new empty module into our book (menu Insert – Module) and copy the text of two simple macros there:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False 'Disable screen updating to speed up For Each cell In ActiveSheet.UsedRange.Rows(1).Cells 'Iterate over all cells in the first row If cell.Value = "x" Then cell .EntireColumn.Hidden = True 'if in cell x - hide column Next For Each cell In ActiveSheet.UsedRange.Columns(1).Cells 'go through all cells of the first column If cell.Value = "x" Then cell.EntireRow.Hidden = True 'if in cell x - hide the row Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'cancel all hiding rows and columns Rows.Hidden = False End Sub
As you might guess, the macro Hide hides and the macro Show – Displays back labeled rows and columns. If desired, macros can be assigned hotkeys (Alt + F8 and button Parameters), or create buttons directly on the sheet to launch them from the tab Developer – Insert – Button (Developer — Insert — Button).
Method 4. Hiding rows/columns with a given color
Let’s say that in the above example, we, on the contrary, want to hide the totals, i.e. purple and black rows and yellow and green columns. Then our previous macro will have to be slightly modified by adding, instead of checking for the presence of “x”, a check for matching the fill color with randomly selected sample cells:
Sub HideByColor() Dim cell As Range Application.ScreenUpdating = False For Each cell In ActiveSheet.UsedRange.Rows(2).Cells If cell.Interior.Color = Range("F2").Interior.Color Then cell.EntireColumn.Hidden = True If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True Next For Each cell In ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Range("D6").Interior.Color Then cell.EntireRow.Hidden = True If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub However, we must not forget about one caveat: this macro only works if the cells of the source table were filled with color manually, and not using conditional formatting (this is a limitation of the Interior.Color property). So, for example, if you automatically highlighted all deals in your table where the number is less than 10 using conditional formatting:
… and you want to hide them in one motion, then the previous macro will have to be “finished”. If you have Excel 2010-2013, then you can get out using instead of the property Interior property DisplayFormat.Interior, which outputs the color of the cell, regardless of how it was set. The macro to hide the blue lines might then look like this:
Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = False For Each cell In ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Then cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Cell G2 is taken as a sample for color comparison. Unfortunately the property DisplayFormat appeared in Excel only starting from the 2010 version, so if you have Excel 2007 or older, you will have to come up with other ways.
- What is a macro, where to insert macro code, how to use them
- Automatic grouping in multilevel lists