Table Style formatting meets multiple needs and, in some cases, may offer all the Excel functionality you need for your lists.
Creating a table
Start with a well-built list to achieve optimal results: one row, one record; column titles; and consistent data types in each column. On the Home tab, Styles group, click the Format as Table button.
This reveals the Table Style gallery from which you can choose a variety of shadings and borders. Your data will take on the visual characteristics you chose, as well as the following features:
- Column filters
- Automatic expandability
- Feature toggle switches
- Automatic named range
- “Sticky” column titles
The Table Tools contextual tab, called Design, contains these features. It will appear as soon as you create the table and anytime you click inside it. That’s a hint, by the way. If you click on a work sheet cell that isn’t in the table, you’ll notice the Table Tools contextual tab is no longer there. It’s an easy misstep, so just remember when a contextual tab disappears, ask yourself, “What was I working on?” Then, click on it!
Using table style options
The Table Style Options group contains checkboxes to turn on and off headers, first and last column highlighting, banded rows and columns and a totals row. The Total Row option allows you to select from a dropdown list of functions at the bottom of each column to sum, count, average, etc., the data in that column.
Here are some other automatic features to try.
1. With Total Row turned on, place your cursor on the last cell of the last row of data, just above your Total Row, and press Tab. You will notice that a row is inserted below the row you were on, but above the Total Row. Totals will now include your new row of data without adjustment.
2. Add a new column with a formula, and it will automatically format like your table and copy itself down the column.
3. Although the Remove Duplicates feature doesn’t only apply to formatted tables, you’ll notice this along with a couple of other options in the Tools group on the Table Tools Design contextual tab.
4. Scroll down your long lists and notice that your column titles remain at the top of the work sheet.
Contributor: Melissa P. Esquibel, Microsoft® Certified Trainer