UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Tables in Excel    
Tables in Excel

Contents

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

Image:File01A.jpg

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.

Image:File02.jpg

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.

Image:File03.jpg

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.

Image:File04.jpg

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.”

Image:File05.jpg

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]]
Note: Starting with Excel 2010, this syntax has been shortened to =[@Sales]/[@Contacts].
[Sales] means the entire sales column (exclusive of headers and Totals) while [@Sales] means the value of the Sales column on the current row.

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.

Image:File06.jpg

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.

Image:File07.jpg

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.

Image:File08.jpg

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]]

Note: Starting with Excel 2010, this syntax has been shortened to =[@Sales]/[@Contacts] [Sales] means the whole column (excluding headers and total row) while [@Sales] means the value in the Sales Column on the current row.

Image:File09.jpg

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. Image:File10.jpg

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.

Image:File11.jpg

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.

Image:File12.jpg ................................................... Image:File13.jpg

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.

Image:File09.jpg

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:

Image:BFile01.jpg ................. Image:BFile02.jpg

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:

Image:BFile03.jpg

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:

Image:BFile04.jpg

Referencing Tables in VBA

Tables can be referenced in VBA in much the same way as they are referenced in regular Excel. Since VBA does not have VLOOKUP or INDEX that can use a whole table, for the most part, you will be working with a table column rather than the whole table itself.

Suppose in the above example we wanted to go down the list of people’s last names and get the occupation and put it on Sheet2.

Since #Data is the default condition for the table we don’t need to specify it. All we need to do is reference Range("TableName[ColumnName]").

CODE

Sub CopyJobs1()
Dim cl As Range
Dim RowNum As Long
Dim sh As Excel.Worksheet

Set sh = Sheets("Sheet2")

' Clear the old data, create headers and starting row on sheet 2
sh.Cells.ClearContents
sh.Range("A1") = "Name"
sh.Range("B1") = "Occupation"
RowNum = 2

‘ Now go down the column and get the data
For Each cl In Range("Table_Employee[Last Name]")
   sh.Cells(RowNum, 1) = cl.Value
   sh.Cells(RowNum, 2) = cl.Offset(0, 3).Value
   RowNum = RowNum + 1
Next
   
End Sub

That’s one way to reference a table column in VBA. However, sometimes it is advantageous to reference the column name by the header name and get the value using offset. This might come in handy if you move columns around in a table, or maybe you want to make the column name dynamic for some reason.

The following is admittedly a contrived example but it illustrates how to reference by column header name. In Column K, I copied the occupations and then sorted them alphabetically. In Column L, I used to formula =MATCH(K2,Table_Employee[Occupation],0) to find on what row (relative to #Data) the occupation was found.

Image:Sorted_Jobs.jpg

To use this method, reference the column using: Range("TableName[[#Headers],[ColumnName]]") and apply the offset to it.

CODE

Sub CopyJobs2()
Dim cl As Range
Dim RowNum As Long
Dim sh As Excel.Worksheet

Set sh = Sheets("Sheet3")

' Clear the old data, create headers and starting row on sheet 2
sh.Cells.ClearContents
sh.Range("A1") = "Name"
sh.Range("B1") = "Occupation"
RowNum = 2

For Each cl In Sheets("Sheet1").Range("L2:L13")
   sh.Cells(RowNum, 1) = Range("Table_Employee[[#Headers],[Last Name]]").Offset(cl.Value, 0)
   sh.Cells(RowNum, 2) = Range("Table_Employee[[#Headers],[Occupation]]").Offset(cl.Value, 0)
   RowNum = RowNum + 1
Next

End Sub

Clearing Old Data From a Table

The following code segment can be used to clear data from an existing table. It first clears out any filters that might be applied and then checks to see if there is data in the table. The syntax is: ClearTable SheetName, TableName.

CODE

Sub ClearTable(TableSheet As String, TableName As String)

If FilterIsOn(Sheets(TableSheet).ListObjects(TableName)) = True Then
   Range(TableName).AutoFilter
   Range(TableName).AutoFilter
Else
   Range(TableName).AutoFilter
End If

If Range(TableName).Rows.Count > 2 Then
   Range(TableName).Delete
   Exit Sub
End If

If CountFields(Range(TableName & "[#Headers]").Offset(1, 0)) > 0 Then
   Range(TableName).Delete
End If

End Sub

Function CountFields(MyRange As Range) As Long
Dim cl As Range
Dim Counter As Long

On Error Resume Next
Counter = 0
For Each cl In MyRange
   If Len(cl.Value) > 0 Then
       Counter = Counter + 1
   End If
Next

CountFields = Counter

End Function

Function FilterIsOn(lo As ListObject) As Boolean

Dim bOn As Boolean

bOn = False
On Error Resume Next
If lo.AutoFilter.Filters.Count > 0 Then
    If Err.Number = 0 Then bOn = True
End If
On Error GoTo 0
FilterIsOn = bOn
End Function


Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 20,346 times.  This page was last modified 14:37, 8 January 2016 by dflak. Contributions by Jack Leach  Disclaimers