Susan Harkins shows you how to combine familiar features to create more flexible Excel Table objects.
Excel 2007 introduced a new object, the Table object. A Table is a formatted range that has built-in functionality that makes working with data much simpler. You're probably familiar with Table objects by now, but you might not realize how flexible they are; almost anything you can do to a non-Table range, you can do a Table. In this article, I'll show you how to combine two common features that you probably use often with a Table to create a more flexible and functional Table. Specifically, I'll show you how to apply conditional formatting and data validation to a Table. In both cases, the feature enhances the Table.
I'm using Excel 2013 on a Windows 7 system. You can work with any small data set or download the example .xlsx file. Excel's Table object isn't support by Excel 2003 or earlier.
Convert text to a Table
Our first step is to generate a Table. Fortunately, it's easy to convert existing data into a Table object as follows:
- Click a single cell anywhere inside the data, and click the Insert tab. (Don't click two or more cells within a data set, because Excel won't interpret the range correctly.)
- Click Table in the Tables group.
- In the resulting dialog, you can change the range, but most likely you'll never need to. Be sure to check the My table has headers option (Figure A) when appropriate.
- Click OK, and Excel will convert the selected data into a formatted Table (Figure B).
When using Excel 2013, you can use the new Quick Analysis tool to format existing data as a Table. First, select the data range (all cells including the header row). Doing so will display the Quick Analysis tool in the lower-right corner. Then, click this tool to display an options palette. Click the Tables option, and then click Table. (I don't think this route is any quicker, but you might prefer it.)
Built-in styles are a good start
Excel has a number of built-in Table styles. There are a few good reasons to consider using these built-in styles:
- They're dynamic and Excel applies these formats as you add and delete rows and columns, when sorting, and even filtering.
- There are several.
- You can tweak a style to save time.
- Using Live Preview, you can see how the style looks before applying it.
- Because styles are thematic, other objects—such as charts, PivotTable objects, and so on—can be quickly formatted using the same theme in order to maintain consistency.
Now that you know why you might want to use these built-in styles, let's look at what's available and how to apply them. With the Table selected (clicking any cell inside the Table will do), click the contextual Design tab. All of the options on this tab relate only to the Table object. In the Table Styles Quick Gallery, you can see a few. To see more, click the More button. Then, a quick click to one of the thumbnails in the resulting gallery (shown in Figure C) applies that style. (With Live Preview enabled, the Table will update accordingly as you move the cursor over the different options.)
The applied style is denoted in the gallery by its thick border.
After applying a style, you can adjust it to better meet your needs. For instance, Figure D shows black font in the header cells and the Currency numeric format applied to the Commission column. Excel knows these new formats are now part of the Table and will use them as you add records.
Enhance the default style.
There are a number of ways you can format the Table:
- If you don't need the filter arrows, turn them off by clicking inside the Table, clicking the Data tab, and then clicking the toggle Filter option in the Sort & Filter group.
- You can also access built-in formats from the Home tab: click inside the Table and choose Format As Table in the Styles group.
- To remove the row banding, uncheck Banded Rows in the Table Style Options group on the contextual Design tab.
- You can create the Table and apply a non-default format at the same time by clicking inside the data range and choosing the non-default format from the Format As Table drop-down gallery in the Styles group on the Home tab. You just eliminated a few clicks!
The truth is, you probably don't need a lot of help from me to format a Table—knowing you can is the real tip. Next, I'll show you how to combine a Table with features you're familiar with to add functionality and flexibility to a Table.
Add conditional format
You might not realize that you can apply a conditional format to a Table object. For instance, let's add a conditional format that highlights commission values when they're greater than $350, as follows:
- Select the Commission column. To do so, hover over the Commission header cell until the black down arrow appears, and then click. Doing so will select the existing columnar data without selecting the header cells or any cells below the Table.
- Click the Home tab.
- In the Styles group, choose New Rule (Figure E) from the Conditional Formatting drop-down.
- In the resulting dialog, choose Use a formula to determine which cells to format in the Select a Rule Type list.
- In the Format values where this formula is true: control, enter the formula =$E2>350, and then click Format.
- In the Format Cells dialog, click the Fill tab, choose a color, and click OK to return to the first dialog, which now displays the formula and the format (Figure F).
- Click OK to see the results shown in Figure G.
This conditional format is simple, but almost any conditional format you can apply to a non-Table range, you can apply to a Table object. When designing a Table, add formatting to the first row so the Table can automatically format new records as you enter them.
An Excel Table uses an autofill feature to extend formats and formulas to new rows during data entry. It'll do the same for a validation control, which a non-Table range can't do. Data validation is a great tool with a stable input cell, but the feature can't (easily) handle an expanding range. To illustrate how easily a Table handles this tool, let's add a validation control that lets users select personnel, as follows:
- First, you need a unique list of names (personnel). You could do this many ways, but to simplify the example, enter PersonnelList, James, Luke, and Martha in H2:H5.
- Using the instructions given earlier, convert this list into a Table (Figure H). Be sure to denote correctly that the new name table has a header cell. (I'll explain why you want to use a Table for your name list later.)
- Select the existing values for the Personnel column, C2:C34. (Don't select the header cell in row 1; when the Table is empty, select the single cell in the first empty row.)
- Click the Data tab.
- In the Data Tools group, click Data Validation.
- From the Allow drop-down, choose List.
- Click inside the Source control, and then select the list of names or enter the range $H$3:$H$5 (Figure I).
- Click OK.
After assigning data validation to the column, you can select any cell in the Personnel column and choose a new name from the drop-down instead of typing the name manually. When you add a row, the Table will extend the validation control to the new row, as shown in Figure J. You can't (easily) do this in a non-Table range.
A Table extends validation to new rows.
Earlier, I recommended that you convert the personnel list to a Table. That's so you can quickly add new names to your validation list. Simply add a new name to the Table, and the validation drop-down will reflect the addition automatically, as shown in Figure K. You didn't have to do a thing other than add the name.
The validation drop-down reflects changes made to the source Table.
More than a Table
If you're not utilizing the Table object because you think they're limited, I hope you realize now how much they can do for you! Generate the Table and then use Excel's features and formats with it, the same as you would a non-Table range. Unfortunately, there are a few features, such as Subtotal, that the Table object doesn't support. If you run into this situation, you can temporarily convert a Table to text using the Convert To Range option on the contextual Design tab.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers that I help. You can contact me at firstname.lastname@example.org.