The Table feature was originally introduced into MS Excel in 2003 and called Lists. In 2007, they changed the name to Tables and that's the way it remains in 2010. So if you're coming to Excel 2010 from version 2003, just be aware of the difference. The features of the tool are identical.

Creating a Table Neatness counts for everything in Excel, so the data in your worksheet should already be organized into neat rows and columns before creating a list. Let's say you have already done that and you have a worksheet that looks like the one below with names and dates.

There are a couple of ways to convert this list into a table. Actually, it's the same process, just different ways to access the tools.

One way go to the Home tab and click the Format as Table button. It can be found in the Styles group of the ribbon and looks like this.

Clicking this button will unfurl a dropdown menu from which you can choose a predefined table style, or create your own.

Once you've selected a style, a floating window will open.

Select the data range by either typing it in, or clicking any cell in the data range.

This dialogue box confirms the area for the data and that your worksheet has column headers. If it doesn't, that's okay. Excel will create them for you. Just uncheck the box. (Column headers are the letters of the columns in the greenish box.)

After clicking OK, the worksheet above now looks like this:

Another way to convert a list into a Table is by going to the Insert tab and clicking the Table button . You will see the same Create Table window as above.

Anytime a Table is created or selected, the Table Tools/Design tab becomes active.  Below the Table Tools Design ribbon are zooms of the left and right sides of this ribbon.

From here you can easily change the Table Style by simply selecting one from the list on the right. You can also choose to insert or remove a header row, a total row, whether to add a dark line between each column or row (by using the Banded Rows or Banded Columns boxes).

Let's take another look at our finished table:

Notice that the column headers now have little arrows next to them? Those are called AutoFilter buttons. Clicking them opens a drop-down menu that allows you to easily sort the items in your table. For instance, in our example, we may want to sort our list of names alphabetically from A to Z. To do so, simply click the AutoFilter arrow and select Sort A to Z.

From this menu, you can also choose which items to remove from your list. For instance, if I were to uncheck the Andy and Candy boxes, those entries would be removed from my table. But don't worry if you do this--you can easily restore them by launching the dropdown and checking the Select All box.

Be aware though, that the AutoFilter menu changes as the date in each column changes. As an example, take a look at the AutoFilter menu for our Date column:

Now, instead of being able to sort alphabetically, you're able to sort the columns from Newest to Oldest or vice versa. You can also select which dates to include by checking the boxes.

We'll talk more about this process later in this article.

Inserting Rows and Columns into a Table

Adding rows or columns to your table is easy. In the bottom corner of every table is a wedge, as illustrated in the next example.

You can enter information into the cells just like you would any other cell in the spreadsheet.

We'll talk about how to add columns between columns or rows between rows later on in this article.

Adding Up Values

To add up the values in a list, you must use the Total Row checkbox in the Table Tools/Design tab. It looks like this:

The Total Row button automatically totals the columns. See the snapshot below.

If Column C had data that couldn't be totaled, like a list of names, it would tally the number of names instead. 

However, that doesn't mean we have to produce a sum for the last column. Since it contains numbers, MS Excel automatically gave us the sum, but we have other choices Excel lets us make. Click on the cell that gives the total (83) and an arrow will appear.

We're going to choose, for this example, to average the numbers rather than producing a sum. We'd select Average. The average of all the numbers in Column C would then appear in the total box instead of the sum.

Later in this article, we'll learn how to total on columns other than the last one.

Entering Data into a Table

You can enter data into a table just like you entered data into any cell in Excel 2010. Just click on a cell and start typing.

Sorting Data in a Table

Any column in your table can be easily sorted by clicking on the AutoFilter arrow beside the heading and choosing one of the selections. Let's say, for this example, we wanted to sort the names alphabetically.

First, we'd click the AutoFilter arrow beside the word Names.

Then, we'd click on Sort A to Z.

When we click on Sort A to Z, all names are alphabetized.

You should also notice in the above example that the AutoFilter button acquires another icon. In this case, it is an upward pointing arrow, which represents an ascending sorting.

Using Filters to Sort Tables

Filtering data in your Table is as easy as sorting it. Simply click on the AutoFilter arrow and select the data by which to filter the table.

