Analyzing Data with Tables and Charts in Microsoft Excel 2013

Using tables to organize and analyze data

You can create a simple database just by entering data into rows, with or without headings to indicate what’s in each column. For example, you can enter a list of names in column A and then, in column B, enter a phone number alongside each name. As long as that list remains short and simple enough to scan quickly, you don’t need to do anything more.

But lists have a way of growing, and even moderately long lists can benefit from sorting, searching, filtering, outlining, and summarizing. When you turn a range into a table, Excel provides several tools that simplify the way you work with even long, detail-rich collections of data.

Microsoft introduced the concept of tables (not to be confused with data tables, which are a rarely used relic of Excel’s distant past) in Excel 2007. Tables are roughly equivalent to the feature known as lists in Excel 2003, but with more sophisticated formatting options. If you open a worksheet originally created in Excel 2003 that contains one or more lists, you’ll need to manually convert those lists to tables.

The quickest way to turn a range into a table is to click Table in the Tables group on the Quick Analysis tool, which applies the default table format to the current selection or region immediately. If you want more control over the process, select the range (or select any cell within the range if the list is in a self-contained region), and then click Table in the Tables group on the Insert tab. (You can also use the keyboard shortcut Ctrl+T or Ctrl+L.) You’ll see the Create Table dialog box shown in Figure 13-2.

Had we instead clicked Format As Table, in the Styles group on the Home tab, we would have been required to select a table style before seeing the Format As Table dialog box (which is identical to the Create Table dialog box shown in Figure 13-2). Using either keyboard shortcut applies the default table style. (We discuss table styles in much greater detail in the next section, Formatting tables with table styles.)

If the selected range contains a header row with labels for each column, you should select the My Table Has Headers check box. (For the most part, Excel correctly detects the presence of headers, but it can be confused by some configurations, so check this setting before you proceed.) Clicking OK applies the default table style to the range and makes a few other changes. The end result looks like this:

Defining a range as a table makes the following changes, some of which are not immediately apparent. (We explain these changes in more detail shortly.)

• Column widths expand as needed to display header text in full. If column headers are not included, Excel adds generic headers—Column1, Column2, and so on.

• A down arrow appears to the right of each column heading, allowing quick sort and filter operations.

• When any cell or range within the table is selected, a Design tab with customization options appears on the ribbon, under the Table Tools heading.

• A default name is assigned to the table; you can change the name to a more descriptive one by using the Table Name box in the Properties group on the Table Tools Design tab.

• Any cell addresses used in formulas you add within the table are automatically converted to structured references. (For an explanation of how structured references work, see “Adding formulas and totals to a table,” later in this chapter.)

• A triangular handle in the lower-right corner of the table allows you to quickly add rows or columns to the table, preserving formatting and copying formulas automatically.

Tables offer a tremendously versatile way to work with large and small amounts of data. In fact, as we explain in Chapter 14, a table can serve as the source of data for a PivotTable report. You can also export a table to a SharePoint list or to a PivotDiagram in Visio (a member of the Office family we don’t discuss in this book), using the Export command in the External Table Data group on the Table Tools Design tab.

After you convert a range into a table, you can fine-tune its appearance with custom formatting, expand its size in either direction, and add totals.

What if you change your mind and want to remove the table features, turning the data back into a plain ol’ range? Easily done: Click to select any cell within the table and then click Convert To Range, in the Tools group on the Table Tools Design tab. Or right-click any cell in the table and then click Table, Convert To Range on the shortcut menu. This option removes the special treatment of header rows and converts formulas back to ordinary cell references. Any special formatting (such as banded rows) remains in place and must be changed manually unless you remove the formatting first, using the Clear option at the bottom of the Table Styles gallery on the Design tab.

The effect is similar to what happens if you freeze the top row of the table, but it requires no effort from you beyond creating the table in the first place.

Formatting tables with table styles

Table styles apply formatting—colors, fonts, borders, and shading—to the region that makes up a table. Excel offers a selection of 60 options in the Table Styles gallery (plus a None option that removes table formatting). These options are divided into Light, Medium, and Dark groups that correspond to the intensity of the colors used. You can choose from the entire list in either of two ways:

• Click Format As Table in the Styles group on the Home tab. If the current range or region has not already been defined as a table, this option displays the Format As Table dialog box after you make a selection.

• Click the arrow to the right of the Table Styles gallery (or below the Quick Styles button) in the Table Styles group on the Table Tools Design tab.

Figure 13-3 shows the Table Styles gallery in operation. As with other Office galleries, allowing the mouse pointer to rest over an option in the gallery previews the formatting in the table. (It also shows the name of the style in a ScreenTip.)

The colors and fonts associated with a style vary depending on the theme used for the current worksheet. If you change the theme, any existing table formats change to pick up the color schemes and fonts from the new theme.

If you’ve applied manual formatting to fonts, font colors, cell shading, and so on within a table region, your formatting is preserved when you apply a table style. To clear any manual formatting and use only the formatting specified in the table style, right-click the style in the Table Styles gallery, and then click Apply And Clear Formatting.

Many of the built-in table styles include formatting that adds shading to alternate rows, which makes reading across wide tables easier. The seven check boxes in the Table Style Options group on the Design tab allow you to fine-tune the following format settings:

• Header Row Clear this check box to hide all column headings. Doing so also hides the down arrow used to access sort and filter options for each column. Note that Excel remembers the name assigned to any column and continues to use it in structured references within formulas.

• Total Row Select this check box to add a row beneath the table, with options to automatically subtotal the contents of one or more columns. If the table contains more than one column, the word Total appears in the first column. If you expand the table, this row remains at the bottom of the new range. You can customize the formulas beneath each column by using a drop-down list, as we explain shortly.

• Banded Rows Apply different background colors to alternating rows to make reading across a wide table easier.

• First Column Click to apply special formatting to the first column in the table. Use this option to highlight labels that identify each row.

• Last Column Click to apply special formatting to the last column in the table. This option is especially useful if the last column contains totals for each row.

• Banded Columns Apply different background colors to alternating columns.

• Filter Button Remove the arrow to the right of a column heading that allows you to filter, sort, or search using the contents of that column.

In effect, these check boxes allow you to provide very specific customizations to the current table style. When you combine these options with the 60 entries in the built-in Table Styles gallery, each of whose color palette and font can in turn be reset using any of 21 built-in themes, you have literally thousands of possible looks to choose from. That’s not enough? Then build your own table style by clicking the New Table Style link at the bottom of the Table Styles gallery. Doing so brings up the New Table Style dialog box, where you can set the properties for each part of the table individually.

Starting from scratch to create a custom table style is difficult and potentially confusing. In our experience, you’ll find it much simpler to duplicate an existing table style and then modify the style you copied. In the Table Styles gallery, right-click the style you want to use as your starting point, and then click Duplicate. That opens the Modify Table Style dialog box shown next.

In the Name box, replace the default name with a descriptive name. Then select individual table elements from the list in the center of the dialog box and adjust their definition as needed. Click the Format button to change font style (bold, italic, and so on), cell borders, and shading. The four stripe options allow you to set how many rows are in each stripe that makes up a band. The default is 1, which means that shading alternates from one row to the next. If you choose 2 for First Row Stripe and Second Row Stripe, each band of shading is applied to two rows at a time.

Custom styles appear at the top of the Table Styles gallery. If you right-click on a saved custom table style, you’ll notice that Modify and Delete options are available on the menu; you can’t modify or delete any of the predefined table styles.

Expanding (or shrinking) a table

Although you can define a table by selecting a range of any size, the most common and useful scenario defines a table using the current region, which is demarcated by blank cells (or the worksheet’s edge) on the top, bottom, and sides. To add a row to the table, click in the cell in the lower-right corner of the table range (if your table has a Total row, use the row above it), and then press Tab. Excel adds a new row using the colors, fonts, and shading from the current table style and moves the active cell to the first column in the newly created row. If your table includes a Total row, it shifts down to accommodate the new row as well.

To expand a table manually, look in the lower-right corner of the table for a small triangular handle. Make sure that that cell is not selected, and then aim the mouse pointer at the handle until the pointer turns into a two-sided diagonal arrow. Drag down to add rows to the table, shifting the Total row down if necessary. Drag the handle to the right to add one or more columns (each new column gets a generic heading that you can change later when you’re ready to enter data).

You can also drag the table sizing handle up or to the left to remove rows or columns from the defined table range. Any data currently contained in those cells remains, but loses its table formatting.

You can add a reference to any table by using its name, as defined in the Table Name box in the Properties group on the Design tab. The range defined by this name automatically expands when you add rows or columns to the table. (It does not, however, include the Header and Total rows.) If you use the current table as the basis for a PivotTable, any new rows or columns you create are automatically available for use in the PivotTable. Likewise, when you use data from rows or columns in a table to define data series, labels, or other elements in a chart, those elements are automatically updated when you expand the table.

Adding totals and formulas to a table

When you create a table, Excel allows you to perform a few tricks with formulas that aren’t available within a normal range. The most obvious is the Total row, which you can use to quickly add summaries of table data. As we explained earlier in this chapter, you can manually enable the Total row by selecting its check box in the Table Styles Options group on the Design tab.

Figure 13-4 shows the Total row for a table containing a month’s worth of stock prices. Note that Excel automatically added a formula that totals the rightmost column in the table.

Although the result is functionally the same as if you had clicked the AutoSum button, the formula itself uses the SUBTOTAL function. You can change the results for an existing formula by selecting the cell and clicking the arrow just to its right. In this example, we’re about to change the current formula, which shows the total share volume for the month, to one that shows the average volume per day:

In a Total row, you can add a summary formula to any cell. Click that cell to reveal an arrow that you can click to display a drop-down list of available formulas.

What if you want to create a calculated column that displays totals, averages, or other summaries on a per-row basis? Excel can do that automatically. In the previous example, click any cell in any column to the right of the table range and begin entering a formula. In this worksheet, the Open price for each day is in column B and the Close price is in column C, with Volume in column D. So we can click in E2, type an equal sign, click C2, type a minus sign (–), and click B2. As soon as we press Enter, Excel creates a new column using the current table format and copies the formula we just typed to every cell in that column.

The new column includes a generic heading that you’ll probably want to replace with a descriptive heading, and you might also want to insert a formula in the Total row, but Excel does all the work of creating the calculated column, as shown in Figure 13-5.

If you look carefully at the formula bar in Figure 13-5, you’ll see that the formula Excel creates includes some unusual cell references. These are called structured references, which are designed to make it easy to automatically copy formulas as you add new rows. They’re created automatically when you click to select cell references for use in a formula; you can choose to use standard references instead by simply typing the cell address. Brackets indicate a column heading name and an @ sign indicates the current row. The # sign is used with one of four keywords to refer to specific parts of the table: #All, #Data, #Headers, or #Totals.