Microsoft Excel 77-727 – 3.1 and 3.2 Create and Manage Tables

1. 3.1.1 and 3.1.2 Create an Excel Table from a Cell Range and Reverse

Many worksheets consist of a rectangular block of data in which the roles of data have a common structure and format. Excel offers you the ability to define this block as a table. The reason that this is a useful facility is because you can use a variety of tools in Excel, such as selecting summary formulas, formatting, sorting and filtering. While many of these features are available available even without using tables, you’d have to manually select the range of cells before using the features. If you define a range of cells as a table, excel knows that this group belongs together as a single unit. These same tools are then easier to apply because Excel now knows which cells to include. If we look at the passenger number worksheet, we can see that these rows all have a common structure consisting of the name of the airport and the passenger numbers in each month of the year. This is a good example of a range of cells which can be categorized as a table.

To create the table, just click anywhere in the cell range, then go to the Insert tab and click on Table. The Create Table dialog box is displayed and you can see that Excel is guessing the range of data that you want to use in the table. It’s better to select the range of sales first before inserting the table. So given that we don’t want to do any analysis on the title of the worksheet, we can exclude that from the selection. I go back and select the data that I want first and then insert the table. For the moment, don’t worry about what these dollar signs signifies signify as they would be explained later in the course. This box indicates whether the table information has headers.

Well, our selection does have headers which are located in this row, so we want this checkbox to be armed. Now we can see how the table looks when I press the OK button. To create a table, the data must be contiguous. That is, there must not be any blank rows or columns in the range of cells. Also, the data must be arranged in row order with a header at the top of each column and the data listed below. Notice that every table has a name and you can see here that the default name is divided by Excel as Table One, Table Two, et cetera, which doesn’t actually tell us anything about the data, so it’s a good idea to give the table a meaningful name. We call this one Passengers, so I’ll change the name and press the return key.

Now, no matter where I am in this worksheet, I can click on the name box and when I select Passengers, I’m brought directly to the table. Once the table is selected, you can see that the Table Design Tools menu is activated. You have the option to turn on a total row which adds a new row onto the table like so and calculates a total like this. This may not be appropriate in all cases, and indeed in this case it doesn’t make sense to create this new total, so I turn it off. Every column on the table has a small pulldown menu beside the column title, which gives access to a range of facilities such as sorting, filtering and others. For example, if I want to sort the information alphabetically by Airport, then I can select the Airport header and select Sort from A to Z.

The information is now sorted from a upwards. Be careful how you do this. However, notice that the monthly total role is now within the table rather than at the end. Because I selected the whole table. You’ll cover all of these options in detail later in the course, so for now it’s enough to be aware of the possibilities. Each column provides the same type of operators, so this is a powerful way in which you can analyze the data. Also notice that in the total row, if I click on any of the cells, I have a pull name from which I can select statistical functions to analyze the data. For example, I could change this row name to Average and then indicate that I want to see the average for the month by selecting the Average function. Then I can use Autofill to complete the series.

There may be occasions where you want to get the data in the table back to being just a range of cells. So you need to convert the table to a range, regular set of roles and columns of data without the table definition. Select the table and under the table design Tools in the Tools group, click the Convert to Range command and we can see that the data is now no longer defined as a table. The table name is also no longer in existence, as I can’t see it in the list of available.

2. 3.1.3 Add or Remove Table rows and Columns

Until the table is created. You can add or delete rows and columns of data to add a new column of data at the far right of a table, just enter any data into the first blank column. Excel will automatically extend a table to include this new column. Similarly, Excel will extend a table to include a new row of data that you can enter directly below a table as long as the Total row feature is now activated. As you can see in the Table Design Tools, if the total row is activated, you must use one of the following methods manually insert a new blank data row by clicking on the bottom right cell containing data meets the above the total row, and then click the tab key.

Manually insert a new blank data row by clicking any cell in the bottom data row. Then on the Home tab in the Cells group, click the Insert arrow and click Insert Table row below. The Insert button on the Home tab can be used to insert a new row or column anywhere in the table, just not just as the last row of data. Similarly, on the Home tab in the Sales group, you can use the Delete button to delete rows or columns of data from the tape.

3. 3.2.1 to 3.2.3 Table Styles

Change the way that the table looks by using the Table styles, which is available in the Table Tools design options. As I hover over each one of the preset options, you can see the effect on the table and how it looks. The Table Style Colors option can be removed and reapplied later if needed. Again, you can also create a customized one, select the new Table style and give Style a name. Recall this one.

DM Style I want the total row in my table not to be very distinctive, so I select that row. Now I can bring up the Format Cells dialog and change that row whatever way I want. All I have to do is actually apply this custom style. As you can see that the total row is bold and italicized the Tablet’s Design tab also allows you to activate or deactivate other table formatting options, highlighting the first or last column, or both with the Bold font option.

By default, both options are turned off. Banding rows by using different color shades for alternating rows. This feature often improves the readability of tables that are very wide because data in the same row has the same color shade. By default, the Bandit Rows option is turned on, but Banded Columns is turned off. If you click the banded columns checkbox to turn it on, you can see that every second columns now highlighted. You can also turn the Auto filter icon off. The final requirement in this section of the domain is to know how to turn the total row on and off, which is just a case of selecting the table. And in the table design options, turn the automatic total on or off by clicking this checkbox.

4. Exercises Domain 3.1 and 3.2

We’re going to open the habitat in slight detail and we’re going to convert the data in the cell range that I specified to a table. So we go insert a table. You can see that you have the right sell range. Click OK. And now we have a table. We name the table by clicking on the table name box and typing in the appropriate name. When you have a table selected, you have design options and you can select the different styles that you want. So we want Medium 14 and apply it.

The design also has the option to uncheck Bandit rows or check Bandit columns which are done and then remove the filter. Now we convert the table back. So click and go to Design and have convert to Range. So now it’s gone back to being arranged. Now we convert it back to a table again by going to Insert Table and we name this table as Flights. Click up here and type in Flights. The design option then allows you to turn on Total Row.

So we click on this check box and we have a total row at the bottom of the table. Now in the total row you can change the functions that you have a bunch of functions to select from you to go for average and we get the answer. Insert a new column to the right. You see insert. Once you have the table selected, you can insert column to the right. We’ll widen it slightly. Never create a new table style. So click on the pull down for the stars.

Go to new here I want to modify the header row. You can select various elements of the table. So give it a name. Then select the one that I said is the header row and change that to blue. So we format and make it Blue and press OK. And then we can apply that style, that New Style practice style to our table. So we click on the pulldown and click on the custom drive.