From the course: Cert Prep: Excel 2016 Microsoft Office Specialist (77-727)

Create and manage tables

From the course: Cert Prep: Excel 2016 Microsoft Office Specialist (77-727)

Start my 1-month free trial

Create and manage tables

“

- [Lecturer] Creating a table from a range of data in your worksheet will make it so much easier to work with that information and during the MOS exam you may be asked to create a table from a range data in a worksheet. You may also be asked to convert that table back to a normal range. Once you have your table created you may need to modify the columns that are included and the rows that are included within your table either adding to or removing that information. These are all skills you may be asked to demonstrate your knowledge of on the Excel 2016 MOS exam. And I have the 04_01 exercise file open so if you're ready, let's create some tables. Here we have our orders table for h+Sport. It's a little overwhelming to look at all this data. I want to show you how easy it is to convert this information into a table so that we can then work more easily with the information. We will begin by scrolling over to the left hand side, selecting A3 which is our first column heading, I'll then use my mouse to click and drag all the way across and using Control + Shift and my down arrow I can select all of the information in this worksheet. Notice that there are 2,701 rows of information in here. Now to convert this to a table, there's a couple of different ways that I can do this. I can use the keyboard shortcut Control + T for Table or I can go in my Home tab and select Format as Table. I'll go ahead and select a medium table style number two. Excel is asking me to confirm that it has selected the correct data to be included in the table. And also, I want to make sure that 'my table has headers' is selected. I'll go ahead and click OK because everything looks good. Here's my new table. Now look at what I can do with this information. If I want to go ahead and filter by region, I can click the sort and filter button, uncheck 'select all' and let's just take a look at the north region. I'll go ahead and click OK. Everything else is now filtered out. All I'm seeing is the data for the north region. I'm going to go ahead and remove that filter, select all, click OK and now I'm seeing all of the records for all of my regions. Now I also can delete an entire column of information if I want to. I'm going to go over to my Priority column, column F. Select the entire column and right click and delete. Now that information has been removed from the table. If I scroll all the way over to the right hand side, to column T, I'll click in T3 and enter a new column heading. I'll type in New Customer and as soon as I exit out of that cell, did you notice how it picked up the formatting for both the column heading and for every row down below. Isn't that awesome? So it takes care of all the formatting for me without me having to do anything. It's one of the great benefits of working with tables. But what if I want to convert this table back into a normal range? I can do that and also when I convert it back I can hang onto all the formatting that's been applied. I have my banded rows, my column headings are all nicely formatted and I'd really like to keep the formatting. I'll just click anywhere in the table and go to my table tools design tab. Over on the left hand side of my tools group I have Convert to Range. I'll go ahead and select it. I have a pop up window that says 'Do you want to convert the table to a normal range?' And I'm going to say yes. So what happened? Well my sort and filter buttons disappeared so I no longer have the ability to sort or filter the information. But it did maintain all my formatting, which is exactly what I wanted it to do. I love the table functionality in Excel and I always convert my data into a table. It just makes good sense and it speeds up my ability to sort and filter the data. Now you know how easy it is to convert a range into a table and also how you can convert a table back into a normal range. Now don't be surprised if on the MOS exam you're asked to create a table and then convert it back to a normal range. Remember, the more you can practice working with tables the better prepared you'll be for the MOS exam.

Contents