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

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.