What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

When you make a table (more on this in a sec) you can easily add more rows to it without worrying about updating formula references, formatting options, filter settings etc. Excel will take care of everything thus making you a Data God.

How to create table from a bunch of data?

To create an excel table, all you have to do is select a range of cells and press the table button from Insert ribbon in Excel 2007.

See this simple tutorial:

Today we will learn 10 excel data table tricks that will make you a data god, no, lets make it data GOD.

1. Change table formatting without lifting a finger

Excel 2007 has some great pre-defined table formatting options. Just select any cell in your table and change the table formatting by going to “format as table” button in the home ribbon.

If you are bored with the predefined formats, you can easily define your own table formatting color schemes and apply them.

2. Add Zebra Lines to Tables without doing Donkey Work

When you create a table, zebra lines come as a bonus. And when you add new rows to the table, excel takes care of zebra lining or banding automatically. You can turn on / off the banded rows feature from “design ribbon tab” as well.

That means you don’t need to use conditional formatting or manually format alternative rows in different color.

3. Tables come with Data Filters and Sort Options by default

Each data table comes with filters and sorting options so that you can filter and sort the data in that table independently. That also means, if a worksheet has 2 tables, they each get their own data filters (usually excel wont allow you to add more than one set of filters per sheet, but when it comes to tables, all exceptions are made, just for you)

4. Bye, bye cell references, welcome structured references

The most important advantage of tables is that, you can write meaningful looking formulas instead of using cell references. When you create and name the table (you can name the table from design tab), you can write formulas that look like this:

The beauty of structured references is that, when you add or remove rows, you dont need to worry about updating the references.

5. Make Calculated Columns with ease

Any tabular data will have its share of calculated columns. Excel tables make having calculated columns very easy. With structured references, all you need to know is English to make a calculated column. The beauty of calculated columns in table is that, when you write formula in one cell, excel automatically fills the formula in the rest of cells in that column. Aint that good for a God?

6. Total your Tables without writing one formula

The ability to summarize data with pivot tables is extended to excel tables as well. You can add total row to your table with just a click.

What more, you can easily change the summary type from “sum” to say “average”.

7. Convert table back to a range, if you ever need to

Tables is a new feature in Excel 2007. So when you need to send that excel file to a colleague running excel 2003, you can easily convert the tables back to named ranges.

Excel will take care of the formulas and change the references to cell references.

8. Export Tables to Pivot Tables, Woohoo

What good is a bunch of data when you can analyze it? That is where Pivot tables come in to picture [pivot table tutorial]. Thankfully, you dont need to do much. Just click a button and your table goes to pivot table.

9. Push the table data to Sharepoint Intranet Site

If you have a corporate intranet Sharepoint portal, you can easily publish the excel tables as share-point lists. This can be handy if you want to publish, say the top 10 sales persons of the quarter on the intranet.

10. Print Tables Alone, with out all the other stuff around

Select the table, hit CTRL+P and select “Table” for “print what?” and you will be able to print the tables alone. This is far more easier and cooler than trying to adjust print settings when you are printing tabular data.