Excel Tables Tutorial Video - Beginners Guide for Windows & Mac - Excel Campus
49

Excel Tables Tutorial Video – Beginners Guide for Windows & Mac

The following video tutorial is an in-depth look at Excel Tables.

I show you how to create a Table and then explain 10 awesome features that will save you lots of time when working with your data.  The Tables feature is available in Excel 2007, 2010, 2013, and 2011 for Mac.  This video is a step-by-step guide and the file I work with in the video can be downloaded below.

_

Watch in Full Screen HD (may take a few seconds to load high quality video)

_

Download File

Excel Tables Examples - Unformatted.xlsx (25.6 KB)

_

What is an Excel Table?

Table Formula Example

Tables are a feature in Excel that let you convert a range of data into an organized list called a table.  They help automate many repititive tasks such as formatting, sorting, filtering, and summarizing your data.  Tables bring a new and intuitive way to write formulas using structured references.  They also integrate with Excel’s most powerful tools including charts, pivot tables, remove duplicates, PowerPivot, and more.

_

10 Awesome Reasons to Use Excel Tables

There are a lot of great benefits to using tables.  Here are the 10 that I explain in the video.

  1. Formatting | 1:55
  2. Table Names | 2:44
  3. Sorting & Filtering | 3:15 
  4. Auto Expansion & Navigating | 4:35
  5. Total Row | 6:34
  6. Remove Duplicates | 7:18
  7. Create Unique List | 7:53
  8. Pivot Table Integration | 8:33
  9. Chart Integration | 10:24
  10. Table Formulas | 11:26

The video also shows some incredible new features for Excel 2013 including Recommended Pivot Tables and Recommened Charts.

_

Additional Resources & Videos

Here are some additional videos and articles that helped me learn tables.  Please let me know if you have any others to add to the list.

Videos

Excel Tables Feature (Excel as a Database) | Excel Is Fun (18:29)

Create an Excel Table With a Specific Style Applied | Contextures (2:14)

Microsoft Excel 2013 Timeline Video | Excel and Access (8:50)

Chandoo Explains Structured Table Reference | Mr. Excel and Chandoo (5:52)

Absolute References in Structured Table Formulas | Excel Campus (7:17)

Articles

Creating an Excel Table | Contextures

Save Time With Excel 2007 Tables | MyOnlineTrainingHub

Learn Excel Data Tables | Chandoo

Why you should use structured references in tables | Zack Barresse

Absolute Formula References in Structured Tables | Excel Campus

Working with Microsoft Excel tables in VBA | Zack Barresse

Overview of Excel tables | Microsoft

Books

Excel Tables eBook - Zack Barresse - Kevin Jones
A whole book on Tables! My friends Zack Barresse and Kevin Jones have written THE GUIDE to everything Tables.  This is a great read if you are looking to master Excel Tables. It also includes tons of information and resources for writing VBA for Tables.
eBook Version – Excel Tables Book
Paperback version – Excel Tables Book

How Do You Use Tables?

Do you use Tables?  Why or why not?  Please leave a comment below.  And don’t forget to subscribe to my free email newsletter below to get more articles like this and learn Excel.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 49 comments
Ron MVP - October 21, 2017

Thanks for another great video, I’ve seen your videos on pivot tables and dashboards. So I thought it might be worth while to take a step back and learn more about “simple” tables. It is.

One question about create unique list. I figured that out, copy/pasted the unique values, now how do I un-deduplicate to return to the original table? kills the copied unique values too …

There must be a simpler way than displaying the clipboard, repeating the copy/paste, using then repeating the paste from the clipboard …

Reply
Rajeev - September 20, 2017

Hi Jon,
Kindly tell us…
How can hide the formula with continue working formula,I meant can not show to other but formulas should work accordingly..

Reply
Sam - August 23, 2017

Good morning Jon,
Great info on tables. Great job on the presentation. I think I might start to like them now.
You are the bomb!

