Hiding cells in Excel—just one or two or even 100 at a time

BY STANLEY ZAROWIN

HIDING CELLS IN EXCEL—JUST ONE OR TWO OR EVEN 100 AT A TIME
I often deal with very large worksheets with a massive ocean of data-filled cells. In an effort to make the worksheet easier to view, I try to hide cells that are not essential. While it’s easy to hide whole columns and rows—and even a whole page—I can’t figure out how to hide just a few or maybe even some that are not contiguous in a row or column. Mind you, although I want them visually out of the way, I want the data in the hidden cells to continue to calculate as if they were not hidden. Any suggestions?

You’re right, Excel does not excel when it comes to hiding individual cells, and Excel 2007 is no better than 2003. While there are several ways to do it, all require multiple steps and are not flexible. The major tool to use is Format. It’s good at hiding whole columns and/or rows—and, yes, even a whole page—but when you want to hide individual cells or groups of cells, you have to go through some gymnastics.

Here, for example, are your limited choices when you evoke Format in Excel 2003…


…and this in Excel 2007 (Format is in the Home ribbon).


Here is how to get around that whole-column or whole-row obstacle in both Excel 2003 and 2007: Highlight the cells you want to hide; to highlight noncontiguous cells, hold down the Ctrl key as you highlight them in sequence. When finished, press Ctrl+1 to evoke the Format Cells screen, click on the Number tab, go down to Custom and in the Type box select three semicolons (;;;) and then OK and, presto, the cells are hidden (see screenshot below).

As you can see in the screenshots below, even when I hide two cells (56.99 and 2,233.00), the Total remains the same.

A bonus: If you’re seeking a hiding-cells shortcut, here’s an unpublished tip for quickly hiding whole rows and columns without taking your fingers off the keyboard (except to select the cells).

The sequence: For rows, highlight the cell (or cells) in the row (rows) you want to hide, and press Ctrl+9. To reverse the process, press Ctrl+Shift+(. You’ll notice that the open parenthesis and the number 9 share a key, so holding down Shift evokes the open parenthesis. For columns, it’s the same process, only use the 0 (zero) key and the close parenthesis.

SPONSORED REPORT

Questions to ask before committing to the cloud

Cloud computing has its pros and cons. In this report, we answer common questions CPAs may have as they consider transitioning partially or fully to the cloud.

QUIZ

News quiz: IRS reopens an online service, but criticism endures

The IRS brings back the Get Transcript Online service, but the agency faces criticism for its handling of the aftermath of the event that led to the shutdown of the service. See how much you know about other recent news with this quiz.

CHECKLIST

Auditing risks in culture

Cultural flaws can seriously damage an organization. Here’s how internal auditors can reduce risks by embedding culture audits into existing audit programs.