In the general sense, a table is just data that you have arranged into columns and rows. But in Excel, when you use the Format As Table button to define an area on your spreadsheet as an Excel table, there are several things you’ll be able to take advantage of that make entering and working with your data a little easier.
You can define a table before or after you have started entering data. If you’re starting from a blank worksheet, select the area you’ll be using for your table. Don’t worry about the number of rows, that will be adjusted later. If you already have data on your worksheet, just make sure your cursor is within the area you want to define as a table. Click the Format as Table button (It’s located in the Styles section of the Home ribbon) and choose a formatting style from the list. In the window that pops up. You can adjust the area selected for the table in the “Where is the data for your table” field. Also choose whether or not your table has headers. Normally that box should be checked. Clearing it will tell Excel that the first row in your table is data, not column headings. Click OK. You’ve just defined and formatted a table. So what does that do for you?
Easier Data Entry – When you enter something in the last column of a table, pressing the tab key will take you to the first column of the next line instead of to the next column.
Easily Locate Information – You’ll see a down arrow button on the right edge of each column heading (the header row). This button gives you quick access to filtering and sorting your table.
Shaded (Banded) Rows – If you miss green bar paper you’ll love this. Many of the table formatting options have alternating light and dark shaded rows to make it easier to follow each line. And when you reach the end of the formatted area, Excel will automatically extend the formatting as you enter data in the next row. Formatting is also adjusted if you insert rows or columns. On the Design tab of the ribbon there’s an option for banded columns if you prefer to have the columns shaded instead of rows.
Formulas Automatically Copied to Each Row – When you enter a formula in a table, it will automatically be copied to each cell in that column. It will even be placed in new rows as you add them.
Easy to Read Formulas – If you enter a formula in a table by clicking on the cells (for example, type “=” then click on cell B2, type “+” and click on cell C2), your formula will use the column headings instead of the cell reference. For example if column D is “Qty” and column E is “Unit Price” you might end up with a formula that reads =[@Qty]*[@[Unit Price]] instead of =D2*E2. Microsoft calls this a “structured reference” and “=D2*E2” is called an explicit reference. If you rename a column, the formulas will self adjust.
Flexible Total Row – On the Design tab of the ribbon (visible only when you are in the table) in the Table Style Options there’s a check box for Total Row. Check it and a row for totals will be added at the bottom of your table. By default only the last column will have a total, but click in any cell on that row and a button will appear that lets you choose a total type for that column. Choices are:
- None
- Average
- Count
- Count Numbers
- Max
- Min
- Sum
- StdDev
- Var
- More Functions (this will open the function wizard, letting you select from all of Excels functions)
Easy to Resize – As I mentioned before, when you get to the end of a table, entering data on the next line will automatically expands the table, including formatting and formulas. The same is true for columns. But you can also resize a table by dragging the small triangle shaped handle that’s in the bottom right corner of every table. Or, on the Design tab of the ribbon you can click the Resize Table button if you want to type in a cell range for your table.
If you’re like me you’ve ignored tables in Excel because you were already comfortable doing most of these things other ways. But when you look at everything tables offer (especially easier data entry) it makes sense to change your routine and take advantage of tables.
