A range of cells filled with data is generally called a table. So, you put ‘All Borders’ around your data and think it is a table no? Maybe also format the headers a bit. But, this is just formatting applied to a normal Excel data range. Sure, when you print it out, it looks like a table. Nothing wrong with this, but you are missing out on a lot.
We have a simple dataset of fruit sales over a period of time. Each sale is recorded with the date, fruit sold, price, quantity and discount percentage.
Once you convert a range into a table, it opens many new features just for it. To do this, just click somewhere inside the data and press Ctrl + T to convert it to an Excel Table. Tick the box if your data has headers and press OK or Enter.
You will see this immediately formats the data. Also, there is a new Table Design tab on the ribbon. The first option on the far left, there is an option to rename the table. This is especially useful when there are multiple tables in the workbook.
The features available with Excel Tables:
1. Easy Formatting
Ready-made styles are applied automatically to an Excel table. They can be changed easily using the Table Design tab. You can also define your own style. Automatic banding of alternate rows / columns is a big time-saver.
2. Filter & Sort Buttons
Did you notice that filters are applied automatically, ready to use? You can easily turn them off in the Design Tab. The keyboard shortcut to toggle this is Ctrl + Shift + L
3. Auto-Freeze Headers
Scroll down to see the Table Headers replace the column names (A, B, C, …). No need to use the Freeze Top Row feature.
4. Single-Click Selections
You can do this by clicking the top and left sides of the table (not on usual Excel row columns). Useful for quickly selecting rows or columns. Clicking on the Top–Left corner of the table will select the entire table. When you take your cursor near these areas, it will change into a small black arrow. Clicking on the Excel row (1,2,3…) or column (A,B,C…) headers will select the whole sheet; not just data in the table.
Alternatively, you can use keyboard shortcuts. With the cursor in your data try these:
- Ctrl + A will select only the data without the headers. Pressing it again will select the entire Table.
- Ctrl + Space will select the entire column in the table
- Shift + Space will select the entire row in the table
5. Easy Rearrangement
Holding the border of a selection, you can drag and drop the data to rearrange very easily.
6. Easy Growth of Data
This can be done by simply adding new data below or to the right; either by typing or pasting. Alternatively, while entering data manually, the Tab key is very useful to enter data and navigate. It will keep moving the cursor to the right till it comes to the last cell of that row. Then it will move to the first cell of the next row. When you reach the end of the table, it will just add a new row!
7. Special Printing Option
Special option to select only table while printing. Using this will ignore all the data around selected table.
8. Automatically Copy formulas.
Entering formulas anywhere in an empty column will auto-copy to entire column. No need to drag down using the fill handle.
9. Structured References
Structured References are basically ‘verbose’ cell references. Instead of column and row (A1), it will read as (Fruit_Sales[Date]). There are 2 advantages to this. Firstly, formulas become very ‘readable’. Instead of showing =D2*E2; a structured reference will show =Price*Qty. Second advantage is that the names are fixed. Which means the column will always be called by its header i.e. they are fixed (absolute) references. No need to go crazy with dollar signs.
10. Dynamic Named Range
Having fixed names also means that whenever new data is added to the column, it still stays part of the column. There is no need to update any reference like changing A1:A5 to A1:A10 because 5 new rows of data are added. This means the data will keep growing; but the Table name will remain the same. So, any references to the Table will not have to be updated every time new data is added. For example, if a pivot table is based on an Excel Table, it will keep seeing all the new data that’s been added.
A few common features where this can be very useful:
- Conditional formats
- Data Validation lists
11. Pivot Tables
A big disadvantage of pivot tables is that new data is not included automatically. In cases with regularly growing data, it becomes a chore to keep changing the source data of the pivot table. But, if the source of data is from an Excel Table, any growth of data will not change the name of the table. Therefore, only the refresh button needs to be used to feed all the new data into the pivot tables. This is especially useful; and recommended when there are multiple pivot tables involved.
12. Special Total Row
What’s so special about totals? This:
- The total row will always stay below the last row of data.
- Multiple kinds of calculations like counts, averages, etc.
- Use in multiple columns. E.g. You can have a grand total & a count of all the items.
- Will be excluded from pivot tables automatically.
13. Insert Slicer
Add slicer filters to data without making pivot tables or any data connections. A slicer is basically a panel that allows you to filter values in a column. It can be placed anywhere independently of the table. You can click on the buttons to show only those items.
So, as you can see, a lot of functionality is added when you define an Excel Table. It helps save a lot of time and headache in the long run. Also, it becomes easier to do both, data entry and calculations. Once the format of your dataset is fixed, it is a good idea to convert it into an Excel Table. One last tip is that there is also a way to convert an Excel Table into a normal Excel range by using the ‘Convert to Range’ button in the Table Design tab!