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).
So how to use tables?
Simple, select any cell in your data and press the big button.
To use a table:
- Select any cell in your data
- Press CTRL + T or click on Insert > Table
- 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.
- Start with introduction to Excel tables
- Learn all about structural referencing
- Connect multiple tables with relationships
- Using VLOOKUP and other formulas with tables
- Customize table styles
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.
22 Responses to “This year, become a Very Table Genius”
nice post and thank you for sharing
And a clever ass title!
I would add that knowing and understanding table references is a nice step stone towards mastering DAX as well.
Ha, definitely missed that until reading the comments.
Ha, I definitely missed that one until I read the comments.
Ha, I definitely missed that one until I read the comments.
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.
@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.
@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.
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.
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...
I rarely use Tables,
but I use pivots a lot
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.
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
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
Dear Rudra
WHat is Option Compare Text?
Must confess it took me around 5 reads for the penny to drop on the title!
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...
Very useful article..........keep sharing
thanx
Love the title - very topical, very clever!
Hey there,
Telling VBA to consider case insensitivity...
e.g.
rudra = Rudra or RUDRA or rUdRa