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.
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. 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 [email protected] to let me know
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.