Excel Tables Read online

  1 What Are Tables?

  Microsoft Excel is a versatile application that millions use to solve many different kinds of problems and perform everyday tasks. We've seen it used to calculate math problems, evaluate complex choices, and analyze data. It's been used to create grocery lists and manage checkbooks. Some use it to create and manage business plans, generate forecasts, and present performance reports.

  One of the most common uses of Excel is to create, maintain, and analyze a simple two-dimensional list—anything from a list of financial transactions to the standings and statistics of the sports teams in a league. You typically create, manage, and analyze data by using a database such as Access or FileMaker, but Excel makes it much easier to handle these tasks. Unlike more database-oriented applications, Excel exposes functionality to analyze a list's data by using sorting, filtering, graphing, and other tools in a more intuitive and flexible fashion, thus making your experience more dynamic and, ultimately, more productive with less effort.

  If you have a list of data that has a header row and one or more data rows, you can turn that data into an Excel Table that has additional functionality and rules that help you better manage and analyze the data in the Table. When you identify a list of data as an Excel Table, Excel provides tools to, among other things, format, sort, and filter the rows of data more easily than if the list of data were not identified as a Table.

  NOTE

  It is important to note here that the only real difference between a simple list of data and an Excel Table is the designation of that list as being an Excel Table versus not being an Excel Table. Designating a list of data as a Table makes additional Table functionality available. You can convert a list of data into an Excel Table and back to a simple list of data without any loss of data. A simple list and an Excel Table are essentially synonymous from a data values perspective.

  Who Uses Tables?

  Any list of data that has multiple columns, a header row, and an optional total row at the bottom is an excellent candidate for Excel Table designation. Excel Tables are versatile and useful to anyone using spreadsheets to maintain lists of data. Identifying a list of data on a worksheet as an Excel Table instantly adds both additional functionality for maintenance and analysis as well as rules to help keep the data organized and clean. The fact that you can convert a list of data into an Excel Table and back into a simple list of data without any loss of data makes trying out Tables a very easy proposition.

  If you're importing data from an external source such as an online analytical processing (OLAP) cube, the default destination is an Excel Table. If you're using one of the newer business intelligence (BI) tools like Power Query, the results of queries are placed in Excel Tables. In essence, Microsoft realizes the universal appeal of a robust table function and is leveraging Excel Tables as much as possible and wherever that kind of functionality makes sense.

  Why Use Tables?

  Tables don't overwrite existing data or create new data. An Excel Table encapsulates data, extends functionality and visual appearance, and enables faster implementation of solutions. Some of the advantages include:

  Data is structured in rows and columns, without spaces.

  New rows inserted into the Table are automatically formatted the same as the other rows.

  Changes to formulas and formatting in a cell are automatically applied to the entire column.

  An optional total row can be displayed, and each column's "total" value can be any of a number of different aggregate functions.

  Special Table formatting styles instantly format an entire Table with banded rows and/or columns.

  These dynamic features and many others are readily available with Excel Tables and are covered in this book.

  History of Tables

  Excel 2003 introduced the concept of "Lists," which had limited functionality. With Lists, even though a lot of the currently available Excel Tables functionality was available in one form or another (sorting, filtering, formatting), it was not presented as part of the List itself and therefore was rather elusive for the average Excel user.

  Excel 2007 redefined Lists as Excel Tables. Microsoft rethought how Tables could be most effectively used and put significant effort into this redesign. Not only did Tables become more user friendly, they became much more prominent in Excel's user experience model: They became the default downstream presentation form for OLAP cube queries and Power Query.

  Microsoft continues to provide new functionality and refinements in Tables. Excel 2013 includes a number of changes and additions to Tables, as described in this book. Tables have become a mainstay in many versions of Excel, from the desktop version to Excel Online (the browser-based, free version of Excel). Excel Tables are here for the long term.

  2 Table Behavior and Anatomy

  In this chapter we discuss Table anatomy and behavior—the cornerstones in working with Tables and the rules every Table adheres to. This chapter also covers naming conventions and compatibility issues. Not all versions of Excel are the same, so this chapter explains things you should be aware of in the various versions.

  Table Anatomy

  There are three main parts to an Excel Table: the header row, the data body range, and the total row. Together they represent the entire Table. The following sections discuss these three parts. The following sections also cover calculated columns and the sizing handle.

  The Header Row

  The header row is the topmost row of a Table. You can optionally hide it from view, but the default is for the header row to be visible. The value in the header row for a column defines that column's name, which is also called the field name. To maintain consistency and allow advanced formula references (structured references), all header row values must be constants and not formulas. If a header can be changed in the course of working with that Table, you can use a validation list to present the user with a list of possible header values. When a header is changed, all formula references to that column are automatically adjusted.

  In addition to defining the column or field names, the header row is used to display a filter button (a downward-pointing black triangle) over each column. When you click the filter button, Excel opens a dialog that presents many options for filtering and sorting the Table using that column. The next figure highlights the header row and filter buttons in a Table.

  The header row of a Table.

  Table header values must be unique within a Table. When you enter a header value that is the same as another header in the same Table, Excel appends a number to the end of the rightmost header value to make it unique. For example, if there is a column with the header "ID", if you enter "ID" into another column to the left of the existing value, the rightmost header value, the original "ID", changes to "ID2". If instead you enter the new "ID" to the right of the existing "ID" header, Excel changes the new header to "ID2".

  TIP

  Formulas aren’t allowed in the header row. However, you can get around this limitation. To use formulas in place of a Table’s header row, you hide the header row and then enter the formulas in the empty row above the Table. Note that you can’t display the filter and sort drop-downs without making the Table’s header row visible, so this isn’t a very practical solution for Tables you’re using for analysis that involves heavy sorting and filtering.

  The Data Body Range

  The data body range is the area of a Table between the header row and the total row, where data is displayed, entered, and maintained. If there is no data, the Table displays one empty row for entering data. The number of rows in a Table's data body range is limited only by the number of rows in a worksheet. The next figure highlights the data body range in a Table.

  The data body range of a Table.

  The Total Row

  The to
tal row is the bottom row of a Table. You can optionally show this row, but the default is to hide the total row. When you select a cell in the total row, a drop-down menu control appears; you can click this drop-down to see a list of possible built-in aggregate functions. The functions aggregate only the visible cells in the column directly above the total row. In addition to using the built-in functions, you can enter into any total row cell custom functions that reference any cells inside or outside the Table. The next figure highlights the total row in a Table.

  The total row in a Table.

  NOTE

  You aggregate visible cells by using the SUBTOTAL function, which can aggregate all cells in a range or only the visible cells in a range. This feature is useful when you’re filtering rows and want to see the aggregation of only the rows that match the filter criteria. Chapter 4 discusses this feature in more detail.

  Calculated Columns

  A calculated column is a column that contains a single formula applied to the entire data area part of that column. A column that contains different formulas in different cells is not a calculated column.

  In a calculated column, any formula entered is automatically assumed to be the new default formula and is applied to all cells in the data area range of the column. In a column that contains formulas or values but is not a calculated column, entering a formula in any cell changes only that cell's formula. An AutoCorrect Options button appears in the bottom-right corner of the cell, with an option to apply the formula to the entire column; you can select this option to enable the calculated column and overwrite everything in the column with the formula you enter.

  Excel maintains the formulas in calculated columns and uses them in new rows.

  TIP

  There is no easy way to determine whether a column is a true calculated column. The only way we currently know to make this determination is to change a formula in the column and see if Excel presents the AutoCorrect Options button. If you change a formula and then see an option to apply the formula to the entire column, select that choice, and the column is then a calculated column.

  Excel is smart enough to know whether a formula has actually been changed and when it has just been reentered, so, for the purposes of this tip, you have to change the formula, not just re-enter it.

  The Sizing Handle

  In the bottom-right corner of a Table is a small icon called the sizing handle. This icon both shows where the Table ends and allows you to increase or decrease the Table's range: Just drag the handle.

  The sizing handle of a Table. Click and drag it to resize a Table.

  NOTE

  You can also resize Tables from the ribbon by clicking {TABLE TOOLS} DESIGN | Properties | Resize Table. The Resize Table dialog appears, and in it you can enter a revised range for the Table.

  CAUTION

  You can resize Tables only if the worksheet is unprotected.

  Table Behavior

  Before you create a Table, it is important to understand what works with a Table and what does not. Several limitations affect how Tables appear and how they function. These limitations are in place to support the functionality that Excel's Table functions provide. For example, Excel does not allow you to give two Tables in a workbook the same name, as doing so would prevent you from referencing the Tables within formulas.

  Table Limitations

  When a list of data is defined as an Excel Table, Excel does not allow that Table to:

  Have headers in more than one row

  Have totals in more than one row

  Include duplicate column header values

  Include multiple-cell array formulas (but single-cell entered array formulas are allowed)

  Have any cells overlap any other Table

  Be named the same as any other Table in the workbook

  Excel also does not allow you to save a workbook with any Tables as a shared workbook. However, you can share an Excel Table by using SharePoint.

  As long as you follow these rules, Tables behave as any normal range, with a few subtle exceptions that are discussed in this book.

  Basic Table Behavior

  A Table is basically just a list of data that has been designated as an Excel Table. With that designation you get some additional functionality designed to make working with the list of data easier and more productive. The following are some examples of the differences between a list of data and an Excel Table:

  Feature

  List of Data

  Excel Table

  Header row

  You have to manually insert header rows above the data. You can use more than one row as headers, and they are not required to be unique.

  Excel creates one header row automatically. Each column header must be unique in the Table. You can hide or show the header row by clicking a check box in the ribbon.

  Data rows

  You insert and delete data rows by selecting a range and performing the action. Excel blindly follows instructions, regardless of the list's boundaries, so undesirable changes to the worksheet's layout could occur.

  You insert and delete data rows by using Table-aware commands. Excel performs the actions while constraining the effects to the Table region and ensuring that entire rows are affected rather than just partial rows. Excel goes to great lengths to ensure that formulas remain consistent throughout a Table.

  Total row

  You must manually add total rows below the data. You can use more than one row for totals. You must manually enter any aggregation formulas.

  Excel creates one total row automatically. You can hide or show the total by clicking a check box in the ribbon. Excel provides a set of possible aggregate functions via a drop-down menu for each column.

  Sorting and filtering

  Sorting and filtering are effectively the same with lists as with Excel Tables. Excel may prompt you to expand a selection if it can't figure out your data structure.

  Sorting and filtering are effectively the same with Tables as with simple lists of data. Excel doesn't prompt you to expand a selection because it knows the Table's boundaries.

  Formatting

  You must do all formatting manually, and Excel generally propagates that formatting to new rows only one row at a time.

  Excel provides automatic formatting, including banding and other options. Excel maintains formatting with all Table rows and columns with Table styles. (Chapter 7 discusses Table styles.)

  Formulas

  All formulas use standard range references and, for the most part, have to be manually maintained.

  Formulas use structured references that refer to a Table's parts, providing more robust referencing. Excel automatically keeps formulas consistent down an entire column.

  How Tables Interact with Other Worksheet Objects

  Unlike with a list of data, Excel confines actions taken with a Table to that Table's range. Cells outside the Table's range are left intact as much as possible. Excel never overwrites existing data when inserting new rows and columns, nor does Excel resize a Table to overlap other cells that contain values or formulas. When new rows are inserted into a Table, Excel shifts down only cells in the Table (below the row[s] being inserted) and cells below the Table. When you add a new row to the bottom of a Table (by pressing TAB
while the last cell of the last row is selected), Excel adds the new row without shifting anything below unless there is no room, in which case it shifts down everything below by one row.

  NOTE

  There is a distinction between inserting rows in a Table and appending rows in a Table. Inserting rows means selecting one or more cells and using Excel’s Insert function, via the ribbon or the contextual right-click pop-up menu. Appending rows means adding a row to the bottom of a Table. You can append in two different ways:

  1. Select only the last cell in the Table (the bottom-right cell) and press the TAB key.

  2. Enter a value in any cell in the first empty row below the Table.

  Subsequent sections have more to say about how Excel handles inserting and appending.

  CAUTION

  When you stack Tables vertically, each Table must use fewer columns or the same number of columns as the Tables above. Say that no more rows can be appended to a Table that has fewer columns than any Tables below the one you’re editing; Excel displays an error when you attempt to insert rows into that Table.

  The next figure shows three Tables stacked vertically. These Tables are separated by two blank rows. If you append a new row to the bottom of the topmost Table by using the TAB key, Excel increases the Table size by a single row, without shifting the Tables below. The topmost Table takes up one of the rows below it that was originally blank, increasing the Table size by one row. It can do that one more time because there is another blank row below the Table. Then, when there are no more empty cells for appending rows to the Table, the selection moves to the next row in the first column, but the Table is not resized and extended down any more rows.