Overview of Excel tables

To make managing and analyzing a group of related data easier, you can turn a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells into a Microsoft Office Excel table (previously known as an Excel list). A table typically contains related data in a series of worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

 Note    Excel tables should not be confused with the data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) that are part of a suite of what-if analysis commands. For more information about data tables, see Calculate multiple results with a data table.

In this article

Learn about the elements of an Excel table

Managing data in an Excel table

Learn about the elements of an Excel table

A table can include the following elements:

• Header row  By default, a table has a header row. Every table column has filtering enabled in the header row so that you can filter or sort your table data quickly.

• Banded rows  By default, alternate shading or banding has been applied to the rows in a table to better distinguish the data.

• Calculated columns  By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column.

• Total row  You can add a total row to your table that provides access to summary functions (such as the AVERAGE, COUNT, or SUM function). A drop-down list (drop-down list box: A control on a menu, toolbar, or dialog box that displays a list of options when you click the small arrow next to the list box.) appears in each total row cell so that you can quickly calculate the totals that you want.

• Sizing handle  A sizing handle in the lower-right corner of the table allows you to drag the table to the size that you want.

 Top of Page

Managing data in an Excel table

You can use one table to manage your data, but if you want to manage several groups of data, you can insert more than one table in the same worksheet.

If you have access to and authoring permission on a Microsoft Windows SharePoint Services site, you can use it to share a table with other users. By exporting table data to a SharePoint list, other people can view, edit, and update the table data in the SharePoint list. You can create a one-way connection to the SharePoint list so that you can refresh the table data on the worksheet to incorporate changes that are made to the data in the SharePoint list. You can no longer update a SharePoint list with changes that you make to the table data in Excel. After exporting the table data to a SharePoint list, you can open a SharePoint list in Excel as read-only  — any changes that you want to make can be made only to the data on the SharePoint site.

 Note    Because table functionality is not supported in shared workbooks (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.), you cannot create a table in a shared workbook.

Table features that you can use to manage table data

• Sorting and filtering  Filter drop-down lists (drop-down list box: A control on a menu, toolbar, or dialog box that displays a list of options when you click the small arrow next to the list box.) are automatically added in the header row of a table. You can sort tables in ascending or descending order or by color, or you can create a custom sort order. You can filter tables to show only the data that meets the criteria that you specify, or you can filter by color. For more information on how to filter or sort data, see Filter data or Sort data.
• Formatting table data  You can quickly format table data by applying a predefined or custom table style. You can also choose Table Styles options to display a table with or without a header or a totals row, to apply row or column banding to make a table easier to read, or to distinguish between the first or last columns and other columns in the table. For more information on how to format table data, see Format an Excel table.
• Inserting and deleting table rows and columns  You can use one of several ways to add rows and columns to a table. You can quickly add a blank row at the end of the table, include adjacent worksheet rows or worksheet columns in the table, or insert table rows and table columns anywhere that you want. You can delete rows and columns as needed. You can also quickly remove rows that contain duplicate data from a table. For more information about adding and deleting table rows and columns, see Add or remove Excel table rows and columns.
• Using a calculated column  To use a single formula that adjusts for each row in a table, you can create a calculated column. A calculated column automatically expands to include additional rows so that the formula is immediately extended to those rows. For more information on how to create a calculated column, see Create, edit, or remove a calculated column in an Excel table.
• Displaying and calculating table data totals  You can quickly total the data in a table by displaying a totals row at the end of the table and then using the functions that are provided in drop-down lists for each totals row cell. For more information on how to display and calculate table data totals, see Total the data in an Excel table.
• Using structured references  Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula.
• Ensuring data integrity   For tables that are not linked to SharePoint lists, you can use the built-in data validation features in Excel. For example, you may choose to allow only numbers or dates in a column of a table. For more information on how to ensure data integrity, see Prevent invalid data entry on a worksheet.
• Exporting to a SharePoint list  You can export a table to a SharePoint list so that other people can view, edit, and update the table data.

 Top of Page

See Also

• Add, edit, find, and delete rows by using a data form
• Create or delete an Excel table in a worksheet
• Define and use names in formulas

Excel > Worksheet and Excel table basics > Using Excel tables

Create or delete an Excel table in a worksheet

Excel 2007

When you create a table (previously known as list) in a Microsoft Office Excel worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.), you can manage and analyze the data in that table independently of data outside the table. For example, you can filter table columns, add a row for totals, apply table formatting, and publish a table to a server that is running Microsoft Windows SharePoint Services 3.0.

If you do not want to work with your data in a table, you can convert the table to a regular range while keeping any table style formatting that you applied. When you no longer need a table, you can delete it.

 Note    Excel tables should not be confused with the data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) that are part of a suite of what-if analysis commands. For more information about data tables, see Calculate multiple results with a data table.

What do you want to do?

Create a table

Convert a table to a range of data

Delete a table

Create a table

You can use one of two ways to create a table. You can either insert a table in the default table style or you can format your data as a table in a style that you choose.

Insert a table

1. On a worksheet, select the range of cells that you want to include in the table. The cells can be empty or can contain data.
2. On the Insert tab, in the Tables group, click Table.

Keyboard shortcut  You can also press CTRL+L or CTRL+T.

3. If the selected range contains data that you want to display as table headers, select the My table has headers check box.

Table headers display default names if you do not select the My table has headers check box. You can change the default names by typing the text that you want.

 Note    If you do not want to display table headers, you can turn them off later. For more information about how to turn table headers off, see Turn Excel table headers on or off.

Tips

• After you create a table, the Table Tools become available, and a Design tab is displayed. You can use the tools on the Design tab to customize or edit the table.
• Unlike lists in Office Excel 2003, a table does not have a special row (marked with *) for quickly adding new rows. For more information about how to add or insert rows in a table, see Add or remove table rows and columns in an Excel table.

Format data as a table

1. On the worksheet, select a range of empty cells or cells that contain the data that you want to quickly format as a table.
2. On the Home tab, in the Styles group, click Format as Table.

 Note    When you use Format as Table, Office Excel automatically inserts a table.

3. Under Light, Medium, or Dark, click the table style that you want to use.

 Note    Custom table styles are available under Custom after you create one or more of them. For information about how to create a custom table style, see Format an Excel table.

Tips

• After you create a table, the Table Tools become available, and a Design tab is displayed. You can use the tools on the Design tab to customize or edit the table.
• Unlike lists in Office Excel 2003, a table does not have a special row (marked with *) for quickly adding new rows. For more information about how to add or insert rows in a table, see Add or remove table rows and columns in an Excel table.

 Top of Page

Convert a table to a range of data

1. Click anywhere in the table.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the Tools group, click Convert to Range.

 Note    Table features are no longer available after you convert the table back to a range. For example, the row headers no longer include the sort and filter arrows, and structured references (references that use table names) that were used in formulas turn into regular cell references.

Tips

• You can also right-click the table, point to Table, and then click Convert to Range.
• Immediately after you create a table, you can also click Undo on the Quick Access Toolbar to convert that table back to a range.

 Top of Page

Delete a table

1. On a worksheet, select a table.
2. Press DELETE.

Tip  You can also click Undo on the Quick Access Toolbar to delete a table that you just created.

 Top of Page

See Also

• Add or remove table rows and columns in an Excel table
• Add, edit, find, and delete rows by using a data form
• Create, edit, or remove a calculated column in an Excel table
• Format an Excel table
• Overview of Excel tables
• Rename an Excel table
• Select rows and columns in an Excel table
• Total the data in an Excel table
• Turn Excel table headers on or off

Excel > Formula and name basics > Working with names

Using structured references with Excel tables

Structured references make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table. They are especially useful because table data ranges often change, and the cell references for structured references adjust automatically. This minimizes the need to rewrite formulas as rows and columns are added and deleted in a table, or when external data is refreshed.

In this article

The Department Sales table example

Components of a structured reference

Table names and column specifiers

Reference operators

Special item specifiers

Qualifying structured references in calculated columns

Examples of using structured references

Working with structured references

Structured reference syntax rules

The Department Sales table example

The following is an example, referenced throughout this article, of a table that is based on sales in a department of six employees with the latest sales amounts and commissions.

A Department Sales table

The entire table (A1:E8)

The table data (A2:E7)

A column and column header (D1:D8)

A calculated column (E1:E8)

The Totals row (A8:E8)

 Top of Page

Components of a structured reference

To work with tables and structured references effectively, you need to understand how to create the syntax of structured references when you are creating formulas. The components of a structured reference are illustrated in the following example of a formula that adds up total sales amounts and commission amounts:

A table name is a meaningful name that you provide to reference the actual table data (excluding the headers row and totals row, if any).

A column specifier is derived from the column header, enclosed in brackets, and references the column data (excluding the column header and total, if any).

A special item specifier is a way to refer to specific portions of the table, such as the Totals row.

The table specifier is the outer portion of the structured reference that is enclosed in square brackets following the table name.

A structured reference is the entire string beginning with the table name and ending with the table specifier.

 Top of Page

Table names and column specifiers

Each time that you insert a table, Microsoft Office Excel creates a default table name (Table1, Table2, and so on) at the global workbook level or scope. You can easily change the name to make it more meaningful to you. For example, to change Table1 to DeptSales, you can use the Edit Name dialog box. (On the Design tab, in the Properties group, edit the table name in the Table Name box.)

A table name refers to the entire range of data in the table with the exception of the header and total rows. In the Department Sales table example, the table name, DeptSales, refers to the cell range A2:E7.

Similar to table names, column specifiers represent references to the entire column of data with the exception of the column header and total. In the Department Sales table example, the column specifier, [Region], refers to the cell range B2:B7, and the column specifier, [ComPct], refers to the cell range D2:D7.

 Top of Page

Reference operators

For added flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.

 Top of Page

Special item specifiers

For added convenience, you can also use special items to refer to various portions of a table, such as just the Totals row, to make it easier to refer to these portions in formulas. The following are the special item specifiers that you can use in a structured reference:

 Top of Page

Qualifying structured references in calculated columns

When you create a calculated column, you commonly use a structured reference to create the formula. This structured reference can be unqualified or fully qualified. For example, to create the calculated column called, ComAmt, that calculates the amount of commission in dollars, you can use the following formulas:

The general rule to follow is this: If you are using structured references within a table, such as when you create a calculated column, you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference.

 Top of Page

Examples of using structured references

There are a number of ways that you can use these special items and combine them with table names and column references as the following information shows:

 Top of Page

Working with structured references

Consider the following when you work with structured references.

Using Formula AutoComplete  

You may find that using Formula AutoComplete is very useful when you enter structured references and to ensure the use of correct syntax. For more information, see Use Formula AutoComplete.

Deciding whether to generate structured references for tables in semi-selection  

By default, when you create a formula, clicking a cell range within a table semi-selects the cells and automatically enters a structured reference, instead of the cell range in the formula. This semi-selection behavior makes it much easier to enter a structured reference. You can turn this behavior on or off by selecting or clearing the Use table names in formulas check box in the Working with formulas section of the Formulas category in the Excel Options dialog box.

Converting a range to a table and a table to a range  

When you convert a table to a range, all cell references change to their equivalent A1 style references. When you convert a range to a table, Excel does not automatically change any cell references to this range to their equivalent table names and column references.

Turning off column headers  

If you turn off table column headers (On the table Design tab, in the Table Style Options group, clear Header Row), structured references that use these headers are not affected, and you can still use them in formulas.

Adding or deleting columns and rows to the table  

Because table data ranges often change, the cell references for structured references adjust automatically. For example, if you use a table name in a formula to count all of the cells of data in the Department Sales table, such as =COUNTA(DeptSales) in The Department Sales table example, the returned value is 30 because the data range is A2:E7. If you then added a row of data, the cell reference automatically adjusts to A2:E8, and the new returned value is 35.

Renaming a table or column  

If you rename a column or table, Excel automatically changes the use of that table and column header in all structured references that are used in the workbook.

Moving, copying, and filling structured references  

All structured references remain the same when you copy or move a formula that uses a structured reference.

When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.

 Top of Page

Structured reference syntax rules

The following is a list of syntax rules that you need to be aware of when you create and edit structured references.

 Note    Table names follow the same rules as defined names. For more information, see Use names to clarify formulas.

The use of brackets in specifiers  

All table, column, and special item specifiers must be enclosed in matching brackets ([ ]). A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers.

Example   =DeptSales[[SalesPers]:[Region]]

Column headers are text strings  

All column headers are text strings, but do not require quotes when they are used in a structured reference. If a column header contains numbers or dates, such as 2004 or 1/1/2004, these are still considered text strings. Because column headers are text strings, you cannot use expressions within brackets.

Example   =DeptSalesFYSummary[[2004]:[2002]]

Special characters in table column headers  

If a table column header contains one of the following special characters, the entire column header must be enclosed in brackets. Effectively, this means double brackets are required in a column specifier with the following special characters: Space, tab, line feed, carriage return, comma (,), colon (:), period (.), left bracket ([) , right bracket (]), pound sign (#), single quotation mark ('), double quotation mark ("), left brace ({), right brace (}), dollar sign ($), karet (^), ampersand (&), asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (/).

Example   =DeptSalesFYSummary[[Total$Amount]]

The only exception to this is if the only special character that is used is a space character.

Example  =DeptSales[Total Amount]

Special characters in column headers that require the use of the escape character  

The following characters have special meaning and require the use of a single quotation mark (') as an escape character: Left bracket ([), right bracket (]), pound sign(#), and single quotation mark (').

Example   =DeptSalesFYSummary['#OfItems]

Using the space character to improve readability in a structured reference  

You can use space characters to improve the readability of a structured reference in the following way:

• One space after the first left bracket ([) and preceding the last right bracket (]).
• One space after a comma.

Example   =DeptSales[ [SalesPers]:[Region] ]

Example   =DeptSales[[#Headers], [#Data], [ComPct]]

 Top of Page

Excel > Worksheet and Excel table basics > Using Excel tables

Convert an Excel table to a range of data

Excel 2007

After you create a table in Microsoft Offfice Excel, you may not want to keep working with the table functionality that it comes with. Or you may want a table style without the table functionality. To stop working with your data in a table without losing any table style formatting that you applied, you can convert the table to a regular range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of data on the worksheet.

Important  To succesfully complete this procedure, you must have created an Excel table in your worksheet. For more information, see Create or delete an Excel table in a worksheet.

1. Click anywhere in the table to make sure that the active cell is in a table column.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the Tools group, click Convert to Range.

 Note    Table features are no longer available after you convert the table back to a range. For example, the row headers no longer include the sort and filter arrows, and structured references (references that use table names) that were used in formulas turn into regular cell references.

Tips

• You can also right-click the table, point to Table, and then click Convert to Range.
• Immediately after you create a table, you can also click Undo on the Quick Access Toolbar to convert that table back to a range.

See Also

• Create or delete an Excel table in a worksheet
• Define and use names in formulas
• Overview of Excel tables

Excel > Worksheet and Excel table basics > Using Excel tables

Total the data in an Excel table

You can quickly total the data in a Microsoft Office Excel table by displaying a totals row at the end of the table and then by using the functions that are provided in drop-down lists for each totals row cell.

1. Click anywhere in the table.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the Table Style Options group, select the Total Row check box.

 Note    The total row appears as the last row in the table and displays the word Total in the leftmost cell.

3. In the total row, click the cell in the column for which you want to calculate a total, and then click the drop-down list arrow that appears.
4. In the drop-down list, select the function that you want to use to calculate the total.

Tip  Formulas that you can use in the total row are not limited to the functions in the list. You can enter any formula that you want in any total row cell.

 Notes 

• When you enter a formula in the row directly below a table without a total row, the total row is displayed with the formula and without the word Total.
• You can also type text entries in the total row.

See Also

• Create or delete an Excel table in a worksheet

Excel > Worksheet and Excel table basics > Using Excel tables

Create, edit, or remove a calculated column in an Excel table

In a Microsoft Office Excel table, you can quickly create a calculated column. A calculated column uses a single formula that adjusts for each row. It automatically expands to include additional rows so that the formula is immediately extended to those rows. You need to enter a formula only once. You don't need to use the Fill or Copy command.

You can enter additional formulas in a calculated column as exceptions, but Excel notifies you of any inconsistencies so that you can resolve them, if needed. You can also update the formula in a calculated column by editing the calculated column.

What do you want to do?

Create a calculated column

Include calculated column exceptions

Edit a calculated column

Delete a calculated column

Create a calculated column

1. Click a cell in a blank table column that you want to turn into a calculated column.

Tip  If needed, insert a new column in the table. Select a table column to the left of which you want to insert a blank table column. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Table Columns to the Left. You can also right-click a cell in the table column, click Insert, and then click Table Columns to the Left.

2. Type the formula that you want to use.

The formula that you typed is automatically filled into all cells of the column — above as well as below the active cell.

 Notes 

• Copying or filling a formula into all cells of a blank table column also creates a calculated column.
• If you enter a formula in a column below the table, a calculated column is created, but the rows outside of the table cannot be used in a table reference.
• If you type or move a formula in a table column that already contains data, a calculated column is not automatically created. However, the AutoCorrect Options button is displayed to provide you with the option to overwrite the data so that a calculated column can be created. You do not have this option if you copy a formula into a table column that already contains data.
• You can quickly undo a calculated column. If you used the Fill command or CTRL+ENTER to fill an entire column with the same formula, click Undo on the Quick Access Toolbar. If you typed or copied a formula into a cell of a blank column, click Undo twice.

 Top of Page

Include calculated column exceptions

A calculated column can include formulas that are different from the column formula, which creates an exception that will be clearly marked in the table. This way, inadvertent inconsistencies can easily be detected and resolved.

Calculated column exceptions are created when you do any of the following:

• Type data other than a formula in a calculated column cell.
• Type a formula in a calculated column cell, and then click Undo on the Quick Access Toolbar.
• Type a new formula in a calculated column that already contains one or more exceptions.
• Copy data into the calculated column that does not match the calculated column formula.

 Note    If the copied data contains a formula, this formula will overwrite the data in the calculated column.

• Delete a formula from one or more cells in the calculated column.

 Note    This exception is not marked.

• Move or delete a cell on another worksheet area that is referenced by one of the rows in a calculated column.

 Top of Page

Edit a calculated column

• In a calculated column that does not contain exceptions, do one of the following:
  • Click any cell, and then edit the formula in that cell.
  • Copy another formula into any cell of the calculated column.

 Notes 

• If you edit or copy more than one formula, the column will not be updated, but Excel notifies you of any inconsistencies so that you can resolve them.
• If you change a formula in a calculated column that contains exceptions, Excel cannot automatically update the calculated column. However, the AutoCorrect Options button is displayed to provide you with the option to overwrite all formulas in that column with the modified formula so that a calculated column can be created.

 Top of Page

Delete a calculated column

1. To delete a calculated column, select the calculated column.
2. On the Home tab, in the Cells group, click Delete.

Tip  You can also press DELETE.

 Top of Page

See Also

• Create or delete an Excel table in a worksheet
• Print an Excel table
• Total the data in an Excel table

Excel > Worksheet and Excel table basics > Using Excel tables

Refresh an Excel Table

After you export a Microsoft Office Excel table to a Windows SharePoint Services list and create a connection to that list, you can update the table data to incorporate changes that are made to the SharePoint list. You cannot update data in the SharePoint list to incorporate changes that you make to data in the Excel table.

Important  Updates from a SharePoint list overwrite the original exported data in addition to any changes that you made to the table data in the worksheet after you exported it to the SharePoint site.

1. In the worksheet, click anywhere in the table that you want to update.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the External Data group, click Refresh.

See Also

• Export an Excel table to a SharePoint list
• Unlink an Excel table from a SharePoint list
• What happened to SharePoint list synchronization?

Excel > Worksheet and Excel table basics > Using Excel tables

Add or remove table rows and columns in an Excel table

After you create a Microsoft Office Excel table in your worksheet, you can easily add table rows and columns. You can quickly add a blank row at the end of the table, include adjacent worksheet rows or worksheet columns in the table, or insert table rows and table columns anywhere you want.

You can delete rows and columns as needed. You can also quickly remove rows that contain duplicate data from a table.

 Note    Adding and removing table rows and columns is different from adding and removing worksheet rows and columns. To add and remove worksheet rows and columns, see Insert or delete cells, rows, and columns.

What do you want to do?

Add a blank row at the end of the table

Include a worksheet row or worksheet column in a table

Resize a table

Insert a table row or table column

Delete rows or columns in a table

Remove duplicate rows from a table

Remove blank rows from a table

Add a blank row at the end of the table

• Press TAB in the last cell of the last row to add a blank row at the end of the table.

 Note    If a totals row is displayed in the table, pressing TAB in the last cell of the totals row does not add a new row.

 Top of Page

Include a worksheet row or worksheet column in a table

• Do one of the following:
  • To include a worksheet row in the table, type a value or text in a cell that is directly below the table.
  • To include a worksheet column in the table, type a value or text in a cell that is directly adjacent to the right of the table.
  • To include worksheet rows or worksheet columns by using the mouse, drag the resize handle at the lower-right corner of the table down to select rows and to the right to select columns.

 Top of Page

Resize a table

1. Click anywhere in the table.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the Properties group, click Resize Table.

3. In the Select the new data range for your table box, type the range that you want to use for the table.

Tip  You can also click the Collapse Dialog button  at the right end of the Select the new data range for your table box and then select the range that you want to use for the table on the worksheet. When you finish, click the Collapse Dialog button again to display the entire dialog box.

Tip  To resize a table by using the mouse, drag the triangular resize handle at the lower-right corner of the table up, down, to the left, or to the right to select the range that you want to use for the table.

 Top of Page

Insert a table row or column

1. Do one of the following:
   • To insert one or more table rows, select one or more table rows above which you want to insert one or more blank table rows.

Tip  If you select a cell or range in the last row, you can also insert a row above or below that row.

• To insert one or more table columns, select one or more table columns to the left of which you want to insert one or more blank table columns.

Tip  If you select a cell or range in the last column, you can also insert a column to the left or to the right of that column.

2. On the Home tab, in the Cells group, click the arrow next to Insert.

3. Do one of the following:
   • To insert table rows, click Insert Table Rows Above.
   • To insert a table row below the last row, click Insert Table Row Below.
   • To insert table columns, click Insert Table Columns to the Left.
   • To insert a table column to the right of the last column, click Insert Table Column to the Right.

Tip  You can also right-click one or more table rows or table columns, point to Insert on the shortcut menu, and then select what you want to do from the list of options. Or you can right-click one or more cells in a table row or table column, point to Insert, and then click Table Rows Above or Table Columns to the Left.

 Top of Page

Delete rows or columns in a table

1. Select one or more table rows or table columns that you want to delete.

Tip  You can also just select one or more cells in the table rows or table columns that you want to delete.

2. On the Home tab, in the Cells group, click the arrow next to Delete, and then click Delete Table Rows or Delete Table Columns.

Tip  You can also right-click one or more rows or columns, point to Delete on the shortcut menu, and then click Table Columns or Table Rows. Or you can right-click one or more cells in a table row or table column, point to Delete, and then click Table Rows or Table Columns.

 Top of Page

Remove duplicate rows from a table

Just as you can remove duplicates from any selected data in Excel, you can easily remove duplicates from a table.

1. Click anywhere in the table.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the Tools group, click Remove Duplicates.

3. In the Remove Duplicates dialog box, under Columns, select the columns that contain duplicates that you want to remove.

Tip  You can also click Unselect All and then select the columns that you want or click Select All to select all of the columns.

 Note    Duplicates that you remove are deleted from the worksheet. If you inadvertently delete data that you meant to keep, you can click Undo on the Quick Access Toolbar to restore the deleted data. You may also want to use conditional formats to highlight duplicate values before you remove them. For more information, see Add, change, or clear conditional formats.

 Top of Page

Remove blank rows from a table

1. Make sure that the active cell is in a table column.
2. Click the arrow in the column header.
3. To filter for blanks, in the AutoFilter menu at the top of the list of values, clear (Select All), and then at the bottom of the list of values, select (Blanks).

 Note    The (Blanks) check box is available only if the range of cells or table column contains at least one blank cell.

4. Select the blank rows in the table, and then press CTRL+- (hyphen).

Tip  You can use a similar procedure for filtering and removing blank worksheet rows. For more information about how to filter for blank rows in a worksheet, see Filter data in a range or table.

 Top of Page

See Also

• Create or delete an Excel table in a worksheet
• Select rows and columns in an Excel table
• Turn Excel table headers on or off

Excel > Worksheet and Excel table basics > Using Excel tables

Select rows and columns in an Excel table

You can select cells and ranges in a table just like you would select them in a worksheet, but selecting table rows and columns is different from selecting worksheet rows and columns.

See Also

• Create or delete an Excel table in a worksheet

Excel > Worksheet and Excel table basics > Using Excel tables

Turn Excel table headers on or off

Excel 2007

When you create a Microsoft Office Excel table on a worksheet, table headers are automatically added and displayed by default.

Table headers either display default names that you can change on the worksheet, or you can specify that they display the header data that is on your worksheet. In a long table, table headers replace the worksheet column headings (column heading: The lettered or numbered gray area at the top of each column. Click the column heading to select an entire column. To increase or decrease the width of a column, drag the line to the right of the column heading.) so that they stay visible when you scroll the table data. If you do not want to view the table headers, you can turn them off.

 Note    Table headers should not be confused with worksheet column headings or the headers for printed pages. For more information about how to turn off worksheet column headings or headers for printing, see Use Page Layout view to fine-tune pages before printing.

Important  To succesfully complete this procedure, you must have created an Excel table on your worksheet. For more information, see Create or delete an Excel table in a worksheet.

1. Click anywhere in the table to make sure that the active cell is in a table column.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the Table Style Options group, clear or select the Header Row check box to hide or display the table headers.

 Notes 

• When you turn the table headers off, the table header AutoFilters and any applied filters are removed from the table.
• When you add a new column when table headers are not displayed, the name of the new table header cannot be determined by a series fill that is based on the value of the table header that is directly adjacent to the left of the new column. This only works when table headers are displayed. Instead, a default table header is added that you can change when you display table headers.
• Although it is possible to refer to table headers that are turned off in formulas, you cannot refer to them by selecting them. References in tables to a hidden table header return zero (0) values, but they remain unchanged and return the table header values when the table header is displayed again. All other worksheet references (such as A1 or RC style references) to the table header are adjusted when the table header is turned off and may cause formulas to return unexpected results.

See Also

• Create or delete an Excel table in a worksheet
• Use headers and footers in worksheet printouts
• Use Page Layout view to fine-tune pages before printing

Excel > Worksheet and Excel table basics > Using Excel tables

Print an Excel table

If the data that you want to print is in a Microsoft Office Excel table, you can print just the Excel table.

1. Click a cell within the table to activate the table.
2. Click Microsoft Office Button , and then click Print.

Keyboard shortcut  You can also press CTRL+P.

3. Under Print what, click Table.

Excel > Worksheet and Excel table basics > Using Excel tables

What happened to Excel lists?

Symptoms

You cannot find the Excel List feature on the Ribbon, which is a component of the Microsoft Office Fluent user interface.

Cause

The Excel List feature still exists, but it has been renamed. Excel lists are now called Excel tables.

Resolution

To create an Excel table (previously known as an Excel list), select the range that you want to turn into a table, and then do one of the following:

• On the Insert tab, in the Tables group, click Table.

Keyboard shortcut  In addition to CTRL+L, you can now press CTRL+T to create a table.

• On the Home tab, in the Styles group, click Format as Table, and then select the format that you want to use.

Tip  This displays the Table Tools, adding a Design tab that provides access to additional table features, such as table options and styles.

For more information on how to create a table, see Create or delete an Excel table.

See Also

• Create or delete an Excel table in a worksheet
• Overview of Excel tables
• What happened to the data form?

Excel > Worksheet and Excel table basics > Using Excel tables

Export data to a SharePoint list

Excel 2007

If you have data stored on a worksheet, and you want to share the data with others on a Microsoft Windows SharePoint Services site, you can export the data to a SharePoint list and share it with others who use the site. To export data by using this procedure, you must have have a datasheet control installed that is compatible with Windows SharePoint Services. A compatible datasheet control is installed with Microsoft Office Professional 2007 or Microsoft Office Access 2007.

In this article

Overview

Export data to a new SharePoint list

Update data from a SharePoint list

Overview

When you export data from Microsoft Office Excel 2007 to a SharePoint site, people can continue to update the list on the SharePoint site as they do any other SharePoint list.

You can create a one-way connection to the data, so that when the data changes on the SharePoint site, the data can be updated in Excel. When you update your data in Excel, the latest data from the SharePoint site overwrites the data on the Excel worksheet, including any changes that you made to the data. You can also choose to export the data without a connection to the SharePoint list, so that changes to the SharePoint list cannot be updated on the Excel worksheet.

To use an Office program to manipulate data from a SharePoint list and have those changes reflected on the SharePoint site, you can use Office Access 2007 or a solution that uses Visual Basic for Applications (VBA).

You can also manipulate data from a SharePoint list in a workbook that is saved as an Excel 97-2003 workbook (.xls format). However, if you save an Excel 2003 workbook as an Office Excel 2007 workbook (.xlsx format), only a read-only connection is available from the SharePoint list.

To export data to a SharePoint list, first create a table with your data in Office Excel 2007. A table is a series of rows and columns, also known as a range of data, that contains related data that is managed independently from the data in other rows and columns on the worksheet. When you create a table from your data, Excel can more easily manage, export, and import the data, because it is clear which rows and columns belong in the table.

After you create a table, you can use the Export Table to SharePoint List Wizard in Office Excel 2007, which enables you to provide a name and description for the list on the SharePoint site. The data that you export is stored as a custom list in datasheet view on the SharePoint site.

To export data to a list on a SharePoint site, you need to have a connection to the SharePoint site where you are creating the list and the permission to create lists on the site.

Tip  You can also create a list directly from a SharePoint site by using data from Microsoft Office Excel. On the SharePoint site, you create a new list by using the Import Spreadsheet command and then either select the table or named range or enter the range of cells that you want to use for the list.

Data types

Some data types from Excel cannot be exported to a list on the SharePoint site. When the data is exported, some data types are converted to data types that are compatible with SharePoint lists. Formulas that you created in Excel are converted to values in the SharePoint lists. You can create formulas for the columns on the SharePoint site after the data is converted.

When you publish an Excel table to a SharePoint site, each column is assigned one of the following SharePoint data types:

• Text (single line)
• Text (multiple lines)
• Currency
• Date/time
• Number
• Hyperlink

 Note    If a column has cells with different data types, Excel applies a data type that can be used for all cells in the column. For example, if a column contains numbers and text, the data type in the SharePoint list will be text.

 Top of Page

Export data to a new SharePoint list

To export data to a SharePoint list, start with a table. A table is a series of rows and columns that contains related data that is managed independently from the data in other rows and columns on the worksheet. If your data isn't already defined as a table, you can create a table before you export the data.

1. Do one of the following:
   • If the data is already in a table, click anywhere in the table.
   • If the data is not in a table, use the following procedure to create a table.

Create a table in Excel

1. Select the range of empty cells or data that you want to make into a table.
2. On the Insert tab, in the Tables group, click Table.
3. If the selected range contains data that you want to display as table headers, select the My table has headers check box.

In most cases, the table headers become the column names for the SharePoint list.

4. Click OK.
2. On the Design tab, in the External Table Data group, click Export, and then click Export Table to SharePoint List.

The Export Table to SharePoint List Wizard appears.

3. In the Address box, enter the Web address of the SharePoint site you want to export the list to.

If you have visited the SharePoint site before, the address may appear in the drop-down list.

4. To create a connection to the SharePoint list so that you can update the data on your worksheet with any changes on the SharePoint site, select the Create a read-only connection to the new SharePoint list check box.

If you want to export the data to a list without creating a connection to it in your Excel table, do not select the check box.

5. In the Name box, type a name for your list. The list name is required.

The name appears at the top of the list page, becomes part of the Web address for the list page, and appears in navigational elements that help users to find and open the list.

6. In the Description box, type a description for your list. The description is optional.

The description appears at the top of the list in most views, underneath the name of the list.

7. Click Next.
8. On the next page, the Export Table to SharePoint List Wizard shows how the data types will be exported from Excel to the SharePoint list. If the data types are correct for the list, click Finish.

If the data types are not correct for the list, click Cancel, and then confirm that the key cell can be converted into a data type that can be supported in the SharePoint list. For example, you may need to convert the data on the Excel worksheet into a simpler format, such as plain text or a number.

9. When a message appears to confirm that your list was exported, click OK.

The message displays a link that you can click to see your list on the SharePoint site, or you can view your list later by clicking the Open in Browser button on the External Table Data tab.

Tip  Find links to more information about working with external data in the See Also section.

 Top of Page

Update data from a SharePoint list

As people continue to update the list on the SharePoint site, you can update the data on your worksheet with the latest changes. Doing this overwrites the data that was originally exported to the SharePoint list and any changes that you made to the data in Excel.

1. Click anywhere in the table that you want to update.
2. In the Design group, on the External Table Data tab, click Refresh.

 Note    If you no longer want a connection between your worksheet and the SharePoint site, you can unlink the table. On the External Table Data tab, click Unlink.

Tip  Find links to more information about updating external data in the See Also section.

 Top of Page

See Also

• Add or remove table rows and columns in an Excel table
• Connection properties
• Create or delete an Excel table in a worksheet
• Create, edit, and manage connections to external data
• Overview of Excel tables
• Overview of sharing and collaborating on Excel data
• Refresh connected (imported) data
• Remove the data connection from an external data range
• Using Excel Services to collaborate

Excel > Worksheet and Excel table basics > Using Excel tables

Unlink an Excel table from a SharePoint list

Excel 2007

When you unlink a Microsoft Office Excel table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) that was exported to a custom list (list: A Web site component that stores and displays information that users can add to by using their browsers. Requires a Web server that is running Windows SharePoint Services.) on a Microsoft Windows SharePoint Services site, the connection to that Windows SharePoint Services list is removed. The table will remain intact on the worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) and the SharePoint list will remain on the SharePoint site, but you will no longer be able to update your table with changes that are made to the SharePoint list.

Important  After you unlink a table from its corresponding SharePoint list, the connection is lost permanently. You cannot undo unlinking or restore the connection.

1. In the worksheet, click anywhere in the table that you want to unlink from the SharePoint list.

Tip  This displays the Table Tools, adding the Design tab.

2. On the Design tab, in the External Data group, click Unlink.