Tables help you manage and analyze a group of related data easier.  Therefore, you can turn a range of cells into a Microsoft Office 2007-2010 Excel table (previously known as an Excel list). A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

To determine if a table is a complete table, just click in cell of that table and click CNTRL+A for Select All. See below:


Notice that the table is highlighted in blue and it is surrounded by empty clear cells like an island. Therefore, if you wanted to sum each quarter or sum by salespeople, you just click in the empty cell and click AutoSum icon. This is also true for sorting a column. If you want to sort by salespeople, all you need to do is click anywhere in the salesperson’s column and click on the Sort icon. No need to highlight the entire table like in the previous versions.

Once you established a table, you can click anywhere in that table and go to Insert Table and state what is the range of the table and that the first row is the header. This command will automatically insert filters, banned rows, and will allow the table to be expand keeping style of the table intact (this is called table expansion). See below:

 

A Table Tool menu appears that allows you to change the design of the table and much more.  The beauty of tables is that the formulas are now in a natural language.  If you use AutoSum, it no longer creates the typical sum formula (for example: =SUM(B4:E4)).  This what it will create:

=SUM(Table1[@[Qtr 1]:[Qtr 4]])

What it says is that in this table which is named Table1 will sum all the values from quarter 1 through quarter 2 for this row. See below:

 

Once you hit ENTER, the sum of the the salespeople are generated instantaneously down to the last row (row 19) of the table. BINGO that easy!No need tocopy down the formula! See below for the results:

 

In the row 19, sum is display.  My suggestion is to delete this word and go to the Table Tool menu and check the box that states Total Row. Excel will automatically place the word Total and will leave a blank row above it. This row does not only sum but if you click in an empty cell, a down arrow symbol appears to the right of the empty cell and you have a choice to choose not only Sum but Average, Count, Count Numbers, Max, Min, StdDev, Var, More Functions. See below:

Lastly, you will notice that there are arrows to right of the headers.  These are the filters where you can query the table quickly.  If you do not like to see these arrows, you can simply remove them by clicking on the Data Tab and click on the icon called Filter.

I commend Microsoft for a job well done on tables.  Go and have fun.  This will excite you.

Advertisements