Hidden Columns Excel

[box style=”1″ close_btn=”true”]

When it comes to techie tips, I’m a junkie.  Hopefully there is something new for everyone in this weekly series or at least one special “Ah ha, so that is how you do it” when it for Outlook, Excel, Word, Acrobat, etc.

Special thanks to Google, my colleagues, and the exasperated IT guys over the years who have all contributed to expanding my geeky knowledgebase.

[/box]

Who Would Hide Perfectly Good Data?

Column A and B are both hidden.  Notice that Column G is also hidden.

Hopefully you are already aware that you can hide columns and rows in Excel spreadsheets.  This technique is great for stowing away formulas you don’t want anyone to accidentally edit/overwrite in a shared workbook or perhaps to make a spreadsheet skinny enough for printing.  I also like to hide columns in my report where every row contains the same value since like data aren’t as helpful for filtering or reviewing.  There are other reasons the hide function is helpful, what have you used it for?

You can see in the example to the right that Column A and B are both hidden. Notice that Column G is also hidden. Look between F and H in the gray bar above the data entry cells of the worksheet and you will see the divider line between the columns in the gray bar is slightly darker indicating another hidden column.

Hide/Unhide How-To

hiding data column where every row value is the sameYou can execute the hide command when you have a column or row selected by just right-clicking; it is there at the bottom of the pop-up menu.

The column or row does have to be selected first or else you won’t see the option in the right-click menu.  Quickly select a column or row by stepping on the gray letter above the worksheet column or the row number to the left of the worksheet row.

format cells ribbonformat cells visibility hide row columnThe hide function is also available on the ribbon
( Home | Cells | Format | Hide & Unhide )

For multiple rows or columns, assuming the rows and columns you want to hide are not actually next to each other, Just use the ‘CTRL’ key while you click and select each one.

If you are hiding a continuous chunk or group of columns/rows, you can use the ‘SHIFT’ key plus directional arrows or just select one row/column and drag the pointer to highlight the range then use the right-click option menu to hide or unhide.

When the First Column in a Spreadsheet is Hidden use ‘CTRL’+’G’

if Column A is hidden and you select Column B and choose Unhide in most instances, nothing happens.  Column A remains hidden.  The problem is that you didn’t select A (it was hidden!)  The solution for this is to select multiple columns including A and then execute the Unhide command.

For one method, you just select a visible column (just click on the column header letter) and then drag the mouse left while holding down the left-click button all the way to the left-edge of the view; which is tricky at best.

The whole reason I wanted to post this tip today is because I find it difficult to use the tiny little trackpad on my laptop to click and drag and coordinate to unhide the first column in a spreadsheet.  This always seems to make me fumble and is frustrating to solve.  If you have ever struggled with this, hopefully you can relate and my keyboard shortcut approach will be a timesaver for you in the future.

ctrl g select a to z

An alternative method: If you click ‘CTRL’+’G’ you can quickly highlight (go to) any reference in the workbook.  I typed in “A:Z” which commands Excel to select the full range of columns A through G.

Now when I go back to my spreadsheet the active range will show as selected and when I right-click and choose Unhide, Column A will finally reveal itself.

You can also try “CTRL+A” to select the whole spreadsheet but this method isn’t totally fail-safe for me so I just do ‘CTRL’+’G’ right away and then right-click->Unhide to force display of those pesky hidden leading columns.

More Computer Timesavers Again Next Tuesday

If you have questions, comments, or other topic ideas please leave a comment or contact me via email.

Tuesday Timesavers Archives:

Follow the Clinical Operations Toolkit on Facebook

About The Author

Nadia

Nadia Bracken, lead contributor to the Lead CRA blog and the ClinOps Toolkit blog, is a Clinical Program Manager in the San Francisco Bay Area.

Leave A Response