Excel Tables
Page 4
Problems start occurring if the data below a Table is aligned with data in those same rows that is not under the Table. Excel pushes down the data directly below the Table, but it doesn't push down the other data, thus causing the data in those rows to become misaligned. If one or more Tables are present below the Table being expanded, and those Tables extend beyond the left or right edges of the Table being expanded, Excel does not sift anything downward under any circumstances.
That said, if data or one or more Tables are placed under a Table, you might want to maintain some empty rows between the bottom of the expanding Table and the first row of the other data below the expanding Table. In this case, you can merge one or more cells in the row immediately below the last row of the expanding Table. As long as the merged cells do not extend beyond the left or right edge of the Table, Excel shifts down the merged cells, along with every cell below and within the left and right Table boundaries when inserting or appending new rows. As a result, the empty rows are not consumed because the merged cells are above them, and they cannot be consumed.
Merged cells below a Table.
If merged cells extend beyond the left or right edge of the Table above, Excel gives you the warning message shown in the next figure.
Insert row error caused by merged cells.
Moving Columns
To rearrange columns in a Table, you select an entire column, including the header and total rows, if they are visible. Then you hover the cursor over any edge of the selection and click and drag the column left or right, into the desired position. A bold green vertical line appears at the closest insertion point to the mouse, as shown in the next figure.
Moving the column "Cost" between the columns "Item" and "Units".
When the move is complete, Excel adjusts any Table references in formulas. If a column contains formulas that reference a range of columns and that column is moved to a location within that range, Excel gives you the circular reference warning shown in the next figure.
Circular reference warning.
Inserting Rows and Columns
There is no option to insert rows or columns from the {TABLE TOOLS} DESIGN ribbon tab. Instead, you insert rows and columns by clicking HOME | Cells | Insert and then choosing an option from the menu shown in the next figure.
Inserting a column or row from the HOME tab.
Another way to insert rows and columns is to right-click the desired insertion location within a Table to display the context menu and select Table Columns to the Left or Table Rows Above, as shown in the next figure.
Inserting a column or row from the right-click context menu.
When you insert rows or columns in a Table, Excel constrains the new rows or columns to within the Table boundaries, as long as the current selection is within the Table's boundaries. (Excel uses the current selection to determine where to insert the new rows or columns.) If entire rows or columns are selected, Excel shifts entire rows or columns to make room for the new rows or columns.
When you insert rows, Excel assumes that the number of new rows desired is the number of rows selected, regardless of how many columns are selected. When you insert columns, Excel assumes that the number of new columns desired is the number of columns selected, regardless of how many rows are selected.
To append a new row at the end of a Table, select the bottom-right cell in the data range area and press the TAB key.
CAUTION
Excel does not allow you to insert new rows or columns on a protected sheet.
When you insert rows, if there are merged cells anywhere below the Table that extend beyond the left or right edges of the Table, Excel displays a warning, as shown in the next figure, that the action will cause cells to unmerge.
Inserting rows causes merged cells outside the Table dimensions to unmerge.
If Excel cannot insert rows or columns due to structural designs (for example, one or more Tables below extend beyond the expanding Table's left or right edges), Excel cancels the action and displays the message shown in the next figure.
Message displayed if an insertion cannot be completed due to structure conflicts.
NOTE
When you insert columns with the Filter Button disabled ({TABLE TOOLS} DESIGN | Table Style Options | Filter Button), new columns display the AutoFilter drop-down control anyway.
Deleting Rows and Columns
There is no option to delete rows or columns from the {TABLE TOOLS} DESIGN ribbon tab. Instead, you delete rows and columns by clicking HOME | Cells | Delete and then choosing an option from the menu shown in the following figure.
Deleting a column or row from the HOME tab.
Another way to delete rows or columns is to right-click the desired deletion location within the Table to display the context menu and select Table Columns or Table Rows, as shown in the next figure.
Deleting a column or row from the right-click context menu.
As with inserting, when you delete rows or columns in a Table, Excel constrains the deleted rows or columns to within the Table boundaries, as long as the current selection is within the Table's boundaries. (Excel uses the current selection to determine where to delete the new rows or columns.) If entire rows or columns are selected, Excel deletes entire rows or columns.
When you delete rows, Excel assumes that the number of rows to delete is the number of rows selected, regardless of how many columns are selected. When you delete columns, Excel assumes that the number of columns to delete is the number of columns selected, regardless of how many rows are selected.
If Excel cannot delete rows or columns due to the structural design (for example, one or more Tables below that extend beyond the expanding Table's left or right edges), Excel cancels the action and displays the message shown in the next figure.
Message displayed if a deletion cannot be completed due to structure conflicts.
Converting a Table to a Range
Limitations with Tables sometimes prevent certain activities, so converting a Table to a range may be advantageous. For example, duplicate header values are not allowed in a Table, but you can use them if you convert a Table to a range.
There are repercussions when you convert a Table to a static range. All structured references to the Table or any of the Table parts in any formulas or names inside and outside the Table are converted to standard (absolute) cell references. No references break.
For example, this formula with structured references:
=[@Units]*[@Cost]
Is converted to this:
=Sheet1!$H5*Sheet1!$I5
The only exceptions are external and static (text) references, such as when you use the INDIRECT function. Excel doesn't see these references at conversion time and leaves them as-is.
CAUTION
Once a Table is converted to a range, the structured references that are converted to standard references are not converted back to structured references if the range is converted back into a Table. There is no workaround for this.
Before you convert a Table to a range, consider that there may be an alternative solution. For example, you can create two headers that appear to be identical by following one of them with a space character.
Two seemingly identical column headers, but the second one has a space character at the end, shown selected in the formula bar.
Removing Duplicate Rows
Excel 2007 introduced the ability to remove duplicate rows of data from a worksheet. While you can apply this functionality to any data, it's easiest to do when the data is in a Table because Excel knows exactly where the headers are. To start the process of removing duplicate rows, select DATA | Data Tools | Remove Duplicates. Excel asks you which columns to use to determine
when a row is a duplicate of another row. It keeps the first unique row, deletes all subsequent duplicate rows, and shifts up the rows below the deletion.
Exporting Table Data
There are two methods for exporting data from a Table: Export to SharePoint as a List or export to Visio as a PivotDiagram. For both of these options, you must have the respective application. For example, you must have Visio in order to export a Table to a PivotDiagram.
Exporting to SharePoint
SharePoint is the default standard sharing service used with Office 365 and Power BI. To share a Table using SharePoint, you select a range in the Table and select {TABLE TOOLS} DESIGN | External Table Data | Export. Then you select Export Table to SharePoint List to begin the export process.
Exporting data to a SharePoint list.
Excel displays a wizard that takes you through a two-step process. First, you enter the SharePoint URL—for example, http://user.sharepoint.com/TableTools/. Then you specify whether the SharePoint list is read-only and enter a display name and description.
Exporting to a SharePoint list, step 1.
To use this feature, you must log on to the SharePoint site by providing your email address and password.
TIP
If you’re using Office 365, you should sign in to Excel 2013 using the same account you use to access your SharePoint site.
Authentication in exporting to a SharePoint list.
Once you are authenticated, the second step of the wizard verifies the data. Excel asks you to confirm the data type of each column. If any issues require adjustment, you must cancel the process and make the changes before starting the export again.
Exporting to a SharePoint list, step 2.
Excel displays one of the messages shown in the next two figures, indicating whether the action was successful.
Successful export to a SharePoint list.
Unsuccessful export to a SharePoint list.
The connection is an OLEDB connection that you can view by selecting {TABLE TOOLS} DESIGN | External Table Data | Properties. A connection string looks something like this example:
Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="";ApplicationName=Excel;Version=12.0.0.0
CAUTION
Once a Table has been exported to a SharePoint list, you cannot change the data in the Table. If you make any changes, they will be lost (without warning) when the Table is refreshed.
For a Table with an external connection, you get a different right-click menu, as shown in the next figure.
Right-click menu for a Table with an external connection.
Depending on the type of connection, some controls may be disabled. For instance, the control Edit DAX is disabled for a SharePoint list but is enabled for a Table linked to a SQL Server query or another OLAP data source.
Exporting to Visio
You can export a Table to a Visio PivotDiagram. A PivotDiagram is a collection of shapes in a hierarchical tree structure. Each node in the tree is broken down into subnodes, based on the Table data. PivotDiagrams are similar to Excel PivotTables in that they allow you to view the data from different angles in a visual arrangement. You can highlight certain areas, as with conditional formatting in Excel. You must have Visio installed in order to use this function.
To export a Table to a Visio PivotDiagram, select {TABLE TOOLS} DESIGN | External Table Data | Export | Export Table to Visio PivotDiagram. (If Visio is not installed, this selection is disabled.) You must save the workbook before you can export an Excel Table to Visio.
The Export Table to Visio PivotDiagram ribbon command.
Once the Table is exported to Visio in a PivotDiagram, you get a task pane, as shown in the next figure, with the Table columns as categories or totals.
Visio's PivotDiagram task pane of an exported Excel Table.
You can use other data sources for PivotDiagrams, including:
An Access database
A SharePoint list
A SQL Server database
SQL Server Analysis Services
OLEDB or ODBC data sources
If you try to export a Table to a Visio PivotDiagram, and the workbook is not saved, Excel gives you the warning shown in the next figure. To get around this, just save the workbook before attempting to export to Visio.
An unsaved workbook does not export to Visio.
You can manipulate a Visio PivotDiagram in many ways that are beyond the scope of this book, such as visually summarizing data.
Table Options
This section discusses some options that are especially important to understand when dealing with Tables. Referencing the header and total rows can be difficult if you're not familiar with how the Header Row and Total Row options work. Learning about these special Table designations—as well as First Column and Last Column—will help you understand the information in this book. In addition, Filter Button is unlike any other control in Excel and has functionality that cannot be replicated anywhere else.
Header Row
You use the Header Row option to show or hide the header row. If this option is checked, you can reference the header row in formulas; otherwise, if you try to reference the header row, the formula returns an error message. You'll learn more about this in Chapter 4.
Total Row
You use the Total Row option to show or hide the total row. If this option is checked, you can reference the total row in formulas; otherwise, if you try to reference the total row, the formula returns an error message. You'll learn more about this in Chapter 4.
First Column/Last Column
The First Column and Last Column options enable you to use custom formats on the first and last columns. You'll learn more about this in Chapter 7.
First and last columns of a Table.
Filter Button
Filter Button is a new control in Excel 2013. It is completely different from the AutoFilter functionality, which you could use in previous versions of Excel by using access keys. Tables were revamped in Excel 2007, and since then, you've had the ability to set an AutoFilter for the worksheet and also set an AutoFilter for each Table. It is therefore not typically a good idea to have multiple Tables structured horizontally, since filtering one Table will hide the entire sheet row. There may be times when structuring multiple Tables horizontally is beneficial, but you should do this with caution.
CAUTION
When you structure multiple Tables horizontally, if a filter is applied to one of the Tables, it filters the entire sheet row, so it affects the other Tables aligned horizontally with it.
Likewise, when you structure multiple Tables vertically, you should also use caution. Although you’ll avoid the filter issue in this case, you may come up against other obstacles, such as row insertion errors.
Filter Button provides functionality that you can't get any other way:
It removes the AutoFilter drop-down controls from the Table.
It causes the AutoFilter applied to the Table to remain in effect.
This could be beneficial when you're creating workbooks or reports for others to consume and you want them to use only the slicer functionality you've created. Or perhaps you want to leave a filter applied and not allow someone to change it. The keyboard shortcut for this is ALT, JT, {ZL}, B.
The functionality just discussed for Filter Button is different from the traditional functionality you get by toggling the Filter command. If you select HOME | Editing | Sort & Filter | Filter, you can turn the AutoFilter on or off. If you apply a filter, Excel makes your Table unfiltered and removes the AutoFilter drop-down controls. You can accomplish the same thing by using the access key ALT+D, F, F.
CAUTION
Using the access key ALT+D, F, F toggles (that is, enables/disables) Filter Button. People often think of this as a bug. When you’re turning off filtering for a Table with this action, there are no AutoFilter drop-down controls to hide, which makes this control useless, hence the control being disabled.
Excel 2013 provides no access key for the Filter Button functionality (that is, taking away the drop-down controls but leaving the current filter applied). You can access this functionality only via keyboard shortcuts, through the ribbon, or by using VBA, which is covered in Chapter 9.
4 Table Formulas
This chapter covers referencing Tables in formulas, both internal (within the Table) and external (outside the Table). It also covers some methods for dynamically referencing Table ranges.
Referencing Tables
Structured referencing is only as good as the naming conventions used to name a Table and the columns in the Table. It makes range references more readable—as long as both the Table names and Table column names are meaningful. If a Table is named "Table1" and the columns are named "Column 1", "Column 2", etc., then a structured reference using those names will not be much better than traditional A1 or R1C1 referencing. The best names are meaningful and of a reasonable length—not too short and not too long. Name length is a matter of preference, and your preference will develop as you learn more about the syntax of structured referencing.
Understanding how structured references work starts with understanding their syntax, which is shown in the next figure.
Structured reference syntax.
The following descriptions correspond to the numbers in the figure: