Excel – Don’t hide the truth and lose important information!

Filter By Topic

All

excel

“All my data’s gone! Who broke my Excel workbook?”

Even if it hasn’t happened directly to us, we have all heard those words before. How did this happen? what's the biggest culprit? : Hidden cells.

Never hide cells in Excel!

Yes, a bold statement indeed, but it’s for the best. Sure you want to hide all your "workings" - and only show that information/data which is indeed relevant. So how can you do this without Hidden Cells?

There are two options:

1. Use another sheet for your working

Have one sheet for your report that uses formula from one or many other pages.  This is a much cleaner way of ensuring no unwanted data is presented. In fact, you aren't hiding any cells at all. You’re also sharing how you calculated all your data.  That’s a good thing most of the time.

2. Group Cells

Before there was grouping, the only way you could suppress stuff you didn't want to present was hiding.  But we're not using Lotus 1-2-3 anymore people and I suspect grouping was invented because of the perils of hiding cells.  My personal favorite for suppressing where I need to is grouping cells. You’ll find the Group options under the Data tab.  I like to have all my workings out on one sheet with the title "working" on the tab for clarity. Using Grouping means I can hide cells, yet it is still clear that there is information on the sheet that is both useful and important to make the report work. People won't delete rows and columns without looking to see what’s there. It also gives the recipient of the report an ability to drill to see the detail - that's pretty cool too.

group-and-outline-rows-in-excel

So, before you use the Hide option in Excel again, try Grouping first.

Leave a Reply

Your email address will not be published.