Hide Table Details With No Macros

Last week, I was preparing reports for a client, and needed to find a quick and easy way to show or hide details in a long list. After a bit of thought, and experimenting, I found a way to do this – without macros.

You could use this technique on a table or pivot table, to make it easier to explore the data. You could also use this technique on an Excel dashboard, where real estate is in high demand!

The Really Wide Table

My client’s list had sales information, in a table with about 50 columns. He wanted to filter by product name, region or city, without seeing all the address details. Then, when the list was filtered down to just a few records, he wanted the option to open things up.

In the screen shot below, I created a smaller example table, with some fake data.

hidecolumnsgrouping01

Hiding the Details

My first thought was to create a couple of macros, to hide or show some columns. Buttons at the top of the list would run those macros.

Then I remembered Excel’s Grouping feature, and decided to try that. Usually we use grouping to show or hide details in a table with subtotal rows and columns. But grouping can also work nicely in other situations, like this wide table.

To hide the street and postal code details:

  • Select the Street and Postal Code columns
  • On the Ribbon’s Data tab, click Group

A grey bar will appear above the Excel sheet, with a line and button over the grouped columns.

  • Click the – button to hide the columns
  • When it changes to a + button, click that to show the columns

hidecolumnsgrouping02

Group More Columns

To make the table narrower, I also grouped columns with product details, and date details

hidecolumnsgrouping03

With those +/- buttons, you can show or hide the individual groups.

At the far left, you’ll see buttons with numbers 1 and 2. You can click those, to show or hide all the groups.

In the screen shot below, I clicked the “1” button, and that hid all the grouped columns. To see all the detail, click the “2” button.

hidecolumnsgrouping04

Fancier Grouping

AlexJ shared his grouping code on this blog a while ago (yikes, that was 5 years ago!), if you’re looking for something fancier. His technique uses named ranges and macros, and you can download his sample file to see how it works.

To download the file, go to AlexJ’s page on my Contextures website, and in the VBA section, look for VB0001 – Hide Rows with Outlining

hidecolumnsgrouping05

Excel Dashboard Course Recommendation

As I mentioned earlier, you could use this technique to show or hide columns in a dashboard. Let people open up the details when they need them, and hide them the rest of the time.

If you’d like to learn all about dashboards, Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course, for a limited time. And, if you sign up by 8 pm Pacific time on May 1st, you can get the course for 20% off. And I’ve got a special bonus for you too!

The course is video based, delivered online and is available 24/7. You’ll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.

I’ve been through this course, and highly recommend it. The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.

Click here to find out the details, read the student comments, and watch the ‘behind the scenes’ video that shows you what you’ll receive as a member. Remember, if you sign up by May 1st, you can get the dashboard course for 20% off.

Bonus: If you sign up for Mynda’s course through one of my links, I’ll send you a copy of the Excel Dashboard Tools by DataPig.
Just enter the Referral Code – DebraD – in the box just below the Buy Now button, then email me at ddalgleish@contextures.com to let me know

Excel Dashboard Course

DataPig Dashboard Tools

In this very short video, you’ll see 3 of the time-saving features in the DataPig Dashboard Tools. You’ll get this add-in as a bonus, if you sign up up for Mynda’s dashboard course through one of my links. Click here to find out the details, and remember to enter the Referral Code – DebraD – in the box just below the Buy Now button.

For more details on the Dashboard Tools, click here.

__________________________

You may also like...

5 Responses

  1. XLarium says:

    Hello Debra

    In my opinion the Grouping feature is not the best solution for this situation.
    I would use custom views. Custom views also save filtered rows.

  2. XLarium says:

    Yeah, that’s really unfortunate.

  3. Chego says:

    This is a great tip for me today. The data I’m working with has multiple header rows and custom views does not work well in this scenario. This was the perfect solution. Thank you!

Leave a Reply to Chego Cancel reply

Your email address will not be published. Required fields are marked *