Pinterest Pixel
All You Need to Know About

Excel Tables

Add structure to your data and learn how to make a table in Excel!

Excel Tables are very powerful and have many advantages when using them.  You should start using them asap regardless of the size of your data set, as their benefits are HUUUGE!
Here are the top things on what you can do with Excel Tables:

How to Insert an Excel Table

Here’s a quick list of benefits on why you should starting using Excel Data Tables:
1. Structured referencing;
2. Many different built in Table Styleswith color formatting;
3. Use of a “Total Row” which uses built in functions to calculate the contents of a particular column;
4. Drop down lists that allows you to Sort & Filter;
5. When you scroll down from the Table, its Headers replace the Column Letters in the worksheet;
6. Remove Duplicate Rows automatically;
7. Summarize the Table with a Pivot Table;
8. Supports calculated Columns so you can create dynamic formulas outside the Table;

Download excel workbookInserting-a-Table1.xlsx

STEP 1:Select a cell in your table
Excel Tables
STEP 2:Let us insert our table! To do that press Ctrl + T or go to Insert > Table:
Excel Tables
STEP 3:Click OK.
Excel Tables
Your cool table is now ready!
Excel Tables

Excel Table Slicers

Slicers were introduced with Pivot Tables in Excel 2010 and they allowed us to select items to filter with beautiful interactive buttons.
You can see the power of Slicers and the different ways you can format them in these posts.
In Excel 2013, Slicers have been extended to be used with Excel Tables.  WOW!
To insert a Slicer in an Excel Table you have to follow these short steps:
STEP 1: Click inside the Excel Table
STEP 2: Select Table Tools > Design > Insert Slicer
STEP 3:Tick the Table Headers that you want to include in your Slicer and press OK
STEP 4: Click on the Slicer buttons and see how your Excel Table gets filtered without needing to select the filter drop down.
Download workbookTable-Slicers.xlsx

Vlookup in an Excel Table

What does it do?
Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.
Formula breakdown:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
What it means:
=VLOOKUP(this value, TableName, and get me value in this column, Exact Match/FALSE/0])
Excel Tables are just amazing and should be used all the time, whether you have 2 rows or 200,000 rows of data!
When you use a Vlookup formula to lookup in an Excel Table then your formula becomes dynamic due to its structured referencing.
What that means is that as the Excel Table expands with more data added to it, your Vlookup formula’s 2nd argument (table_array) does not need to be updated as it refers to the Excel Table as a whole by referring to its name eg Table1  or Table2  or Table3  etc
In the example below our Excel Table name is Table2 and as we add more rows of data to it, the Vlookup formula does not need to be adjusted.  How bloody cool is that?
Download workbookVlookup_Excel-Tablev2.xlsx

Microsoft Excel Vlookup Formula in an Excel Table

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!