Reply
Rajeev - August 17, 2017

Hi Jon,Very interesting of your cooperation and excel materials.I am very impressed

Reply
Mohamed - August 9, 2017

Thank you for making it clear and easy to follow. I also like using you pivot checklist.

Best,
Mohamed

Reply
Jan - August 8, 2017

Jon I love your presentations. simple and easy to follow. Much appreciated. Keep up the good work.

Reply
Shadrack - July 13, 2017

The 10 Excel Pro Tips Workbook was excellent. I like the way the 10th tip ended. Lol. I finally found that cell I kept thinking about from Tip 6. Excellent presentation.

Reply
Rajsha - July 3, 2017

Hai Jon Thanks for providing such a useful videos with relevant practice materials.. really it helps a lot… and your voice so good for listening.. Thank you soo much for your hard work to extend our excel knowledge.

Reply
Navreet Sarkaria - April 3, 2017

What a informative video? Didn’t know there are so many features in simple table form. And small tips shared to avoid simple mistakes one makes while dealing with the data are very helpful.

Reply
Raed Azroun - January 15, 2017

Hey Jon,

I am working as a financial controller in private sector. as you know as every body else on the field that the business owners’ are highly demandable in a special kind of reports reflecting the financial & operational performance measurement for the accounting period just ended. The reports which is usually asked for financial analysis prepared on excel sheet attached with it dashboard summarizing whole information in one simple sheet.

My main queries how to convert financial information on the excel sheet prepared to A raw data which helps me out later on preparing creation a pivot table which finally helps me prepare a dashboard to the to management.

As you know, financial analysis report prepared in a way that representing the on the column header the year or years ( multiple periods )and on the left side the criteria and core is the financial information figures for a balance sheet and income statement.

hopefully, that I made myself clear enough, to help me preparing the dashboard as how it suppose to be done in a very professional way to be presented later on to top management.

I will be so grateful if you send A case study file with editing ability to study on my personal email stated.

Thanks, for your valuable support and help.
Raed Azroun

Reply
    Jon Acampora - January 20, 2017

    Hi Raed,
    Financial statements can be created with pivot tables. Getting the layout right can be tricky sometimes, depending on how you want to group and categorize the P&L and BS.

    I do have a free video series on pivot tables and dashboards that you might enjoy. You can also create financial statements that pull in data from pivot tables using the GETPIVOTDATA function. This allows for more customization in your layout. I don’t have an article on that yet, but will add it to the list.

    I hope that helps.

    Reply
Musadaq - January 14, 2017

Thanks a lot.

Reply
Paul - January 6, 2017

Wished I had looked at this sooner. Great stuff question do you have anything on tables using VBA code?

Reply
    Jon Acampora - January 13, 2017

    Thanks Paul! I do cover Tables (ListObjects) extensively in my VBA Pro Course. They are a great tool to use with VBA because they create a dynamic range within a sheet that is easy to work with and reference in VBA. I will try to create some additional training videos on Tables and VBA in the future. Thanks!

    Reply
Aslam Velji - November 21, 2016

Hello Jon,

Firstly, thank you very much for sharing above items.

Secondly, I love advance excel, I would like to learn new and surprised things in advance excel.

please share new tips and tricks in advance excel.

Thanks and Regards.

Aslam Velji

Reply
Richard - November 18, 2016

I have an Excel 2010 workbook with 2 tables. One of the tables contains a vlookup formula. If I try to change the formula, instead of recalculating Excel just displays the formula in the cell that I tried to update. As a point of reference, I’m trying to change the formula in the first row directly below the header. I thought this would then update the full column. Automatic calculation is turned on. Even if I try to manually force the calculation, it still will not update the column. Any advice?

Reply
    Jon Acampora - December 1, 2016

    Hi Richard,
    The only thing I can think of is that you have an apostrophe in front of the equal sign. That will cause the formula text to be displayed in the cell and the formula will not calculate. I hope that helps.

    Reply
Fred - November 17, 2016

Jon:
The videos are really helpful and you do a nice job explaining everything with clear and concise direction.

However I keep running into this problem when I try to replicate the vlookup example you demonstrate.

When I click on the cell C3 in your example I get the following
=vlookup(Table4[[#This Row],[Rep Name]:[revenue]]
instead of the ([@[RepId]) you get in your example.

What am I doing wrong?

Thanks.
Fred

Reply
    Jon Acampora - December 1, 2016

    Hi Fred,
    The [#This Row] notation is used in Excel 2007. This changed starting in Excel 2010 to use the @ symbol instead. So you aren’t doing anything wrong. It’s just the version of Excel you are using. Sorry, I forgot to mention that in the video. Thanks!

    Reply
Salim Patel - October 31, 2016

Brilliant video simple informative and practical. Well done I am using all the learning to make life easier and help my work colleagues with data analysis

Reply
Peter Gensler - September 22, 2016

HI Jon,

When should I be using tables vs named ranges in Excel? I think tables are a great feature, but I think for large amounts of data(500k rows), it can be hard to work with in excel.I think it would be beneficial to have a blog post regarding working with large amounts of data in excel.

Reply
Derek - August 20, 2016

I have been using tables for some time now but I have been manually entering new weekly data from two csv files. I want to build a new set of tables, different layout, using the csv files. I have a working vba program which gathers all the ~400 files data into ‘standard’ arrays.
It is possible to block copy table data into a structured reference array.
Questions,
Is it possible to start with such an array before creating the table?
What does the syntax look like to build the array?

Reply
Forrest - May 28, 2016

Hello, I enjoyed your 3 part video on creating a dashboard in excel. I understand slicers and how they can be used to select various pieces of data with one or more of the items selected. But is there a simple way to create a slicer that allows you to input a range of values, either with a text input or a slider you can drag(similar to a timeline slicer)? For example if you wanted to select a range of values that are >0, or a range of 150-1000, instead of manually cntrl+selecting that many boxes in your slicer?

Thanks,
Forrest

Reply
    Jon Acampora - May 30, 2016

    Hi Forrest,
    One approach is to add an additional column (field) to your source data that contains the groupings. You can create the results of this column with a formula. Checkout my article on grouping times for some different ways to group the numbers or text. The VLOOKUP might be the best approach if your groups are uneven.

    Once you have a new column with the grouping, you can just create a slicer based on that field. I hope that helps. Thanks!

    Reply
Jeannette - May 18, 2016

HI, I have just discovered “Tables” and I hope you can help me 🙂 I have a table that contains Columns with a date header from 01/01/16 to 31/12/16 and I have 2 Columns called Live Start Date and Live End Date. I would like the cells (below the date columns) in each row that corresponds to the Start Date and End Date and all cells in between to be updated automatically with the letter “L”

Any help will be greatly appreciated.

Reply
    Jon Acampora - May 21, 2016

    Hi Jeannette,

    Great question! You can use a IF function to create a formula for that. The main issue is that the header cells store the values as text. So we first need to convert the header cell to a date value with the DATEVALUE function. We also need to a special trick to make the start and end date cell references absolute with the structured table references. The formula gets a little long, but is definitely possible.

    I created a quick sample file that you can download and checkout.

    Here are links to some other articles that will help.
    How to write an IF Statement Formula in Excel
    How to create absolute references with Table Formulas

    I hope that helps. Let me know if you have any questions.

    Reply
      Jeannette - May 23, 2016

      Thank you so much your sample is exactly what I needed. I had got as far as realizing the Date Headers were considered as Text Fields but then gave up and came up with a workaround which was not ideal as it left only half my Table as a table and the rest just data. I still can’t believe I haven’t found Tables before now and if I hadn’t stumbled across your article I would still be none the wiser 🙂

      Reply
        Jon Acampora - May 23, 2016

        I am happy to hear it helped. Thanks for letting me know Jeanette! And don’t worry, you are not alone with Tables. Most Excel users don’t use them or know how beneficial they can be. Spread the word! 🙂

        Reply
GEORGE THOBOIS - April 20, 2016

I have been expanding my skills with tables and this video has been very helpful for periodically re-watching. I am also using pivot tables at work and confused a bit on how to expand the pivot table when copying in new data from one spreadsheet and have the data update in the pivot table.

Reply
Kirrily - March 29, 2016

Awesome video Jon. I have learned some tips and tricks today. I was still putting the ‘freeze top row’ on because yay I didn’t realise that the column headings would do that if it’s a long table. Time saving and once again thanks for another handy video. Cheers

Reply
Rajan Singh kushwah, PMP - March 17, 2016

Dear sir,

Thanku very much for sharing this valuable information.

This video is really amazing.

Regards,

Rajan

Reply
Tom Carre - January 25, 2016

Thanks for enlightening me about tables. I had no idea about the versatility. Saved me days!

Reply
    Jon Acampora - January 26, 2016

    Aweseme! Thanks Tom! I’m happy to hear that Tables are saving you some time. They are a really great feature.

    Reply
Rajesh - January 21, 2016

Awesome and easy to understand videos

Reply
JC - August 29, 2015

Hi Jon,

I a newbie at using excel and I keep searching tutorial in Youtube to help be more efficient in using excel application. I gotta tell you, I didn’t realize the value of the table function until I watch this video of yours. Great work!

Reply
    Jon Acampora - August 30, 2015

    Thanks JC! Tables really are one of the best tools in Excel. Not many people use them yet, so spread the word. 🙂

    Reply
Structured Referencing to Identify Parts of Excel Tables - Peltier Tech Blog - June 15, 2015

[…] maintaining significant simplicity. If you’re not familiar with Tables, a good starting point is this blog post (with video) by Excel MVP Jon […]

Reply
Adel - March 19, 2015

hi jon

just wanted to thank you for this good video i discovered your website yesterday i am addicted to it

thanks

Reply
Remembering Table Formulas - December 3, 2014

[…] to—which is ok too. If you want to learn more about Tables (besides this website), check out this blog post from Jon Acampora. And of course, there’s a cool book on the subject (chapter 4 is dedicated […]

Reply
Alexandra Malmberg - September 3, 2014

Hi Jon,

Thanks for a really informative video! Excellent for a beginner like myself. I have one question regarding removing duplicates. I don’t have the same options as you do when trying to remove the duplicates. Excel tells me no duplicates are found, even though I believe I’ve followed your instructions exactly. This of course makes it difficult for me to remove them. I’m using Excel 2011 for Mac, could that be the reason? Is there something I’m missing? It seems like a really great feature and surely would be handy in the future. Thanks! //Alexandra

Reply
    Jon Acampora - September 4, 2014

    Hi Alexandra,

    Thank you, I’m glad you enjoyed the video. I’m not sure why you are having issues with the remove duplicates, but I would be happy to take a look at your file to see if I can help. You can email your file to me at jon@excelcampus.com. I will try it out in the Mac version and see what’s up.

    Thanks again!

    Reply
Phil - March 4, 2014

Hey Jon,

I know that this post is already somehow aged however it landed in my inbox just recently. I really love it as I use CTRL+T all the time but always struggle when someone asks me what’s the advantage of using tables in your spreadsheet files. Now I have a link I can refer people to 🙂

Reply
    Jon Acampora - March 9, 2014

    Hi Phil,

    That’s exactly why I made this post/video. There are so many great features of tables, and most users have not even heard of them yet.

    Thanks!

    Reply
Chelo Pallas - November 20, 2013

I learned so much from your videos. It has saved me so much time on my Excel schedules. Thank you.

Reply

Leave a Reply: