=SUMIF(tblData[[Color]:[Color]],tblSum[[Color]:[Color]],tblData[Q1 Units])
=SUMIF(tblData[[Color]:[Color]],tblSum[[Color]:[Color]],tblData[Q2 Units])
=SUMIF(tblData[[Color]:[Color]],tblSum[[Color]:[Color]],tblData[Q3 Units])
=SUMIF(tblData[[Color]:[Color]],tblSum[[Color]:[Color]],tblData[Q4 Units])
Note that the first two references did not shift but the last reference, sum_range, did shift one column to the right for each column to the right the formula was copied.
NOTE
The technique described here requires the use of the fill handle to copy the formulas across columns. Using copy and paste will not shift the last parameter’s target.
Disabling Structured Referencing
To avoid structured references altogether, you can select FILE | Options | Formulas | Use Table names in formulas and turn off the Use table names in formulas option.
Disabling structured references by turning off the Use table names in formulas option.
Structured Reference Operators
As with standard cell referencing, several operators work identically with structured references:
Colon ( : )—This is the range operator, which joins everything between and including the two references.
Comma ( , )—This is the union operator, which joins everything specified in the two references.
Space ( )—This is the intersection operator, which joins everything overlapping between the two references.
The following table provides some examples using the three different reference operators and the equivalent standard reference. The Table name is "Table1", the Table header is in row 1, and the data body range starts in row 2 and ends in row 20. Columns are in this order, beginning in column A: "OrderDate", "Region", "Rep", "Item", "Cost".
Reference
Refers to
Type
=Table1[[OrderDate]:[Rep]]
A2:C20
Range
=Table1[Region],Table1[Item]
B2:B20,D2:D20
Union
=Table1[[Rep]:[Units]] Table1[[Item]:[Cost]]
D2:E20
Intersection
Special Characters Used in Structured References
When a column name contains any of the following special characters, the name must be enclosed in an extra set of square brackets:
Space ( )
Line feed
Carriage return
Comma ( , )
Colon ( : )
Period ( . )
Left bracket ( [ ) †
Right bracket ( ] ) †
Pound sign ( # ) †
Single quotation mark (apostrophe) ( ' ) †
Quotation mark ( )
Left curly bracket (brace) ( { )
Right curly bracket (brace) ( } )
Dollar sign ( $ )
Caret ( ^ )
Ampersand ( & )
Asterisk ( * )
Plus sign ( + )
Minus sign ( - )
Equal sign ( = )
Greater than ( > )
Less than ( < )
Division ( / )
The characters marked in this list with a † have special meaning and so require an escape character in order to be properly recognized. The escape character is an apostrophe ( ' ):
[Order '#]
When a column name contains any of the special characters listed above and the @ symbol is used to keep the target of the reference on the same row as the reference, an added layer of square brackets surrounding the text is required, as shown below with the column name "$ Amount":
[@[$ Amount]]
Dynamic Referencing
You use dynamic referencing to create named ranges that are dynamic (that is, change depending on conditions) and that can be used in conditional formatting and validation lists. Here are some examples:
Creating a validation list using a column of values in a Table
Creating conditional formatting rules based on looking up a cell's value in a Table to get a value in another column (VLOOKUP) and comparing the result to a specific value
Summing a specific month's sales figures in a Table, where multiple columns contain sales data, and each column contains a single month's sales figures
Named ranges, which are also called named formulas, are useful even when they're not dynamic because Excel does not allow structured referencing in conditional formatting rules or validation lists. The following example demonstrates the steps for creating a simple named dynamic range that is used in a validation list, based on a column in a Table:
1. Create a new Table and name it "tblValues". Name one of the columns "Value" and enter two or more unique values in the column.
2. Select the data area range portion of the "Value" column. If the header or total rows are showing, do not select them; select just the data body range of the column.
TIP
You can move the mouse over the top edge of the Table’s column header (not the worksheet header) to turn the cursor into a bold down arrow. You can click there to select the entire Table data area range column (excluding the header and total rows). You can click again to select the Table’s column, including the header row and total row. Subsequent clicks toggle between these two selections.
3. Select FORMULAS | Defined Names, Name Manager (or use the keyboard shortcut CTRL+F3) to open the Name Manager dialog box.
Name Manager dialog box.
4. Click the New button to open the New Name dialog box.
New Name dialog box.
5. Enter "ValidValues" in the Name text box. Note that the structured reference to the Table's "Value" column is already entered into the Refers to text box. Click OK.
6. Select the cell to which the validation list is to be applied and select DATA | Data Tools | Data Validation | Data Validation. Select "List" from the Allow combo box, enter "=ValidValues" in the Source text box, and click OK.
Creating a validation list using a named dynamic reference.
A validation list that contains the values from the Table's "Value" column is now in place in the selected cell.
Using the Current Row Number
The following formula displays the row number in a Table where the row number is relative to the first row in the Table and not the worksheet's row number:
=ROW()-ROW(Table1[[#Headers],[Column1]])
The row number can also be useful when you're referencing the same row in another Table. The formula works by subtracting the worksheet's row number in which the Table header resides from the worksheet's row number in which the formula resides. The ROW formula returns the worksheet row number of the first row of the range reference passed to it, or the worksheet row number of the row in which the formula resides if no range reference is passed to it.
If the first data row is always in a specific worksheet row, a more direct approach is to use a constant instead of passing the Table's header row to the ROW function:
=ROW()-1
Calculating a Running Total
The following formula displays a simple running total of another column in the same Table:
=SUM(tblRegister[[#Headers],[Amount]]:[@Amount])
This formula assumes that the header row is enabled; the SUM function ignores the text value in the header. If the header row of the referenced column is not enabled, the above example returns #REF!. To avoid this, reference the first cell of the Table c
olumn by using the INDEX function:
=SUM(INDEX([Amount],1):[@Amount])
CAUTION
You can implement this example by using standard referencing as =SUM(D$2:D2), assuming that Amount is in column D. You use absolute row referencing to keep the first part of the range absolute, while the second part shifts as the formula is copied down rows. However, there is a known issue when using absolute standard row referencing (A$1) in a Table: When new rows are added, Excel mangles the reference and the column formatting, rendering the formula in the new row and preceding rows in error. The workaround is to find an alternative approach that avoids absolute standard row referencing, as demonstrated in the preceding example.
If the amounts used to calculate the running total are in two columns, one for debits and one for credits, then the formula is:
=SUM(INDEX([Credit],1):[@Credit])-SUM(INDEX([Debit],1):[@Debit])
Using structured references can have a negative impact on calculation performance on large data sets, as in the example above. In the context of these examples, a large data set is anything over around 100,000 rows of Table data on most modern computers. The problem lies in how structured references are used: Each row sums all the rows above, including the same row, to get the running total for that row.
The next formula example utilizes standard cell referencing to improve calculation performance by working with only three cells in the row above and in the same row; this avoids repeated summing of thousands of rows. From the previous formula, it is assumed that Credit is in column C, Debit is in column D, the running total calculation is in column E, and the headers are in row 1. The formula in cell E2 (the running total in the first data row) is:
=N(E1)+C2-D2
When copied down the length of the column, the formula takes the running total from the previous row, adds the credit, and subtracts the debit on the same row to get a new running total for that row. This assumes that debits are entered as positive values, as they are on an accounting ledger.
Why use the N function? In the first row, the formula uses the header value in a numeric calculation; if the header is a text value, the formula produces a #VALUE! error. The N function solves this problem by taking the parameter passed to it and returning the same value if the parameter is numeric or zero if the parameter is text.
Another way to solve this problem is to use the SUM function, which ignores text values (effectively treating them as zero values):
=SUM(E1,C2,-D2)
5 Pivoting with Tables
Tables make great data sources. They must adhere to specific rules, such as single-row headers and unique header values, that ensure good data structuring. Tables are therefore ideal for use as data sources for PivotTables or other reporting methods. In this chapter we explain why.
Creating PivotTables
Because Tables can expand and contract with data, they make excellent data sources for PivotTables. For example, when you type into a blank row directly beneath a Table, the Table expands to include the new row. As the Table expands and contracts, Excel maintains the Table's name and related range references. This feature makes it convenient to reference the Table from external locations, such as a PivotTable.
CAUTION
Excel does not allow a Table to expand or contract on a protected worksheet, and it produces no error if you try to make such a change.
Creating a PivotTable
To create a PivotTable using a Table's data, select any part of the Table and then select {TABLE TOOLS} DESIGN | Tools | Summarize with PivotTable.
Create a PivotTable by selecting {TABLE TOOLS} DESIGN | Tools | Summarize with PivotTable.
The Create PivotTable dialog box appears. By default, the Table's name is entered as the source, and the destination is directed to a new worksheet.
Create PivotTable dialog box.
Refreshing Manually
Excel does not automatically refresh PivotTables; you have to refresh them manually. To refresh a PivotTable, select any cell in the PivotTable and select {PIVOTTABLE TOOLS} ANALYZE | Data | Refresh or right-click any cell in the PivotTable and select Refresh.
PivotTable Refresh button on the ribbon.
PivotTable Refresh option on the right-click context menu.
Refreshing Automatically, Using Automation
Using VBA (Visual Basic for Applications) for automation is a powerful way to expand Excel's functionality. Chapter 9 extensively discusses automating Tables using VBA. This section explains how to use VBA to trigger an automatic PivotTable refresh. Implementing this solution does not require any knowledge of VBA, programming, or the Excel Object Model.
To implement this solution, right-click the worksheet tab and select View Code, as illustrated in the next figure.
Worksheet right-click menu.
Copy and paste this code into the code window that appears:
Private Sub Worksheet_Activate()
Dim PT As PivotTable
For Each PT In Me.PivotTables
PT.PivotCache.Refresh
Next PT
End Sub
Press CTRL+F11 to return to Excel.
The code runs when the worksheet is activated. It refreshes all PivotTables on the worksheet.
CAUTION
If the worksheet that contains the PivotTable is protected, the VBA code produces an error. A workaround is to set the UserInterfaceOnly parameter of the Protect method to True when protecting the worksheet.
NOTE
VBA code is not allowed in the XLSX file format. As soon as VBA code is added to a workbook, the workbook must be saved in the XLSM format. Other formats allow VBA code, but that topic is beyond the scope of this book.
Power BI
Power BI (Business Intelligence) is a new offering (currently in preview at this writing) for Office 365 ProPlus and SharePoint subscriptions. Through Excel and the power of the cloud, Power BI provides powerful, large-scale BI analysis services to many more users. Its publishing features give Excel users more robust and varied distribution paths and venues to share the results of their analytical efforts.
Beginning with Power Pivot, which was initially released for Excel 2010, Microsoft has introduced several "Power" projects, all of which are components of Power BI: Power Query, Power Map, Power Pivot, Power View, and Power BI Sites. For more information, visit http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx.
NOTE
This book covers Power Pivot and Power Query only as they relate to Tables.
Power Pivot
Understanding how Tables work with Power Pivot requires a basic understanding of what Power Pivot is and how it's used. Introduced for Excel 2010 as an add-in, Power Pivot consumes data from any number of connections to data sources without having to adhere to Excel's row limitations. The data consumed is maintained in the Power Pivot Data Model and delivered to consumers such as PivotTables and PivotCharts via extremely fast queries. In essence, Power Pivot gives Excel users access to very large data sets from multiple sources while providing powerful and fast transformation and query functions to create Excel-friendly tables and charts for analysis and presentation.
NOTE
Beginning with Excel 2007, Excel’s row limit is 1,048,576. If you open a pre-2007 Excel file (XLS), it will retain the old limit (65,536 rows) unless you save it in one of the new file formats, close it, and reopen it.
Prior to Microsoft’s announcement of Power BI, Power Pivot was known as PowerPivot (no space). To keep it more in line with the new suite of add-ins (Power Query, Power Map, Power Vi
ew), Microsoft renamed it Power Pivot. However, the add-in is still named without a space, and it shows up that way on the ribbon.
Microsoft made significant improvements to the Power Pivot add-in with the release of Excel 2013. One of the most significant changes was in the in-memory analytics engine known as VertiPaq, or xVelocity, that drives the Data Model. VertiPaq uses compression and optimized access algorithms to load large amounts of data quickly and efficiently and to make the requested data available to data visualization objects such as PivotTables in worksheets.
AMO, ADOMD.NET, and the OLEDB provider all facilitate data access; they are all installed with the add-in and support connections to the Data Model. In addition, the OData format is a data feed provider for importing and refreshing data.
One of the most powerful characteristics of Power Pivot is its ability to simultaneously connect to multiple disparate data sources, including Excel Tables. Excel Tables can also consume data queried from a Data Model using DAX (described later in this chapter).
NOTE
The terms Power Pivot and Power Pivot Data Model are often interchanged and roughly refer to the same thing. Specifically, Power Pivot is the entire add-in and the user experience exposed to the user, and the Power Pivot Data Model (or just Data Model) is the entire collection of data, relationships, and measures created and maintained by Power Pivot.
NOTE
You cannot use a standard range as a source type for the Data Model. The source must be in Table format.
In Excel 2010, if you're working with a Power Pivot–based workbook, the Power Pivot add-in must be installed. It's a free download for all editions of Excel 2010. To install Power Pivot, you must have administrative privileges, so you may need IT support in a corporate environment. At this writing, there are two versions of Power Pivot available: 10 and 11. We recommend using the latest version, 11, with Excel 2010. The VertiPaq engine is included only in the add-in for Excel 2010 that is required to interact with the Data Model.
Excel Tables Page 6