What Data Does Excel Print By Default

Everything You Need to Know About Excel Tables

In this post, we're going to larn everything there is to know about Excel Tables!

Yes, I mean everything and at that place's a lot.

This post volition tell you lot about all the awesome features Excel Tables have and why y'all should start using them.

What is an Excel Table?

Excel Tables are containers for your data.

Imagine a business firm without whatever closets or cupboards to store your things, information technology would be chaos! Excel tables are like closets and cupboards for your data, they assistance to contain and organize data in your spreadsheets.

In your house, you lot might put all your plates into one kitchen closet. Similarly, yous might put all your customer information into ane Excel table.

Tables tell excel that all the data is related. Without a table, the merely thing relating the data is proximity to each other.

Ok, and so what's so peachy nigh Excel Tables other than existence a container to organize information? A lot actually. This post will tell you lot near all the awesome features tables have and should convince you to start using them.

Video Tutorial

The Parts of a Tabular array

Throughout this postal service, I'll be referring to diverse parts of a tabular array, and so information technology'southward probably a good idea that nosotros're both talking about the same affair.

This is the Column Header Row. It is the first row in a table and contains the column headings that place each column of data. Column headings must be unique in the table, they cannot be bare and they cannot contain formulas.

This is the Trunk of the tabular array. The torso is where all the data and formulas live.

This is a Row in the table. The torso of a tabular array tin can incorporate ane or more rows and if you attempt to delete all the rows in a table a unmarried blank row will remain.

This is a Column in the tabular array. A table must contain at least one column.

This is the Total Row of the table. By default, tables don't include a total row simply this characteristic can exist enabled if desired. If information technology'due south enabled, information technology will be the last row of the table. This row can comprise text, formula or remain blank. Each cell in the total row will have a drop down carte that allows selection of various summary formula.

Create a Table from the Ribbon

Creating an Excel Table is really easy. Select any jail cell inside your data and Excel will estimate the range of your data when creating the table. You'll be able to confirm this range later on on. Instead of letting Excel guess the range you can also select the unabridged range of information in this step.

With the active cell within your information range, go to the Insert tab in the ribbon and press the Table button institute in the Tables section.

The Create Tabular array dialog box will pop up. Excel guesses the range and yous can adjust this range if needed using the range selector icon on the right hand side of the Where is the data for your table? input field. You tin too conform this range past manually typing over the range in the input field.

Checking the My table has headers box will tell Excel the get-go row of data contains the column headers in your table. If this is unchecked Excel volition create generic column headers for the table labelled Column 1, Column 2 etc…

Printing the Ok button when you're satisfied with the data range and table headers check box.

Congratulations! You now have an Excel table and your information should look something like the in a higher place depending on the default mode of your tables.

Contextual Tabular array Tools Design Tab

Whenever you select a cell inside a table, y'all volition observe a new tab announced in the ribbon labelled Table Tools Design. This is a contextual tab and only appears when a table is selected. When the active cell moves outside the table, the tab will disappear over again.

This is where all the commands and options related to tables will live. This is where you lot'll exist able to proper noun your table, find table related tools, enable or disable table elements and change your table's mode.

Create a Table with a Keyboard Shortcut

You tin can likewise create a tabular array using a keyboard shortcut. The procedure is the aforementioned as described in a higher place but instead of using the Table push button in the ribbon you lot can printing Ctrl + T on your keyboard. It's easy to call back since T is for Table!

There is really some other keyboard shortcut that you can use to create tables, Ctrl + L will also exercise the same thing. This is a legacy from when tables were called lists (Fifty is for List).

Name a Tabular array

Anytime you create a new table Excel will give information technology an initial generic name starting with Table1 and increasing sequentially. You lot should always rename your table with a descriptive and brusque name.

Not all names are immune. At that place are a few rules for a table name.

Each table must have a unique name inside a workbook.
Yous tin can but use letters, numbers and the underscore character in a table proper noun. No spaces or other special characters are allowed.
A table name must begin with either a alphabetic character or an underscore, it can not begin with a number.
A tabular array proper name tin have a maximum of 255 characters.

Select whatsoever jail cell inside your tabular array and the contextual Tabular array Tools Design tab will appear in the ribbon. Within this tab you can find the Table Proper noun under the Properties section. Type over the generic name with your new name and press the Enter button when finished to confirm the new name.

Rename a Tabular array

Renaming a table you've already named is the same process as naming a table for the starting time time. If y'all think well-nigh it, when you first name a tabular array you're actually renaming it from the generic name of Table1 to a new name.

So go back to the Table Tools Pattern tab and type your new name over the old one in the Table Proper name and printing Enter. Easy, and the name is changed.

Changing your table name this fashion requires navigating to your table and selecting a cell inside information technology, so information technology can exist boring if you need to rename a lot of tables across different sheets in your workbook. Instead, you can alter whatever of your tabular array names without going to each table using the Name Manager.

Get to the Formula tab and printing the Name Manager push button in the Defined Names section. You'll be able to meet all your named objects hither. The table objects volition have a small table icon to the left of the name. You tin can filter to evidence only the table objects using the Filter button in the upper right paw corner and selecting Tabular array Names from the options.

You can then edit whatever name by selecting the item and pressing the Edit button. You lot'll be able to change the name and add some comments to describe the information in your table.

Navigate Tables with the Proper noun Box

Yous tin can easily navigate to any table in your workbook using the name box the the left of the formula bar. Click on the modest pointer on the right side of the proper noun box and you volition encounter all tabular array names in the workbook listed. Click on any of the tables listed and you lot will be taken to that table.

Catechumen a Table Back to a Normal Range

Ok, you changed your mind and don't want your data inside a table anymore. How exercise you lot convert information technology back into a regular range?

If changing it to a tabular array was the terminal matter you did, Ctrl + Z to undo your concluding action is probably the quickest manner.

If it wasn't the concluding thing you did, then you're going to need to apply the Convert to Range command institute in the Table Tools Design tab nether the Tools section.

Yous'll be prompted to confirm that y'all really want to catechumen the table to a normal range. Noooooo, don't do information technology, tables are awesome!

If yous click on yep, and then all the crawly benefits from tables will be gone except for the formatting design. You lot'll demand to manually articulate this from the range if you want to get rid information technology. You can do this by going to the Habitation tab then pressing the Articulate push found in the Editing department, and so selecting Clear Formats.

This can also be done from the right click carte du jour. Correct click anywhere in the table and select Table from the carte and and so Convert to Range.

Select the Unabridged Cavalcade

If your data is non inside a table then selecting an entire column of the data can be difficult. The usual style would be to select the first cell in the column then hold Ctrl + Shift then press the Downward arrow primal. If the column has bare cells, and then yous might need to press the Down arrow key a few times until you reach the end of the data.

The other option is to select the first cell and then use the scroll bar to scroll to the end of your data then concur the Shift key while yous select the last cavalcade.

Both options can be tedious if y'all accept a lot of data or in that location are a lot of blanks cells in the information.

With a tabular array, you tin can easily select the unabridged column regardless of bare cells. Hover the mouse cursor over the column heading until it turns into a small-scale arrow pointing down then left click and the entire column will exist selected. Left click a 2nd fourth dimension to include the column heading and whatever total row in the option.

Another style to quickly select the unabridged column is to place the agile cell cursor on whatsoever prison cell in the column and printing Ctrl + Space. This will select the entire column excluding the column header and total row. Press Ctrl + Infinite again to include the column headers and total row.

Select the Unabridged Row

Selecting the entire row is merely as easy. Hover the mouse cursor over the left side of the row until it turns into a pocket-size arrow pointing left so left click and the entire row will exist selected. This works on both the column heading row and total row.

Another manner to quickly select the entire row is to place the active cell cursor on any cell in the row and press Shift + Space.

Select the Entire Table

It's likewise possible to select the entire table and there are a couple dissimilar ways to do this.

You lot can identify the active jail cell cursor inside the table and printing Ctrl + A. This will select the entire body of the table excluding the column headers and total row. Press Ctrl + A once more to include the column headers and total row.

Hover the mouse over the top left hand corner of the table until the cursor turns into a modest blackness diagonal right and downward pointing arrow. Left click once to select only the torso. Left click a second fourth dimension to include the header row and total row.

You tin can also select the table with the mouse. Place the active cell inside the table and then hover the mouse cursor over whatsoever border of the table until information technology turns into a four way directional arrow then left click. This will also select the cavalcade headers and total row.

Select Parts of the Tabular array from the Correct Click Menu

Y'all tin can too select rows, columns or the entire table using the correct click carte. Right click anywhere on the row or column you want to select then choose Select and option from the three options bachelor.

Add a Total Row

You can add a total row which allows you lot to display summary calculations in the last row of your table.

