How to Unhide Columns in Excel?

Affiliate disclosure: In full transparency – some of the links on our website are affiliate links, if you use them to make a purchase we will earn a commission at no additional cost for you (none whatsoever!).

Microsoft Excel has been gaining popularity and is pervasive in almost every business today. It is a perfect tool for business analytics. If sources are to be believed, then 98% of the organizations use it daily for applications ranging from petty cash reconciliations to more complicated aspects of preparing management reports and budgets.

Microsoft Excel is, basically, equipped with so many fundamental and advanced features that most of the people, barely know from where to start. One such feature is How to Add columns in excel. But you are here to unhide a column in excel. So here it is:

Hide and Unhide Columns

Most of the people have reported that they experience a lot of complications when it comes to unhide columns in excel. In order to avoid unnecessary confusions, it is better to hide these columns. But sometimes, these columns need to be viewed.

Let’s have a look at the process of how to unhide all columns in excel:

  1. First of all select one column on either side of the hidden column.
  2. Now, just right-click on the column header, with all these columns selected.
  3. A popup menu appears on the screen
  4. Select the ‘unhide’ option and the hidden columns will be visible.

This is by far, the easiest way via excel unhide all the columns.

How-to-unhide-columns-in-excel

How to Unhide Columns in Excel Using the Shortcut Way

There are certain shortcut keys imbibed in your spreadsheets for hiding rows and columns. Let’s have a look at some of them:

Shortcut Key Details
Shift+ Space It is used to select an entire row.
CTRL+9 Hide the selected rows.
CTRL+0 Hide the selected columns.
Ctrl+ Space It is used to select an entire column.
Ctrl+ Shift+= (equal sign) It is used to insert rows or columns.
CTRL+SHIFT+9 Unhide the hidden rows within the selection.
CTRL+SHIFT+0 Unhide the hidden columns within the selection
ALT, O, R, E It opens the Row Height window.
Alt,O,C,W It opens the Column Width window.

 

These keyboard shortcuts are, basically, used while working with rows and columns in Excel. All these shortcuts are not only convenient but even helps to save a lot of time. There are tons of shortcuts available to make your task easier.

However, in order to make these shortcut functions, you need to make some changes in the settings. Follow the following steps for the different version of windows:

Windows 8

  • Click on Language within the Control Panel.
  • Click Advanced Settings, and then click the Change Language Bar Hot Keys link.
  • Click Change Key Sequence
  • Under ‘Switch Keyboard Layout’, set the radio button to Not Assigned
  • Click the ‘Ok’ button and now the unhide column shortcut key will start working in Excel.

Windows 7

  • Click Region and Language within the Control Panel.
  • Choose the Keyboards and Languages tab.
    How-to-unhide-columns-in-excel
  • Then click Change Keyboards.
     unhide-columns-in-excel
  • Click the Advanced Key Settings tab, and then click Change Key Sequence.
     unhide-columns-in-excel
  • Select Not Assigned in the Switch Keyboard Layout section
    How-to-unhide-columns-in-excel
  • Click OK as needed.

Thus, in order to synchronize the keyboard shortcuts with the functionality of Microsoft Excel, you need to change the Keyboard Layout shortcut. As far as Windows XP is concerned, no Control Panel changes are necessary.

Unhide the columns selectively

If you have an Excel table where multiple columns are hidden and want to unhide a few of them only, just follow these steps:

  1. Navigate to the location in your table where the columns are hidden.
  2. Now, select the columns to the left and right of the column you want to unhide.
  3. Let’s say, if you want to display column B, select columns A and C.
  4. Go to the Home tab in Excel.
  5. Click on the Format icon -> Hide & Unhide -> Unhide columns.
  6. And you are done.

You can even carry out this process by using Go To and Format options. Let’s have a glance at how to do this via Go To option:

  1. Go to Home -> Find & Select -> Go To
    how-to-unhide-all-columns-in-excel
  2. You will see the Go To dialog box. Enter C1 in the Reference field.
     how-to-unhide-all-columns-in-excel
  3. Click OK.
  4. Although it won’t be visible to you, cell C1 will be selected.
  5. Now, go to Home -> Format -> Hide & Unhide -> Unhide Columns.
    how-to-unhide-cells-in-excel
  6. And you are done.

Unhide Columns in Excel using a VBA Solution

It is actually difficult to hide entire columns of data based on the value of a particular cell. Now, in case you want to unhide your spreadsheet columns by using a code then you can use the following method for how to unhide cells in excel.

First of all, let’s see the code-

  • Sub Unhide_All Columns()
  • Cells.EntireColumn.Hidden = False
  • End Sub

Now, follow this process:

  • Open an excel workbook
    how-to-unhide-cells-in-excel
  • Press Alt+F11 to open VBA Editor
    unhide-rows-in-excel
  • Insert a Module for Insert Menu
    How-to-unhide-columns-in-excel
  • Copy the above code and Paste in the code window
    unhide-rows-in-excel
  • Save the file as macro enabled workbook
    unhide-rows-in-excel
  • Press F5 to run it.

In case, you also want this macro to unhide rows in excel as well then simply add a line:

ActiveSheet.Cells.EntireRow.Hidden = False  

There are some data in sheets which is important but not required to present. So, for that kind of data, can also hide columns in Excel.

How to Disable the Unhide Column Option

In case, the spreadsheet columns contain some confidential or important information, thus, in order to make sure that no one unhide the columns, use this process:

  1. In order to select the entire table, click on the  Select All button, which is at the intersection of row numbers and column letters.
    How-to-unhide-columns-in-excel
  2. Right-click on the highlighted list and pick the Format Cells.
    How-to-unhide-columns-in-excel
  3. On the Format Cells window, go to the Protection tab.
    How-to-unhide-columns-in-excel
  4. Now, unselect the Locked checkbox.
    How-to-unhide-columns-in-excel
  5. Click OK to save the changes.
  6. Now select the columns you want to protect from being unhidden.
    How-to-unhide-columns-in-excel
  7. Click on one of the highlighted columns and select the “Format Cells” option again.
    How-to-unhide-columns-in-excel
  8. When you see the Format Cells window, go to the Protection tab and tick the Locked checkbox.
  9. Hide the columns: select them and right-click above one of the columns and pick the Hide option from the menu.
    How-to-unhide-columns-in-excel
  10. Go to the Review tab and click on the Protect Sheet icon. Make sure that the checkboxes Select locked cells and Select unlocked cells are ticked. Do enter and re-enter the password.
    How-to-unhide-columns-in-excel
  11. And the Unhide option is disabled.

Conclusion

Excel comes with ample options to simplify the tasks. You can do a lot of calculative tasks within a seconds. You can find the average of big numbers in Excel within seconds. The possibility to hide columns in Excel is really helpful. Just follow these simple keystroke commands, listed above and the process of How to unhide columns in excel becomes a cake walk.

rishika

Leave a Comment