• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Save Time With Excel Tables

You are here: Home / Excel / Save Time With Excel Tables
excel tables
December 10, 2010 by Mynda Treacy

An improved feature since Excel 2007 are Excel Tables (previously known as Lists). The purpose of an Excel Table is to aid with managing and analysing data.

Excel Tables allow for easier sorting, filtering and formatting and interpretation of your data.

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.

Excel Tables Course

This Tables mini-course will teach you all you need to know about working with Tables - in less than an hour.

Tables are really easy to use and give you huge efficiency gains with things like

  • Dynamic named ranges that automatically update formulas, PivotTables and charts
  • Formatting, like banded rows or columns
  • Sorting and filtering
  • Totals and statistics

===> Excel Tables Course <===

You’ll be completing your work faster in no time.


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.

Want to know more shortcuts like CTRL+T? Check out our list of Excel Keyboard Shortcuts
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.

Excel Tables

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:

Excel Table Structured References

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.

To rename the columns simply type a new name in the headers.

Table Formula Shortcuts

To reference:

  • The whole table =Table1[#All]
  • The headers =Table1[#Headers]
  • The entire column = Table1[[#All],[Column1]]
  • All data (i.e. not including the headers) in the table =Table1[#Data]
  • The header value of one column =Table1[[#Headers],[Column1]]
  • The current row in Excel 2007 it's: =Table1[[#This Row],[Column1]] or in Excel 2010 onwards it's simply =@Column1

These Structured References (Table1, #Headers, #Column1 etc) can also be combined e.g.

=Table1[[#Headers],[#Data],[Column1]]

......and so on.

Note: If you renamed your Table you will replace ‘Table1’ in the formula with your Table name.  Likewise you will use your column names if they aren’t the default Column1 and so on.

Using Structured References in Formulas:
  • Sum the Salary column =SUM(Table1[Salary])
  • Average the Salary column =AVERAGE(Table1[Salary])
  • Sum the whole Table =SUM(Table1)
Note: the above formulas exclude the header cells.
  • Multiply Salary by 0.1%
  • Excel 2007 formula: =Table1[[#This Row],[Salary]]*0.1%
  • Excel 2010+ formula: =[@Salary]*0.1%

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 2007 syntax for Table Structured Referencing


Excel 2010+ Syntax:

Excel 2010 Table Structured References


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.
  • Structured References are handy when working with data validation drop down lists, PivotTables, and anything else dependent on the Table range like formulas etc.

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.

excel tables

More Excel Tables Posts

Excel Tables as Source for Data Validation Lists

Excel Tables as Source for Data Validation Lists

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category: ExcelTag: excel tables
Previous Post:Excel Custom Cell Formats
Next Post:Make Excel Speak What You TypeMake Excel Speak What You Type

Reader Interactions

Comments

  1. Barry

    July 1, 2022 at 4:38 pm

    Hi. Always like getting your newsletter – almost always find something I didn’t know. (not suprising as I am still’novice’). The 30 June issue deals with tables. I have a bit of an issue with one I created. Lots of cells full of text. I have named cells (named in the ‘basic’ way) using the Name Box. If I add a new line at the bottom of the table and then sort this into order the cell name don’t go with the data so that if I click a hyperlink on another sheet to take to specific data it goes to the wrong place. Do I need to master Structure Referencing — will that resolve this issue. Currently I just have to remember to use Insert to enter new data in the ‘correct place.
    Thanks for all the assistance.
    Regards.

    Reply
    • Mynda Treacy

      July 2, 2022 at 8:20 am

      Hi Barry, if you need the name to move with the cell as it’s sorted, then you’d need to write a dynamic named range. https://www.myonlinetraininghub.com/excel-dynamic-named-ranges

      Reply
  2. Scott

    August 24, 2021 at 7:55 am

    Which lesson would I need for:

    I need a table to Pop up when I click into it

    Nothing fancy – just a 15 rows by 5 columns

    Reply
    • Scott

      August 24, 2021 at 7:56 am

      Clarification to above – I need a pop up table to “pop up” when I click into a specific cell.

      Reply
      • Mynda Treacy

        August 24, 2021 at 9:36 am

        Because you want to enter data into it, or just containing information? Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

        Reply
  3. Robert Marolt

    May 26, 2021 at 7:38 am

    I’ve created a personal checkbook and love that it is in a tabular format so that I can quickly look at spending in various categories or months by filtering the columns but I have the issue of maintaining a running balance. You take the previous rows balance then add the credit and subtract the debit of the current row. I wrote a macro that I have to run every time I add new entries. Is there a better workaround?
    Respectfully,
    Bob Marolt

    Reply
    • Mynda Treacy

      May 26, 2021 at 9:17 am

      Hi Bob,

      Please see this tutorial: Excel Table running total formula.

      Mynda

      Reply
  4. Salim Gul

    March 1, 2020 at 5:02 pm

    Hi
    Madam Philip Treacy

    Kindly teach me formula of auto schedule of semi annual dates of 5 years
    for example 2 installment in each date of 01-01-16, 01-07-16, 01-01-17, 01-07-17, ……..01-07-20
    if i put only 5 year, 2 installment, the formula auto set schedule of each semi installment of future date till last installment date

    Regards

    Salim Gul

    Reply
    • Mynda Treacy

      March 1, 2020 at 7:03 pm

      Hi Salim,

      It depends on which version of Excel you’re using. Please post your question on our Excel forum where you can upload a sample file showing your data and what you’d like to see. Also let us know your Excel version.

      Mynda

      Reply
  5. RKeev

    January 31, 2020 at 10:51 pm

    Great information; what I have found giving excel classes etc is that many folks don’t know that tables can have slicers. You don’t need a pivot table to have slicers, they work with ranges converted into tables.

    Reply
    • Philip Treacy

      February 1, 2020 at 8:02 am

      Thanks Richard

      Reply
  6. jatinder chhatwal

    January 31, 2020 at 4:24 pm

    ITS A EXCELLENT TUTORIAL-MANY MANYTHANKS

    Reply
    • Philip Treacy

      January 31, 2020 at 5:52 pm

      You’re welcome.

      Reply
  7. Paul Laba

    December 13, 2017 at 2:42 am

    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.

    Reply
    • Catalin Bombea

      December 13, 2017 at 4:38 am

      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

      Reply
      • Paul Laba

        December 14, 2017 at 4:17 pm

        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.

        That’s wildly inconsistent. R1C1 type references certainly don’t behave that way: if a cell’s formula includes a relative R1C1 address (no $ prefixing the row and/or column), copying or dragging that cell to another cell produces the exact same result. Why shouldn’t structured references behave the same way?

        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.

        Reply
        • Catalin Bombea

          December 14, 2017 at 7:30 pm

          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

          Reply
  8. Burdette

    October 7, 2017 at 3:06 am

    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.

    Reply
    • Mynda Treacy

      October 7, 2017 at 10:26 am

      Hi Burdette,

      For the benefit of others reading this, your question is referring to the charts on this post: https://www.myonlinetraininghub.com/label-excel-chart-min-and-max

      The Data, Min and Max columns are set to overlap as per step 3: Overlap columns; right-click any column > format data series > Series overlap 100%, Gap width 60% (or whatever you prefer).

      The colors are set at the series level as per step 4: Colour code min/max; left click the max column > format the fill colour. Repeat for the min column.

      Kind regards,

      Mynda

      Reply
  9. Arif

    September 20, 2017 at 10:56 pm

    Excellent explanation

    Reply
    • Mynda Treacy

      September 21, 2017 at 8:53 am

      Thanks, Arif 🙂

      Reply
  10. Akhil

    June 17, 2017 at 3:48 am

    Hi Mynda,

    I am trying to create a structured reference using this formula – “=LEFT(C2, LEN(C2) – 3)”. Please could you help me with this?
    Table name = P01table
    Column specifier = name
    Item specifier = #Data

    Thanks a ton!

    Reply
    • Catalin Bombea

      June 17, 2017 at 5:09 am

      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.

      Reply
      • Akhil

        June 17, 2017 at 6:19 am

        Hi Catalin,

        Thanks so much for your help – that worked absolutely perfectly!

        Reply
        • Catalin Bombea

          June 17, 2017 at 2:57 pm

          You’re welcome 🙂

          Reply
  11. Bill Palmer

    April 2, 2017 at 8:24 am

    Hi Mynda,

    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?

    Thanks for any help you can provide.

    Bill

    Reply
    • Catalin Bombea

      April 2, 2017 at 6:03 pm

      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

      Reply
  12. Peter Buyze

    March 26, 2017 at 8:46 pm

    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.

    Reply
    • Mynda Treacy

      March 27, 2017 at 8:51 am

      Indeed, Peter. Tables are designed with consistency in mind. Therefore the formula in row 1 should be the same for the entire column.

      Mynda

      Reply
  13. Paul Richardson

    January 23, 2017 at 3:33 pm

    checking it out now

    Reply
  14. Stephen

    December 1, 2016 at 11:07 pm

    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.

    Thanks for your help.
    Stephen

    Reply
    • Catalin Bombea

      December 2, 2016 at 2:04 am

      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

      Reply
      • Cavs

        December 8, 2016 at 9:26 pm

        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!

        Thanks for your help.
        Stephen

        Reply
        • Catalin Bombea

          December 9, 2016 at 6:24 am

          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

          Reply
  15. Carter Hoag

    September 23, 2016 at 10:00 am

    This would be great as a video!

    Reply
  16. Gord Miller

    September 2, 2016 at 12:07 am

    So well presented–concise and so easy to read…passing on to my staff. Thanks.

    Reply
    • Mynda Treacy

      September 2, 2016 at 9:16 am

      Thanks, Gord! Glad you found it helpful.

      Reply
  17. Kamran

    June 4, 2016 at 3:36 pm

    Hi Mynda,

    Is there option to sub total in Excel table

    Reply
    • Mynda Treacy

      June 5, 2016 at 7:40 pm

      Hi Kamran,

      No, you can’t use the Subtotal tool in an Excel Table, but you can use a PivotTable to summarise and subtotal the data.

      Mynda

      Reply
  18. Peter Buyze

    April 15, 2016 at 5:30 pm

    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.

    Reply
  19. Carl Thørner

    March 8, 2016 at 2:34 am

    I am not able to download: Excel_Blog_Workbooks.xlsx (https://www.myonlinetraininghub.com/wp-content/moth-practice-files/Excel_Blog_Workbooks.xlsx).

    Reply
    • Catalin Bombea

      March 8, 2016 at 3:26 am

      Hi Carl, try to right click the link and choose: Save link as or Save target as, depending on your browser. Make sure it is saved as xlsx, your browser might change the extension.
      Cheers,
      Catalin

      Reply
  20. Dave Bonallack

    February 20, 2016 at 10:43 am

    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

    Reply
    • Catalin Bombea

      February 20, 2016 at 3:41 pm

      Hi Dave,
      You have to duplicate the column name, to make it absolute:
      =Table1[@[Days]:[Days]]
      You will find more info here: excel-table-absolute-structured-references
      Catalin

      Reply
      • Dave Bonallack

        February 21, 2016 at 7:49 pm

        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

        Reply
        • Catalin Bombea

          February 22, 2016 at 1:09 am

          Unfortunately, yes, you have to type it manually, there is no shortcut for this.
          Catalin

          Reply
          • Dave Bonallack

            February 22, 2016 at 2:40 pm

            Ok Catalin, thanks for your help.

  21. Rob

    January 10, 2016 at 7:32 pm

    Thanks for the information. Well done.
    However it’s a pity that you don’t describe how to turn off Microsoft’s ridiculous idea of automatically naming columns column1, column2 etc.

    It would also help if you explained how to use Table Formula Shortcuts, such as Table1[#All] or one column =Table1[[#Headers],[Column1]]

    Reply
    • Catalin Bombea

      January 11, 2016 at 3:35 pm

      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

      Reply
  22. Le Hung

    October 29, 2015 at 10:32 am

    This is not new nowadays however like it as easy to share and coach my colleagues. Thanks

    Reply
    • Mynda Treacy

      October 29, 2015 at 11:24 am

      Cheers, Le.

      You’d be surprised how many people haven’t heard of Excel Tables. Microsoft esimate only 1% of Excel users know of and use Excel Tables!

      Spread the word 🙂

      Mynda

      Reply
  23. justine dougherty

    October 29, 2015 at 6:22 am

    Hey Mynda,
    I put the formula in as above BUT this is what it looks like on my sheet
    =[@Salary]/Table9[[#Totals],[Salary]]

    WHY would that be?

    Thanks

    Reply
    • Mynda Treacy

      October 29, 2015 at 11:14 am

      Hi Justine,

      In Excel 2010 the way you reference a row changed to =[@row], whereas in Excel 2007 it is =Table1[[#This Row],[Column1]].

      I’ve updated the post above to include this update.

      Kind regards,

      Mynda

      Reply
  24. Hemant

    October 1, 2015 at 9:03 pm

    Download the workbook and practice what you learn. (https://www.myonlinetraininghub.com/wp-content/moth-practice-files/Excel_Blog_Workbooks.xlsx) is not working.

    Unable to download

    Reply
    • Mynda Treacy

      October 1, 2015 at 9:12 pm

      Hemant,

      I tested it and it works for me. It may be your browswer. Please rignt-click the link > Save As > choose your folder you want to save it in. Make sure the file extension is .xlsx

      Let me know if you’re still having problems.

      Kind regards,

      Mynda

      Reply
  25. Faridz Ridzuan

    July 29, 2015 at 5:55 pm

    Thank you very much , now everything starts to make sense in my brain…;)

    Reply
    • Mynda Treacy

      July 29, 2015 at 10:35 pm

      Glad we could help, Faridz 🙂

      Reply
  26. Lindsey Delaney

    May 16, 2015 at 2:28 am

    thank you! I have read up on tables since I watched you Dashboard training a few weeks ago, but this was a great summary!

    Reply
    • Mynda Treacy

      May 16, 2015 at 8:10 am

      Thanks, Lindsey. Glad you’re making use of Tables 🙂

      Reply
  27. Martin Stephenson

    April 16, 2015 at 8:01 am

    I am working with the instructions above however i cannot understand the comment “Tip: Format the cell before you enter your formula. in Excel Table Magic feature #3. can you help please?

    Reply
    • Mynda Treacy

      April 16, 2015 at 10:35 am

      Hi Martin,

      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.

      Kind regards,

      Mynda

      Reply
  28. Miffy

    April 15, 2015 at 11:48 pm

    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.

    Thank you!!!!

    Reply
    • Mynda Treacy

      April 16, 2015 at 10:33 am

      Thanks, Miffy. I’m glad you’re enjoying our site and finding loads of useful tips.

      Mynda

      Reply
  29. Tayyab Bhutta

    April 2, 2015 at 3:10 pm

    Hi Mynda,

    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.

    Always stay blessed.

    Reply
    • Mynda Treacy

      April 2, 2015 at 7:54 pm

      Thanks, Tayyab. I’m glad you found this tutorial helpful 🙂

      Reply
  30. suzanne eikenberry

    February 12, 2015 at 1:40 am

    Hi Mynda,

    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?

    Reply
    • Mynda Treacy

      February 12, 2015 at 8:58 am

      Hi Suzanne,

      Magic Feature #3 should work in Excel 2013. The trick is to put the formula in the very next available column. If you skip a column Excel will not include it in the table.

      To fix your % of total change your formula to this:

      =[@Salary]/SUM([Salary])

      Kind regards,

      Mynda

      Reply
      • David R

        August 13, 2018 at 7:58 pm

        Or =[@Salary]/SUBTOTAL(9,[Salary]) if you want the % to just be of the rows that are visible (like when you filter the table).

        Though looking at the date of this thread you’ve probably figured that out by now 🙂

        Reply
  31. Moshe Slonim

    February 11, 2015 at 7:35 pm

    Hi Mynda,
    This is just a thank you note.
    I have been exposed to your emails since I took Dashboard webinar.
    The webinar as well as the emails are very clear and helpful.
    All the best
    Moshe

    Reply
    • Mynda Treacy

      February 11, 2015 at 7:48 pm

      Thank you, Moshe 🙂

      I’m glad you’re finding our training helpful.

      Reply
  32. Brian Crawford

    February 2, 2015 at 3:27 pm

    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

    Reply
    • Catalin Bombea

      February 3, 2015 at 12:09 am

      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

      Reply
  33. Ozen Kandirali

    January 31, 2015 at 8:49 am

    Hello Mynda,

    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.

    Thank you for respond in advance

    Ozen

    Reply
    • Mynda Treacy

      January 31, 2015 at 10:15 pm

      Hi Ozen,

      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.

      Please let me know if you still have problems.

      Mynda

      Reply
      • Ozen Kandirali

        February 4, 2015 at 6:08 am

        Hi Mynda,

        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.

        Thanks again for this great job you do.

        Reply
        • Mynda Treacy

          February 4, 2015 at 8:34 am

          Hi Ozen,

          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.

          Not to worry. The main thing is you have managed to open it.

          Kind regards,

          Mynda

          Mynda

          Reply
  34. Wendy Farkas

    January 20, 2015 at 3:00 pm

    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.

    My evening’s entertainment, but very gratifying. Thanks!

    Reply
    • Mynda Treacy

      January 20, 2015 at 5:23 pm

      Hi Wendy,

      I’m so pleased you’ve found this tutorial helpful and are excited about using Tables. The practice you’re doing will be well worthwhile.

      Kind regards,

      Mynda

      Reply
  35. Des Lavender

    November 29, 2014 at 1:47 am

    So, if you start your fomula with a – it will automatically return the result as negative number. Not what people might intend!!

    Reply
    • Mynda Treacy

      November 29, 2014 at 8:36 am

      Correct, Des. Likewise if you begin the formula with a +. In Excel the – is a minus sign in every numeric context unless text.

      Kind regards,

      Mynda

      Reply
  36. Des Lavender

    November 29, 2014 at 1:39 am

    Hi there, this is brilliant!! Just a couple of things:

    1) Because the NHS is poor, very poor, I’m still running Windows XP and the (I’m guessing) screen shots are miniscule and can’t be seen.

    2) The link to the /moth-practice-files/Excel_Blog_Workbooks.xlsx just enabled me to download a .zip file

    Reply
    • Mynda Treacy

      November 29, 2014 at 8:35 am

      Hi Des,

      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.

      Kind regards,

      Mynda

      Reply
  37. Susan Slinkman

    September 16, 2014 at 6:55 am

    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

    Reply
    • Mynda Treacy

      September 16, 2014 at 10:41 am

      Hi Susan,

      Yes, unfortunately that is a limitation with Tables. We’ve got a blog post on a VBA solution to that problem coming soon.

      Mynda

      Reply
  38. Lisa Pederson

    June 5, 2014 at 3:45 am

    Hi Mynda,

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

    Thanks,
    Lisa

    Reply
    • Mynda Treacy

      June 5, 2014 at 11:14 am

      Hi Lisa,

      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:

      =SUMIF(‘[Workbook 2.xlsx]Sheet1’!Table1[column_reference]

      Kind regards,

      Mynda.

      Reply
  39. Allen Reidhead

    May 31, 2014 at 12:46 am

    The information is excellent, well directed, concise and orderly! Thank you.

    Reply
    • Mynda Treacy

      May 31, 2014 at 12:22 pm

      Thanks, Allen. Glad you found it helpful 🙂

      Reply
  40. Ru

    May 30, 2014 at 6:15 am

    Thank you for sharing! It has always been helpful.

    In colume 1, why is there a “+” sign right after “=”? The results seen to be the same with or with the “+”.

    Reply
    • Mynda Treacy

      May 30, 2014 at 9:31 am

      Hi Ru,

      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 🙂

      Kind regards,

      Mynda.

      Reply
  41. Henk Stander

    May 29, 2014 at 8:14 pm

    Hi Mynda, I am unable to download the practise file. It downloads gibberish.

    Thanks again.

    Reply
    • Mynda Treacy

      May 29, 2014 at 8:24 pm

      Hi Henk,

      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

      Kind regards,

      Mynda.

      Reply
  42. Gary E Travnicek

    April 25, 2014 at 8:12 pm

    Excellent tutorial.

    Please correct the inconsistency of 10% versus .1% in the formula and/or the table:

    Multiply Salary by 10% =Table1[[#This Row],[Salary]]*0.1%

    Reply
    • Mynda Treacy

      April 26, 2014 at 8:12 am

      Good spot, Gary. Thanks. I’ve fixed it now.

      Cheers,

      Mynda.

      Reply
  43. RAMNEEK SHARMA

    April 9, 2014 at 9:25 pm

    its great,thanks for helping me

    Reply
    • Mynda Treacy

      April 10, 2014 at 5:15 am

      You’re welcome, Ramneek 🙂

      Reply
  44. Doug

    October 31, 2013 at 12:52 am

    Thanks, your articles are so helpful. Understanding tables is going to make everything so much easier.

    Reply
    • Mynda Treacy

      October 31, 2013 at 8:12 am

      Cheers, Doug 🙂 You’re welcome.

      Reply
  45. Lyn Stevens

    October 30, 2013 at 6:58 pm

    Great, at last someone has given me a simple explanation of what an Excel Table is and how to use it. Thankyou

    Reply
    • Mynda Treacy

      October 30, 2013 at 10:38 pm

      🙂 You’re welcome, Lyn.

      Reply
  46. Oyller

    August 4, 2013 at 10:11 pm

    Magnificent! I’ve been using excel 2003 till very recently. It is a real magic. Thank you so much for sharing this great discovery to the world 🙂

    Reply
    • Mynda Treacy

      August 5, 2013 at 7:39 am

      Cheers, Oyller 🙂 In Excel 2007 Tables got a make over and are a lot more user friendly. Enjoy.

      Reply
  47. Sajid Mahmood

    January 25, 2013 at 10:07 pm

    is there any option or formula to convert digit in words,

    for example i wirte a amoutn 47,587/- in a cell, i want to see this amount in words in a seperate cell,

    is it possible???

    Reply
    • Mynda Treacy

      January 25, 2013 at 10:30 pm

      Hi Sajid,

      Every now and again this question comes up. Yes, it is possible, but it’s so complex it’s not really worth using. Daniel Ferry, MVP (Excel Hero blog) came up with this solution:

      =REPT(INDEX(nwords1,MID(TEXT(A1,n0),1,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),1,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),1,3)>99)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),2,2)>19)*MID(TEXT(A1,n0),2,1)+1)&REPT("-",(--MID(TEXT(A1,n0),2,2)>20)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),2,2)-8)*(--MID(TEXT(A1,n0),2,2)>9)*(--MID(TEXT(A1,n0),2,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),3,1)+1)*((--MID(TEXT(A1,n0),2,2)<10)+(--MID(TEXT(A1,n0),2,2)>19)))&" million ",(LEN(A1)>6)) 
      & 
      REPT(INDEX(nwords1,MID(TEXT(A1,n0),4,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),4,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),4,3)>99)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),5,2)>19)*MID(TEXT(A1,n0),5,1)+1)&REPT("-",(--MID(TEXT(A1,n0),5,2)>20)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),5,2)-8)*(--MID(TEXT(A1,n0),5,2)>9)*(--MID(TEXT(A1,n0),5,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),6,1)+1)*((--MID(TEXT(A1,n0),5,2)<10)+(--MID(TEXT(A1,n0),5,2)>19)))&REPT(" thousand ",--MID(TEXT(A1,n0),4,3)>0),(LEN(A1)>3)) 
      & 
      INDEX(nwords1,(LEN(A1)>2)*LEFT(RIGHT(A1,3),1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),7,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),7,3)>99))&INDEX(nwords3,(--RIGHT(A1,2)>19)*LEFT(RIGHT(A1,2),1)+1)&REPT("-",(--RIGHT(A1,2)>20)*(--RIGHT(A1,1)>0))&INDEX(nwords2,((RIGHT(A1,2)-8)*(--RIGHT(A1,2)>9)*(--RIGHT(A1,2)<20)))&INDEX(nwords1,(RIGHT(A1,1)+1)*((--RIGHT(A1,2)<10)+(--RIGHT(A1,2)>19)))

      Kind regards,

      Mynda.

      Reply
  48. Sonia

    December 18, 2012 at 6:30 am

    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

    Reply
    • Mynda Treacy

      December 18, 2012 at 8:24 am

      Hi Sonia,

      It sounds like you could use a VLOOKUP formula with multiple criteria.

      Kind regards,

      Mynda.

      Reply
  49. NaeemShahzad

    October 4, 2012 at 2:48 am

    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.

    Reply
    • Mynda Treacy

      October 4, 2012 at 2:46 pm

      Hi Naeem,

      In one cell enter the borrow date and time like this:

      1/10/2012 8:00:00 AM

      and in another cell enter the return date and time like this:

      3/10/2012 3:00:00 PM

      In a third cell enter a formula that takes the return date & time less the start date and time and format the cell to a custom number format [h]:mm

      Answer is 55:00

      Kind regards,

      Mynda.

      Reply
  50. NaeemShahzad

    October 4, 2012 at 2:38 am

    very nice i like your post.

    Reply
  51. Vaibhav

    August 30, 2012 at 9:25 pm

    Thanks

    All your tutorials are really helpful, once again , thanks a ton !

    Reply
    • Mynda Treacy

      August 30, 2012 at 9:35 pm

      Cheers, Vaibhav!

      Reply
  52. Hamdullah

    August 23, 2012 at 8:09 pm

    nice

    Reply
    • Mynda Treacy

      August 24, 2012 at 9:07 pm

      Cheers, Hamdulla 🙂

      Reply
  53. oliver

    February 9, 2011 at 4:16 am

    Excellent job highlighting the benefits of Excel tables in 2007. I also learned a few things about strucured references too! I’ll be certain to include this topic in my next Excel training class.

    Reply
    • Mynda

      February 10, 2011 at 10:17 pm

      @Oliver. Thanks.

      Those familiar with earlier versions of Excel are likely to never discover them…and that would be a shame. Spread the word!

      Reply
  54. aromatherapy

    January 27, 2011 at 7:22 pm

    Gosh, I’ve been looking about this specific topic for about an hour, glad i found it in your website!

    Reply
  55. Peter

    December 17, 2010 at 11:41 am

    I’ll have to try using these in my financial analysis spreadsheets.

    Peter

    Reply

Trackbacks

  1. Excel DGET Function • My Online Training Hub says:
    October 9, 2019 at 11:44 am

    […] Excel Tables Course […]

    Reply
  2. PowerApps and Excel - An Introduction • My Online Training Hub says:
    August 30, 2019 at 10:33 am

    […] Excel Tables Course […]

    Reply
  3. Excel Data Types • My Online Training Hub says:
    October 9, 2018 at 6:09 pm

    […] the formula in cell B2 above uses the Excel Table Structured reference, [@Country]. [@Country] could be replaced with the cell reference, A2. i.e. the formula could also […]

    Reply
  4. Remove Formatting from a Table • My Online Training Hub says:
    September 20, 2018 at 7:47 pm

    […] Let's say you have a range that you want to convert to a table […]

    Reply
  5. 23 Essential Excel Keyboard Shortcuts • My Online Training Hub says:
    January 22, 2018 at 10:41 pm

    […] a table. If you haven't discovered tables yet where have you been! Start using them now for all the […]

    Reply
  6. Power Query Lists • My Online Training Hub says:
    September 7, 2017 at 1:58 pm

    […] 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 […]

    Reply
  7. Interactive Excel Formulas • My Online Training Hub says:
    October 6, 2014 at 9:01 am

    […] an Excel Table called ‘data’ (note: Since I’ve formatted my data in a Table I’ll be using Structured References in my […]

    Reply
  8. Microsoft Excel Standard Deviation Functions • My Online Training Hub says:
    September 15, 2014 at 11:21 pm

    […] table was inserted and then converted to a range to create the shaded bands […]

    Reply
  9. Excel VLOOKUP Multiple Values • My Online Training Hub says:
    September 15, 2014 at 12:20 am

    […] : Table1[[Name]:[Pay Rise 2006]] – Our table or data range is an Excel Table hence the data range has the name ‘Table1’. You could easily replace this reference with a […]

    Reply
  10. Excel Factor 5 Dynamic Print Area • My Online Training Hub says:
    September 14, 2014 at 11:00 pm

    […] format your data in an Excel Table > select the entire table and set your print area the normal way (from the Page Layout tab). And […]

    Reply
  11. Display Missing Dates in Excel PivotTables • My Online Training Hub says:
    September 14, 2014 at 10:26 pm

    […] 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 […]

    Reply
  12. Excel COUNT, COUNTA and COUNTBLANK Functions • My Online Training Hub says:
    September 14, 2014 at 5:43 pm

    […] an Excel Table and insert a COUNT […]

    Reply
  13. Excel Dynamic Named Ranges • My Online Training Hub says:
    September 14, 2014 at 9:38 am

    […] Dynamic references are just one of the benefits to housing your data in Excel Tables. Find out how to set them up and the other benefits of Excel Tables here. […]

    Reply
  14. Excel Shortcut When Using Dates as Formula Criteria • My Online Training Hub says:
    September 12, 2014 at 2:07 pm

    […] the data above is formatted as a Table which means we can use Structured References in our formulas instead of cell ranges. Click here to […]

    Reply
  15. Excel Tables as Source for Data Validation Lists • My Online Training Hub says:
    September 12, 2014 at 8:07 am

    […] reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the […]

    Reply
  16. Tour de France Excel Dashboard • My Online Training Hub says:
    September 11, 2014 at 4:53 pm

    […] 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! […]

    Reply
  17. Excel Pivot Tables to Extract Data • My Online Training Hub says:
    September 11, 2014 at 4:47 pm

    […] Tip: see the nice formatting of my source data? That is because I have inserted an Excel Table. […]

    Reply
  18. Excel Tabular Data is the Ideal Format for PivotTables and Formulas says:
    September 9, 2014 at 9:11 pm

    […] 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 […]

    Reply
  19. Excel Implicit Intersection says:
    September 9, 2014 at 8:46 pm

    […] 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: […]

    Reply
  20. Interactive Excel Web App Dashboard says:
    September 9, 2014 at 11:55 am

    […] 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 […]

    Reply
  21. Excel Slicers says:
    August 12, 2014 at 1:34 pm

    […] and they’re an interactive control that enables you to filter data in PivotTables, PivotCharts, Excel Tables and CUBE […]

    Reply
  22. Writing Excel Formulas Efficiently says:
    May 27, 2014 at 10:32 pm

    […] going to be blunt here; if you aren’t familiar with Excel Tables then you are missing […]

    Reply
  23. Interactive Excel Web App Dashboard says:
    May 14, 2014 at 10:13 am

    […] on my Validation sheet (see image below) I have my list of usernames and passwords formatted in an Excel Table called […]

    Reply
  24. Excel Conditional Formatting Zebra Stripes says:
    January 30, 2012 at 1:10 pm

    […] alternative to the above examples (excluding the Filtered Table) is to simply use Excel’s Table tool which automatically inserts shaded […]

    Reply
  25. Save Time With Excel 2007 Tables | excel says:
    December 10, 2010 at 6:07 pm

    […] Save Time With Excel 2007 Tables and-practice, download-the-workbook, insert-and, save-time, tables-, the-workbook, what-you, […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. 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.