If you usually hide columns in Excel as you work, needing access to the most recent one after you’ve moved on can be very frustrating. Here’s how to do it easily.
Try the sample spreadsheet
I have a cash forecast sheet with a column for every week. Each week, I hide the previous week’s column. Is there an easy way to unhide just the column to the left of the current week without having to unhide all the columns?
When you progressively hide columns, it can be frustrating to have to unhide a large number of columns just to see the last one hidden – you then have to hide all the columns again to return to the original state.
Figure 1 has an example of the type of structure causing the frustration. Columns B to J are hidden and we need to unhide column J only.
There are two ways to unhide a single column when a number of columns are hidden: using the Name Box or a macro.
Name Box solution
You can select a cell within a hidden column by using the Name Box.
The Name Box is on the left of the Formula Bar and above the column letters (see Figure 2).
The Name Box usually displays the cell reference of the active cell in the sheet.
Even if you select a range, one cell will always be the active cell.
You can type a cell reference (J1) from the hidden column in the Name Box and press Enter to select that cell (Figure 3
). After you press Enter, the cell reference will be centred within the Name Box.
The heavy black vertical line at the bottom of Figure 3 is the selected, hidden cell in row 1.
Click the Format dropdown on the right of the Home Ribbon tab, click the Hide & Unhide option then select Unhide Columns as shown in Figure 4.
If your system is still running Windows XP, you can use a keyboard shortcut – Ctrl + Shift + ) to unhide the selected column.
This shortcut doesn’t work in Windows 7.
You can right click the Unhide Columns option in Figure 4 to add it as an icon to the Quick Access Toolbar. You can then use a single click to unhide the selected column.
The macro solution shown in Figure 5 allows you to click the cell to the right of the column you want to unhide, then run the macro that will unhide the column. In our example, you could select any cell in column K and run the macro to unhide column J.
The ActiveCell object adds flexibility to the macro as it works with any cell you select before running the macro. ActiveCell provides the base from which you can then refer to other cells by using the Offset command.
The Offset command is similar to Excel’s OFFSET function. In the example in Figure 5, the Offset command selects the cell on the same row (0) and the column on the left (-1) of the active cell.
The first number within the Offset brackets adjusts the row. In our example, the 0 means the row reference doesn’t change. If the first number were 2, it would select a cell two rows below the active cell. Positive numbers move the row reference down the sheet.
Negative numbers move the row reference up the sheet. So -3 would select a cell three rows above the active cell.
The second number in the Offset brackets changes the column reference. The -1 value instructs Excel to move the column reference one column to the left of the active cell.
Negative numbers move the column reference to the left. If we entered 4 in the second number it would refer to a cell four columns to the right of the active cell. Positive numbers move the column reference to the right.
The EntireColumn command selects the whole column of the cell selected by the Offset row and column combination. Setting the Hidden property to FALSE unhides that column. This macro would generate an error if you selected a cell in column A because there is no column to the left.
Three other macros are shown in Figure 6
. They show different unhide options using the Offset method.
The first macro uses the Offset method to refer to the column to the right of the active cell and to unhide it. The second macro unhides the row above the active cell and the last macro unhides the row below the active cell.
Using the ActiveCell object provides flexibility to users by allowing them to select any cell and run a macro based on that cell selection. The macro could operate on the cell itself or use the Offset method to work on surrounding cells.
Running a macro will clear the undo list. This means that you cannot undo a macro and that you cannot undo anything you did before running the macro. In many cases, it is advisable to save the Excel file before trying new macros.
As well as typing a cell reference in the Name Box, you can also type a range reference – for example, A1:C1000 – and press Enter to select that range quickly. This is useful for selecting large ranges.
A little-known feature of the Name Box is the ability to type certain functions into it, so that you can see what range the function returns. This can be helpful when learning how a function works.
As an example, try typing OFFSET(A1,1,2,3,4), press Enter and see what range Excel selects. INDEX and OFFSET are powerful functions that can return range references for use by other functions.
Neale Blackwood CPA is CPA Australia’s resident Excel expert. He runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected] or a4accounting.
This article is from the March 2014 issue of INTHEBLACK magazine.