Join Dennis Taylor for an in-depth discussion in this video Collapsing and expanding data views with outlining, part of Excel 2016 Essential Training.
- We're looking at a worksheet called Outlining. It's in the workbook 08 - Worksheet Views. It's a budget and as we scroll to the right here, you can begin to see we're covering all the months of the year with some quarterly totals and a grand total on the right. Not a huge worksheet. And as we scroll downward, we've got some Expenses starting in row 16. Row 35 is at the bottom. You're a budget analyst, you're about to make a presentation of this information and you don't want to hit your audience with all these numbers at once. You might want to collapse this and show only the quarters.
Now what we could do ahead of time is drag across columns B, C, and D, and with the Control key F, G, and H, and so on. We could hide those columns ahead of time and then during the presentation, when necessary, expose that detail for each month. Now sometimes when you're making presentations, you really don't want the focus to be on how you're using Excel or what you're doing with Excel. You want the focus to be on the numbers. So, there's a feature called Outlining that allows us to collapse this data simply by clicking buttons during a presentation.
And it begins with the thought that we've got formulas, for example in column E, they're tabulating data from the left, We've got some formulas, for example, in row 7. Those are tabulating data from above. We've got some more here, down at the side here, row 13, that sort of thing. This is actually subtracting numbers here and we've got some other totals. We've got totals vertically and horizontally and it's just that simple fact that allows us to activate the Outlining feature. Simply click within the data somewhere, go to the Data tab in the ribbon, far right you'll see a Group button, click the drop arrow, and choose Auto Outline, and suddenly the screen looks quite a bit different.
These are called outlining symbols above the data and then outlining numbers. We've got a vertical 1, 2, 3 here and off to the left a horizontal 1, 2, 3, 4. On the vertical 1, 2, 3, I'm going to click 2. Watch the screen. It collapses the data. We're only seeing the quarterly totals. Now you can see the indicator here for hidden columns. We have hidden columns between these two columns and you can see all along here. We've hidden the monthly totals. Off to the left, we see the horizontal 1, 2, 3, 4.
As I click 3, this collapses the worksheet somewhat, not a lot. Click 2, it collapses this a lot more. We're collapsing this horizontally, you might say. If I click 1, then we're only seeing one row. And then on the vertical 1, 2, 3, if I click 1, we're only seeing the Total. You probably wouldn't start off this way although with a certain flair for the dramatics, maybe you could. This is how much money we're going to make in 2017. There's our net profit. Here are the details as we click 2.
And off to the left, we'll click the 2 over there. And you can imagine at different times wanting to display some or all of this data in different ways. Now, a lot of screen space is being taken up by these symbols and also by the ribbon menu system. You might not even need the menu system during your presentation, so how do we hide it? There are a couple of ways. One is to simply double click the current tab. I'm going to double click Data. It hides the ribbon icons, but not the ribbon tabs. How do we get the icons back? Simply double click Data again.
Another approach, upper right-hand corner, we've got a button here, Ribbon Display Options. We can click this. Auto-Hide hides the entire ribbon including the tabs. We might choose that option. We also have the ability to hide these symbols as well. Simply press ctrl + 8. That's going to hide the outlining symbols as well as the outlining numbers, ctrl + 8. Now, after having done that, and until we resurrect those numbers, we cannot work with those to expand and collapse but maybe we want to see the data this way.
To get those numbers back and the outlining symbols, ctrl + 8. There we are. So at different times you might want be using ctrl + 8. But the simple idea that we can expand and collapse this. And we can do this in some other ways too. For example, some people are asking questions about our 3rd Quarter projections. Let's see the detail on this. Here's the plus that's above 3rd Quarter, and we're seeing the detail on our 3rd Quarter numbers here. To collapse that, here's the minus above it. And over on the left-hand side, maybe for a while you're not showing the detail for Expenses, or you don't want to show the detail, here's a minus button to the left of row 33.
Click the minus and we're seeing the data this way. And because we've got levels of totals in some of the formuas here, we've got more outlining numbers for the horizontal view here so we've got the ability here to collapse and expand. And if you were about to make a presentation of this data, I think you'd be pretty well prepared, in other words, you'd want to rehearse this before thinking out all these implications of expanding and collapsing this. But I think you get the idea here. A lot of capability. You're not really focusing on the power of Excel here, you're focusing on the numbers as you talk about them.
If we need to zoom in and out, we can't do that right now for full screen, so if we go back to the very top of the screen and just click there, we bring back the menu on the top. Maybe we just want to have the tabs visible and that does allow us to get to the slider control bar on the lower right-hand corner. You might click the minuses over there, if that's visible to the audience. And again, you've got these different methods here to expand and collapse the view. If you need to make changes to the layout here, click back in the data, go back to the Data tab, then choose Ungroup and Clear the Outline.
So we're back to our normal view here. And we can resurrect, of course, the remainder of the menu, going back to that button in the upper right-hand corner, and simply Show Tabs and Commands. It's a great feature you want to experiment with a little bit but allows us to make, for example, a presentation with data like this. We have that quick ability to expand and collapse it by way of the outlining capability. And for those of you who are familiar with the Subtotal feature, this feature, which is in the Outline group, is really more about displaying data, but it gives you the outlining capability as well, but in a different way than we've seen it here.
But the outlining capability is certainly a powerful tool for presentation purposes.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros