16

I have a spreadsheet I frequently use , where I always want to hide the D:CE columns . And since that's over 40 columns, I was wondering if there was a keyboard shortcut or command to hide this more quickly than the old shift + ctrl + click

thanks for any tips/guidance

30

If you know the columns you want to hide, you can simply type the address of the range in the Name Box and press Enter. Once it's selected, you can then right click the a column header in the selected range and choose Hide.

This is the Name Box.
enter image description here

Type D:CE in the Name Box and press Enter to select that range. Then right click a column header in the selected range and choose Hide.

enter image description here

| improve this answer | |
  • 1
    Is there a keyboard shortcut to get the focus into the Name box? Using such, typing the columns, and using Shift+F10 (or dedicated right-click menu key on a suitable keyboard) would provide an entirely mouse-free way of doing this – AakashM Dec 18 '19 at 9:11
  • 5
    @AakashM I think F5 gets you to an equivalent dialog. – Neil Dec 18 '19 at 9:41
  • This answer is inferior to the Groups suggestion below, because (a) hidden columns have fewer visual cues indicating their existence; and (b) this is a repetitive task the OP is doing. – Grade 'Eh' Bacon Dec 18 '19 at 15:29
  • 4
    No need to right-click and navigate the menu to hide columns -- Ctrl+0 is the keyboard shortcut. – Kevin Troy Dec 18 '19 at 17:29
  • 1
    @KevinTroy And, you don't even have to select the entire column for that, just 1 cell in it. (for completeness Ctrl+9 is to hide Rows) – Chronocidal Dec 19 '19 at 9:13
39

Try the following:

Ctrl + G (open the Go To menu)

D:CE then Enter (select your range)

Ctrl + 0 (hide the selected columns)

| improve this answer | |
  • For me, the Ctrl+0 did not do anything, but at that point I opened context menu by pressing AppsKey (or Shift+F10 if the former one is not on your keyboard) and then H for the Hide menu item. – miroxlav Dec 19 '19 at 12:52
19

If this is just 1 specific spreadsheet, then you could select the columns (once), then go to "Data" > "Group".

Once grouped, there will be a line (showing which columns are grouped) with a - which you can click to hide the entire group. Once hidden, the button will change to +, which will unhide the entire group.

There will also be 1 (hide all groups) and 2 (unhide 1st-level groups) buttons. You can even have groups within groups, which will create 2nd or 3rd level groups (adding 3 and 4 to the control scheme)

Image showing columns G:N and S:X as open first-level groups, and J:K as a closed second-level group

Once you save the file, groups will remain intact if you reopen it or send it to a colleague.

Shift + Alt + is the shortcut to Group selected Rows / Columns, Shift + Alt + to Ungroup

If you have a cell in a Group selected, then Alt, A, H will hide it, and Alt, A, J will unhide it. Alt, A, U, C will remove all Outline Groups

| improve this answer | |
  • This is the better answer - if it is a frequent task to hide and unhide columns, Groups should be used instead. Further, if you group rows and columns, it is more obvious to the reviewer (including you!) that there is hidden information. It is easier to overlook that columns are directly hidden, because there are fewer visual cues. – Grade 'Eh' Bacon Dec 18 '19 at 15:27
  • Honestly most people don't even know about this feature. I discovered it couple of weeks ago and people's eye bugged out when I used it. It's even on Google Sheets. – Nelson Dec 19 '19 at 1:01
  • 3
    I am seen as a excel 'expert' in my organisation but I wasn't aware of this feature so +1 just for teaching me a new trick :) However; this is only really usable in this situation if it is the 'same' file the op is working on each time. If the file is actually report output from another application then this would have to be set up each time which negates the usefulness of it. – EdHunter Dec 19 '19 at 7:38
  • @EdHunter Agreed, hence the first line - but that is largely for the benefit of people searching for this in future, as OP says "I have a spreadsheet [that] I frequently use". It's a surprisingly underutilised/overlooked feature, considering that it's been available for over 20 years! – Chronocidal Dec 19 '19 at 9:19
1

Another option you could use would be to have a Macro which hides/unhides the columns and assign this to a key combination of your choice. - The below Macro will hide columns D:CE if they are visible and make them visible if they are hidden. Assign this to something like Ctrl+Shift+Z And you can hide/unhide the columns with ease.

Sub column_hide()

If ActiveSheet.Columns("D:CE").EntireColumn.Hidden Then
    ActiveSheet.Columns("D:CE").EntireColumn.Hidden = False
Else
    ActiveSheet.Columns("D:CE").EntireColumn.Hidden = True
End If

End Sub
| improve this answer | |
  • One-liner: With ActiveSheet.Columns("D:CE").EntireColumn: .Hidden = Not .Hidden: End With, or alternatively ActiveSheet.Columns("D:CE").EntireColumn.Hidden = Not ActiveSheet.Columns("D:CE").EntireColumn.Hidden – Kai Dec 20 '19 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.