In Excel 2003, you could create a list from data on a worksheet to get access to filtering options, total rows, and more. In Excel 2016, these are now called tables. In this movie, you will know how to turn a selection of data into a table, add a totals row, add table formatting, and use filtering options to zero in on specific data you want to analyze.
- [Voiceover] If you're accustomed to working with lists in Excel 2003, you'll have difficulty finding them here in Excel 2016, because it's been renamed to tables, and that's what we're going to explore in this movie with this file, Forecast0302, from the chapter three folder of your exercise files. Over here on the left, we have data going through the different Quarters, months of the year for various Departments, and we see some figures. We don't see a total at the bottom. Now, in Excel 2003, you could create a list out of this, by simply clicking anywhere in the table, in this case, the content that will become a table.
And, instead of creating a list, we go to the Insert tab now, here in Excel 2016, and select Table. You can see what happens, it's the same as creating a list in Excel 2003, there's a marquee selection around the data that is to be considered for the table, it's perfect, it found the borders nicely, there's the range right there, you can see A3 to D39. If you want to change, you could click this icon on the right hand side of the field to make a different selection, but it's perfect.
And in fact, we do have at the very top, our first row, some labels, that would be considered a header row, so yes, the My table has headers check box should be checked off, and when you click OK, you now have your old-fashioned list, now called a table. The table comes with the filtering drop down arrows, we'll get to those momentarily. It's also still highlighted, or selected, because the Design tab appears now under Table Tools with a ton of options, like formatting.
Look at the table styles here, click the drop down arrow to see even more options. You can see there are some light, medium, and dark ones to choose from. As you hover over these, you get a nice little preview of what that's going to look like. I kinda like one of these medium ones here, how about Table Style Medium six. Give it a click, and it's applied just like that. Now, it's still selected, or highlighted, to really see what it looks like, you can click outside the table, and that looks pretty good. But we've lost the Table Tools.
Well, just go back inside the table anywhere, click, and you get your Design tab back under Table Tools. Because one thing we might wanna do as we scroll down is add a row at the bottom that totals up this column for revenue. All we do is go to Total Row, and give it a click. Turns it on, adds it to the bottom, and there's your Total, just like that. Now that's great, for everything, but, when it comes to filtering now, we can really get a good sense for the various Departments, Quarters, even Months, by using these filtering drop downs.
Let's say I wanna see what the projections are for the Training Department. Click the drop down for Department, and we can deselect check boxes or deselect everything by clicking the check box next to Select All. This toggles all of them, or none of them. And now, click the check box for Training, and click OK. Now we can see the Training Department only figures for the whole year, there they are, and the Total. You can see how it's updated to show the Total only for what we've selected from the filtering drop down.
Click that same button, and Clear Filter from Department to go back to where we started. Maybe I'm only interested in Q4 results. Let's go to the Quarter drop down, again, we'll deselect everything, clicking the check box next to Select All, and choose Q4. Click OK, and you can see what that Total's looking like for Q4 for all of the Departments. We can add another level, how about just the Sales Department for Q4, deselect everything, select Sales, and click OK.
That's what we're looking at for those three months, Q4, for sales. And of course, we can go to those drop downs, and clear the filter for Quarter and for Department, to go back to our original list. So, while it's not called list anymore, many of the same features and functions you're accustomed to working with in Excel 2003 while working with lists, apply here to tables, plus, you get a lot more options when it comes to formatting your tables here in Excel 2016.
- Understanding user interface differences
- Working in Compatibility mode
- Opening and editing PDFs in Word 2016
- Using templates and images
- Creating macro-enabled workbooks in Excel 2016
- Exploring PowerPoint themes, transitions, and animations
- Importing Outlook contacts, email, and calendars into Outlook 2016