Hi everybody and welcome to this Excel post. 
Since Office 2007, there’s been a new kid on the block in Microsoft Excel called a Table object. 
Before then, Data Lists were the name of the game. Converting a data list into an Excel Table is easy so here's a few Table tips for you to adopt and adapt. 
 
Have fun laying the Table!! ...... 
Converting data to live inside an Excel Table gives you lots more power and allows you to work more efficiently and effectively with the data.  
There are a few downers, because although tables are really useful they don’t allow you to use all of Excel’s list features.  
For example you can’t use the subtotal feature on a table - you would have to temporarily convert it back using Convert to Range
 
3 Golden Rules 
Keep to these 3 Golden Rules before converting a raw Data List to an Excel Table 
NO need to Format the Top Row of the list as Excel will usually recognise it as a Heading Row and will format it for you when you convert a list to an Excel Table 
NO Blank rows (columns) in the DataBase 
(Else it will prevent the whole list from being sorted and manual selection of ranges of cells containing blank rows (columns) is necessary before sorting. 
Select ONE CELL inside the DataBase List so that Excel Sorts, Filters, creates Pivot Tables properly 
 
How to convert a Data List to an Excel Table 
Make sure the Data List adheres to the 3 Golden Rules above then click on ONE Cell in list 
Click Insert tab, Table drop-down (Ctrl+T
On the dialog that appears, check the target cell range (reselect if necessary) 
Tick My table has headers? 
Click OK to finish 
Table now formatted and filtered by default 
It is important to Name the table: 
Click Table Tools Design tab, Table Nam
A default coloured format will be applied to the table - change this if you want 
There are 60 pre-made formats to choose via Table Tools, Design tab, Table Styles.  
Make sure the ONE Cell is selected in the list then hover over an option to get a Live Preview before choosing one. 
 
Automatic Benefits of Tables 
There are many benefits and practicalities associated with using tables they are listed in the table below ... 
 
To see some worked table examples Download my Excel Table Example file - you will need to look at cell contents to discover more ... 
 
As I said, have fun laying the Table!! 
 
See you next time. 
Les is more! 
 
 
 

Benefits of Tables 

Benefit 
What to do and why?? 
Easy sorting and filtering 
Filter dropdowns added in header row 
Quick formatting 
Banded row format applied, can choose any of 60 pre-made formats from Table Tools, Design tab, Table Styles on. Hover over an option to get a Live Preview before clicking 
Easy data entry & Easy to extend 
Start typing in the row below the last row or the column to the right of the last column and Excel extends the table 
Formula Autofill 
No copy necessary, formulas built in the first row are automatically filled down 
Quick Totals 
Click Table Tools Design tab, Total Row ... 
Each cell in the total row can be filled from a drop down … 
"Table" Charts Auto-update 
Charts based on a table will automatically update as you edit the data in the table 
Pivot Tables 
Using a Table as source data for a Pivot Table has a massive advantage as any new rows/columns added to the table will automatically be included in the pivot table 
Automatic Names in Formulas 
When building a formula based on cells from the table Excel uses references based on the table column headings rather than cell refs eg =[@Qty]*110% (Column L in the example file) 
One-click select 
Hover over the extreme top LH corner of the table and click with the m arrow (although Ctrl+A works well too!) 
Change Size 
Click 1 cell in table then Table Tools Design tab, Resize Table 
Use mouse to select the new range on worksheet 
Click OK to finish 
Change Style Options 
Click 1 cell in table then Table Tools Design tab 
Experiment with: 
Header Row, Total Row, Banded Rows/Columns, First/Last Column options … 
Convert back to normal cell Range 
Click Table Tools Design tab, Convert to Range  
Cells retain data and formatting 

Shortcuts List 

Keystoke 
Effect 
Ctlrl+T 
Convert a raw Data List to an Excel Table 
Ctlrl+A 
Select the table - click on ONE Cell in table first 
Share this post:

Leave a comment: