Excel Tips

Tips from the Accounting Trenches: Try This Smoother Alternative to “Hiding” Data in Excel

When manipulating data in Excel, here’s a problem that I’m guessing many of us have experienced at one point or another: you have data that you’d like to keep for reference in an Excel sheet — but you don’t necessarily need to see all that data every time. This includes having rows/columns of additional subcategories or details, or specific formulas for reference, that you’d like to suppress for viewing purposes. 

For example, the following sheet shows extraneous data (i.e., specific states/regions) that I’d like to keep for reference but not have open at all times:

For many Excel users, the first tendency might be to “hide” the rows. Using this common method, you would simply highlight the rows you want hidden, as shown below, then right click and select the “hide” option.

In the next example, we see the end result of this action. (Note how the per-state detail from the East is hidden.)

While the “hiding” function serves its purpose in certain instances, a function that is much cleaner and useful, in my opinion, is the Group function. This feature lets viewers know that there are hidden rows/columns that can be expanded – and presents it in a much cleaner way. Additionally, viewers have a more fluid process for choosing when they’d like to see additional details.

Here’s how to do it: Simply select the rows you would like to hide -> Choose Data from the Top Nav -> Click on the Group function.

As shown in the example below, you’ll notice that there is a “+” function on row 12, alerting users that there are hidden cells that can be easily opened (and then closed again).

This is just a quick everyday tip that I hope you find useful! If you’re interested in more Excel solutions, also check out the following:

Would you like to leverage more advanced capabilities in Excel that can make an immediate impact on your organization? Keep in mind that 8020 Consulting is here to help. Contact us for more information. 

About the Author

Ido has over 15 years of experience in financial planning and analysis, M&A and project management. He has managed budgeting, forecasting, long-range planning, industry analytics, metric analysis, complex modeling, consolidations, standard reporting, due diligence support, ad hoc analysis and senior management decision support. Ido has held key finance and strategic roles in the Banking, Entertainment, Online and SAAS industries for companies such as Move, THQ, Intuit, Blackline and Velocify. He holds a Bachelor of Science degree in Business Administration. In addition, he received his Master of Business Administration with an emphasis in Finance from Cal State Northridge.

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Project Management

Notes from the Field: 5 Success Factors for Cross-Functional Project Management

Finance teams are often looked to for leadership of cross-functional projects. This is often the case because the ultimate project results can be measured by, or affect, financials. A budget or re-forecast process is a clear example of a cross-functional project led by Finance. From our experience in leading cross-functional projects as part of Finance… View Article

January 13, 2021Marco Moreno

Treasury and Cash Management

A 2020 Recap: Cash Management & Why Cash is Still King

Last year brought disruptions in almost every industry. We witnessed significant interruptions in order fulfillment and delivery systems, chaotic drops and soaring demand in certain categories, fresh liquidity for borrowers and cash crunches for others. And cash management underwent rapid change for those suddenly with too much or too little. Short-Term Cash Management Led to Underutilized… View Article

January 7, 2021Danelle Larsen

Financial Planning and Analysis

The End of LIBOR: Are You Ready?

With all of the happenings at year‐end, thinking about how your company structures its borrowings and contracts in the coming year is probably not at the forefront of discussions. But it should be in relatively short order, given that the end of LIBOR is nigh – and especially if the term SOFR is new to… View Article

December 17, 2020Danelle Larsen

See All