Adding summary calculations at the bottom of your data tin exist dangerous as they might cease upward getting included past blow in a pin tabular array using the data. This is another advantage of tables, as the total row won't be included in any pivot tables created with the table.

To enable the full row, get to the Table Tools Pattern tab and cheque the Total Row box plant in the Tabular array Way Options section.

You tin can temporarily disable the full row without losing the formulas yous added to it. Excel will remember the formulas you had and they will appear when y'all enable it once more.

Each cell in the total row has a drop down card that allows you lot to pick diverse accumulation functions to summarize the cavalcade of data higher up.

Yous can too enter your ain formulas. I've entered a SUMPRODUCT formula in the Unit Cost full to sum the Quantity x Unit Cost to calculate a total sale amount. Formulas don't have to return a number, they can besides be text results.

Constant numerical or text values are as well allowed anywhere in the total row. In fact the leftmost column will usually incorporate the text Total past default.

Add a Total Row with a Right Click

You can also add together the full row with a right click. Correct click anywhere on the tabular array and the cull Table and Full Row from the menu.

Add a Total Row with a Keyboard Shortcut

Some other way to speedily add the total row is to place the active jail cell cursor inside your table and use the Ctrl + Shift + T keyboard shortcut.

Disable the Column Header Row

The column header row is enabled by default, but you can disable information technology. This doesn't delete the column headers, it's essentially like hiding them as you will still reference columns based on the column header proper name.

Go to the Table Tools Design tab and uncheck the Total Row box found in the Tabular array Fashion Options section.

Add Bold Format to the Starting time or Last Columns

You can enable a bold formatting on either your get-go or final column to highlight it and draw attention to them over other columns.

Get to the Table Tools Blueprint tab and check either of the Beginning Column or Last Column boxes (or both) plant in the Table Style Options section.

Add Banded Rows or Columns

Banded rows are already enabled by default, only you lot can plow them off if you desire. Banded columns are disabled by default, so you need to enable them if you want them.

To enable or disable either, go to the Table Tools Design tab and check or uncheck the Banded Rows or Banded Columns boxes institute in the Table Style Options department.

I generally find banded rows are the well-nigh useful and if you enable banded columns at the same fourth dimension, the table starts to expect a footling messy. I recommend one or the other and not both at the same time.

Table with no banded rows or columns.
Table with banded rows simply.
Table with banded columns just.
Table with both banded rows and columns.

Table Filters

By default, the tabular array filters option is enabled. You can disable them from the Table Tools Design tab by unchecking the Filter Push box found in the Table Way Options section.

You can also toggle the filters on or off from the active table by using the regular filter keyboard shortcut of Ctrl + Shift + L.

If you left click on any of the filters, it will bring up the familiar filter menu where you can sort your tabular array and use various filters depending on the type of information in the column.

The great affair about table filters is you can have them on multiple tables in the same sail simultaneously. You lot volition need to be conscientious though as filtered items in one table volition affect the other tables if they share mutual rows. You can only take one prepare of filters at a time in a canvas of data without tables.

Total Row with Filters Applied

When y'all select a summary part from the drop downwardly carte in the total row, Excel will create the corresponding SUBTOTAL formula. This SUBTOTAL formula ignores hidden and filtered items. And then when you lot filter your table these summaries will update accordingly to exclude the filtered values.

Annotation that the SUMPRODUCT formula in the Unit Price column still includes all the filtered values while the SUBTOTAL sum formula in the Quantity column does not.

Cavalcade Headers Remain Visible When Scrolling

If you lot scroll downwardly while the active cell is in a table, its column headers will remain visible along with the filter buttons. The table'southward column headings will get promoted into the canvas's column headings where we would normally see the alphabetic column name.

This is extremely handy when dealing with long tables as you won't need to ringlet support to the meridian to see the column proper name or utilize the filters.

Automatically Include New Rows and Columns

If y'all type or copy and paste new data into the cells directly below a table, they will automatically exist absorbed into the table.

The same thing happens when you type or copy and paste into the cells directly to the right of a table.

Automatically Fill Formulas Down the Entire Cavalcade

When you enter a formula inside a table it will automatically make full the formula downward the entire column.

Even when a formula has already been entered and you add new information to the row directly beneath the table whatsoever existing formulas will automatically fill.

Editing an existing formula in any of the cells volition also update the formula in the entire cavalcade. Y'all'll never forget to copy and paste down a formula again!

