Chapter 5: Table and Formula Details

Lesson 7 of Introduction to Excel showed you how to turn a range into a table and why tables make data easier to work with. MO-210 tests the details: every table style option, the total row's functions, resizing and converting tables, multi-level sorts, and the structured references that formulas use to refer to tables. It also tests two dynamic array functions, SORT and UNIQUE, that sort and summarize data with a formula. This chapter covers all of them.

5.1 Table options

Click any cell in a table to see the Table Design tab. It holds nearly every table setting.

5.1.1 Table styles and style options

The Table Styles gallery has Light, Medium and Dark styles. Point at one to preview it, and click to apply it. Clear at the bottom of the gallery removes the style but leaves the table.

The Table Style Options group turns parts of the style on and off:

Option	What it does
Header Row	Shows or hides the row of column headings.
Total Row	Adds a row at the bottom for totals (see below).
Banded Rows	Shades every other row.
Banded Columns	Shades every other column.
First Column	Formats the first column in bold, like a heading.
Last Column	Formats the last column in bold, for row totals.
Filter Button	Shows or hides the arrows in the header row.

5.1.2 The table name

Every table has a name, such as Table1. The name appears in structured references and in the Name Box list. To change it, type a new name in the Table Name box at the left of the Table Design tab and press Enter. Table names follow the same rules as range names: no spaces, and they must start with a letter or an underscore.

5.1.3 The total row

Check Total Row (or press Ctrl+Shift+T) to add a row labeled Total below the table. Excel usually totals the last column. Click any cell in the total row to see an arrow, and choose a calculation from its list: None, Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var or More Functions.

The total row uses the SUBTOTAL function, such as =SUBTOTAL(109,[Amount]). SUBTOTAL ignores rows hidden by a filter, so the totals always describe the visible data. To put a label in the total row, such as Average Price, type it in the cell.

5.1.4 Adding and removing rows and columns

A table grows on its own:

• Type in the row just below the table, and the table expands to include it.
• Type in the column just to the right, and the table adds a column.
• Press Tab in the last cell of the table to add a new row.

You can also right-click a cell in the table and choose Insert › Table Rows Above or Table Columns to the Left, or Delete › Table Rows or Table Columns. These change only the table, not the whole worksheet row or column.

5.1.5 Resizing a table

To change a table's range directly, choose Table Design › Resize Table and type or select the new range. It must keep the same header row. You can also drag the small resize handle at the table's lower-right corner.

5.1.6 Converting a table to a range

Table Design › Convert to Range turns the table back into ordinary cells. Excel asks you to confirm. Afterward:

• the formatting stays, but banding no longer updates when you sort or insert rows
• the filter buttons disappear
• structured references in formulas become ordinary cell references

5.2 Filtering and multi-level sorting

5.2.1 Filtering records

The arrow in each column heading opens the filter menu. Uncheck values to hide those rows, or use the menu's special filters:

• Text Filters: Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain and Custom Filter.
• Number Filters: Equals, Greater Than, Less Than, Between, Top 10 (which can show the top or bottom n items or percent), Above Average and Below Average.
• Date Filters: Tomorrow, Today, This Week, Last Month, Next Quarter, Year to Date, Between and many more.
• Filter by Color: shows only rows with a particular cell color or font color.

Filters on several columns combine. Only rows that pass every column's filter stay visible. The Custom Filter dialog box can combine two conditions on one column with And or Or, such as Amount is greater than 500 And less than 1,000.

To remove a column's filter, choose Clear Filter From "Column" in its menu. To remove every filter, choose Data › Clear. A filtered column shows a funnel icon on its arrow, and the status bar shows how many records are visible, such as 14 of 120 records found.

5.2.2 Sorting by multiple columns

A one-column sort uses A to Z or Z to A in the filter menu. To sort by more than one column, such as by region and then by sales within each region, use the Sort dialog box (Data › Sort):

1. Click any cell in the table or range and choose Data › Sort.
2. Check that My data has headers is selected. It is automatically for a table.
3. In the first level, choose the Sort by column, what to Sort On (usually Cell Values) and the Order.
4. Click Add Level and choose the Then by column, sort-on setting and order.
5. Add more levels as needed, and click OK.

