If you’re organizing and calculating large data sets containing textual and numeric values placed together, using Excel tables can help you out in many ways that you might not have anticipated before.
From organizing to effortless calculating, Excel tables have got it all covered for you. From an experienced point of view, I prefer creating tables with my data sets in a spreadsheet to focus my work without making a mess out of it.
This article is going to cover all the basics to some of the most advanced table functions that you can implement in your workflow. I’ll talk about:
- What Excel tables are
- Every part of the table
- How to insert a table with existing data
- Proper table using methods
- Important benefits of these tables
So, scroll till the end to learn about all of these to make your Excel journey more effective!
Contents
What Are Excel Tables?
Excel tables are a great way to organize and contain all of your data in one place. How an Excel spreadsheet functions is, one data set is related to its immediate adjacent data set, it could be side by side columns or rows one over the other.
Within an Excel table, the program identifies all of it belonging to one broad category of data. Thus, organizing or calculating becomes more liberating and target-specific.
If I must explain it metaphorically, an Excel spreadsheet is like your room where you have all your clothes laid across your bed. And the Excel table is like your closet in which you organize and store all these clothes in one place.
For numerous reasons, I prefer using Excel tables and I’m going to share my preferred methods & techniques with you so that you can do the same!
Excel Tables: The Breakdown
For your ease of understanding, let me break down the Excel tables structure so that you can comprehend its parts individually and paint the whole picture for your efficiency.
Column Header
Usually the first row of the data set is picked up by Excel as the column header row for the entire table. It’s done automatically and there is room for formatting it afterwards.
Column headers contain filters applied to them from the beginning. You can do pretty much any filtering that you would be allowed to do on a regular filter.
This helps you sort your data more effectively and select which headers you want to include as well as exclude from the table.
Body
Everything excluding the column header row and the total row are part of the table body. It means, every row and column in-between the top-most and bottom-most row are eligible to be regarded as the body. This is where all the generic data are stored.
Row
Table rows are similar to the spreadsheet rows. Each of the rows add up to the Excel table to generate calculations.
The good thing about table rows is that, if you choose to delete one row, Excel will automatically close down the gap with the rows adjacent to the deleted one.
If you delete all the rows from the body area, only a blank row will remain for you to fill in data. You can insert rows very easily too at time of your workflow.
Column
Table columns and spreadsheet columns are almost the same as spreadsheet columns. Each of your tables must contain at least one column and you can increase that number to infinity.
Also, you can add or delete any column at any point of your calculation. All the formulas and generated outcomes will adjust automatically unless it involves that particular column.
You can also drag your column from one point to another without any conflict in data calculation which isn’t the case in spreadsheet columns.
Calculated Column
Using the values from the rows, you can apply a formula to a column that’ll generate an outcome based on your direction. This column is called the calculated column.
What’s great about this is that, once you apply a formula to a calculated column, you can automatically spread it down the entire column to generate the outcomes of the same formula.
It cuts down on your time and effort by making your procedure as direct as it can get.
Total Row
The total row is the last row of the table that shows overall calculations from each of the rows or the selected ones.
By default, the total row feature is disabled which you can enable from the table properties. This row has the regular Excel SUM formulas included to make your calculations effortless.
You can select from the dropdown on this row and pick between average, count, sum etc. as the final result for one or all of the rows.
This total row can hold texts, numbers or even formulas of your choice. The formatting is usually bold so that it stands out from the rest of the table.
Sizing Handle
If you hover at the bottom-right corner of your table, you’ll see an angular icon sitting there. This lets you drag your table and expand or shrink it to whatever size you require it to be.
How To Create Excel Tables?
Creating Excel tables is very easy and effortless. You can approach it in two ways. I’ll break down the process below for a clear-cut approach:
Step 1: Go to the Insert tab and click on Table.
Step 2: Excel will give you a prompt to select your table range. Usually the software is able to automatically locate the table, if it fails in any case, you can either manually select the area or type in the range for Excel to identify it.
Step 3: If your table headers are the one you want to use on your table, make sure to tick the “My table has headers box”.
Step 4: Hit OK to generate your Excel table!
Alternative: The alternate way of going about it is to select your table range and then clicking on the Table under the Insert menu.
Excel Tables: Tips & Tricks
Here are some of the techniques you can play with your Excel tables to get the best outcomes out of them. These include some of the basic tips to advanced tricks that’ll help you manage your tables in the best possible way.
Selecting Entire Column
Usually, we go about selecting entire Excel tables columns by either Left-clicking the top cell and pulling down the mouse while keeping it clicked till we reach the last cell and then releasing the click.
Another way of doing it is by Shift+Left Clicking the first cell and then going all the way down to the last cell and Shift+Left Clicking it.
Both of these are very time consuming if there are numerous rows to deal with. Excel tables have some shortcuts that’ll cut down on your time and effort tremendously!
What you need to do is, go to the header row and find the column that you want to select. Once you hover your mouse over that header cell, you’ll see your cursor turn into a Bottom-arrow and click the Left Mouse button.
With one click, it’ll select the entire column excluding the header row and total row cell. Left-Click again to include these two cells as well into the selection.
There’s a keyboard shortcut to selecting an entire column as well. First, select a cell in the column that you want to highlight entirely. Then hit Ctrl+Space to select the entire row without the header and total, hit Ctrl+Space again to select all of it! See? Very easy!
Selecting Entire Row
Similar to selecting the entire column, if you hover your mouse over the left-most cell of a column, you’ll find your cursor turning into a right-arrow.
Click your Left Mouse button to select that entire row. Note that this applies to your header row and total row as well.
The keyboard shortcut for this is Shift+Space. Once you’ve selected a cell of the row you want to select entirely, just press this shortcut to perform the magic!
Hint: If you right click on your table and go to the select option, you’ll find selection categories of column or row without having to press any cursor or hitting shortcuts. Also, press Ctrl+A to select the entire table if you want to.
Adding A Total Row
As mentioned earlier, your tables won’t have a total row included from the default formatting. You must enable it later on if you feel the need for it.
To add it to your table, select your table and go to the Table Tools Design tab on the top. There, you’ll find the Table Style Options, under which there are few categories present with checkboxes.
You need to check the box of Total Row and it’ll directly add the total row at the bottom of your table.
The good thing about the total row is that, if you borrow your data for a Pivot Table from your spreadsheet, your total calculations at the bottom row might get included in the Pivot summary as an accident and generate misleading data.
But if your Pivot data is collected from an Excel table, it’ll identify the total row as not a part of the data set and produce accurate data without any room for mistake!
You can also add the total row by right clicking on the table, going to Table>Total Rows option. The keyboard shortcut for this is: Ctrl+Shift+T.
Using Filters
The filters in Excel tables are almost the same as the regular filters in the spreadsheet. But there are certain facilities that you’ll get out of these filters which are restrictive in an open sheet.
You can apply unique filters to each of the tables, but you must remember that the same headers in different tables will be categorized as the same filter.
The filters are applied automatically with default settings. To turn it off, you have to go to Table Tools Design > Table Style Options and untick the Filter Button.
There’s also a keyboard shortcut for this operation: Ctrl+Shift+L. From the header row, click on the filter arrow to open up the same filter menu and sort your data any way you want.
Total Row: When you apply a filter to your table and opt for a summary function, the Total at the Total Row usually implements the SUBTOTAL formula. What it does is, it calculates based on your applied filters and automatically updates the result according to your every filter change!
Resize Table Anytime
I’ve mentioned one way of resizing the table with the bottom-right Size Handle cursor. Apart from that, you can resize your table size and include/exclude data from the ribbon.
You need to go to Table Tools Design > Properties > Resize Table and a pop-up window will show up.
Click the range selector icon and select your data set again to resize your table and every value within it will update automatically by Excel!
Insert Or Delete Rows & Columns
Inserting or deleting anything from a table is straightforward and you’ll be able to perform it any time during your operation.
Select a cell from the column or row where you want to insert a row or column. Right click on that row and you’ll find the Insert option from there. Under this, you’ll find the options to insert a row or a column right next to that cell.
Similarly, right click on a cell and go to the Delete option. There are two options: Table Columns & Table Rows, picking either would delete the entire row or entire column which that selected cell belongs to.
Changing Table Styles
It’s always nice to change your table style to make your work look more engaging. Apart from just looks, certain styles even allow you to distinguish between rows and columns of repetitive data and render your table comprehensive to some extent.
To alter your table styles, go to Table Tools Design > Table Styles tab where you’ll find a large variety of options to choose from. At the bottom-right corner, click the down arrow icon to expand the styles to see the entire gallery.
There are plenty of color palettes you can choose from. Selecting any style applies it to your entire table and continues on with the newly added rows or columns. There is an option to create your own style to cater to your taste perfectly.
If you have one style that you want to keep on applying to your future tables as the default, you can right click on that style and select the Set As Default option to save you time.
Removing Duplicates
Removing duplicates is the same one from the Data tab that usually applies to the whole spreadsheet. In this case, it’ll only apply to your table and its data only.
To achieve that, go to Table Tools Design > Tools > Remove Duplicates. It’ll remove all the duplicate data values from your selected table.
Pivot Table Summarization
PivotTable summarization functions similar to the regular spreadsheet PivotTable. With your Excel table selected, go to Table Tools Design > Tools > Summarize with PivotTable.
The Create Pivot Table pop-up will appear and you can proceed as the regular PivotTable work with your Excel table data. Inserting PivotTable from the Insert tab will work the same way for tables.
Reverting Table Values Back
At some point of your workflow with an Excel table, you might come to conclude that the table isn’t really necessary here and you want to revert your data back to the regular spreadsheet. Is that achievable? Of course it is!
To get your table values back, go to Table Tools Design > Tools > Convert to Range. If you’ve had a table style applied, that style will remain. You’ll have to manually remove it by going to the table styles options and selecting the “None” table style before you convert your data to range.
Benefits Of Excel Tables
There are quite a few practical benefits of using Excel tables than having your data stored across the spreadsheets. Some of the most important benefits are highlighted in this section with application techniques.
With multiple tables in your spreadsheet, your navigation is much easier than regular spreadsheet data. You’ll be surprised to see that you can move from one table to another without having to scroll through large data volumes!
To do this, click on the bottom arrow in your Name Box which is placed at the left of the Formula Bar. There, you’ll find the names of your tables in the sheet. Click any of them to allow Excel to navigate you directly to that position!
Column Headers
There’s often a troubling factor while working with an open spreadsheet is that, after scrolling down longer data ranges, I fail to track which column represents which header. Then I find myself scrolling back up again to make sense of it.
Tables have column headers present at all times, no matter how far below you scroll. It’s great for assessing between data directly and comprehending the range without much fuss.
Expansion
While there are a few expansion options for your Excel table, you’ll be delighted to learn that it expands automatically as well.
For instance, if you press Enter from one row, your cell will expand to the following cell carrying all the attributes of the table.
These attributes contain the table style, applied formulas and every other aspect. It’s like working on a pre-existing row or column but you have the control over when to introduce it.
Automatic Formula Application
Excel tables automatically apply your formula without having to manually implement it or drag it across the entire column or row.
The Calculated Column is one instance of it as I’ve mentioned earlier, and the other is the Total Row. Each of these are automatically taken care of by Excel to save you time and effort!
Also, the Total Row implies that you’re applying a function in the table without having to manually type in any formula as Excel presents them for you with a one-click away option!
Creating Dynamic Charts
Much like PivotTables, you can create any category of regular dynamic charts available in Excel using your Excel tables at any time.
The great thing about these charts is that they automatically update themselves if you alter, add or remove any data from your table.
Also, if you manually input or even copy/paste data into your table, or expand your table to occupy more data, the chart will update itself to provide you with the latest graphical outcome.
Good thing about this chart is it doesn’t include the Total Row or the header row as part of the data. It makes room for flawless outcomes while spreadsheet data might produce misleading visuals.
Changing Table Names
You can change your Excel tables names at any time without causing any disruption to your data range, calculations or PivotTable generations.
Assigning specific names to your table helps you organize and identify your tables better and you’ll be able to make proper distinctions between them very easily.
To change your table name, click anywhere in your table and go to the Design tab. At the very left on your navigation tab, you’ll find the name of your table with the default ‘Table’ text.
Change it to anything you want and Excel will automatically accept that as its new name!
Adding Slicers
Slicers are real time filter buttons that generate instant filtering with a more tactile sense and easier control over your data presentation.
You must have a PivotTable applied to your Excel table to include the slicers to the data. Once it’s under Pivot jurisdictions, click on your PivotTable and go to Insert > Slicer.
Excel will ask you which fields you want to include in your slicer filtering, select those and hit OK. Now play with the slicer filters to present your data any way you want!
Also, you can apply Slicers to the charts you made with your Excel tables. You must Analyze your data first to get there. A Slicer with visual data provides more effective results instantly.
Using Structured References
Excel tables have a unique feature named Structured References. It’s the same as Excel formulas, but more comprehensive in the sense that it types out in a more readable form and anyone with minimum Excel knowledge will understand how the data is being calculated.
For instance, to sum-up a column with an Excel formula, you’d have to type in =SUM(C2:C12) considering the C2 and C12 is the data range for this operation.
But the Structured References work with the header row title in such calculation and names the formulas differently. So, if you are to sum-up a column of Marks, you’d have to type in like this for the Excel table:
=SUM(Mark,[Marks Obtained])
Here, =SUM implies the summation, which you can alter with formulas like COUNT, SUBTOTAL etc. Mark refers to the name of the table and Marks Obtained is the name of the column that you are calculating with.
Final Thoughts
So now that you’ve come to the very end of this article, you’re one step ahead in the field of Excel journey, more precisely, someone with knowledge about Excel tables.
Use these tables in your work frequently and see how things get easier to organize, sort or filter. This article is supposed to open a whole new door for you in this regard.
Also, using the table with PivotTools and Structured References allow you to take your work to the next level!
Incorporate your creativity to generate unique results that are unpredictable and makes your data more affable than ever!
You’re only one step away from being claimed as the master data analyst – and that step is “practice, practice, practice”.
Read Also: