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.

CHECKLIST

Boost your LinkedIn profile

LinkedIn is No. 1 when it comes to business-related social media. Be prepared when an unexpected career opportunity arises by following these tips for keeping your LinkedIn page current.

PRACTICE MANAGEMENT

Millennial women: How to woo a new generation of employees

Ambitious, educated, and tech-savvy, Millennial women make ideal employees. Win them over with flexibility and clear paths to advancement.

PROFESSIONAL ISSUES

Belicia Cespedes: A CPA at 17

Through hard work and determination, Belicia Cespedes earned the credential before she was even eligible to vote.