Excel sorts by the first level, then breaks ties with the second, and so on. Use Move Up and Move Down to change the order of levels, Delete Level to remove one, and Copy Level to duplicate one.

Other choices in the Sort dialog box:

• Sort On can be Cell Values, Cell Color, Font Color or Conditional Formatting Icon. For colors, choose the color and whether it goes On Top or On Bottom.
• Order can be A to Z, Z to A, Smallest to Largest, Largest to Smallest or Custom List, for orders like Low, Medium, High or month names.
• Options sets case-sensitive sorting, or sorts left to right (by rows instead of columns).

5.3 Structured references

When a formula refers to a table, Excel uses structured references, which name the table and column instead of using cell addresses. You don't have to type them. Click table cells while you build a formula and Excel writes them for you. You do need to read them, and the exam expects you to use them.

5.3.1 The parts of a structured reference

Suppose a table named Orders has the columns Item, Qty and Unit Price.

Reference	Refers to
Orders[Qty]	All the data in the Qty column, without the heading or total
[@Qty]	The Qty value in the same row as the formula
[@[Unit Price]]	The same, for a column name with a space (extra brackets)
Orders[#Headers]	The header row
Orders[#Totals]	The total row
Orders[#Data]	All data rows
Orders[#All]	The whole table, including headers and totals
Orders[[#Totals],[Qty]]	The total row's cell in the Qty column
Orders[[Qty]:[Unit Price]]	The data in every column from Qty to Unit Price

5.3.2 Calculated columns

Type a formula in an empty column of a table and press Enter, and Excel fills it down the whole column automatically. This is a calculated column. For example, in a new Total column, the formula =[@Qty]*[@[Unit Price]] multiplies each row's quantity by its unit price. New rows added to the table get the formula automatically.

If you want only one cell to have the formula, click the AutoCorrect Options button that appears and choose Undo Calculated Column.

5.3.3 Referring to a table from outside it

A formula outside the table uses the table's name. These formulas keep working as the table grows, because the table reference grows with it:

=SUM(Orders[Qty])
=AVERAGE(Orders[Unit Price])
=COUNTIFS(Orders[Item], "Lantern")

5.4 SORT and UNIQUE

SORT and UNIQUE are dynamic array functions, like SEQUENCE in chapter 4. Each one returns a whole list of results that spills into the cells below and beside the formula. The source data doesn't change. The formula shows a sorted or de-duplicated copy that updates whenever the source changes.

5.4.1 SORT

SORT(array, [sort_index], [sort_order], [by_col])

Argument	Meaning
array	The range or table to sort.
sort_index	Which column to sort by: 1 for the first column of array, 2 for the second, and so on. The default is 1.
sort_order	1 for ascending (the default) or -1 for descending.
by_col	FALSE (the default) sorts rows. TRUE sorts columns. You'll rarely need it.

Examples, for a table Staff with the columns Name, Department and Salary:

=SORT(Staff[Name])                    names A to Z
=SORT(Staff, 3, -1)                   the whole table, highest salary first
=SORT(Staff[[Name]:[Department]], 2)  names and departments, sorted by department

5.4.2 UNIQUE

UNIQUE(array, [by_col], [exactly_once])

UNIQUE returns each different value once. Its third argument changes what "unique" means:

• FALSE (the default) returns every distinct value once. For a list of departments, it returns each department one time.
• TRUE returns only values that appear exactly once in the list.

=UNIQUE(Staff[Department])           each department once
=UNIQUE(Staff[Department],,TRUE)     departments with only one staff member

5.4.3 Combining them

Functions can be nested, so =SORT(UNIQUE(Staff[Department])) returns each department once, in alphabetical order. This is a common way to build a list for a report or a drop-down.

5.4.4 Spill ranges

To refer to everything a dynamic array formula returns, add # after the cell that holds the formula. If =SORT(UNIQUE(Staff[Department])) is in cell F2, then =COUNTA(F2#) counts how many departments it returned, even if the number changes later.

If anything blocks the cells a formula needs, it returns #SPILL!. Click the warning icon to see which cells are in the way, and clear them.

5.5 Practice