Tables began as lists in the menu version of Excel, but they’ve become more powerful in the Ribbon versions. Converting a data range into a table extends functionality, which you can then use to work more efficiently and effectively. Here’s a look at why you should consider using a table instead of an ordinary data range.
To create a table, click anywhere inside the data range and click Table in the Tables group on the Insert tab. This article doesn’t include step-by-step instructions for working with lists (the table’s predecessor) in Word 2003, although much of the functionality is present in the older list feature. Also, bear in mind that as handy as tables are, they don’t accommodate all of Excel’s features. For example, you can’t use Excel’s Subtotal feature with tables. When you need a feature that tables don’t support, temporarily convert the table into a range.
1: Easy sorting and filtering
Excel automatically adds filter controls to the header row when you convert a range into a table. You don’t have to do a thing but use them to sort and filter the table’s records. You can execute a sort quickly by choosing a criteria item or color from the drop-down. Figure Ashows the results of using a filter control to display only those records that meet the selected item (or color).
Figure A
This table of 14 records displays only two after you select Collectible from the Category field’s drop-down filter.
2: Quick formatting
Excel automatically applies a banded row format to new tables. If you don’t like that format, you can choose a new one from 60 predefined formats in the Table Styles gallery on the contextual Design tab. You can also use the Format As Table option on the Home tab. As you hover over each option, Live Preview updates the actual table, as shown in Figure B. That means less experimentation and reformatting.
Figure B
Excel’s live preview takes the guesswork out of formatting.
After choosing a format, you can quickly distinguish the header column and a totals column by checking the First Column and Last Column options, respectively. You’ll find these on the contextual Design tab in the Table Style Options group.
3: Effortless data entry
You can easily extend a table, making data entry much easier. To insert a new record, select any cell in the row below the table’s last row and enter a value. Excel will automatically extend the table’s formatting to the new row, as shown in Figure C. It also works with columns.
Figure C
To insert a new record into the table, just start entering its data!
4: Automatic nomenclature
When entering formulas, you use cell references and range names. When working with a table, Excel works all that out for you. To illustrate, let’s add a column to our example database that adds five days to the order date, to determine the optimum delivery date:
- In D2, enter the = sign and click C2 — that row’s order date. Instead of displaying C2 in the expression, Excel displays OrderDate, the column’s name, as shown in Figure D. The table resolves the name automatically; you don’t have to do a thing.
- Enter the rest of the formula, +5, and press [Enter]. Excel will enter the new date’s serial value and offer a set of AutoCorrect options. (You can apply a date format later.)
- Choose Overwrite All Cells In This Column With This Formula, as shown in Figure E. InFigure F, you can see that the formula copied to the remaining cells in the table.
Figure D
The table’s naming construct enters a reference instead of a cell reference.
Figure E
Choose the only AutoCorrect option to copy the new formula.
Figure F
Excel copied the formula to the remaining cells.
Excel also extended the table’s formatting to the new column (#3). When referring to the table from outside the table, Excel will include the table’s names in the reference.
5: Quick totals
Want a quick order count? Add a Totals row to the table as follows:
- Click anywhere inside the table and then click the contextual Design tab.
- Check the Total Row option in the Table Style Options group. Figure G shows the resulting row.
Figure G
Excel’s Total Row option adds totaling functions to the table.
This toggling option displays and hides a row of formulas. You can toggle it off for easier data entry (#3). Excel does its best to guess at your needs by evaluating the actual data. In this case, the only total added is a COUNT() function — the table contains 15 records. Using that function’s drop-down, you can easily change the function. Each field has a drop-down from which you can choose or change a function.
6: Always visible headers
In a data range, header cells disappear as you scroll, but table headers are always visible — with absolutely no work on your part. Figure H shows the result of scrolling down using the vertical scroll bar. Excel replaces the column header cells with the table’s header row. If you don’t see this at work in your own table, make sure the active cell is in the table.
Figure H
A table’s header row is always visible when scrolling down.
7: Formula autofill
When you extend a table to include a new row, it doesn’t just extend the formatting (#3). The table will also copy any formulas. Figure I shows a newly added record. As you can see, Excel extended both the formatting and the new formula entered in #4. The formula will update when you enter a valid date into C17.
Figure I
Tables automatically extend formulas when you add a new record.
8: No copy necessary
You can further automate #7 by allowing the table to copy the formula when you enter it. These columns are called calculated columns. To generate calculated columns automatically, simply change the following option:
- Click the File tab and choose Options. In Office 2007, click the Office button and choose Excel Options.
- Click Proofing in the left pane.
- In the AutoCorrect Options section, click AutoCorrect Options.
- Click the AutoFormat As You Type tab.
- Check the Fill Formulas In Tables To Create Calculated Columns option shown in Figure J.
- Click OK twice.
Figure J
Check this option to create calculated columns automatically.
The next time you enter a formula into a table, Excel will automatically copy it to the remaining cells in the column. You can repeat the steps in #4 for a quick test, if you like.
9: Dynamic charts
If you base a chart on a table, Excel will update the chart automatically as you change and enter new data into the table. Figure K shows a simple chart based on the example table after adding a column that stores the actual delivery date. After adding a record, Figure Lshows the updated chart after adding a new row to the table.
Figure K
This simple chart compares expected and actual delivery dates.
Figure L
Updating the table automatically updates the chart.
10: One-click select
One of the quickest shortcuts for selecting a data range is [Ctrl]+[Shift]+8. To select a table with a single click, hover the mouse over the top-left corner of the top-left cell until the small southeast-pointing arrow shown in Figure M appears. Then, simply click to select the entire table.
Figure M
When this arrow appears, click to select the entire table.
Courtesy: techrepublic.com