This year, become a Very Table Genius

Posted on January 11th, 2018 in Learn Excel - 20 comments

Let’s start the new year with a bang.

Excel Tables were introduced more than a decade ago, but a lot of people don’t know them or under utilize them. So start this year by becoming a very table genius.

What is Excel Table?

Excel tables are a simple and elegant way to structure and store your data. Let’s say you have staff details like below. Instead of calling it like A1:E72, you can convert this data in to a table and call it, you guessed it right, covfefe (or more coherent option like – staff).

Raw data to Excel Tables

So how to use tables?

Simple, select any cell in your data and press the big button. 

Insert Table in Excel

To use a table:

  1. Select any cell in your data
  2. Press CTRL + T or click on Insert > Table
  3. Bingo, your data is now a table.

Excel will name this table as Table3 or something like that. Just use Design tab to rename the table to whatever you want.

But why use tables at all?

Tables offer many powerful data analysis, reporting and storage features.

  • Structure and format the data: As soon as you turn your data in to a table, Excel will apply several quick formatting rules to your data. This simplifies how you enter, read and understand your data. Also, tables are logical structures to store your data. So when you add data at the end of table, the formatting and range for the table automatically extends.
  • Use structural references: If your data is in tabular format, you can apply structural referencing to write formulas. This is so much simpler than cell address based referencing of data. For example, to count number of staff at Manager level, you can use this formula:
    =COUNTIFS(staff[Manager?],”Manager”)Tutorial: All you need to know about structural references and table formulas.
  • Apply sub-totals and filters with ease: You can add a total row to any table to show various kinds of totals (sum, count, average etc.) for the displayed data. If you filter, update or add to your table, the totals change too.
  • Slice your data: Starting with Excel 2013, you can apply slicers to Excel tables. This way you can visually filter your data with ease.Tutorial: All about Excel slicers
  • Connect multiple tables with relationships: Starting Excel 2013, you can connect multiple tables just like a database. Once you have a data model like this in Excel, you can create powerful and insightful connected pivot tables too. Learn all about table relationship feature.
  • Send tabular data to Power Pivot, Power Query or Power BI with ease: Tabular data can be easily accessed by power tools like Power Pivot for Excel, Power Query (Get & Transform Data) and Power BI. This liberates your Excel data and enables powerful data clean up, analysis and visualizations.
  • Analyze data without hassle: If you create a pivot table from tabular data, then any changes to table are available to pivots automatically. You just need to refresh the pivot reports to see updated summaries. Learn more about Excel pivot tables.
  • Visualize data with ease: If you create a chart from tabular data, then anytime your table is updated (add new rows, delete rows or update data), then your chart is updated automatically. This enables powerful interactive and dynamic charting experiences for your users. Check out below dynamic chart examples.

 

Become a table genius then…

Press your big button without hesitation (or simply use CTRL+T) to enter the awesome world of tables. Check out below tutorials and tips to guide you along the way.

Are you a #TableGenius?

Are you #XLTableGenius? Go ahead and thump your chest and let the world know. Tweet with #XLTableGenius and post comments about how you rock the world with tables.

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

20 Responses to “This year, become a Very Table Genius”

  1. cybrot says:

    nice post and thank you for sharing

  2. dan l says:

    And a clever ass title!

  3. GraH says:

    I would add that knowing and understanding table references is a nice step stone towards mastering DAX as well.

  4. VvM says:

    I think you have a typo on the third bullet: there is no way to add sub-totals, only grand totals. I would love it if someone can prove me wrong, but it's nearly impossible to add sub-totals within a table. This is the primary reason I've seen many users convert back to range.

    • Hui... says:

      @VvM
      You can manually add subtotals using the SubTotal() or Agregate() functions and the Tables Grand Total doesn't include these in the Total.
      But it is an annoyance, that the Table doesn't offer this functionality.

      • GraH says:

        @VvM, It's kinda what's explained in the bullet point, no? That totals become subtotals when the table gets filtered/sliced.
        Personally I've never been a great fan of the sub-totals within ranges. Used that feature during a very limited time only. I then discovered pivots. And those work wonderful with tables.
        It is perhaps a matter of taste. I believe there are more advantages then disadvantages with using tables.

        @Hui, you are right, it can be done, but that kind of kills the advantage of structured columns in the table, I reckon.

        • VvM says:

          Don't get me wrong, I love and constantly extol the virtues of tables and pivot tables. But it's important to remember that we're sophisticated users that understand how to use and benefit from these features.

          For example, if I send some invoice data to one of the accountants I work with - they want to add subtotals at each change of (customer, date, etc), not at the bottom of the table that changes when they filter it. They don't want to create a pivot table, they don't want to have to learn new formulas, they just want to insert rows and SUM the rows above them and not mess up formulas or grand totals. Since they can't do this easily, they don't want a table - the cons far outweigh the pros for these users in this situation.

          • GraH says:

            Like I said, a matter of taste... I would just give them a finished (tabular) pivot. 🙂 But I understand what you're saying, I know that kind of users as well (and some dare call themselves Excel experts). Keep on advocating tables and pivots...

        • Hui... says:

          I rarely use Tables,
          but I use pivots a lot

          • grah says:

            But then again, sir Hui, you posses an impressive armory of formula, charting and not to forget VBA skills, you actually only need them occasionally. For those less skilled -- like me -- tables can be a blessing.
            I like them even more since I discovered Power Query.

  5. Deepak says:

    When I use this code that code give me error
    cdb1 is not highlight can u explain me
    Extract number from text in 2012 video

  6. Rudra says:

    I use excel tables everyday. I have a macro in my personal folder which does create excel table and does some sort of formatting too. Here is the code. ShortCut is Ctrl + Shift + T

    Option Explicit
    Option Compare Text

    Sub CreateAndFormatTable()
    Application.ScreenUpdating = False
    Dim TableName As String
    Dim Cell As Range

    Const ThemePath = "C:\Program Files\Microsoft Office\Document Themes 16\Theme Colors\"

    ' Keyboard Shortcut: Ctrl+Shift + T
    '
    On Error Resume Next

    ActiveSheet.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes).Name = Format(Now, "mmmddyyyyhhmmss")

    If Err.Number = 1004 Then
    MsgBox "Table Exists Already", vbInformation, "Table Exists"
    'Exit Sub
    End If

    On Error GoTo 0

    TableName = ActiveCell.ListObject.Name
    ActiveSheet.ListObjects(TableName).TableStyle = "TableStyleLight9"

    ActiveWindow.DisplayGridlines = False

    With ActiveSheet.ListObjects(1).HeaderRowRange
    .Font.Color = vbWhite
    End With

    With ActiveSheet.ListObjects(1).DataBodyRange.Font
    .Name = "Calibri"
    .Size = 11
    End With

    On Error Resume Next
    ActiveSheet.Name = "Detail"

    If Err.Description = "That name is already taken. Try a different one." Then
    ActiveSheet.Name = "Detail2"
    End If

    On Error GoTo 0

    For Each Cell In ActiveSheet.ListObjects(1).HeaderRowRange
    If Cell.Value Like "*Date*" Then
    Cell.EntireColumn.NumberFormat = "m/d/yyyy"
    End If

    Next Cell

    ActiveSheet.ListObjects(1).DataBodyRange.WrapText = False

    'ActiveWindow.DisplayZeros = False 'Not ActiveWindow.DisplayZeros
    ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
    "C:\Program Files\Microsoft Office\Document Themes 16\Theme Colors\Blue II.xml")
    'ActiveWorkbook.Theme.ThemeColorScheme.Load (ThemePath & "Blue II.xml")

    End Sub

  7. Harry says:

    Must confess it took me around 5 reads for the penny to drop on the title!

    • grah says:

      I was wondering, why are people going on about the title being clever?
      Then I took a closer look,... Got it now! I had to read in it French, though...

  8. siddik says:

    Very useful article..........keep sharing

  9. Brian says:

    Love the title - very topical, very clever!

Leave a Reply