Excel Tables
Page 5
1. This is a single structured reference, representing a range of cells.
2. This is the Table name. If the structured reference is in a cell inside the Table, then the Table name is not required. If you don't enter the Table name when specifying multiple columns, Excel adds it automatically when you enter the structured reference. The Table name references the entire data body range, excluding header and total rows.
3. The Table reference specifies the range in the Table by specifying the column name(s) and, optionally, a special identifier that modifies the column name reference.
4. The special identifier modifies the column name(s) reference to refer to specific predefined areas of the Table:
[#All] refers to the entire Table, including the header and total rows, if they're enabled.
[#Headers] refers to the header row, if it's enabled.
[#Data] refers to the data body range if any data rows exist.
[#Totals] refers to the total row, if it's enabled.
[#This Row] refers to the same row in which the structured reference is entered. In Excel 2010, this special identifier was deprecated and the @ symbol used instead. (This is discussed in more detail later in this chapter.)
5. The column name refers to a specific Table column or a range of columns.
Implicit Intersections
When working with Tables and structured references, understanding implicit intersections is an important part of understanding how structured references function. Sometimes referred to as relative intersections, implicit intersections have been a feature of Excel for many years.
An implicit intersection occurs when a reference in a cell refers to a single column of cells in another column, with the cell containing the reference in a row that intersects the referenced column of cells. With an implicit intersection, Excel resolves the reference to the single cell in the referenced column of cells that is in the same row as the cell containing the reference.
Excel does not resolve the reference down to a single cell if the cell containing the reference to the column of cells is not in a row that intersects the referenced column of cells. It also does not resolve the reference if the reference is being passed to a function that expects an array of cells. In the former case, Excel returns a #VALUE! error.
The next figure illustrates an implicit intersection with a reference to a column in a Table that uses structured referencing.
An example of an implicit intersection.
The advantage with implicit intersections is that the same formula is used in multiple cells, but the formula takes the value from a single cell in the same row where it intersects the referenced column of cells.
Advantage of Structured References
Besides encouraging the use of implicit intersections, which eases formula maintenance, structured references have the distinct advantage of being easier to read. Before structured references, Excel users relied on named ranges to make formulas easier to read and understand. With Tables, Excel is more diligent about encouraging the use of names versus column letters and row numbers. Consider this formula using regular referencing:
=A2-B2
The strings A2 and B2 don't tell you what the formula really refers to, and you need to look further to understand the purpose of the formula. When structured references are used with reasonably well-named Tables and Table columns, the purpose of the formula is clearer, without additional discovery. For example, these more meaningful names make the formula easier to understand and maintain:
=[@Income]-[@Expenses]
NOTE
When you’re using a structured reference, if a Table column name includes manual line breaks (ALT+ENTER) and other special characters, you must include those special characters in the structured reference. (Use of special characters in column names is covered later in this chapter.) When you change column headers, Excel automatically ensures that all structured references that refer to those column names are also changed.
Creating Formulas
Clicking on cells when entering formulas is an easy way to get Excel to enter cell references instead of typing them. You can also do this with Tables, and Excel creates the structured reference for you. If Excel cannot represent the selected cells with a structured reference, it enters a standard reference. For example, in the next figure, the referenced cell is one row below the cell into which the formula is being entered.
Referencing a Table cell that cannot be represented as a structured reference.
In contrast, if the referenced cell is in the same row as the cell in which the formula is being entered, Excel creates a structured reference, as shown in the next figure.
Referencing a Table cell that can be represented as a structured reference.
CAUTION
When you enter a formula with multiple cells selected, Excel inserts standard references instead of structured references. The workaround is to enter a formula with only one cell selected.
CAUTION
If the SUBTOTAL function is used in a calculated column, each time the Table is filtered, additional rows are excluded from the filter for no reason, even though they are included in the filter criteria. This is a confirmed bug in Excel 2013. The workaround is to not use the SUBTOTAL function in a calculated column.
Referencing Header Rows
You reference a header row by using the [#Headers] special identifier. For example, this reference resolves to the header cell in the column "Category":
=tblRegister[[#Headers],[Category]]
When you reference the header row in formulas, the header row must be enabled, or the formula will return #REF!. This might not seem obvious at first because a Table always has headers, even when the header row is disabled. But when the header row is disabled, there is no range to reference, and thus Excel resolves any reference to it as a reference error.
A formula referencing a visible Table header in the Table named "Web_Table".
A formula referencing a disabled Table header in the Table named "Web_Table".
Referencing Total Rows
You reference the total row in a Table by using the [#Totals] special identifier. For example, this reference resolves to the total cell in the column "Category":
=tblRegister[[#Totals],[Category]]
When you reference the total row in a formula, the total row must be enabled, or the formula will return #REF!.
Unlike a header row, a total row can contain functions. Excel provides some built-in functions that work well with Tables and take into consideration rows hidden with the Table filter function. In addition to using these built-in functions, you can enter any formula or constant into a total row cell, and it can reference any Table part or, for that matter, any cell outside the Table.
NOTE
You cannot use data validation in a total row.
Excel shows a list of built-in functions if you select a total row cell and click the drop-down menu button. These built-in functions utilize the SUBTOTAL function. When you select one of these aggregation functions, Excel places the SUBTOTAL function in the total row cell with the appropriate parameters to produce the desired result. The table below lists the available aggregation functions and their respective SUBTOTAL function numbers.
Total Row Function
SUBTOTAL Function Number
None
Clears the cell
Average
101
Count
103
Count Numbers
Max
104
Min
105
Total Row Function
SUBTOTAL Function Number
None
Clears the Cell
Sum
109
StdDev
107
Var
110
More Functions…
Brings up the Function Wizard dialog
The SUBTOTAL function is not specific to Tables, but because you will see it used in the total row by default to calculate all the aggregation functions, we include a description below. The following is the syntax for the SUBTOTAL function:
SUBTOTAL(function_num,ref1,[ref2]…)
These are the various parts of the syntax:
function_num—A required number in the range 1 to 9 or 101 to 109. If you use 1 to 9, SUBTOTAL ignores filtered rows but not manually hidden rows. If you use 101 to 109, the SUBTOTAL function ignores filtered rows and manually hidden rows.
ref1—A required range or reference you want to subtotal.
ref2—An optional range or reference you want to subtotal. You can add up to 254 additional references.
In addition:
Other SUBTOTAL functions in the range(s) calculated are ignored to avoid double-calculating figures.
Subsequent ranges do not need to be sized identically.
You are not limited to a single column reference.
Referencing Columns
Each column reference is enclosed in square brackets:
tblRegister[Category]
The default behavior of this reference is to use implicit intersections to resolve the reference. When you pass it to a function that expects an array, Excel resolves it to the Table's column.
When you reference multiple columns, you use a second set of square brackets, with a colon separating the two column references, just as you do with regular references:
tblRegister[[Description]:[Category]]
This reference is always resolved to entire data area columns. To constrain the reference to just a row, you use an @ symbol in front of the first reference:
tblRegister[@[Description]:[Category]]
When you AutoFill formulas across columns, Excel treats structured references as relative references and shifts them across columns. However, it does not treat multiple-column references as relative references; it treats them as absolute references. Therefore, to prevent Excel from shifting single-column references, you can construct a single-column structured reference as if it were a multiple-column reference:
tblRegister[[Category]:[Category]]
We cover absolute referencing in structured formulas in more detail later in this chapter.
The [#This Row] or @ Special Identifier
In Excel 2007, to constrain a column reference to the row in which the reference resides, you precede that column reference with [#This Row]:
tblRegister[[#This Row][Description]]
Excel 2010 replaced [#This Row] with @:
tblRegister[@[Description]]
In the context of implicit intersections, the @ symbol is taking away some of the implicit nature of the syntax and stating explicitly "use the value from the column in this row."
Using IntelliSense
IntelliSense ("intelligent sense") is a helpful feature that makes formula entry easier. When you enter text in the formula bar, Excel presents a pop-up list of possible objects or functions you might want to use. You can select an object or function from this pop-up list to insert it into the formula. If you're using the keyboard rather than the mouse to enter a formula, you can use arrow keys to select the desired object or function and then press the TAB key to enter the selection into the formula.
When you enter a formula into a Table's cell, IntelliSense presents all the Table parts that are legal, given the syntax entered to that point.
Structured reference IntelliSense.
Using Absolute Structured Referencing
Absolute referencing and relative referencing are concepts that describe how Excel modifies references to cells as a reference is moved to other locations on a worksheet. With an absolute reference, the target does not move as the reference is moved. With a relative reference, the target moves in the same way the reference moves.
For example, with traditional A1 referencing, placing a dollar sign ($) in front of both of the column letter and the row number establishes that reference as an absolute reference (for example, $A$1). No matter where the reference is moved, the reference will always refer to A1. Without the $ signs, the target shifts with the reference.
A reference can be a mixed reference, with a $ sign in front of the column but not the row, in which case the column is not changed, and the row shifts. If the $ sign is in front of the row but not the column, the row is not changed, but the column shifts.
Structured references are naturally relative references, and there is no obvious way to make them absolute references. With regard to rows, structured references—and Tables in general—do not support the notion of specific rows, nor are they of much concern because, most of the time, formulas are concerned only about other cells in the same row. How Excel manipulates these references when you move or copy formulas depends on the method used. You can move or copy formulas from one column to another by using four different methods:
Copy/paste—Structured references do not change when they are moved to other columns.
Fill handle—Structured references do change when they are moved to other columns.
Fill—Structured references do not change when they are moved to other columns.
Array formulas—Structured references do not change when they are moved to other columns.
Not only does Excel shift a structured reference's target in some cases, when it shifts a target, it does so in a way that is not always useful. Excel doesn't always shift the target in the same direction as the reference; when it reaches the edge of the Table, it moves to the other edge and then continues across the Table again, like a loop. Remembering the circumstances under which references' targets are shifted and how they are shifted can be confusing.
Excel does not provide an obvious way to make a structured reference an absolute reference in order to avoid shifting reference targets. However, you can use a couple tricks to force a structured reference to be an absolute reference so that Excel does not move the target when the reference is moved. One trick is to use OFFSET or INDIRECT, but there are some drawbacks to using these functions. There is a way to create absolute structured references without additional formulas.
To create an absolute structured reference, you can use a multiple-column structured reference that targets a single column:
Table1[[Column1]:[Column1]]
This reference's target ("Column1") will not move, regardless of how the formula is copied. It is equivalent to the traditional A1 reference with absolute column referencing:
$A1
Unlike with standard cell referencing, you cannot toggle the absolute/relative syntax by using the F4 key.
The following figure shows a Table named "tblData" that is used in the following examples.
Sample data and method provided courtesy of Jon Acampora, of www.excelcampus.com.
This example shows how to accomplish a simple summary of a Table by using formulas. It demonstrates why absolute referencing is advantageous when you're developing and copying formulas using structured referencing to reference data in a Table.
In this case
, you want to summarize all units by quarter and color, using the layout shown in the next figure, for the Table named "tblSum".
Formula destination range: B2:E4.
You want to build a formula that can be copied from column to column without some of the targets changing so that you won't have to build multiple formulas. Using the SUMIF function, the formula is:
=SUMIF(tblData[[Color]:[Color]],tblSum[[Color]:[Color]],tblData[Q1 Units])
NOTE
The SUMIF formula’s syntax is:
SUMIF(range, criteria, [sum_range])
where range is the range of cells to compare against the criteria, criteria is the criteria to use to select which cells to sum, and sum_range is the range of cells (optional) to sum if the criteria are satisfied in that row.
You use these references in the formula:
tblData[[Color]:[Color]]—This is the range to compare against the criteria. Because it is a multiple-column reference (even though only one column is referenced), it will not shift as the formula is copied from column to column. Because the SUMIF function expects a range for this parameter, the entire column is used.
tblSum[[Color]:[Color]]—This is the cell that contains the criteria used to compare against the first range. It is a multiple-column reference and will not shift. Because the function expects a single value for this parameter, only the cell's value in the same row that contains the formula is used.
tblData[Q1 Units]—This is the range to sum if the criteria are satisfied in that row. It is a single-column reference and therefore will shift as the formula is copied.
Placing the formula in cell B2 in the "tblSum" Table and using the fill handle to copy it across to the other three columns yields these formulas: