Excel Tables

by Zack Barresse

  Multiple Tables stacked vertically.

  TIP

  To prevent empty rows below a Table from being consumed when you append rows by using TAB or inserting Table rows, you can merge two or more cells in the first empty row below the Table; the merged cells cannot extend beyond the Table’s columns. When merged cells are present and you append rows by using TAB, Excel always inserts rows rather than appending them. Note that you cannot append rows by entering values below the Table (entering a value, pasting, or filling down) when you have merged cells in the first blank row below a Table.

  Excel manages Table header and total rows in a similar manner. When a Table's header row is turned on, Excel shifts down cells only if there is no room for the header row. When the header row is turned off, Excel does not shift any cells, and a row of empty cells remains in its place. When the total row is turned on, Excel always shifts the following cells down one row. When the total row is turned off, Excel always shifts the following cells up one row.

  Awareness and Visibility

  Sometimes it may be difficult to tell whether a range of cells or a single cell is part of a Table. Selecting any cell within a Table results in the display of the contextual {TABLE TOOLS} DESIGN tab. If this tab is present on the ribbon, the active cell resides in a Table.

  NOTE

  Excel displays a contextual tab when you select an object to which that tab is relevant. A single group heading with one or more related tabs below it is displayed in the ribbon. For example, when you select a PivotChart, Excel displays three tabs—ANALYZE, DESIGN, and FORMAT—under the common header PIVOTCHART TOOLS. This book uses the convention {CONTEXTUAL TAB} TAB to refer to such tabs—for example, {PIVOTCHART TOOLS} ANALYZE.

  Other contextual ribbon tab groups are CHART TOOLS, PIVOTTABLE TOOLS, SLICER TOOLS, TIMELINE TOOLS, SPARKLINE TOOLS, DRAWING TOOLS, PICTURE TOOLS, SMARTART TOOLS, INK TOOLS, HEADER & FOOTER TOOLS, and EQUATION TOOLS.

  You might have noticed that the names of ribbon tabs and tab groups are now all uppercase. Microsoft introduced this convention in Office 2013 as part of its user experience that includes Office Online applications.

  Determining Column Names

  Excel Tables can become very large. When you're working with a large Table, you may have trouble identifying columns when the Table's header row is not visible. One way to solve this issue is to freeze the Table's header row (by selecting VIEW | Window | Freeze Panes | Freeze Panes) so that it doesn't scroll. If the worksheet's Freeze Panes option is not enabled, Excel displays the Table's headers in the worksheet header row (where normally it displays the column letters A, B, C, etc.) as long as the active cell is inside that Table, as shown in the next figure.

  With Freeze Panes disabled, Table headers are visible while scrolling through a large Table.

  When the Table's headers are displayed this way, the filter buttons are available.

  CAUTION

  You can toggle the Filter Buttons option ({TABLE TOOLS} DESIGN | Table Style Options | Filter Buttons) to show and hide the filter buttons. However, if the Table’s header row is displayed in the worksheet header, the filter buttons do not immediately reflect the new setting. You must scroll the worksheet to make the Table’s header row visible in order to reset the visibility of the filter buttons to match the option’s setting.

  Accessibility

  For users who have accessibility needs and tools, such as screen readers, many Excel objects support alternative text to help those users understand the content. You edit a Table's alternative text by right-clicking any cell in the Table and selecting Table | Alternative Text.

  Accessing the Alternative Text option by right-clicking a cell.

  You may also want to use alternative text when publishing content to a web page or DAISY-formatted file. When a user hovers the mouse over an object that has alternative text, that text displays.

  Table Names

  Every Table has a name. As you already know, within a workbook, every Table name must be unique. Excel automatically assigns the name "Table" with a number appended for each new Table—for example, "Table1", "Table2", etc. When you copy a Table to another location, Excel uses the name of the original Table with another number appended to ensure that the name is unique within the scope of the workbook; for example, Excel might change "Table2" to "Table21". When you import data from an external database such as SQL Server or Access, Table names default to "Table_QueryName"—for example, "Table_Employees".

  Changing Table Names

  You can change a Table's name at any time. Besides being unique within the scope of the workbook, a Table name must:

  Not contain special characters other than an underscore

  Not contain spaces

  Always start with a letter or an underscore

  You change a Table's name by editing the name displayed in the {TABLE TOOLS} DESIGN | Properties | Table Name text box, as illustrated in the next figure.

  Editing a Table name in the Properties ribbon group.

  As shown in the next figure, Excel displays an error message if the name you enter is not unique or does not follow one or more of these rules.

  An error message for an invalid name.

  When you change the name of a Table generated as the result of a query, the name does not revert back to its default name when you refresh the query. However, if you delete the Table and reconnect the query, the new Table gets the default name.

  When you change the name of a Table, Excel automatically applies the change to any and all formulas that reference the Table by name.

  CAUTION

  Be careful when deleting a Table generated as the result of a query. When you do, Excel converts the references in any formulas referring to that Table to #REF!, and you can’t undo this without manually editing every affected formula.

  Naming Conventions

  A traditional naming convention is called Hungarian notation. Hungarian notation uses the first few letters of a name to identify the type of object (called the type prefix). These letters are set in lowercase. They are usually a shortened form of the object type, such as "wkb" for workbook or "wks" for worksheet. For Tables, it's generally accepted to use either "tbl" (for Table) or "lo" (for List object). Programmers and Excel users have used type prefixes for many years. Although most modern standards no longer use them, they can make object names more intuitive.

  However, you really don't need to use a type prefix for Table names , and doing so can be confusing. Meaningful names such as "Data", "Assets", "Expenditures", etc. can be just as helpful or more so to those using and maintaining workbooks that contain Tables. We recommend using meaningful names over prefixed or Hungarian notation.

  Keep in mind two things when naming Tables:

  With formulas using structured references that reference a Table but are located outside that Table, you need to use Table names.

  You can and should use Table names for referencing Tables when automating Tables using VBA, as discussed in Chapter 9.

  Forward and Backward Compatibility

  Microsoft works very hard to ensure that files will be forward compatible—that is, that files created in older versions of Excel still work with newer versions. Microsoft also tries for backward compatibility—that is, ensuring that files created in newer versions work with older versions. With backward compatibility, for example, a file created in Excel 2013, using the full feature set and tools that may not be available in previous versions, will still be able to open, without errors, in previous versions of Excel. Although some of the newer features aren't available in previous versions of the application, the file won't break or become corrupted when it's opene
d in the older version. In this section we describe how Microsoft has worked to ensure backward and forward compatibility with Excel Tables.

  Formulas

  When Excel 2007 introduced Tables as a reinvented version of lists, one of the major additions was the introduction of structured references. A structured reference accomplishes the same result as a traditional R1C1 or A1 reference: It references a range of one or more cells. However, it uses the names of the Table parts to create the reference. (Chapter 4 covers structured references in detail.)

  What is relevant about structured references in the context of forward and backward compatibility? Changes were made to the structured reference syntax and rules in Excel 2010 that are not compatible with Excel 2007.

  Consider this simple formula in Excel 2010 or 2013:

  =[@Col1]+1

  This formula references a Table column within square brackets, and the @ character instructs Excel to use the current row for that reference. In Excel 2007, you would need to construct this formula differently:

  =Table1[[#This Row],[Col1]]+1

  Note that the Table name is prepended to the reference and the text [@Col1] is changed to [[#This Row],[Col1]].

  In Excel 2010 and 2013, you can enter formulas using either the old or new syntax. Excel automatically converts the old syntax to the new syntax. Also, when you open workbooks with the new syntax in Excel 2007, Excel converts the new syntax to the old syntax so the formulas still work. However, when you're using Excel 2007, you can enter only the old syntax.

  NOTE

  How does Excel 2007 accommodate the new syntax introduced in Excel 2010? The answer is both simple and clever: It doesn’t. Excel 2010 and 2013 do not store formulas using the new syntax in the workbook; rather, they store the formulas using the old syntax but display them using the new syntax. This means Excel 2007 can open an Excel 2010 or 2013 workbook and calculate the formulas. The only limitation is that you can’t enter the new syntax in Excel 2007.

  NOTE

  Excel 2007 and later workbook files (with the XLSX, XLSM, or XLAM extension) are XML-formatted files. With very little effort, you can compress (zip) such a file and then unzip the workbook file, find the XML representing the Table, and inspect it for various Table properties, such as the calculated column formulas. Visit www.exceltables.com for more information and instructions on how to unzip and inspect XLSX, XLSM, and XLAM workbook files.

  Objects

  Excel 2010 introduced a new filtering tool called the Slicer, which is a visual filter for PivotTables and PivotCharts. Excel 2013 also supports the use of Slicers in Tables. (Chapter 6 covers Slicers as they apply to Tables.)

  If you add a Slicer to a Table in Excel 2013 and then open that workbook in Excel 2010, the Slicer stays in the same location, but you cannot use or edit it. Any attempt to change the Slicer's properties renders the Slicer invalid. Instead of getting an error message, users see a shape where the Slicer was, with a message like the one shown in the next figure.

  A Table Slicer opened in Excel 2010.

  The message in the shape isn't as useful as it could be: It doesn't explain is that if you move the Slicer, Excel converts that Slicer into a functionless shape that will no longer function when that workbook is opened in Excel 2013.

  Excel 2013 includes an enhancement to Slicers called Timelines. Timelines are Slicers that handle date such as time data in a more intuitive manner. The next figure illustrates what a Timeline slicer looks like when a workbook is opened in a version of Excel earlier than Excel 2013.

  A Timeline opened in Excel 2010.

  3 Working with Tables

  In this chapter we discuss different methods for working with Tables, including creating tables, resizing them, inserting and deleting rows and columns, moving columns, and exporting data from Tables. These are some of the essential operations you need to become fluent with when working with Tables.

  Creating Tables

  You can create a Table with or without existing data. When you create a Table, Excel prompts for the location of the data and whether there are headers. The next figure illustrates this prompt. If there are no headers, uncheck the box My table has headers, and Excel creates default headers. Excel names the first column "Column 1", the second "Column 2", the third "Column 3", etc.

  Create Table dialog box.

  Creating a Table from the Ribbon

  There are multiple ways to create a Table from the ribbon. One way is to click HOME | Styles | Format as Table. Then you select any Table format from the menu shown in the next figure. Excel then inserts a Table.

  Creating a Table from the HOME tab.

  Alternatively, you can select INSERT | Tables | Table to insert a Table.

  Creating a Table from the INSERT tab.

  Excel Online has a similar INSERT ribbon tab.

  Excel Online INSERT ribbon tab.

  Creating a Table from the Keyboard

  Two access keys instruct Excel to insert a Table: CTRL+T and CTRL+L. To insert a Table using keyboard shortcuts, from the HOME tab press ALT, H, T and then use the keyboard arrows to navigate to the desired Table. You can then press the Enter key to insert a Table that has the selected style. To insert a Table from the INSERT tab, press ALT, N, T.

  NOTE

  Why CTRL+L for creating a Table? The letter L doesn’t seem like a logical choice with Tables. When Microsoft introduced the ribbon in Excel 2007, it removed command bars but allowed the keyboard shortcuts from Excel 2003 to continue to work in 2007 and beyond. (These keystrokes are now called access keys.) Remember that Excel 2003 had Lists rather than Tables. In it, the keyboard shortcut for creating a List was CTRL+L, and Microsoft has maintained that association.

  In versions of Excel other than the US English version, CTRL+L is the only keyboard shortcut to use for creating Tables. CTRL+T does not always work because it is sometimes repurposed for other commands in languages other than English.

  Excel 2003 command bar for creating a List.

  Once a Table is created, a new ribbon tab appears, titled {TABLE TOOLS} DESIGN. It is a contextual tab, which means it's visible when the active cell is within a Table. If the active cell is not part of a Table, then the {TABLE TOOLS} DESIGN ribbon tab is not displayed.

  Many types of objects have contextual tabs, including PivotTables, Slicers, pictures, charts, etc. We discuss some of these in more detail in subsequent chapters.

  Resizing Tables

  You can resize a table either manually or automatically. In addition, you can use a workaround for maintaining blank rows below a Table.

  Resizing a Table Manually

  There are two ways to manually resize a Table:

  Using the sizing handle—The sizing handle is a small black icon located at the bottom-right corner of a Table. By clicking and dragging the sizing handle, you can resize a Table to include additional or fewer rows or to include additional or fewer columns—but you can't do both at the same time. To change the number of rows and columns, you use the sizing handle twice in two separate gestures, or you can use the Resize Table control, as described below.

  Using the Resize Table control on the ribbon—If you click {TABLE TOOLS} DESIGN | Properties | Resize Table, Excel displays a dialog box with a single RefEdit control, as shown in the next figure. Enter the desired range or select the desired range on the worksheet and click OK. You can select any range, as long as at least one header cell and one body cell are selected. The new range must overlap the current Table range.

  Resize Table dialog box.

  Resizing a Table Automatically

  When you enter data in the first empty row below a Table, Excel automatically expands the Table to include that new row of data. The new row is
initialized with all the formats and formulas in calculated columns.

  You can tell Excel whether to automatically include new rows and columns as values are entered into cells adjacent to the Table. To do so, you select FILE | Options | Proofing | AutoCorrect Options | AutoFormat As You Type and then either check or uncheck Include new rows and columns in table. The option, which is an application-level option, is enabled by default.

  AutoCorrect options for Tables, both to include new rows and columns in Tables and to fill in formulas for calculated columns.

  By selecting the bottom-right cell in the data body range (not the total row, if displayed) and pressing the TAB key, you can append a new row. The Include new rows and columns in table option discussed above does not affect this behavior.

  CAUTION

  If a worksheet is protected, regardless of the Insert Rows parameter setting, you cannot insert a new row.

  Maintaining Blank Rows Below a Table

  Placing data below a Table or stacking more than one Table vertically on a worksheet is generally not recommended, although there may be times when the workbook design dictates that you use one of these methods. As a Table grows with new rows of data, it consumes the empty rows below. If there is any data (values, formulas, or another Table) below the Table, Excel starts shifting those cells downward to make room for the new rows once the empty rows are consumed.