Excelling at Excel - Hidden cells and subtotals

Excelling at Excel - Hidden cells and subtotals

  • Comments 3
  • Likes

When you're working in complex Excel workbooks, you may feel like you're getting lost in the clutter. There is a way to hide what you don't need to see so you can focus on the data you're really working with.

To hide a column or row, right-click its letter or number and select Hide from the dropdown menu:

clip_image001

You can select multiples columns and rows at once; hold the Control key if you want to select any that are non-consecutive (Columns H and M plus Row 32, for instance). Restore hidden columns and rows by selecting the columns or rows around the missing ones, then right-click and choose Unhide.

Normally, hiding rows won't interfere with any formulas so your results will remain the same even if values are called from hidden cells. But say you WANT to exclude hidden cells from a sum. Use the subtotal function with function number 109 to add up only the visible cells, as in this example:

=SUBTOTAL(109,A1:A64)

If you check the function numbers for Subtotal (viewable using auto complete when you start typing the formula into a cell), all function numbers above 100 will limit the function to visible cells only:

clip_image002

It's a handy trick to pull out whenever you hide cells for a reason: not just to get them out of your way, but to exclude them.

Suzanne

Comments
  • thank you very much! Very useful :)

  • great tip, saved me a ton of time. thanks

  • I would like to do the same thing to my subtotals at the end of rows and ignore values in hidden columns instead, but the Subtotal function doesn't do that (explicitly says so in the excel help box). Is there another way to go about doing this??

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment