What’s on the Table?
Posted on 4th November 2016 at 08:00
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 Name
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: