Excel 2016: File Management—Tables

Updated 2017.07.30

Objectives:

·        Know the meaning of the database terms table, record, and field

·        Learn how to format a table.

·        Learn how to add rows to a table.

·        Learn how to add columns to a table.

Tables

We need some sample data to play with. Open the Advising.txt file. This is a tab-delimited text file. This means that the columns of data are separated by tab characters, and when the file gets imported into Excel, every time Excel sees a tab character, it will put the data following the tab into a new column. We need to import this file into Excel.

 

One of the most common applications of Excel is not to do lots of calculations (spreadsheet stuff), but to do sorting and filtering (database, or "table" stuff). Before we can work with tables, we need to know the vocabulary of tables.

Field: the smallest unit of meaningful data (e.g. First Name or Zip Code). In Excel, a field is represented by a single cell. The yellow cell below is an example of a field. It is the Last Name field for row 3 of the table (the "John A. Adams" row).

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-Tables_files/image001.jpg

Sometimes, the term "field" refers to an entire column, for example the Major field:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-Tables_files/image002.jpg

You can usually tell by the context whether the word "field" refers to an individual cell or an entire column. 

The next step up in the data organization hierarchy is the record.

Record: a collection of related fields. In Excel (and all other database programs), all of the fields making up a record are on the same row. The yellow cells below make up a single record (the "John Adams" record).

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-Tables_files/image003.jpg

Field names are usually given in the top row of the file. In the "Advising" file, field names are "Last Name", "First Name", "Major", "Advisor", "Credits", and 'ID Number". Note that column headings must be in a single cell. For example, do not put "Last" in row 1 and "Name" in row 2 when the field name is "Last Name". Field names are highlighted in yellow:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-Tables_files/image004.jpg

A stack of records is called a table. In the picture above, the first two records of the table are visible.

Table: a collection of related records. In Excel, a table is stored on a single worksheet. And, in Excel, you can formally define a series of rows and columns as an "official" table. When you do so, it makes some tasks easier (like sorting and filtering).

Creating a table in Excel

To create a table in Excel, select the data (including the column headings). Then:

(1) Click on the Home tab.

(2) In the Styles group, click on the Format as Table button.

(3) In the list of possible styles, click on the one you want to use.

Your data will now look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-Tables_files/image006.jpg

It is also possible to create a table that has no data—just headings. The process is the same as described above, except you only select the headings. To add rows to the table, just go to the last column in the last record and press the Tab key.

Advantages of tables

You are not required to use Excel's table features when you want to do database things with your data. However, if you define your data as a table (as described above), Excel makes your life easier. Excel will:

·        Format your table with the heading row formatted differently than the rest of the rows.

·        Format the rest of your table with a border and (with many of the formatting options) banded rows to enhance readability.

·        Give you the option of formatting the first column differently than the rest of the columns.

·        Make it easy to insert columns and rows (see below).

·        Make it easy to add a totals row (or any other type of calculated value) to the bottom.

·        Make it easy to add total columns (or any other type of calculated value) to the right.

·        Automatically adds drop-down arrows to the right edge of each heading cell to make it easy to sort and filter on any field.

·        Makes it easier to do other things like create pivot tables, remove duplicate rows, and add rules for data validation.

Inserting rows or columns

To insert a new column (field) or row (record):

·        Right-click on the column (or row) where you want the new field (or row) to appear. The following menu will pop up.

·        Click on Insert.

·        From the sub-menu, click on Table Columns to the Left to insert a new column. Click on Table Rows Above to insert a new row.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-Tables_files/image007.jpg

Sorting

Sorting is rearranging the data in a table, using a specific field to determine the order. For example, you might want to sort a table using a Last Name field to put the records in alphabetical order. Or you might want to sort a table using a Sales field to put the person with the highest sales value at the top of the list. There are two types of sorts:

·        Ascending Sort: When using numbers, an ascending sort puts the data in increasing order. When using character data, an ascending sort will put the data in alphabetical order. When using date data, an ascending sort will put the data in chronological order (earliest events at the top of the list, most recent at the bottom).

·        Descending Sort: When using numbers, a descending sort puts the data in decreasing order. When using character data, a descending sort puts the data in reverse alphabetical order. When using date data, a descending sort puts the data in reverse chronological order (most recent events at the top of the list, earliest events at the bottom).

More info on sorting: Sorting

Filtering

Filtering is selecting records from a table based on whether they match some filtering rule (criterion). Filtering does for a table what Google does for the Internet; it shows you the records that you want to see and hides the ones you don't want to see. More info on filtering: Filtering

Other table-related pages

·        Subtotals

·        Pivot Tables

·        Assigning names to ranges