We've chosen the Name column and to filter the data using Candy. You can easily select or deselect all the names by clicking the (Select All) button.

Only Candy's information is showing. The rest have been filtered out.

To clear the filter, launch the AutoFilter dropdown and click Clear Filter From "Names". Names, in this case refers to the header. If the column header was Date, it would have said, "Clear Filter from 'Date'".

We can also use the Text Filters options. You access this by launching the AutoFilter dropdown menu, clicking Text Filter, then select an option.

Using Table Border to Expand a Table

Click and drag the lower right hand corner of any Table to expand it or shrink it. Any new data that you cover when you expand it will be automatically included in the Table.

Totaling and Tallying Data

Earlier in this article, you learned how to tally and total information in the last column by checking the Total Row box on the Table Tools/Design tab. However, you can get tallies and sums for other columns as well once you've totaled the last column.

Simply click in the cell on the Total Row beneath the column that you want to total or tally. An arrow will appear in that cell. Click that arrow to choose what kind of total you want Excel to produce. 

We're going to choose Average.

As you can see, Excel filled in the average date for us.

Data Validation

Data Validation lets you choose what information is acceptable to enter into a cell. For instance, you may have a product code that has four digits. You can set up a cell so that anything other than a 4-digit number will display an error message.

To set up Data Validation, select a single cell, or even a range of cells, then go to the Data tab. The Data Validation button can be found in the Data Tools group.

 Clicking the icon automatically opens the Data Validation window. If you click the lower portion of the button, however, you will be given three options. Click Data Validation to launch the Data Validation window. It looks like this:

Since we're going to set up a cell to accept only a 4-digit number, we will select Text length from the drop down menu that says "Allow" over it. From the Data dropdown menu, we are going to select "equal to" and in the length text field we will type "4." That tells Excel we want an entry with four characters.

From here, we can hit OK and have Excel provide a generic warning that looks like this:

Alternatively, we can create a custom warning by selecting the Error Alert tab. It looks like this:

Here we have selected the style called "Stop" and entered the title and the text of our error message. When a user enters a code of less or greater than four digits, the message will look like this:

There are three kinds of error messages available in Excel: information messages, warning messages, and stop messages. Information and warning messages do not prevent invalid information from being entered into the cell--they simply inform the user that such an entry has been made. Users can choose to ignore the warning. A stop message, on the other hand, will not allow an invalid entry at all. It has two buttons, Cancel and Retry. Cancel restores the cell to its original value, and retry returns them to the cell for further editing.

You can also set up a message to remind users what the restrictions or expectations are. Use the Input Message tab to create a custom reminder. It will display anytime the cell or the range of cells is selected, as in the example below.

Finding Invalid Entries

You can easily find invalid entries by going to the Data Validation button and clicking the downward pointing arrow at the bottom of it. This will unfurl a tab with three options, as in the following example.

If you click the Circle Invalid Data button, a red circle will appear around any invalid entries.

Correcting the entry will make the circle vanish, of course, or you can choose to leave the information as it was entered. You can then remove the circles from your spreadsheet by clicking the Clear Validation Circles button.

Auditing

MS Excel 2010 gives you a variety of tools to audit information in a worksheet. Just like data forms, formula auditing can take some of the confusion and frustration out of dealing with lots of different formulas. The formula auditing functions can be found on the Formulas tab, of course.

It is not always apparent which cells have formulas in them. Therefore, the first step to evaluating formulas is finding them. One way to do this is by going to the Formulas tab and clicking the Show Formulas button.

Ordinarily, Excel only shows the answer to the formula in the cell. When you click Show Formulas, however, Excel shows you the formula. A spreadsheet in which the formulas are visible looks something like this:

As you can see, all of the light gray cells contain formulas. Another thing you can do to identify formulas is go to the Home tab and click the Find & Select button. This will reveal a drop-down menu as in the next example.

Click Go To Special, then check Formulas in the Go To Special window.

Doing this locates and selects all of the cells in your worksheet that contain a formula. Now you can use the Fill Button  to highlight all of the cells that contain a formula in your spreadsheet. As noted in the next example, this does not reveal the actual formula, it just gives you an easy way to identify the cells that contain them.

When you're finished editing your document, be sure to return to the Go To Special window and reselect the formula cells. Then change the fill color back to the original.

Trace Precedents and Dependents

A precedent cell is one that is referred to by a formula in another cell. For instance, in the next example, we've selected the cell highlighted in yellow. The formula for that cell is "=SUM(D8:D18)", which means that Excel will add the values of cells D8 through D18 and display the sum. Cells D8 through D18 are precedents for the selected cell.

Looking at a formula can be confusing though, so MS Excel gives you a tool that will help you visualize the relationship between a selected cell that contains a formula and its precedents. this is the Trace Precedents  button.

Take another look at the following example. You should see a blue arrow stretching from cell D8 to the cell highlighted in yellow. This shows us that the formula in the selected cell relies on the values of the cells above it to fulfill the equation.

If we were to click the Trace Precedents button a second time, Excel 2010 would show us the precedents for the precedents, as in the next example.

So if the cells shown by the blue arrows above are called "precedents", the cell highlighted in yellow must be the "dependent". Obviously, since it "depends" on the other cells to perform a correct equation.

MS Excel 2010 also gives you a tool to be able to see the dependents of a cell. This is called the Trace Dependents button and looks like this: . To use this feature, select a cell that is part of a formula and click Trace Dependents. Below is an example of what you will see.

In the example above, the cell highlighted in yellow has at least two dependents, as indicated by the blue arrows. You can continue to click the Trace Dependent button to see even more dependents associated with that particular cell:

The Remove Arrows button  does exactly as its name applies. Click it to remove all of the precedent and dependent arrows in your document. You can also choose to remove only the precendent arrows or only the dependent arrows. To do this, click the tiny arrow to the right. This will unfurl a dropdown menu from which you can make your selection.

Checking Formulas For Errors

In this example, the issue was introduced into the cell highlighted in yellow, but, as you can see, that error also affects the formulas in its dependent cells (those that are green.)

If you were to select a cell that contains an error, an exclamation point would appear next to it.

Click on this exclamation point to see more information about the issue.

Here, as inside the cell, we're told that the issue is an "Error in Value." You can get help with the error or see the calculation steps. You can also choose to ignore the error or edit it in the Formula Bar.

If you were to click on one of the cell's dependents, you'd also have the option to trace the error, which we've done in the next example. The red line points to all of the cells that are affected by the error.

Another way to find errors in your spreadsheet, is to navigate to the Formulas tab and click the Error Checking button: 
If you've ever used the spellcheck feature of Microsoft Word, then this feature should seem familiar to you. It works on the same concept. The Error Checking goes through your spreadsheet and points out errors as it comes to them.

Below is an example of the screen you'll see when Error Checking arrives at an issue.

As you can see, you have pretty much the same options you'd have if you clicked the exclamation point next to the error-filled cell. You can get help with the error, show the calculation steps, ignore the error, or edit it in the Formula Bar. As you can see though, you get more information about the error. First, it tells you the formula "=B15-F3". That way you can easily double-check it to see if it was correct. Then it explains the type of error. In this case, it was an "Error in Value", which means that one of the values is of the wrong data type. That is because the cell we chose to create our error with is a cell with text in it, rather than a number.

It should be noted here, that you can also trace the error by clicking the arrow at the right of the Error Checking button and clicking Trace Error.

Clicking the Show Calculation Steps will launch the Evaluate Formula window, which will show you each step in the formula and help you find out where the error is. With this wizard, you'll see a different screen for every step in the equation. In this case, we can see that the formula in our cell is trying to subtract 10 from "Projected Monthly Income." Obviously, this is the source of our error, because at the bottom of the window it tells us "The next evaluation will result in an error."



You can also launch the Evaluate Formula window by clicking the Evaluate Formula button  .

Using the Watch Window

The Watch Window helps you easily keep track of the information you've entered into a cell. It's a floating window that tells you the location of the cell, a name if you've given it one, the value in the cell and any formula that it contains.

You can find the Watch Window button in the Formula Auditing group of the Formulas tab. It looks like this:

The Watch Window looks like this:

As you can see in the illustration above, we're monitoring cells D15, D4, and G11. You can add more cells by clicking the Add Watch button and selecting the cell to be added. You can also delete watched cells by selecting it in the watch window and clicking Delete Watch.