While doing data analysis in excel you would have noticed that all the data is not important for the end user (can be your boss).
Some of the columns are used merely for calculation purpose and that’s why it’s better to hide these columns as they create un-necessary confusions.
But now when it comes to unhide these columns, there are few complications. In this post I will show you a few ways by which you can easily unhide your hidden columns in Excel.
So here we go:
Un-hiding Columns in Excel the Easy Way
This is the easiest way to unhide columns in excel. Follow the below steps to use this method:
- First of all select one column on either side of the hidden column.
- Now, with all these columns selected right-click on the column header.
- This will open a popup menu, select the ‘unhide’ option and the hidden columns will be visible.
Unhide Columns in Excel Using the Shortcut
Actually, Microsoft Excel has provided certain shortcut keys for hiding rows and columns in your spreadsheets. These shortcut keys are in the below table:
| Shortcut Key | Description |
|---|---|
| CTRL+9 | Hides the selected rows. |
| CTRL+0 | Hides the selected columns. |
| CTRL+SHIFT+9 | Unhides the hidden rows within the selection. |
| CTRL+SHIFT+0 | Unhides the hidden columns within the selection. |
The procedure of using these shortcut keys is quite simple. First, select one column on either side of the hidden column and then press the shortcut key.
Unhide Column or Rows shortcut keys not working?
Many Windows 8 and Windows 10 users have reported that the shortcut key for un-hiding the hidden columns doesn’t work on their systems.
Let’s understand why this happens. Microsoft Windows 8 and Windows 10 operating systems have assigned the ‘CTRL+SHIFT’ keys for Regional/ Language settings for changing the keyboard layouts. This functionality probably assigns numbers to different layouts.
So, the root of this problem is that whenever you press the column un-hiding shortcut key your OS gulps the keys and hence it interferes with the functionality of Microsoft Excel.
How to resolve this issue
The resolution of this issue is pretty obvious and that is changing the Keyboard Layout shortcut. Follow the below steps:
- First open the ‘Control Panel’, navigate to the ‘Change keyboards or other input methods’ link.
- Now a ‘Region and Language’ dialog box opens, select the ‘Keyboard and Language’ Tab and click on the ‘Change Keyboard’ button.
- Next, a new window ‘Text Services and Input Languages’ opens, here select the ‘Advance Key Settings’ tab and then click the ‘Change Key Sequence’ button.
- Under ‘Switch Keyboard Layout’, set the radio button to Not Assigned (or one of the other options, if you need this feature to be available).
- Click the ‘Ok’ button and now the unhide column shortcut key will start working in Excel.
This resolution is suggested in the Microsoft Knowledge Base article.
Unhide Columns or Rows in Excel using a VBA script
If you want to unhide your spreadsheet columns by using a code then you can use the following macro for this task:
Sub Unhide_AllColumns()
ActiveSheet.Cells.EntireColumn.Hidden = False
End Sub
This small macro will unhide all the hidden columns in your active worksheet. If you also want this macro to unhide all the rows as well then simply add a line:
ActiveSheet.Cells.EntireRow.Hidden = False
So, these are some of the ways to Unhide Columns in Excel. Do share if you know any other ways to do the same.
