Want to become a Data God? Learn Excel Data Tables

Posted on September 10th, 2009 in Excel Howtos , Featured , Learn Excel - 99 comments

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.

Excel 2007 Tables Tutorial TipsWhat 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:
Create Table Excel 2007 Tables

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.

Table Formatting Options Excel 2007 Tables

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.

Easy Zebra Lines Excel 2007 Tables

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)

Tables Come With Filters Sorting Excel 2007 Tables

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:

Structured References Excel Tables Excel 2007 Tables

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?

Calculated Columns Excel 2007 Tables
Tables Total Calculated Field Excel 2007 Tables

6. Total your Tables without writing one formula

Tables Totals Excel 2007 TablesThe 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

Convert Tables To Named Ranges Excel 2007 TablesTables 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

Easy Pivot Tables Excel 2007 TablesWhat 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.

Export Tables To Sharepoint Excel 2007 Tables

Print Tables Only Excel 2007 Tables

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

99 Responses to “Want to become a Data God? Learn Excel Data Tables”

  1. Peter H says:

    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 .

  2. Dan Murray says:

    Nice post. I’ve been using Pivot Tables for years but haven’t used the “table” button until now.

  3. Michelle says:

    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?

  4. Nimesh says:

    Nice post. Nice to see such useful options in 2007.

  5. Frederick says:

    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?

  6. Doug says:

    Thank you. This is truly a gem that will save hours and hours of time.

  7. Robbert says:

    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.

  8. Michael says:

    Chandoo,
    nice post. Seems to be a useful feature. Does data tables exist in Excel2003 as well?

  9. Chandoo says:

    @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.

  10. [...] since I have learned the tables feature in Excel 2007, I have fallen in love with that. They are so awesome and so user [...]

  11. bazlina says:

    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!

  12. [...] Microsoft Excel Table Tips and Tricks – Learn Data Tables and Become a Data God | Pointy Haire…By chandoo.org October 16, 2009- ???????Excel???????????????……?X?…… [...]

  13. [...] Microsoft Excel Table Tips and Tricks – Learn Data Tables and Become a Data God | Pointy Haire…By chandoo.org October 16, 2009- ???????Excel???????????????……?X?…… [...]

  14. [...] Microsoft Excel Table Tips and Tricks – Learn Data Tables and Become a Data God | Pointy Haire…By chandoo.org October 16, 2009 [...]

  15. g7 says:

    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

    • Joe Blauh says:

      This is a huge negative for “structured” references. Excel created a nifty tool that requires me to accept a significant loss of functionality if I decide to use it. It also prohibits dragging cell references to change formulae, so if I drag to fill, I have to click into the cell to manually edit the reference cells to what they should be instead of being able to drag the cell reference box back to the correct column. A small step forward and a giant step back. Or, more succinctly, Microsoft – ‘nuf said.

  16. sb says:

    Chandoo,

    I have a question on this. In my sheet I have the “Total Row” added. In that I take an average of a column values and I need to reference this final Average value elsewhere. How Can I reference this specific “totalled average cell” such that when new rows are added the same cell is taken?

    As of now, since the total is at the bottom of the table, when a new row is added the cell id of this “total average” row keeps changing. I tried to move it to the top of the table to keep it constant but I couldn’t.

    thank you
    sb

  17. Chandoo says:

    @SB.. you can use the [#totals] tag in the structured reference to total row else where like this:

    =Table1[[#Totals],[Column1]] This will work even when you add more rows to the table.

  18. sb says:

    Thanks @chandoo.

  19. [...] will save precious amount of time when you are busy modeling. (100s of tips on keyboard shortcuts, excel tables, formulas, charting & [...]

  20. [...] Select your list of products (or invoices or cats) and make it in to a table. (here is a helpful tutorial on excel tables). [...]

  21. V S Venkatraman says:

    Dear Chandoo (Excel Guru)

    Thank you very much for sharing such useful tips… now I feel more confident in analyzing a data with Excel….

    Regards

  22. DangerMouse says:

    Chandoo,

    Where referecing table columns as range input to formulas such as sumif(), is it possible to get Excel to treat the reference as static for “fill” purposes?

    Cheers,

    Steve

  23. Chandoo says:

    @DangerMouse.. you can use ctrl+c ctrl+v instead of drag fill technique to treat the table references as static.

  24. olegko says:

    How to make absolute reference on Table column?
    I have Table1 and use such formula:
    =SUMIFS(Table1[Pay],Table1[Month],G9)

    I want to “freeze” columns “Table1[Pay]” and “Table1[Pay]” in formula (like $A:$A).
    How to do it?

  25. Chris says:

    Looking at article like these make me sad :( – still being laden with 2003 really goads when you see so many fantastic improvements just out of ones rech like this……

  26. [...] Excel Tables, a newly introduced feature in Excel 2007 is a very powerful way to manage & work with tabular data. I really like tables feature and use it quite often. If you are new to tables, read up Introduction to Excel Tables. [...]

  27. Avinash Ahire says:

    I want to learn some excel course from you.
    If you have any training centre in Mumbai, Please let me know..
    Excellent Work!

    Thanks
    Avinash Ahire

  28. 5.antiago says:

    Is there a better way than ctrl-c-ctrl-v to expand the ~ifs() formulae across a row while keeping the absolute references to the auto-named ranges in the data-table? I know this has been mentioned a few times in the comments but I’m hoping someone cleverer than me might re-look at this…

    All I’ve got so far is transposing my new table I’m trying to create so I’m dragging down instead of across, which holds the named column references, but I would prefer a dragging across solution

    This is my first post on your website Chandoo, but I’ve been reading for a couple of weeks. It’s a fantastic site! Thanks for all your efforts

  29. brices11 says:

    I love it, I love it, I love it. Don’t know how many errors I run into because of bad cell references this should help mitigate this.

  30. PM says:

    Ok REALLY stupid question, I created the table and I have been putting in formulas using the column names, how do I “without using my mouse” select the table name from the tool tip ( or drop down) that shows up. Currently I have to scroll my mouse down to the right column name and then click it add the bracket etc. This also applies for when I use formulas in general. Please help, I am trying to be “Mouse free” :)

    I have been using Excel forever this is amazing.

  31. Jim Morley says:

    Hi Chandoo,

    I am using Excel 2007.

    I am using an excel workbook to enter data for several different sites (each site has its own worksheet). I want to establish a summary worksheet showing the consolidated data accross all sites so as I enter data into the individual site worksheets the data is also replicated in the summary worksheet which is then sorted by site to show the consolidated view of all sites. Each of the site worksheets have the same data headings which will be replicated in the summary worksheet.
    Can you please give me some advice as to how I can go about this?

    I know how to use the basics of excel but know nothing about using databases like Access so I would prefer to continue to gather the information in excel if this can be done.

    Hoping you can help me,

    Jim Morley

  32. [...] The Data Table function should not be confused with the Insert Table function. [...]

  33. [...] Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural [...]

  34. padmanarayanan says:

    Chandoo,
    I really missed you discount offer last week or so,That day i could not access the internet.Any chance of getting discount offer, i am really interested in joining your course.pl let me know.

  35. Jen says:

    Chandoo, thanks a lot for all your tips and tutorials. Your tutorials are really great and easy to follow!!!

  36. Shanmugavel says:

    Hi, Can i access Excel 2007 table via code – JavaScript?

  37. Jonathan says:

    WOW!
    I’ve spent days organising a “table” of my own (without knowing it) writing formulas,creating helper columns and generally getting stuck and i just figured out that tables and pivot tables do all this in minutes. I dont know whether to feel downhearted or elated that i’ve learnt this! (all be it too late)

  38. [...] Learn Conditional Formatting 3. Making Dashboards using Excel 4. Project Management with Excel 5. Working with Excel Tables Topics & Archives 1. Learn Excel – Topic-wise 2. Charting Tips, Tricks and Tutorials 3. Ask an [...]

  39. Debaranjan samal says:

    Hi ,

    I need a vba for excel training in delhi, kindly suggest regarding this..

  40. Deb says:

    How do I get the AutoFormat of Double-Bars in XL 2010? It was so easy in 2003, but I cannot find a way to do it, w/o VBA, in 2010! It can’t be that hard.

    In 2003, Format, AutoFormat, choose the List 2 style. It formatted rows in groups of twos, or pairs. Very handy in lots of cases.

    Any ideas?

    Thanks

  41. Premalatha says:

    I have become a fan of tables now. :-)

  42. Premalatha says:

    Hi Chandoo,

    I used structured reference but when I close and open again, they have all become cell references! Is there a way to fix this? I am using 2010. Thanks,
    Prem

    • Prasad says:

      Even I have the same problem and worst is it when we add new rows the formulas now do not get changed as it is now having cell references :(

      Regads,
      Prasad DN

  43. Muhammad Bilal Yousaf says:

    Dear Sir,
    i want tech the micro soft excel data table and function for opperating the micro soft excel. please give send the tip of micro soft excel.

    with best regards and wishes,

    Muhammad Bilal Yousaf

  44. FarooqAsim says:

    Dear
    I went to some new learn work in excel
    latest
    best Regard
    Farooq Asim

  45. Linda says:

    Absolute references:
    As Chandoo says, these move if you use drop and drag.  However, if you highlight the required cells, place the cursor in the cell to be copied and use Ctl Enter then the formula copies keeping the references “absolut”

    Linda

  46. [...] have set up this data in an Excel Table called as tblSales so that it is easier to write [...]

  47. Eric says:

    I’ve tried to name tables so that I can reference them in functions, etc… but I’m not having any luck getting the name to stick.  I can click in the area to the left of the function button and type a new name for the table, but clicking outside of that causes the name to revert back to what it was (for instance, 386, or 3).  Thoughts?

    • Hui... says:

      @Eric

      Select any cell in the Table you want to rename

      You should see a Table Tools, Design Ribbon

      Select that Ribbon and on the Left there is a Table Name: dialog

      Change the name and press Enter

       

  48. [...] wrote a great post several years ago that explained the basics of Excel tables and provided a number of tips and tricks related to them. [...]

  49. Naresh Koganti says:

    How to rename a Data Table name in Excel.

  50. vimal says:

     i like you your website, but i quesestion i wil  written to excel in accountant but evry time name party not  incuding, so anything i find to party name  is all ready this excel in ready name ,so you my question solve and reply to me my email id vimal_jariwala15@yahoo.ca  

  51. Nitin K. says:

    Hi,

    How can one add zebra lines in table rows in Office 2010?

  52. Paul Nickell says:

    I have learned so much in the last few weeks since joining in here. Well done.
     Now I have a query that I need hlep with. The company I have started working for uses loads of data. Reports are exported to Excel then sorted etc etc…
     The Key ref numbers are numeric, 12345.1 and 12345.10 etc etc. Excel will obviously sort the .10 above the .1 – Its an habbit of the reporting software to create a .1 and not .01 Is there anyway within Excel to block alter all the .1′s to .01′s? I’m talking thousands of them, not just a handful that could be formated as text manually.

  53. [...] Use Excel Tables: Since Excel 2007, we can create tables from structured data and write formulas, create charts that refer to dynamic ranges with ease. Click here to know more about tables. [...]

  54. [...] the dashboard on the Property Register converted to an Excel Table (a 2007+ feature that many are still unaware of) enables the use of  Slicer selectors in Excel 2013 to quickly give a dashboard feel. It’s [...]

  55. Steve Jones says:

    Great site, and your instructions are so easy to follow. Thanks so much!
    On sheet 1, I have a table of names and demographic information for each.
    On sheet 2, I am using the same table of names but different data across the columns.
    When I add a New Name to the table on Sheet 1, I have to manually extend the table on Sheet 2, to expose the new name.
    Is there a way to automatically expand the table on sheet 2, to show the new name added to the table on Sheet 1??
    Thanks so much
     

  56. Paddy says:

    Has anyone tried using named tables as data sources outside of Excel? For instance, referencing the table for a mail merge? If so, how have you got it to work? Thank you all!

    • Colleen says:

      Yes.  I use tables in a mail merge.  There’s no trick (I can think of) to it different than using any other Excel data to mail merge.  My own notes for mail merging mention that I open the Excel spreadsheet first and that I need to make sure that the active sheet is the sheet with the table I want to use on it.  I do seem to have had problems if a different sheet is the active one.

      There IS a trick to making sure your Excel formatting (like number formatting for $, etc.) comes through.  But that is true whether or not you are using a table.

  57. Colleen says:

    I like to say, “If you’re not using tables, you’re doing it wrong.”  Or at least 90% of the time you should be using them!

    I would like to say, “Tables are the best thing since sliced bread”, but it then occurs to me that sliced bread is highly overrated.  I can slice my own loaf much quicker than doing many of the things manually in Excel that tables make easier.

    I LOVE tables!  They have changed how quickly and awesomely I can analyze and present data. 

  58. Gijs says:

    Hi Chandoo, another awsome tutorial.

    Can you expand the VBA so that i can leave some categories blank and the table will show ALL entries for that category?  (eg all Customer Types or all Regions)?

    Thanx, Gijs. 

  59. [...] ourSales[month] refers to the month column in the ourSales table. Works only in Excel 2007 or above. Know more about Excel Tables. [...]

  60. Chandra says:

    So I created a data table and wrote forumlas using the data table and column names.  It all calculated out beautifully.  HOWEVER, now that I want to add lines to my data table, none of the formulas are seeing the added data.  I’ve gone in to expand the range of the table, etc.  But still no luck.  So stinkin’ frustrated :/  Any advice??

  61. [...] best way to create a tracker is to use Excel tables. Set up one with 4 columns – Employee name, vacation type, start date & end date, like [...]

  62. Reena says:

    Hi,
    I need to convert some data into columns can anyone help me?
     

  63. [...] Visit link: Microsoft Excel Table Tips and Tricks – Learn Data Tables and … [...]

  64. Louis Moodie says:

    Dear Chandoo,
    I have done much research and your Excel offering is certainly highly rated and I would like to subscribe. I am using Excel 2011 for Mac. Will the course content be compatible? I assume it will but perhaps you have been made aware of some idiosyncrasies?
    I look forward to hear from you.
    Louis
    P.S. Are you aware that your reference to “GOD” will be offensive to many in the Biblical-based faith community (of which I am a practising one)? I suspect you are using it in a different context but it does detract from your brilliance.

  65. [...] how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us [...]

  66. [...] how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us [...]

  67. [...] select any cell in range and press CTRL+T. Specify a name for your table from design tab. Read introduction to Excel tables to understand [...]

  68. Rina says:

    Dear Chandoo,

    Just one word “Amazing” I like this site ;)
    Thanks for sharing all of these

    Good Luck

  69. [...] wrote a great post several years ago that explained the basics of Excel tables and provided a number of tips and tricks related to them. [...]

  70. arindam says:

    hi chandoo,
    I m unable to get the point 6. plz help.

    • Hui... says:

      @Arindam
      The Menu’s Chandoo shows as Pt 6 vary from Excel version to Version
      They are accessible by
      Click in a PT anywhere
      A menu Bar will appear with Pivot table Tools
      It may have a Options Tab and a Design Tab
      Select the Design Tab
      You should see the options shown in Pt 6 here

  71. […] am sure you all are aware of a feature called as Excel Tables OR Structured References in Excel. Excel Tables is (in my opinion) the best way to store your raw […]

  72. Mona says:

    Hi, in point number 6 of http://chandoo.org/wp/2009/09/10/data-tables/
    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”.

    How do you change the summary type from sum to say average?

  73. Denis J says:

    Table Text Filtering – is it possible to have column 1 ( in this case named “WO”) in the table filter automatically based on the date referenced in another worksheet cell within the same workbook?

    Thank you :)

  74. Vinay Chande says:

    Excellent presentation…it would surely interest the most excel fearing people I knw

  75. […] LinkExcel data table is a series of rows and columns with related data that is managed independently. … Excel 2007 has some great pre-defined table formatting opt…chandoo.org […]

  76. Arif says:

    how can add last paid amount in vacation track.

Leave a Reply