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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.