SubscribeWant to become a Data God? Learn Excel Data Tables
Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.
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.
So, What do you think about data tables?
I say, give them a try. You can find some cool uses for tables in your data to day work. They are intuitive, easy to use and provide great power without added complexity.
Related Material
Excel 2007 Review – 10 things better and cooler in excel 2007
Excel 2007 Productivity Tips & Tricks
Excel Pivot Tables – Tutorial & Tricks
Excel Conditional Formatting – 5 Must Know Tricks
Trackbacks & Pingbacks
- Pingback by Another Reason why Tables are so awesome [quick tip] | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on October 8, 2009 @ 9:27 am
- Pingback by Google Reader Shared Items – October 17, 2009 on October 16, 2009 @ 6:01 pm
- Pingback by Shared Items – October 17, 2009 on October 16, 2009 @ 6:04 pm
- Pingback by Shared Items – October 17, 2009 on October 17, 2009 @ 12:15 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Adjust width of Column Shortcut Alt,...
Chandoo, I have only been using data tables for a few weeks & have discovered that they can be used to have charts dynamically expand to take in new data.
Simply set up the chart data in a block with appropriate headings (headings must be text, not formula).
Convert it into a table, select the whole table and insert a chart.
Format the chart as required.
When new data is added the the table and chart will expand to show the data.
The only problem I have found is that the last column of data must show a number when inserting the chart- a blank, #N/A and perhaps text will cause wierd changes in the chart. However this only occurs when first inserting the chart once set up it accomodtes blanks & #N/A etc. This is easily over by adding a temporary number when required on setting up the chart then replacing it with the correct formula when the chart is complete.
This seems to be a lot easier than using offset formula for the series .
Nice post. I’ve been using Pivot Tables for years but haven’t used the “table” button until now.
This is great! Never used data tables before but it helps a lot. One question though, if I try drag your SUMIF formula across a row, different columns are selected in the formula. Normally you can make the columns static in a formula by using “$” – any suggestions on doing the same here?
Nice post. Nice to see such useful options in 2007.
hi Chandoo,
How do you “record” the screen captures of the screens that you need to show into the animated gifs? Do you use a special software for it?
Thank you. This is truly a gem that will save hours and hours of time.
Nice post! However, in a basic form this functionality already existed in Excel 2003 as a ‘List’ (ctrl-L). So it not needed to convert the table back to a normal range for excel 2003 users.
Chandoo,
nice post. Seems to be a useful feature. Does data tables exist in Excel2003 as well?
@Peter H: Very cool tip about the charts and data tables.
@Dan: Tables are very useful and simple. Pivot can be very powerful for data analysis, but tables are good for maintaining databases.
@Michelle: The sumif formula in the article is written outside the table in a cell. If you write formulas in and copy (ctrl+c) and paste them, then the references are not changed. But if you drag the cell (thus auto-fill), then the cell references to table columns are changing. Not sure why excel would behave like this.
Also, inside the table, you can use [#this row] operator to calculate values for that row alone.
@Frederick: I use camtasia studio to record the screen. It is a nice software. You can test it from techsmith website.
@Doug: You are welcome
@Robert: My mistake, I meant version earlier than excel 2003.
@Michael: yeah, they are called as Lists. Press, Ctrl+L to create one.
i kept saying OMG WOW THATS AMAZING over and over.
i’m quite new with excel so your blog helps a lot and this post, is truly great. thank you!
4. Bye, bye cell references, welcome structured references
it does not beat absolute reference to cell i.e $A$1, because if named range is used, it will not copy correctly if u use cell dragging horizontally… it will move to the next name range