Turn Off the Car Include and Car Fill Settings

Y'all can turn off the characteristic that automatically adds new rows or columns and fills down formulas.

Go to the File tab and select Options. Choose Proofing then press the AutoCorrect Options button. Navigate to the AutoFormat Every bit You Type tab in the AutoCorrect dialog box.

Unchecking the Include new rows and columns in table option allows you to type directly underneath or to the right of a table without it absorbing the cells.

Unchecking the Fill up formulas in tables to create calculated columns option means the formulas in a table will no longer automatically fill down the column.

Resize with the Handle

Every tabular array comes with a Size Handle plant in the bottom rightmost cell of the table.

When you hover the mouse over the handle, the cursor volition turn into a double-sided diagonally slanting arrow and you tin can then click and drag to resize the table. You can either aggrandize or contract the size. Information will exist absorbed into the table or removed from it appropriately.

Resize with the Ribbon

You can likewise resize the table from the ribbon. Go to the Table Tools Pattern tab and press the Resize Tabular array command in the Properties department.

The Resize Tabular array dialog box will popular upward and you lot'll exist able to select a new range for your table. Employ the range selector icon to select a new range. You can select either a larger or smaller range, only The table headers will demand to remain in the same row and the new table range must overlap the old table range.

Add a New Row with the Tab Primal

Yous can add together a new blank row to a table with the Tab primal. Place the agile cell cursor inside the table on the prison cell containing the sizing handle and press the Tab key.

The tab key act similar a wagon return and the agile cell is taken to the rightmost jail cell on a new line that's added directly below.

This is a handy shortcut to know because when the total row is enabled, information technology's non possible to add a new row past typing or copying and pasting information directly beneath the table.

Insert Rows or Columns

You tin insert extra rows or columns into a table with a right click. Select a range in the table and right click and so cull Insert from the carte. You can then either choose to insert Tabular array Columns to the Left or Table Rows Above.

Tabular array Columns to the Left volition insert the number of columns selected to the left of the choice and the number of rows in the pick is ignored.

Table Rows Above will insert the number of rows selected just above the choice and the number of columns in the pick is ignored.

Delete Rows or Columns

Deleting rows or columns has a similar story to inserting them. Select a range in the tabular array and correct click and so choose Delete from the menu. You can then either choose to delete Table Columns or Tabular array Rows.

Formats in a Table Automatically Employ to New Rows

When y'all add new information to your tabular array, you don't need to worry about applying formatting to match the rest of the data in a higher place. Formatting volition automatically make full downwards from above if the formatting has been applied to the entire column.

I'thou not just talking most the tabular array style formats. Other formatting like dates, numbers, fonts, alignments, borders, conditional formatting, prison cell colours etc. will all automatically fill downwards if they've been applied to the whole column.

If you lot've formatted all your numbers every bit a currency in a column and you lot add new data, it also volition get the currency format applied to information technology.

You lot never need to worry about inconsistent formatting in your information.

Add a Slicer

You can add a slicer (or several) to a table for an piece of cake to use filter and visual style to run into what items the tabular array is filtered on.

Become to the Table Tools Blueprint tab and press the Insert Slicer button plant in the Tools section.

Change the Mode

Irresolute the styling of a table is quick and piece of cake. Go to the Table Tools Design select a new style from the pick plant in the Table Styles section. If you left click the small downward pointer on the correct hand side of the styles palette, it will expand to show all bachelor options.

These table styles employ to the whole table and will also apply to any new rows or columns added after.

There are many options to choose from including light, medium and nighttime themes. Every bit y'all hover over the various selections, you'll be able to see a alive preview in the worksheet. The style won't actually modify until you click on one though.

You tin can even create your own New Tabular array Fashion.

Set a Default Tabular array Style

You tin set any of the styles bachelor equally the default so that when you lot create a new table you don't need to change the mode. Correct click on the manner you lot want to ready every bit the default and and then choose Fix As Default from the menu.

Unfortunately, this is a workbook level setting and will only bear on the electric current workbook. You lot will need to set up the default for each workbook you create if you lot don't want the awarding default option.

But Print the Selected Table

When you place the active prison cell cursor inside a tabular array and then try to print, there is an option to simply impress the selected table. Become to the Print menu screen by either going to the File tab and selecting Print or using the Ctrl + P keyboard shortcut, so select Print Selected Table in the settings.

This will remove any items from the print area that are non in the table.

Structured Referencing

Tables come with a useful feature chosen structured referencing which helps to brand range references more than readable. Ranges within a table can be referred to using a combination of the table proper name and cavalcade headings.

Instead of seeing a formula like this =SUM(D3:D9) you might come across something like this =SUM(Sales[Quantity]) which is much easier to empathize the significant of.

This is why naming your table with a brusk descriptive name and column headings is important as it volition improve the readability of the structured references!

When you reference specific parts of a tabular array, Excel volition create the reference for yous so you don't need to memorize the reference structure simply it will assist to sympathise it a bit.

Structured references can contain upwards to three parts.

This is the tabular array name. When referencing a range from inside the table this function of the reference is non required.
These are range identifiers and place certain parts of the reference for a table like the headers or full row.
These are the column names and volition either be a single cavalcade or a range of columns separated past a full colon.

Example of structured References for a Row

=Sales[@[Unit of measurement Price]] will reference a unmarried prison cell in the body.
=Sales[@[Product]:[Unit Cost]] will reference role of the row from the Product column to the Unit of measurement Cost column including all columns in between.
=Sales[@] will reference the total row.

Example of Structured References for Columns

=Sales[Unit Toll] will reference a single cavalcade and merely include the body.
=Sales[[#Headers],[#Data],[Unit of measurement Price]] volition reference a single column and include the column header and torso.
=Sales[[#Information],[#Totals],[Unit Price]] will reference a single column and include the body and total row.
=Sales[[#All],[Unit Toll]] will reference a single cavalcade and include the cavalcade header, body and total row.

Instance of Structured References for the Full Row

=Sales[#Totals] will reference the entire total row.
=Sales[[#Totals],[Unit of measurement Cost]] will reference a cell in the total row.
=Sales[[#Totals],[Production]:[Unit Toll]] will reference function of the total row from the Product cavalcade to the Unit Price cavalcade including all columns in between.

Example of Structured References for the Column Header Row

=Sales[#Headers] will reference the entire column header row.
=Sales[[#Headers],[Order Appointment]] volition reference a cell in the column header row.
=Sales[[#Headers],[Product]:[Unit Price]] will reference part of the column header row from the Production column to the Unit Price column including all columns in between.

Instance of Structured References for the Tabular array Torso

=Sales will reference the entire trunk.
=Sales[[#Headers],[#Information]] volition reference the entire column header row and body.
=Sales[[#Data],[#Totals]] will reference the unabridged body and total row.
=Sales[#All] will reference the entire column header row, body and total row.

Using Intellisense

One of the great things nearly a table is the structured references will appear in Intellisense menus when writing formulas. This means y'all tin can easily write a formula using the structured references without remembering all the fields in your table.

After typing the first letter of the table proper noun, the IntelliSense menu will prove the table name among all the other objects starting with that letter. Yous tin can use the arrow keys to navigate to it so press the Tab central to autocomplete the table proper name in your formula.

If you want to reference a part of the table, y'all can so type a [ to bring up all the bachelor items in the tabular array. Again, you can navigate with the arrow keys and then employ the Tab key to autocomplete the field proper noun. Then yous can close the detail with a ].

Turn Off Structured Referencing

If you're not a fan of being forced to utilise the structured referencing organisation, then you lot can turn it off. Whatsoever formulas that take been entered using the structured referencing will remain and they will yet piece of work the aforementioned. You'll likewise still exist able to utilize structured references, Excel just won't automatically create them for you.

To turn information technology off, go to the File tab and then select Options. Cull Formulas on the side pane and then uncheck the Use table names in formulas box and press the Ok button.

Summarize with a PivotTable

You can create a pivot table from your tabular array in Tabular array Tools Pattern tab, press the Summarize with PivotTable push establish in the Tools section. This volition bring upwards the Create PivotTable window and you can create a pivot table every bit usual.

This is the aforementioned as creating a pivot table from the Insert tab and doesn't give any extra options specific to tables.

Remove Duplicates from a Tabular array

You lot tin can create remove indistinguishable rows of data from your tabular array in Table Tools Design tab, printing the Remove Duplicates button plant in the Tools department. This will bring up the Remove Duplicates window and delete duplicate values for one or more columns in the table.

This is the aforementioned equally removing duplicates from the Data tab and doesn't requite whatever extra options specific to tables.

About the Author

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Ability Automate, Ability Apps and SharePoint. Y'all can detect other interesting manufactures from John on his web log or YouTube channel.