They have some features that I think are MAGICAL, so keep reading to see why you should be using them to save time and make your workbooks easier to use days, weeks and months after you created them.

NOTE: Excel Tables are not to be confused with Data Tables that can be used for what-if analysis.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the workbook and practice what you learn. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

How to insert Excel Tables

They typically only have column headings, with no blank columns within the table.

To insert an Excel Table select a cell anywhere in the data range you want converted to a table and press CTRL+T.

In the example below the Table begins with the headers on row 3. Rows 1 and 2 are not part of the Table.

Magical Feature 1

Once your table is inserted you will see that formatting is applied automatically and a new tab will appear on the ribbon called ‘Design’.

Here you can choose from 61 different built in styles to change the format as I have done above, or create your own in line with your branding.

If you don’t want to work with your data in an Excel Table but you like the instant formatting it provides, you can convert the table to a regular range while keeping the style formatting you applied.

You’ll find this option on the Design tab of the Ribbon, or simply right click anywhere in the table and select Table/Convert to Range.

Magical Feature 2

Insert a Total Row. Whoop-dee-doo I hear you say. Ah, but this isn’t any ordinary Total Row.

OK, I agree it looks pretty ordinary so far, but take a look at the picture below. Simply select a different function from the drop down list, or select More Functions to enter something else.

The Total formulas don’t have to reference the Excel Table; however it’s unlikely you’ll need to do this.

You can toggle the Total Row on and off and Excel will remember the functions that exist.

As you add rows/columns your Total Row will automatically update to include the new data.

The Total formulas use the SUBTOTAL function so that if you Filter your table the Totals will only SUM the visible cells.

Magical Feature 3

Insert a formula in a new column.

1) Type in your new heading immediately after the last column and the Table will automatically incorporate the new column in the Table range and apply the formatting.

2) Tip: Format the cell before you enter your formula.

3) Enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.

4) Add rows and your formula will AutoComplete to fill the new rows.

5) Any changes you make to the formula will automatically propagate to all rows.

6) Notice the formula in the formula bar below does not refer to cell references. Instead it uses what Excel calls ‘Structured Referencing’ which is very similar to Named Ranges. Read Magical Feature 4 below for more on this.

Excel Tables 2007 Syntax:

Excel Tables 2010+ Syntax:

Magical Feature 4

Working with Formulas in Tables

When you insert a Table Excel automatically gives the Table and each column a Structured Reference, similar to a Named Range.

This Structured Reference automatically updates as you add or remove data.

The name for the table is usually something like ‘Table1’ and the columns will have names based on the column headings in the table. If you don’t have any column headings Excel will automatically insert headings like Column1, Column2...and so on.

As you add or remove columns the Structured References automatically update.

Renaming Structured References

You can rename the default ‘Table1’ name on the Design tab in the Properties section.

Note: [#This Row] or [@row] for Excel 2010 onwards is indicating that the formula is applied to each row individually as in the example below.

Excel 2007 Syntax:

Excel 2010+ Syntax:

Getting to grips with using Structured References in formulas takes a little practice, but just imagine how much time you will save once you can write a formula from anywhere in your workbook without to-ing and fro-ing back and forth between sheets.

Important things to know about Structured References:

Table names are unique to the workbook. If you have 5 Tables in a workbook on many sheets, each table will have its own name which you can reference from any sheet.

You can’t have a Named Range with the same name as a Table or Column.

You will find your Table names in the Name Manager alongside your Named Ranges.

If the table name or columns names are changed the formulas will automatically update with the new names.

Magical Feature 5

They automatically insert Filters.

I’m not going to go into how to use Filters here, but for those of you who know how to use them, you'll be pleased to know that all formulas used in the total row automatically exclude any rows hidden by the filter.

And any new rows added are automatically included in the total.

Absolute and Relative References

Unfortunately you can't use the F4 key to apply absolute references to Structured References in formulas. They have some quirky rules depending on whether you're copying and pasting a formula or left-clicking and dragging it. More on working with absolute references for Table Structured References.

What do you like about Excel Tables? Are there any features that you find annoying?
Did you like this tutorial or could it have been better? Let me know your thoughts in the comments below.

Please share this with your friends and colleagues on Twitter, LinkedIn, Google+ and Facebook etc.

Comments

I’m a big fan of Excel tables and their structured referencing. Much more intuitive than R1C1 style addressing when referencing table rows and columns.

But one thing I *dislike* about structured referencing is that, when I copy a formula from one table column to another, and the source column includes a structured reference to another column, that reference doesn’t change in the target column as it would with relative references.

For example, suppose I have a table containing month columns (Jan, Feb, … Dec), and the Jun column contains the formula =1.1*[@May]. If I copy that formula to the Jul column, I end up with the same formula (=1.1*[@May]), not =1.1*[@Jun] as I might expect.

That’s because structured references are, by definition, absolute. If relative references are desired in a table, they must be the standard R1C1 style addresses.

I wish Excel provided a way to tag a structured address as relative, similar to how I can use $ within an R1C1 style address to tag a row or column as absolute. I could then copy formulas from one table column to another with the expected (relative reference) result.

Hi Paul,
Strange, by default, table structured references are relative, not absolute, the column name should change when you copy the cell to the right.
You can make structured references absolute, here is a tutorial: excel-table-absolute-structured-references
If you want to prevent the formula to change the column name when the formula is copied across, the absolute reference should look like this:
=1.1*Table1[@[May]:[May]]
Catalin

Sure doesn’t work that way for me. When I *copy* a formula from a table column containing a structured reference to another table column, the column name reference *does not change*. That means, at least in the case of copying/pasting, structured references are absolute, not relative.

Apparently, as I just discovered, if you use a cell’s handle to drag it’s formula to another cell, any structured references in the formula *is* treated as relative, and changes accordingly in the target cell.

In my opinion MS made a mistake here. Structured references *should* be relative by default, just as R1C1 style addresses are relative by default, and should produce the same result whether the cell is copied or dragged.

Hi Paul,
There is indeed a difference between copying and dragging a cell with a formula in a table, but it’s not the only difference between normal excel cell references and structured references. For example, there is no switch to make the reference absolute in a table, like the $ sign in a normal cell reference.
Can’t tell if this is a mistake or not, a table is a complex object, it’s hard to transfer all normal operations to a different type of object.
Just knowing these differences, should be enough for you to decide what operation you should do, right? 🙂
Catalin

Love the column chart… Like to know how you combined the 3 columns (Data, Max, Min) into one for the chart. Also how did you get the column color and only the correct data label to follow – did you use Conditional formatting?

FYI – I did add different numbers to the data and the column (line chart did also) came out correctly.

If you do not have time to reply, I understand but these are something that would be very good in the classroom and in business.

Hi Akhil,
If you type this formula in the same table in another column, excel intellisense should automatically enter the table reference if you select a cell from the table
=LEFT([@name], LEN([@name]) -3) should be the formula.
If the autocomplete feature does not work, check your settings from File-Options-Formulas-make sure the option: Use table name in formulas is checked.

This seems like a simple problem, but I’m struggling. Maybe you can help.

I’m using a userform for data entry, with a listbox tied to a named range (MainDB). When I add a new record, I can see the database update, but the list does not update properly until I exit the form and return. Is there a way to refresh the listbox data without exiting? Should I convert the named range to a table?

Hi Bill,
Usually, that listbox is populated when the form is activated. If you add new values to the source of the list, you should write code to update that listbox (use a new procedure to update list: clear existing entries and use the same code you have for populating the listbox again)
To clear the listbox: listBox1.Items.Clear()
Catalin

Excel Tables are 1 of those fabulous features, I use them almost invariably now.
Nevertheless, there is 1 thing that one should bear in mind. Before setting up an ET or converting an existing data set to an ET, one should carefully consider the following.

When a new row of data is added to the ET, the AutoComplete is done based on what is in the 1st row of data. If, after setting up an ET one wants to add a new row to act as row 1, hoping to have that new row update existing rows & fill in new rows at the bottom, it won’t work, i.e. it does not update.

One could convert the ET back to a normal table, then reconvert into an ET, but that only sometimes do the trick. I have not come across a satisfactory solution for this.

In my opinion the best way is to carefully plan beforehand what data should be in row 1, then stick with that.

I’ve recently found your site and guidance; they’re excellent and very informative. Congratulations.

I have a question about remote access to Excel Tables. I’m using MS Office Professional Plus 2013.

In one workbook I have a number of data tables (the Source). Some of these have been formatted as Excel Tables, some as named ranges.

A separate workbook has been configured as an Excel Template from which I generate a number of different workbooks that look up data from the Source dependent on a drop-down constrained list. The setup works really well and the newly generated workbooks populate properly and update when the Source is changed and when they are opened or forced to refresh data.

EXCEPT I’ve just discovered that the data is not looked up if the Source is not open. Look ups to the named ranges work whether or not the Source is open. Look ups to the Excel Tables do not. If I have a generated workbook open when the Source is closed the looked-up cells are blank (due to IFERROR). As soon as I open the Source, the blank cells populate properly and immediately.

Is this something that is known or designed, or do I need to start error tracking? I’m using Excel Tables because the data in the Source is dynamic. If I can’t use Tables I suppose I’ll have to go back to OFFSET.

Hi Stephen,
Welcome to MyOnlineTrainingHub, glad to hear you like our materials.
What “Lookups” are you using? VLOOKUP or INDEX-MATCH?
I would extract data from the Template with Power Query, this way you will no longer have external links, and you will still be able to refresh data from Template. It’s a much stronger setup, in my opinion.
Cheers,
Catalin

Hi Catalin,
Thanks for your reply. I’ve done some more work on this since I posted and I’ve established that the remote lookups (using INDEX in an Array formula to return multiple matches) do not work when the source is an Excel Table, unless the source file and the destination file are both open on the same computer. The same formulae work when the source is a Named Range, even if the source is not open, and even though the cell references for the Range are identical with the Table. I find that very odd.

I should mention that this is on a corporate infrastructure so clients using the documents might be in different parts of the UK, and all the files are on a central SharePoint server. I don’t think the Excel has Power Query installed and I can’t add it to the corporate installation.

I’m managing the central source index and I can do most of the reporting on my local machine, my issue was just making sure that any time anyone opened one of the destination documents it always contained the current data, looked up as it opened. I’ve achieved that by eliminating Tables, I just have to manage the changes in range myself. When I get time I’ll automate that as well!

Then you can try to bring source data from Data tab, Get External Data section, From Other Sources – From Microsoft Query , from excel files. You will have to browse to your excel file, then select the tables/sheets you want to bring into your file. This data connection is refreshable also.
Cheers,
Catalin

One other nice feature to mention is that when you scroll down a table to the point where the column headers would normally disappear, those headers get “absorbed” in the column in place of the column’s letter designation. That way you don’t actually have to freeze your headers in a long table.

Hi Mynda,
I have a Table (Table1) in columns B thru H
I have this formula in K5 =J5+Table1[@Days]
I want to fill across from K5 to BD5
I want J5 to increase as I go (J6, J7 etc) but I want the table reference to stay absolute.
I can do this one cell at a time by selecting K5, then hitting Ctrl+R, L5, then hit Ctrl+R etc
But I can’t seem to do all the cells with a single swish
Can we make table references absolute?
Regards – Dave

Ah, OK, thanks. To create the original formula, I typed ‘J5+’ then clicked on a cell in the table. Is there some way the absolute table reference can be entered by clicking, or do I have to type it in manually?
Dave

Hi Rob,
The automatic naming is not optional, it cannot be turned off. Power Query and Power Pivot will also give automatic names to new columns if you don’t provide one, excel cannot work with columns with no names…

The Table Formula Shortcuts should be used like any other range reference in a formula, they are structured references to excel ranges.
For example, if the range A1:C10 contains an excel table, where row 1 is the Headers row (with these column Names: Agent Name, Month, Sales), and you want to sum the data from column C, a formula will look like this: =SUM(C2:C10)
With structured references, the formula will look like this: =SUM(Table1[Sales]). Structured referfences will make formulas more readable, because you can actually understand what the formula will sum, without the need to go to the range of cells C2:C10 to see what is in those cells.
Note that you refered to a table column like “one column =Table1[[#Headers],[Column1]]”. The expression is refering to a single cell, in headers row, column 1, not to the entire column 1. A reference to a column will look like Table1[Column1], or Table1[[#All],[Column1] (this last expression includes the column name, not only the data range)
Cheers,
Catalin

Point #3 explains that when you enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.

I hope that clarifies things. It doesn’t really matter if you format before or after entering your formula but I find it’s quicker to select one cell to format than a range of cells that might expand past the visible page. I prefer not to format entire columns as it can bloat your file size.

Seriously. Before finding your site, I had considered myself a bit of an Excel guru. Now, I feel like I don’t even know a fraction of what there is to know about Excel. Fortunately, because of my background, your webinars & tutorials are very easy to follow and apply immediately. Working in Process Improvements and Change Management, I can use the new features I am learning about on a daily basis to inform leadership and monitor our progress.

This tutorial is excellent and fulfills all the required information that is necessary for the creation of tables and insertion of Data along with Formulas. I really admire your endeavoring that are helpful people like me and I am really thankful and grateful for your kindness with respect to spreading knowledge.

Magic Feature #3 is not working for me when I download your practice sheet. Is it possible it is because I am using Excel 2013? I had to manually add the new column to my table. When I added the formula, I had to right click on the cell to get the option to copy down.

Second question on the extra column, I created a % of total by writing the formula: =[@Salary]/table1[[#Totals],[Salary]]. However, if I want to change the total line to maximum then the formula changes to % of maximum. Is there an easy way to keep it constant even if I change the way I look at the total?

I do Excel development using a lot of VBA and while I only use tabular data (thought you’d appreciate that) I’ve not formally used Tables before. I have an upcoming project where they might be appropriate but it was not clear from the article if or how to reference the Table data using VBA.
Could you comment on that or point me in an appropriate direction to get more information
Some great material on your web site, clear and easy to understand.
Thanks
Brain

Hi Brian,
The syntax to refer to table objects is quite simple:
-To refer to an entire table:
Range(“TableName”)
-To refer to a column:
Range(“TableName[ColumnName]”)
-To refer to Headers:
Range(“TableName[#Headers]”)
To easier manipulate the table, you can refer to table components , using ListObjects method:
Sheets(“Sheet1”).ListObjects(“TableName”).HeaderRowRange is identical to Range(“TableName[#Headers]”)
More info you can find at:the-vba-guide-to-listobject-excel-tables
Cheers,
Catalin

I take your dashboard online course. I do follow the links you put to understand better the subject. Here it says download the data but it doesn’t work. I wonder I do some mistakes? I have mac 2011 excel.

I suspect your PC/Browser is changing the file extension when you download it. The file is a .xlsx file so please ensure when you right-click then File Save As that the file extension is .xlsx and if not please change it. You should then be able to open the file like any other Excel file.

I really really appreciate your messages and my words are insufficient to explain my thanks. I thank you many many times.
I will tell you how I solved the issue. My problem was with the link (Download the workbook and practice what you learn) on above the page. When I tried to download the link I was getting a page with written an ascii code. It was not an excel file. So I copied the link in an excel worksheet and then I tried to open the link. Yay! Here the excel file is. It works well.
My macbook might cause the problem, I don’t know what the reason was. Anyway it works well and I am on my way to learn from your excel pages.

It sounds like you were originally left-clicking (or the equivalent on the Mac) the link and your browser was trying to open the Excel file. Whereas you should be right-clicking the link and saving the file as an Excel file and then browsing to the file location to open it.

This was great! I had Excel for Mac 2008 at home for several years while I was working with Excel 2007 in Windows at work. Have since upgraded to Excel 2011 at home. Am slowed down a bit by having to “translate” concepts between the platforms, but it was very helpful to learn about Excel tables. In particular, I want to incorporate Structured References into my current project.

And those Magical Features – they ARE just that! It might be nice to see these illustrated in a future video.

What’s been most helpful is to be able to listen to your Dashboard tutorial, stopping every few slides to read your related tutorials (like this one) and work through the downloadable workbook. Your Raw Data sheet was already formatted when I opened that file, so I brushed up on removing the table formatting and filters, finding the formula for selecting the data range with a keyboard shortcut, and then creating the table (which is invoked with CTL+L on the Mac). I do believe I’ll catch up soon.

Are you referring to the screenshots on the blog post above? If so, they aren’t miniscule when I look at them so there must be something on your PC causing the problem.

The download file is a .xlsx but your browser is changing the extension on download. Download the file again and at the File Save As screen type over the .zip extension with .xlsx and you will be able to open the file as a regular Excel file.

One snag I just ran into with tables is that if you protect the sheet, the table won’t add new rows for the user. One way to get around that was to use data validation (instead of protecting the sheet) that would keep users from overwriting important formulas but then you get those ugly triangles all over your sheet. I ultimately had to take away the table for that project – it was a shame because it worked so well as a table. Susan

I have an Excel table set up in one workbook and am trying to do a SUMIFS function in a different workbook using some of the structured ranges in the table. I can’t seem to be able to make it work, so am wondering if the table functionality and structured ranges are available only within the same workbook?

If I set up the SUMIFS function within the same workbook I can type “Table1[” and once I type the [ I get a list of the available structured ranges. This doesn’t happen when I begin the function in a different workbook and then click over to the workbook with the table and begin typing “Table1[“.

I’m trying to practice using tables so that I become more familiar with them. Finding out about these and how they work has been a real eye-opener. 🙂

The easiest way to reference cells in another workbook is to start your formula in workbook 1, that is type in =SUMIF( then with your mouse navigate to the other workbook and select the range of cells in the table.

Excel will fill in the necessary file name, sheet name and table references. It should look something like this:

When you enter a formula in Excel you can start it with + or – and when you press ENTER Excel will automatically add the = sign for you. I sometimes write my formulas starting with a + since it’s on the numeric keypad and easy to press…. saves me reaching all the way over to the equals sign 🙂

The download file is a .xlsx file. It sounds like your browser is changing the file extension on download. You need to download it again and make sure that the file extension in the ‘Save as’ (or equivalent for your browser) is .xlsx before saving it.

So, right click the link > choose ‘Save file as’ or ‘Save as’ or similar > choose a file location and change the file extension to .xlsx

I am creating a database if you will that shows ALL temps on one sheet.
then on the 2nd and 3rd sheet I would like to have terms on one and active on the other. I dont know what would work best to have the terms automatically extracted from sheet one into sheet 3 once term date has been entered on sheet one. Can you help please

please solve my problem
i am working in library my problems is a man take a book for rent.
book issu time 8:00 am and book return time 3:00 pm so reader book reading in 2days. what i can use excel formula this condition. and tell me time in 24 hours. tell me how much time he was reading book total time in24 hours.
i shall be thank full for solve my problems.

[…] all in a list format. We can load lists (Home tab > Close & Load) into our worksheet in an Excel Table, or load it to the data model (Power Pivot where it’s converted to a Table), but in their list […]

[…] strings together in the one formula. More on joining text here. More on the TEXT function here. More on Excel Tables and Structured References here. I’d like to say a big thank you to Roger Govier for sharing this tip. Download the Excel […]

[…] Tables to house the source data, and Structured References to build formulas quickly. The other benefit of using Tables was as each stage finished I just pasted the new data under the existing data and it was automatically incorporated into my formulas. Voila, alright! […]

[…] my formulas above use Structured References because my tabular data is formatted as an Excel Table. Excel Tables have many advantages from quickly writing formulas to automatic set up of dynamic […]

[…] Excel Tables come with inbuilt named ranges called Structured References. When you reference a cell in a Table from an adjacent column Excel will automatically use the Table’s structured references because implicit intersection can be employed. You can see the structured reference in the formula bar below: Note: If I were to tab through the cells D2:D9 you would see the same formula in every cell. However, if you reference a cell in a table from a cell outside of the table’s rows (2:9) you will get the regular cell references, as you can see in the formula bar below for cell D11: […]

[…] on my Validation sheet (see image below) I have my list of usernames and passwords formatted in an Excel Table called ‘security’. Tip: make your passwords a bit more robust and less predictable than mine […]

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?