Video: Excel lists have now become tables

If you've been looking for the list feature in Excel 2007, you can stop. It's not available any more. It's been replaced by a new feature called Excel Tables. Let's see how this works. In order to do this we need some information to work with. I've opened the EatCake Inventories List spreadsheet included, included in your Exercise Files. If you'd like to follow along and do that, please open that file now. You'll see this is a basic list of information, but I'd like to use the table functionality in Excel to really be able to enhance the information that I'm looking at. To do that, you select a cell within the area of the table and go to Insert tab.

Like the other applications in Microsoft Office 2007, Excel 2007 boasts upgraded features and a brand-new look. In Excel 2007 Essential Training , instructor Lorna A. Daly introduces the new version in detail. The training begins with the essentials of using the program, including how and why to use a spreadsheet, how to set up and modify worksheets, and how to import and export data. Lorna then moves on to teach more advanced features, such as working with functions and macros. Exercise files accompany the tutorials.

Excel lists have now become tables

If you've been looking for the list feature in Excel 2007, you can stop. It's not available any more.It's been replaced by a new feature called Excel Tables. Let's see how this works. In order to do this we need some information towork with.I've opened the EatCake Inventories List spreadsheet included, included in your Exercise Files. If you'd like to follow along and do that, please open that file now.You'll see this is a basic list of information,but I'd like to use the table functionality in Excelto really be able to enhance the information that I'm looking at. To do that,you select a cell within the area of the table and go to Insert tab.

In the first group, you'll see the Table grouping of commands, and the Create Table button. By selecting the Create Tablebutton, it's going to automatically default to the information that you have on your spreadsheet.In the dialogue box that pops up, it's going to confirm that we are actually looking at all of the information that's goingto be included in your table.You can also see it visually by the rotating neon indicator around the information that's included in your spreadsheet.You'll also want to tellthe Create Table command that your table has headers,by making sure that you have the check box selected in the area that I'm pointing to here.

Once you've set that up, you click OK, and your table is created. It's default to the last table style that we wereworking with, which is blue. If you take a look at the ribbon now, you'll see that we have a new Table Tools tab available for us to look at. ThisTable Tools tab gives us easy access to some commands that we've already explored, such as removing duplicates,and playing with the table styles.It also allows as to define our own table style options,work with external data so that we could import this information and make it real time,as well as doing simple things like re-naming your table. I'm going to rename this table to be InventoryQ1, so that this is distinctfrom any other tables that we may be working with.

In working with this table, you'll notice that I might have AutoFilter options in all of thecolumn headers in the top row of my table. And if I wanted to sort on it, I can simply click on the downward pointing arrow, select the area that I'm interested in taking a look at, for example Flour-Pastry,and it will sort the information, and just present the information to me that I'm looking at and interested in at thetime. If I want to remove that sorting option, I simply click on the sort filter,select all of my options, click OK, and my table is restored.

Q: When trying to apply the techniques from the “Relative and absolute referencing” video to a worksheet other than the exercise file included with the title, the formulas did not work for the entire worksheet. The formulas would only work when going through the worksheet row by row. What could be causing this to happen?

A: When trying to apply formulas to a whole workshee, here is a tip to try:

If you want to always refer to the same cell then use an absolute reference. For example, always pulling the value from cell A3 would be referenced as $A$3. This will never change no matter where you copy it to in the spreadsheet.

If you want to reuse the same formula, but with values in different cells, use the relative reference, A3. This way formula =A3*B3 will become =A4*B4 as you copy it down a column.

Q: In the chapter 7 video "Sorting and Grouping" at approximately 4:05, the author says to go to cell 5 on the worksheet and click on Subtotal to subtotal the grouping. My screen will not allow me to click on the Subtotal option at the top of the page. Is this an issue with my version of Excel?

A: It seems that there is an error in the instructions in this video. The video should have instructed users to do the subtotaling first, then create the table.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Already a member ?

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships.
Learn more

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.