Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
In the worksheet called Outlining, we're seeing Budget Projections for 2014 and this sheet contains a lot of data. Now imagine if you'd like to display this information for presentation. It contains 12 months, it's got quarterly totals, a lot of information here and perhaps overwhelming, too much for a visual display and what you might want to consider doing ahead of time is hiding, for example, columns B, C and D and showing the first quarter totals and then doing the same kind of thing for the other months as well. You'd like to be able to present the data in a more compact way, not showing all the detail.
Similarly, looking at the rows here, maybe you don't want to show that detail or this detail here. You do want to show some of those total rows. We've got expenses down here. You might not want to show that data as well. Now, hiding columns and rows doesn't take that long, but if you're making a presentation, you might want to have the flexibility to quickly expand and collapse the data and not necessarily show the audience the various Excel commands needed to get there. What if we could quickly Click a button or two and watch this data expand or collapse? Now there's nothing unusual in this worksheet really.
Column E has quarterly totals as does column I, and a few others to the right. So there are formulas in column E, not in every single cell as we can see, but there's one here that's tabulating the sum of these and so on. Now, just by having these in place, Excel's outlining capability will pick up on that. It will allow us to essentially collapse this data and possibly show only the quarterly totals or maybe even show only the grand total. And so, you might think that the data in this worksheet is rigged or has to be set up in a special way, it isn't.There are certain rows here, for example, row 7 that tabulate data from the two cells above.
There's a formula right there, simply adding the two and we see other kinds of rows here as well, not nearly as many as we have for columns perhaps, but nevertheless they're there. So how do we activate the outlining capability here? The active cell is somewhere within the data. We go to the Data tab and in the Outline group, we have an option called Group. Click the drop arrow and choose AutoOutline. Look what happens to the worksheet here. We could see some symbols up above.
We see some vertical numbers 1, 2, 3 and some horizontal numbers over here, 1, 2, 3, 4. I'm going to Click the 2 where we see the vertical 1, 2, 3. This collapses the display so that we only see the quarterly totals. If I Click one, we're only seeing the grand total. Now I haven't done anything with the rows just yet, but if we Click number 3, we are going to see the horizontal 1, 2, 3, 4, this will collapse the detail somewhat, but not completely. We've got interim totals there.
That's why we have four instead of three levels. We'll Click the 2 and we're not seeing too much of a display there right now, that's it. Click one and now we're down to one cell, which probably we wouldn't use very often. So a more typical display for this data might be Click the 2 or the 3 here for the horizontal data and then Click the two up top where we see the vertical 1, 2, 3 for the rows this way. Now, depending upon how you're making the presentation or what it is you want to show, you could imagine saying or thinking, those Outlining symbols take up a lot of space or a couple of options.
You may be familiar with the idea that you can quickly hide most of the ribbon except for the tabs simply by Double-Clicking the current tab. So if we Double-Click Data at the top of the screen, that collapses and gives us a bit more room. If you want to hide the Outlining symbols, you want to press Ctrl+8 and that hides them and you can get them back with Ctrl+8 as well, so maybe you want to keep the display this way. Another option here, too, is with those arrows present, so we'll press Ctrl+8 again, bring them back. If a discussion begins around what happened in the third quarter last year, if someone is interested in seeing the detail for the third quarter at least momentarily, you can Click the plus right here that's above the third-quarter and what happens? We see the detail for the third quarter for a while.
We'll leave it there for a bit. The discussion has run its course. What we do now? We collapse it. Similarly, with the data down the left-hand side, if we don't necessarily want to see the Gross Profit detail broken out this way, Click the minus, collapse it that way. Do we need to see those expenses? Well, we might at different times and at other times, maybe collapse them so we're looking at a list like this. And so in the different ways, we can expand and collapse this view. Once again, getting of rid of those symbols, it's Ctrl+8, bringing back, it's Ctrl+8.
If we Click the three in the vertical 1, 2, 3 as I just did, we see the vertical display expand. Click in the four here, we're back to the original display. If you want to redesign the layout of this particular worksheet, you want to exit this procedure and here we do want to bring back the data tabs so we'll Double-Click Data and then go to Ungroup and choose Clear Outline. And we're back to a normal display. This is potentially an ideal vehicle if you're making presentations of complex data.
So, rather than hiding the columns, let the outlining capability work for you as you use these features available from the Outlining group on the Data tab in the Ribbon.
There are currently no FAQs about Excel 2013 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.