Thursday, November 15, 2012

Hide and Unhide in Excel

Okay this will sound like a really basic thing, but it is often overlooked by most Excel users. Hide and Unhide. Usually the write off is that it is so easy to unhide, why hide in the first place? The point being about hide and unhide, is presentation. Excel is almost like the Wizard of the Emerald Palace. There can be a lot going on, but it's just subterfuge, never mind the man behind the curtain. Likewise, Excel isn't a programming environment in which to write programs that you don't want decompiled. Excel is about presenting information and that's where you should put it first and foremost. So hide and unhide are just functions of presentation. Somethings you don't want people to readily see. You don't want to hide them from existence, just so they don't detract from what you are trying to show. So now that I've beat the gong about hide and unhide, what's it good for? Hiding stuff is useful when you want in-between calculations to be preformed but only want to show the final calculation. For an example, calculating the check digit for Universal Product Codes is not a huge task, but it can be easy to break the steps up into smaller formulas, as opposed to one giant formula. Breaking it up into smaller formulas helps end users understand what exactly is going on here. However, some users are not really worried about the details, or some power users know all the steps and do not care to be bugged with all the in between steps. Hiding those in between steps can be really useful for either of these groups. So let's take a look at how one goes about hiding and unhiding stuff in Excel. Let's take a look at a basic Excel sheet.
Now obviously, this type of sheet you would want all the information displayed, but for example sake let's say that someone wants the OSHA Fine row hidden. To do so let's click on the number seven row marker at the far right to highlight the entire row.
Now let's head over to the Ribbon and use the Home tab. Click on the Format button in the Cells group and a drop down menu appears. You will see a section called Visibility and a menu item called Hide & Unhide. Clicking on that brings up a sub-menu, you should see a Hide Rows, clicking on it hides the rows that have been highlighted, namely row seven. Also note that hovering over the sub-menu item shows that the shortcut key is CTRL+9. I do not know who thought that was an awesome shortcut key but hey it's worth remembering just in case you want to hide without the whole menu clicking thing, which is what I'm big on. Screenshot to help you visualize it all.
The same can be done for columns. Unhide allows you to undo the hide operation. The important thing to note here is that hidden things are still valid for calculations, so you can still calculate values on hidden cells, just not see them. That's what make the Hide and Unhide a presentation thing. It only changes what people see, not how things are calculated. For reference the shortcut keys are:
Hide rowCTRL+9
Hide columnCTRL+0
Unhide rowCTRL+SHIFT+9
Unhide columnCTRL+SHIFT+0

No comments: