---

Contents 1 Excel Tables 1.1 Creating Tables 1.2 Adding Columns 1.3 The Bottom Line 1.4 Addressing a Table 1.5 Changing a Table Name or Size 1.6 Other Elements 1.7 Some Examples 1.8 Pasting Data into a Table

Excel Tables

Excel Tables came into existence with Excel 2007. They are a powerful tool that can make life a lot simpler. The main strengths of tables are:

• They are dynamic (they automatically adjust as data is added or deleted)
• They address the elements in a number of ways.
• They automatically “copy down” formulas you enter.

Creating Tables

Data coming into a spreadsheet as a result of a query or data import will come in as a table. However, any existing range of data can be converted into a table. In this article, the following data are used



Download the sample data here Media:Sample Employee File.zip

To convert a range of data that isn’t already a table into a table, put the cursor anywhere in the range and select Format as Table from the Home Ribbon.

This brings up an intermediate menu that presents the various styles of tables (colors, alternating lines, etc.) Once a table style is selected, the utility continues and makes a guess as to what range is to be included in the table.

The range shown is the initial range for the table. You can change this range if you do not agree with what Excel selected. As mentioned above, once the table is established, it will expand and contract to accommodate the data.

The range is converted into a table with the style selected, and filters are automatically put in place.

The table is now ready to do “Table Things.”

Adding Columns

Adding a column is simple. In the title row adjacent to the existing table type in the new title. It will take on the characteristics of the rest of the table (color, filter, etc.). Type in the title, “Personal Sales.”

Now to add the formula which in this case is the Number of Sales divided by the Number of Contacts. Typing in =H2/G2 into cell I2 will duplicate that formula all the way down. It is OK to do this if you want to keep the formulas short.

On the other hand, selecting the cells expands the selection much like selecting a cell in a Pivot Table expands the selection. So typing an equal sign and selecting cell H2 and then typing a slash and selecting cell G2 produces the following formula: =Table3[[#This Row],[Sales]]/Table3[[#This Row],[Contacts]]

Both formulas do the same thing. The table syntax will be explained in more detail in a following section.

This technique doesn’t work across rows, if a cell is selected in another row, it gets assigned the normal cell designation.

With either method: manual typing or selection, the formula automatically gets copied down.

The Bottom Line

Hidden in the Table Style Options is a very powerful tool: Total Row.

Clicking on this option adds a row at the end of the table into which formulas can be entered. These formulas apply to all entries between the header and total row regardless of growth or shrinkage.

Clicking on the cell produces a dropdown list of prepackaged formulas. Clicking on the More Functions option brings up the formula wizard.

Formulas can also be entered manually using the formula bar at the top of the spreadsheet or by selecting cells in the table.

For this exercise, the Sum (from the dropdown list) is selected for the Contacts and Sales Columns, and the following formula is used to calculate the company-level personal sales metric. Use the select cells method to create the formula in the formula bar. =Table3[[#Totals],[Sales]]/Table3[[#Totals],[Contacts]]

Computing each person’s contribution to the company’s overall sales – Company Sales – (as a percent) demonstrates the power of the automatic addressing of the select method. This metric is computed as the person’s sales quantity divided by the company’s total sales, or:

=Table3[[#This Row],[Sales]]/Table3[[#Totals],[Sales]]

No matter where the Total Row winds up, this formula will still work.

Addressing a Table

The real power of tables comes from the many ways you can address the cells. The above examples show some of these techniques.

Excel’s autofill feature is a great help in developing table formulas. The following formula shows how to compute the number of sales made by men.

As the formula is typed, Excel takes a guess at what function or range to use, so as soon as the “ta” is typed, a menu appears with choices. It so happens that the table created in the original demonstration was called Table3. A different table name may appear. To select an table, use the up and down arrows. To put it in the formula, either double click it or press the TAB key while it is selected.

The way to address a table element is to type in an open square bracket ([). This action presents a list of table headers and properties that can be used in the formula. In this case, Sales is wanted. After opening the square bracket, type an “S” and the list will shrink to contain only those items starting with “S” or use the arrow keys or click on it. Continue building the formula to produce:

=SUMIFS(Table3[Sales],Table3[Gender],"M")

One of the strengths of using the table reference is that they are absolute references that grow and shrink with the table. This formula can be copied to the next line with “F” substituted for “M” to find the sales figures for the women without having to re=adjust the range.

=SUMIFS(Table3[Sales],Table3[Gender],"F")

Changing a Table Name or Size

Normally tables will grow and contract as you add columns or rows. However, there may be times when a manual selection is desired. Changing the table name is simple. Select a cell in the table, and in Table Tools, click on Design. At the left of the spreadsheet, is the table name box.

...................................................

To change the table name, overtype the table name in the box indicated. To change the table size, click on resize table and enter the new range.

Other Elements

The elements in the table (what appears as options when a left square bracket is typed) contain the column headers and also the following elements:

• #All – refers to the entire table: headers, data and totals.
• #Data – refers just to the data portion of the table.
• #Headers – refers just to the header portion of the table.
• #ThisRow – refers to the current row. This was seen above where formulas used several items on the same row.
• #Totals – refers to the total row.

Some Examples

Using #Data is a quick way to do VLOOKUPS.
....=VLOOKUP (A2, TABLE_MyData[#Data],4,False)
However #Data is the default, so
....=VLOOKUP (A2, TABLE_MyData,4,False) is equally as valid.

To look up the 7th element in the total line, use
....=VLOOKUP (“Total”,TABLE_MyData[#Totals],7,False) = 890
However, it might just be easier to use
....=SUM(TABLE_MyData[Sales]).

We can make use of Excel’s INDEX function to do a reverse VLOOKUP.

In this example, the objective is to find the first name associated with Contract Number A0009.
....=INDEX(Table_Mydata[#Data], MATCH(“A0009”,Table_Mydata[Contract Number],0), COLUMN(Table_Mydata[First Name]))

• Table_Mydata[#Data] is the range.
• MATCH(“A0009”,Table_Mydata[Contract Number],0) finds where “A0009” appears in column Contract Number. The formula gives the row.
• COLUMN(Table_Mydata[First Name])) finds the position of the [First Name] Column. The formula gives the column.

All of this is done without having to know column letters, offsets and ranges. The table figures it all out.

Pasting Data into a Table

Once a table is set up, it is useful to supply it with new data from time to time. Ideally this would be done by invoking a query that pulls data from a database. However, sometimes copy and paste is the only alternative.

This is the table whose relationships and formulas are to be kept.

The first step is to get rid of the old data.

• Highlight cells in the rows containing the old data.
• Do not highlight the Headings Row, or the Total Row (if there is one).
• Right Click and select Delete

You will see one of the following two displays:

.................

The display you see (Delete Table Rows) or (Delete Entire Sheet Row) depends on whether the selection includes rows hidden by filters. If the row is hidden by a filter the Entire Sheet Row is presented. Entire Sheet Row deletion does exactly that: it deletes the entire row for all columns on the sheet.

Table Row deletion deletes the row only in the table. Data in other columns on the sheet outside of the table are not affected.

Once the old data is deleted, the table should look like:

If you look out in the Personal Sales and Company Sales columns it appears that the formulas have disappeared.

Copy and paste the alternate data into the first cell of the table. The formulas will return. The finished table should look like: