In This Chapter
A list is a rectangular range of data that usually has a row of text headings to describe the contents of each column. Excel 2007 introduced a twist by letting you designate such a range as an “official” table, which makes common tasks much easier. More importantly, this table feature may eliminate some errors.
This chapter discusses Excel tables and covers what we refer to as lists, which are essentially tables of data that have not been converted to an official table.
It seems that Microsoft can’t quite make up its mind when it comes to naming some of Excel’s features. Excel 2003 introduced a feature called lists, which is a way of working with what is sometimes called a worksheet database. In Excel 2007, the list features evolved into a much more useful feature called tables, and that feature was enhanced a bit in Excel 2010. To confuse the issue even more, Excel also has a feature called data tables, which has nothing at all to do with the table feature. And don’t forget about pivot tables, which are not actual tables but can be created from a table.
In this chapter, we use these two terms:
Figure 9.1 shows a small list that contains employee information. It consists of a Header row, seven columns, and several rows of data. Notice that the data consists of different data types: text, numerical values, dates, and logical values. Column E even contains a formula that calculates the monthly salary from the value in column D.
You can do lots of things with this list. You can sort it, filter it, create a chart from it, create subtotals, summarize it with a pivot table, and even remove duplicate rows (if it had any duplicate rows). A list like this is a common way to handle structured data.
Figure 9.2 shows the employee list after we converted it to a table, using Insert ➜ Tables ➜ Table. The table looks similar to a list, and the most obvious difference is the formatting (which was applied automatically).
Apart from cosmetics, what’s the difference between a list and a table?
It may take you a while to get used to working with tables, but you’ll soon discover that a table offers many advantages over a standard list.
The sections that follow cover common operations that you perform with a table.
Although Excel allows you to create a table from an empty range, most of the time you’ll create a table from an existing range of data (a list). The following instructions assume that you already have a range of data suitable for a table.
Choose Insert ➜ Tables ➜ Table (or press Ctrl+T).
Excel responds with its Create Table dialog box. Excel tries to guess the range and whether the table has a Header row. Most of the time, it guesses correctly. If not, make your corrections before you click OK.
Click OK.
The table is automatically formatted, and Filter mode for the table is enabled. In addition, Excel displays its Table Tools contextual tab (as shown in Figure 9.3). The controls on this tab are relevant to working with a table.
In the Create Table dialog box, Excel may guess the table’s dimensions incorrectly if the table isn’t separated from other information by at least one empty row or column. If Excel guesses incorrectly, just specify the exact range for the table in the dialog box. Or, click Cancel and rearrange your worksheet such that the table is separated from your other data by at least one blank row or column.
To create a table from an empty range, just select the range and choose Insert ➜ Tables ➜ Table. Excel creates the table, adds generic column headers (such as Column1 and Column2), and applies table formatting to the range. Almost always, you’ll want to replace the generic column headers with more meaningful text.
When you create a table, Excel applies the default table style. The actual appearance depends on which document theme you use in the workbook. If you prefer a different look, you can easily change the entire look of the table.
Select any cell in the table and choose Table Tools ➜ Design ➜ Table Styles. The Ribbon shows one row of styles, but if you click the bottom of the vertical scrollbar, the Table Styles group expands, as shown in Figure 9.4. The styles are grouped into three categories: Light, Medium, and Dark. Notice that you get a live preview as you move your mouse among the styles. When you see one that you like, just click to make it permanent.
For a different set of color choices, choose Page Layout ➜ Themes ➜ Themes to select a different document theme.
You can change some elements of the style by using the check box controls of the Table Tools ➜ Design ➜ Table Style Options group. These controls determine whether various elements of the table are displayed and whether some formatting options are in effect:
Moving among cells in a table works just like moving among cells in a normal range. One difference is when you use the Tab key. When the active cell is in a table, pressing Tab moves to the cell to the right, as it normally does. But when you reach the last column, pressing Tab again moves to the first cell in the next row.
When you move your mouse around in a table, you may notice that the pointer changes shapes. These shapes help you select various parts of the table:
To add a new column to the end of a table, just activate a cell in the column to the right of the table and start entering the data. Excel automatically extends the table horizontally.
Similarly, if you enter data in the row below a table, Excel extends the table vertically to include the new row. An exception to automatically extending tables is when the table is displaying a Total row. If you enter data below the Total row, the table will not be extended. To add a new row to a table that’s displaying a Total row, activate the lower-right table cell and press Tab.
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. The Insert shortcut menu command displays additional menu items that describe where to add the rows or columns.
Another way to extend a table is to drag its resize handle, which appears in the lower-right corner of the table (but only when the entire table is selected). When you move your mouse pointer to the resize handle, the mouse pointer turns into a diagonal line with two arrow heads. Click and drag down to add more rows to the table. Click and drag to the right to add more columns.
When you insert a new column, the Header row displays a generic description, such as Column 1, Column 2, and so on. Normally, you’ll want to change these names to more descriptive labels.
To delete a row (or column) in a table, select any cell in the row (or column) that you want to delete. If you want to delete multiple rows or columns, select them all. Then right-click and choose Delete ➜ Table Rows (or Delete ➜ Table Columns).
To move a table to a new location in the same worksheet, move the mouse pointer to any of its borders. When the mouse pointer turns into a cross with four arrows, click and drag the table to its new location.
To move a table to a different worksheet (in the same workbook or in a different workbook), do the following:
If data in a table was compiled from multiple sources, the table may contain duplicate items. Most of the time, you want to eliminate the duplicates. In the past, removing duplicate data was essentially a manual task, but it’s easy if the data is in a table.
Start by selecting any cell in your table and then choose Table Tools ➜ Design ➜ Tools ➜ Remove Duplicates. Excel responds with a dialog box like the one shown in Figure 9.5. The dialog box lists all the columns in your table. Place a check mark next to the columns that you want to include in the duplicate search. Most of the time, you’ll want to select all the columns, which is the default. Click OK, and Excel weeds out the duplicate rows and displays a message that tells you how many duplicates it removed.
Unfortunately, Excel does not provide a way for you to review the duplicate records before deleting them. You can, however, use Undo (or press Ctrl+Z) if the result isn’t what you expect.
Each column in the Header row of a table contains a clickable control (a Filter button), which normally displays a downward-pointing arrow. That control, when clicked, displays sorting and filtering options.
Figure 9.6 shows a table of real estate listing information after clicking the control for the Date Listed column. If a column is filtered or sorted, the image on the control changes to remind you that the column was used in a filter or sort operation.
Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the total sales made.
To sort a table by a particular column, click the drop-down arrow in the column header and choose one of the sort commands. The exact command varies, depending on the type of data in the column. Sort A to Z and Sort Z to A are the options that appear when the columns contain text. The options for columns that contain numeric data or True/False are Sort Smallest to Largest and Sort Largest to Smallest. Columns that contain dates change the options into Sort Oldest to Newest and Sort Newest to Oldest.
You can also select Sort by Color to sort the rows based on the background or text color of the data. This option is relevant only if you’ve overridden the table style colors with custom colors or you’ve used conditional formatting to apply colors based on the cell contents.
You can sort on any number of columns. The trick is to sort the least significant column first and then proceed until the most significant column is sorted last.
For example, in the real estate listing table (refer to Figure 9.6), you may want the list to be sorted by agent. And within each agent’s group, the rows should be sorted by area. And within each area, the rows should be sorted (descending) by list price. For this type of sort, first sort by the List Price column, then sort by the Area column, and then sort by the Agent column. Figure 9.7 shows the table sorted in this manner.
Another way of performing a multiple-column sort is to use the Sort dialog box. To display this dialog box, choose Home ➜ Editing ➜ Sort & Filter ➜ Custom Sort. Or right-click any cell in the table and choose Sort ➜ Custom Sort from the shortcut menu.
In the Sort dialog box, use the drop-down lists to specify the first search specifications. Note that the searching is opposite of what we described earlier. In this example, you start with Agent. Then click the Add Level button to insert another set of search controls. In this set of controls, specify the sort specifications for the Area column. Then add another level and enter the specifications for the List Price column. Figure 9.8 shows the dialog box after entering the specifications for the three-column sort. This technique produces the same sort as described previously.
Filtering a table refers to displaying only the rows that meet certain conditions. After you apply a filter, rows that don’t meet the conditions are hidden. Filtering a table lets you focus on a subset of interest.
Using the real estate table, assume that you’re only interested in the data for the N. County area. Click the drop-down control in the Area Row header and remove the check mark from Select All, which deselects everything. Then place a check mark next to N. County (see Figure 9.9). Click OK, and the table will be filtered to display only the listings in the N. County area. Rows for other areas are hidden.
You can filter by multiple values—for example, filter the table to show only N. County and Central.
You can filter a table using any number of columns. For example, you may want to see only the N. County listings in which the Type is Single Family. Just repeat the operation using the Type column. All tables then display only the rows in which the Area is N. County and the Type is Single Family.
For additional filtering options, select Text Filters (or Number Filters, if the column contains values). The options are fairly self-explanatory, and you have a great deal of flexibility in displaying only the rows that you’re interested in.
In addition, you can right-click a cell and use the Filter command on the shortcut menu. This menu item leads to several additional filtering options. For example, you can filter the table to show only rows that contain the same value as the active cell.
When you copy data in a filtered table, only the visible rows are copied. This filtering makes it easy to copy a subset of a larger table and paste it to another area of your worksheet. Keep in mind that the pasted data is not a table—it’s just a normal range.
Similarly, you can select and delete the visible rows in the table, and the rows hidden by filtering will not be affected.
You cannot, however, unhide rows that are hidden by filtering. For example, if you select all the rows in a table, right-click, and choose Unhide, that command has no effect.
To remove filtering for a column, click the drop-down control in the row Header and select Clear Filter. If you’ve filtered using multiple columns, it may be faster to remove all filters by choosing Home ➜ Editing ➜ Sort & Filter ➜ Clear.
Another way to filter a table is to use one or more Slicers. This method is less flexible but more visually appealing. Slicers are particularly useful when the table will be viewed by novices or those who find the normal filtering techniques too complicated. Slicers are visual, and it’s easy to see exactly what type of filtering is in effect. A disadvantage of Slicers is that they take up a lot of room onscreen.
To add one or more Slicers, activate any cell in the table and choose Table Tools ➜ Design ➜ Tools ➜ Insert Slicer. Excel responds with a dialog box that displays each header in the table. Place a check mark next to the field(s) that you want to filter. You can create a Slicer for each column, but that’s rarely needed. In most cases, you’ll want to be able to filter the table by only a few fields. Click OK, and Excel creates a Slicer for each field you specified.
Figure 9.10 shows a table with two Slicers. The table is filtered to show only the records for Adams and Jenkins in the Central area.
A Slicer contains a button for every unique item in the field. In the real estate listing example, the Slicer for the Agent field contains 14 buttons because the table has records for 14 different agents.
To use a Slicer, just click one of the buttons. The table displays only the rows that correspond to the button. You can also press Ctrl to select multiple buttons and press Shift to select a continuous group of buttons, which would be useful for selecting a range of List Price values.
If your table has more than one Slicer, it’s filtered by the selected buttons in each Slicer. To remove filtering for a particular Slicer, click the icon in the upper-right corner of the Slicer.
Use the tools from the Slicer Tools ➜ Options contextual menu to change the appearance or layout of a Slicer. You have quite a bit of flexibility.
The Total row is an optional table element that contains formulas that summarize the information in the columns. Normally, the Total row isn’t displayed. To display the Total row, choose Table Tools ➜ Design ➜ Table Style Options ➜ Total Row. This command is a toggle that turns the Total row on and off.
By default, the Total row displays the sum of the values in a column of numbers. In many cases, you’ll want a different type of summary formula. When you select a cell in the Total row, a drop-down arrow appears, and you can select from a number of other summary formulas (see Figure 9.11):
Using the drop-down list, you can select a summary function for the column. Excel inserts a formula that uses the SUBTOTAL function and refers to the table’s column using a special structured syntax (described later). The first argument of the SUBTOTAL function determines the type of summary displayed. For example, if the first argument is 109, the function displays the sum. You can override the formula inserted by Excel and enter any formula you like in the Total row cell. For more information, see the sidebar “About the SUBTOTAL function.”
Adding a Total row to a table is an easy way to summarize the values in a table column. In many cases, you’ll want to use formulas within a table. For example, in the table shown in Figure 9.12, you might want to add a column that shows the difference between the Actual and the Projected amounts. As you’ll see, Excel makes this easy when the data is in a table.
To add a column that calculates the difference between the Actual and Projected columns, follow these steps:
Press Enter to end the formula.
Excel copies the formula to all rows in the table.
Figure 9.13 shows the table with the new calculated column.
If you examine the table, you’ll find this formula for all cells in the Difference column:
=[@Actual]-[@Projected]
The syntax is a bit different if a column header contains spaces or nonalphanumeric characters such as a pound sign, an asterisk, and so on). In such a case, Excel encloses the column header text in brackets (not quote marks). For example, if the column C header was Projected Income, the formula would appear as follows:
=[@Actual]-[@[Projected Income]]
Some symbols in a column header (such as a square bracket) require the use of an apostrophe as an escape character. It is usually much simpler to create table formulas by pointing and let Excel handle the syntax details.
Keep in mind that we didn’t define names in this worksheet. The formula uses table references that are based on the column names. If you change the text in a column header, any formulas that refer to that data update automatically. That’s an example of how working with a table is easier than working with a regular list.
Although we entered the formula into the first data row of the table, that’s not necessary. We could have put that formula in any cell in column D. Any time you enter a formula into any cell in an empty table column, it will automatically fill all the cells in that column. And if you need to edit the formula, edit the copy in any row, and Excel automatically copies the edited formula to the other cells in the column.
The preceding steps use the pointing technique to create the formula. Alternatively, you can enter the formula manually using standard cell references. For example, you can enter the following formula in cell E3:
=D3-C3
If you type the formulas using cell references, Excel still copies the formula to the other cells automatically; it just doesn’t use the column headings.
The preceding section describes how to create a column of formulas within a table—often called a calculated column. What about formulas outside a table that refer to data inside a table? You can take advantage of the structured table referencing that uses the table name, column headers, and other table elements. You don’t need to create names for these items.
The table itself has a name that was assigned automatically when you created the table (for example, Table1), and you can refer to data within the table by using the column header text.
You can, of course, use standard cell references to refer to data in a table, but the structured table referencing has a distinct advantage: the names adjust automatically if the table size changes by adding or deleting rows.
Figure 9.14 shows a simple table that contains regional sales information. Excel named this table Table2 when it was created because it was the second table in the workbook. To calculate the sum of all the values in the table, use this formula:
=SUM(Table2)
This formula always returns the sum of all the data, even if rows or columns are added or deleted. And if you change the name of the table, Excel adjusts all formulas that refer to that table automatically. For example, if you rename Table1 to be Q1Data, the preceding formula changes to this:
=SUM(Q1Data)
Most of the time, your formulas will refer to a specific column in the table rather than the entire table. The following formula returns the sum of the data in the Sales column:
=SUM(Table2[Sales])
Notice that the column name is enclosed in square brackets. Again, the formula adjusts automatically if you change the text in the column heading.
Excel provides some helpful assistance when you create a formula that refers to data within a table. Figure 9.15 shows the Formula AutoComplete feature helping create a formula by showing a list of the elements in the table. The list appeared after we typed the left square bracket.
Here’s another example that returns the sum of the January sales:
=SUMIF(Table2[Month],"Jan",Table2[Sales])
Using this structured table syntax is optional—you can use actual range references if you like. For example, the following formula returns the same result as the preceding one:
=SUMIF(B3:B8,"Jan",D3:D8)
To refer to a cell in the Total row of a table, use a formula like this:
=Table2[[#Totals],[Sales]]
This formula returns the value in the Total row of the Sales column in Table2. If the Total row in Table2 is not displayed, the preceding formula returns a #REF error.
To count the total number of rows in Table2, use the following formula:
=ROWS(Table2[#All])
The preceding formula counts all rows, including the Header row, Total row, and hidden rows.
To count only the data rows (including hidden rows), use a formula like this:
=ROWS(Table2[#Data])
To count only the visible data rows, use the SUBTOTAL or AGGREGATE function. For example, you can use either of these formulas:
=SUBTOTAL(103,Table2[Region]) =AGGREGATE(3,5,Table2[Region])
Because the formula is counting visible rows, you can use any column header in the preceding formula.
A formula that’s not in the table but is in the same row as a table can use an @ reference to refer to table data that is in the same row. For example, assume the following formula is in row 3, in a column outside Table2. The formula returns the value in row 3 of the Sales column in Table2:
= Table2[@Sales])
You can also combine row and column references by nesting brackets and including multiple references separated by commas. The following example returns Sales from the current row divided by the total sales:
=Table2[@Sales]/Table2[[#Totals],[Sales]]
A formula like the preceding one is much easier to create if you use the pointing method.
Table 9.1 summarizes the row identifiers for table references and describes which ranges they represent.
Table 9.1 Table Row References
Row Identifier | Description |
#All | Returns the range that includes the Header row, all data rows, and the Total row. |
#Data | Returns the range that includes the data rows but not the Header and Total rows. |
#Headers | Returns the range that includes the Header row only. Returns a #REF! error if the table has no Header row. |
#Totals | Returns the range that includes the Total row only. Returns a #REF! error if the table has no Total row. |
@ | Represents “this row.” Returns the range that is the intersection of the formula’s row and a table column. If the formula row does not intersect with the table (or is the same row as the Header or Total row) a #VALUE! error is returned. |
In some cases, you may need to convert a table back to a normal list. For example, you may need to share your workbook with someone who uses an older version of Excel. Or, maybe you’d like the use the Custom Views feature (which is disabled when the workbook contains a table). To convert a table to a normal list, just select a cell in the table and choose Table Tools ➜ Design ➜ Tools ➜ Convert To Range. The table style formatting remains intact, but the range no longer functions as a table.
Formulas inside and outside the table that use structured table references are converted, so they use range addresses rather than table items.
In many cases, standard filtering (as we described previously in this chapter) does the job just fine. If you run up against its limitations, you need to use advanced filtering. Advanced filtering is much more flexible than standard filtering, but it takes a bit of upfront work to use it. Advanced filtering provides you with the following capabilities:
You can use advanced filtering with a list or with a table.
The examples in this section use a real estate listing list (shown in Figure 9.16), which has 125 rows and 10 columns. This list contains an assortment of data types: value, text string, logical, and date. The list occupies the range A8:J133. (Rows above the table are used for the criteria range.)
Before you can use the advanced filtering feature, you must set up a criteria range, which is a range on a worksheet that conforms to certain requirements. The criteria range holds the information that Excel uses to filter the table. The criteria range must conform to the following specifications:
You can put the criteria range anywhere in the worksheet or even in a different worksheet. However, you should avoid putting the criteria range in rows that are occupied by the list or table. Because Excel may hide some of these rows when filtering, you may find that your criteria range is no longer visible after filtering. Therefore, you should generally place the criteria range above or below the table.
Figure 9.17 shows a criteria range in A1:B2, located above the list that it uses. Notice that the criteria range does not include all the field names from the table. You can include only the field names for fields that you use in the selection criteria.
In this example, the criteria range has only one row of criteria. The fields in each row of the criteria range are joined with an AND operator. Therefore, after applying the advanced filter, the list shows only the rows in which the Bedrooms column is 3 and the Pool column is TRUE. In other words, it shows only the listings for three-bedroom homes with a pool.
You may find specifying criteria in the criteria range a bit tricky. We discuss this topic in detail later in this chapter in the section “Specifying Advanced Filter Criteria.”
To perform the advanced filtering:
Choose Data ➜ Sort & Filter ➜ Advanced.
Excel displays the Advanced Filter dialog box, as shown in Figure 9.18.
Specify the criteria range.
If you happen to have a named range with the name Criteria, Excel will insert that range in the Criteria Range field—you can also change this range if you like.
To filter the database in place (that is, to hide rows that don’t qualify), select the option labeled Filter the List, In-Place.
If you select Copy to Another Location, you need to specify a range in the Copy To field. Specifying the upper-left cell of an empty range will do.
Figure 9.19 shows the list after applying the advanced filter that displays three-bedroom homes with a pool.
When you apply an advanced filter, Excel hides all rows that don’t meet the criteria you specified. To clear the advanced filter and display all rows, choose Data ➜ Sort & Filter ➜ Clear.
The key to using advanced filtering is knowing how to set up the criteria range, which is the focus of the sections that follow. You have a great deal of flexibility, but some of the options are not exactly intuitive. Here, you’ll find plenty of examples to help you understand how to create a criteria range that extracts the information you need.
The examples in this section use a single-selection criterion. In other words, the contents of a single field determine the record selection.
To select only the records that contain a specific value in a specific field, enter the field name in the first row of the criteria range and the value to match in the second row.
Note that the criteria range does not need to include all the fields from the database. If you work with different sets of criteria, you may find it more convenient to list all the field names in the first row of your criteria range.
You can use comparison operators to refine your record selection. For example, you can select records based on any of the following single criteria:
To select the records that describe homes that have at least four bedrooms, type Bedrooms in cell A1 and then type >=4 in cell A2 of the criterion range.
Table 9.2 lists the comparison operators that you can use with text or value criteria. If you don’t use a comparison operator, Excel assumes the equal sign operator (=).
Table 9.2 Comparison Operators
Operator | Comparison Type |
= | Equal to |
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
< > | Not equal to |
Criteria that use text also can make use of two wildcard characters: an asterisk (*) matches any number of characters; a question mark (?) matches any single character.
Table 9.3 shows examples of criteria that use text. Some of these are a bit counterintuitive. For example, to select records that match a single character, you must enter the criterion as a formula (refer to the last entry in the table).
Table 9.3 Examples of Text Criteria
Criteria | Selects |
=”=January” | Records that contain the text January (and nothing else). You enter this exactly as shown: as a formula, with an initial equal sign. Alternatively, you can use a leading apostrophe and omit the quotes: ‘=January |
January | Records that begin with the text January. |
C | Records that contain text that begins with the letter C. |
<>C* | Records that contain any text, except text that begins with the letter C. |
>=L | Records that contain text that begins with the letters L through Z. |
*County* | Records that contain text that includes the word county. |
Sm* | Records that contain text that begins with the letters SM. |
s*s | Records that contain text that begins with S and has a subsequent occurrence of the letter S. |
s?s | Records that contain text that begins with S and has another S as its third character. Note that this does not select only three-character words. |
=”=s*s” | Records that contain text that begins and ends with S. You enter this exactly as shown: as a formula, with an initial equal sign. Alternatively, you can use a leading apostrophe and omit the quotes: ‘=s*s |
<>*c | Records that contain text that does not end with the letter C. |
=???? | Records that contain exactly four characters. |
<>????? | All records that don’t contain exactly five characters. |
<>*c* | Records that do not contain the letter C. |
~? | Records that contain text that begins with a single question mark character. (The tilde character overrides the wildcard question mark character.) |
= | Records of which the key is completely blank. |
<> | Records that contain any nonblank entry. |
=”=c” | Records that contain the single character C. You enter this exactly as shown: as a formula, with an initial equal sign. Alternatively, you can use a leading apostrophe and omit the quotes: ’=c |
Often, you may want to select records based on criteria that use more than one field or multiple values within a single field. These selection criteria involve logical OR or AND comparisons. Here are a few examples of the types of multiple criteria that you can apply to the real estate database:
To join criteria with an AND operator, use multiple columns in the criteria range. Figure 9.20 shows a criteria range that filters records to show those with at least four bedrooms, more than 3,000 square feet, a pool, and a list price of $390,000 or less.
Figure 9.21 shows another example. This criteria range displays listings from the month of August 2012. Notice that the column name (Date Listed) appears twice in the criteria range. The criteria selects the records in which the Date Listed date is greater than or equal to August 1, and the Date Listed date is less than or equal to August 31.
To join criteria with a logical OR operator, use more than one row in the criteria range. A criteria range can have any number of rows, each of which joins with the others via an OR operator. Figure 9.22 shows a criteria range (A1:D3) with two rows of criteria.
In this example, the filtered table shows the rows that meet either of the following conditions:
A property with a square footage of at least 1,800, in the Central area
or
Using computed criteria can make filtering even more powerful. Computed criteria filter the table based on one or more calculations. For example, you can specify computed criteria that display only the rows in which the List Price (column D) is greater than average.
=D9>AVERAGE(D:D)
Notice that this formula uses a reference to cell D9, the first data cell in the List Price column. Also, when you use computed criteria, the cell above it must not contain a field name. You can leave that cell blank or provide a descriptive label, such as Above Average. The formula will return a value, but that value is meaningless.
By the way, you can also use a standard filter to display data that’s above (or below) average.
The next example displays the rows in which the listing has a pool and the price per square foot is less than $100. Cell D9 is the first data cell in the List Price column, and cell G9 is the first data cell in the SqFt column. In Figure 9.23, the computed criteria formula is
=(D9/G9)<100
Here is another example of a computed criteria formula. This formula displays the records listed within the past 60 days:
=B9>TODAY()-60
Keep the following points in mind when using computed criteria:
To create formulas that return results based on a criteria range, use Excel’s database worksheet functions. All these functions begin with the letter D, and they are listed in the Database category of the Insert Function dialog box.
Table 9.4 lists Excel’s database functions. Each of these functions operates on a single field in the database.
Table 9.4 Excel Database Worksheet Functions
Function | Description |
DAVERAGE | Returns the average of database entries that match the criteria |
DCOUNT | Counts the cells containing numbers from the specified database and criteria |
DCOUNTA | Counts nonblank cells from the specified database and criteria |
DGET | Extracts from a database a single field from a single record that matches the specified criteria |
DMAX | Returns the maximum value from selected database entries |
DMIN | Returns the minimum value from selected database entries |
DPRODUCT | Multiplies the values in a particular field of records that match the criteria in a database |
DSTDEV | Estimates the standard deviation of the selected database entries (assumes that the data is a sample from a population) |
DSTDEVP | Calculates the standard deviation of the selected database entries, based on the entire population of selected database entries |
DSUM | Adds the numbers in the field column of records in the database that match the criteria |
DVAR | Estimates the variance from selected database entries (assumes that the data is a sample from a population) |
DVARP | Calculates the variance, based on the entire population of selected database entries |
All the database functions require a separate criteria range, which is specified as the last argument for the function. The database functions use the same type of criteria range as discussed earlier in the “Specifying Advanced Filter Criteria” section (see Figure 9.24).
The formula in cell B24, which follows, uses the DSUM function to calculate the sum of values in a table that meet certain criteria. Specifically, the formula returns the sum of the Sales column for records in which the Month is Feb and the Region is North.
=DSUM(B6:G21,F6,B1:C2)
In this case, B6:G21 is the entire table, F6 is the column heading for Sales, and B1:C2 is the criteria range.
This alternative version of the formula uses structured table references:
=DSUM(Table1[#All],Table1[[#Headers],[Sales]],B1:C2)
If you’re an array formula aficionado, you might be tempted to use a literal array in place of the criteria range. In theory, the following array formula should work (and would eliminate the need for a separate criteria range). Unfortunately, the database functions do not support arrays, and this formula simply returns a #VALUE! error.
=DSUM(B6:G21,F6, {"Month","Region";"Feb","North"})
Excel’s Data ➜ Outline ➜ Subtotal command is a handy tool that inserts formulas into a list automatically. These formulas use the SUBTOTAL function. To use this feature, your list must be sorted because the formulas are inserted whenever the value in a specified column changes. For more information about the SUBTOTAL function, refer to the sidebar, “About the SUBTOTAL function,” earlier in this chapter.
Figure 9.25 shows an example of a list that’s appropriate for subtotals. This list is sorted by the Month field, and the Region field is sorted within months.
To insert subtotal formulas into a list automatically, activate any cell in the range and choose Data ➜ Outline ➜ Subtotal. You will see the Subtotal dialog box, similar to the one shown in Figure 9.26.
The Subtotal dialog box offers the following choices:
When you click OK, Excel analyzes the database and inserts formulas as specified—it even creates an outline for you. Figure 9.27 shows a worksheet after adding subtotals that summarize by month. You can, of course, use the SUBTOTAL function in formulas that you create manually. Using the Data ➜ Outline ➜ Subtotals command is usually easier.
All the formulas use the SUBTOTAL worksheet function. For example, the formula in cell E20 (Grand Total) is as follows:
=SUBTOTAL(9,E2:E18)
Although this formula refers to other cells that contain a SUBTOTAL formula, those cells are ignored, to avoid double-counting.
You can use the outline controls to adjust the level of detail shown. Figure 9.28, for example, shows only the summary rows from the subtotaled table. These rows contain the SUBTOTAL formulas.
18.226.98.32