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.

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

Group More Columns

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

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.

Fancier Grouping (With Macros)

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

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.

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.

__________________________