Search results
People also ask
How do I change a table name in Excel?
Can I Change column Letters in Excel?
How do I name a column in Excel?
How do I rename a table in Excel?
Nov 16, 2016 · In Excel 2007 and later, you can convert a range of data into an Excel Table (Insert Ribbon > Table). An Excel Table can use structured table references instead of cell addresses, so the labels in the first row of the table now serve as a name reference for the data in the column.
- Overview
- What happens when I use explicit cell references?
- How do I change a table name?
- Structured reference syntax rules
- Reference operators
- Special item specifiers
- Qualifying structured references in calculated columns
- Examples of using structured references
- Strategies for working with structured references
- Need more help?
When you create an Excel table, Excel assigns a name to the table, and to each column header in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does:
That combination of table and column names is called a structured reference. The names in structured references adjust whenever you add or remove data from the table.
Structured references also appear when you create a formula outside of an Excel table that references table data. The references can make it easier to locate tables in a large workbook.
To include structured references in your formula, click the table cells you want to reference instead of typing their cell reference in the formula. Let’s use the following example data to enter a formula that automatically uses structured references to calculate the amount of a sales commission.
1.Copy the sample data in the table above, including the column headings, and paste it into cell A1 of a new Excel worksheet.
2.To create the table, select any cell within the data range, and press Ctrl+T.
If you enter explicit cell references in a calculated column, it can be harder to see what the formula is calculating.
1.In your sample worksheet, click cell E2
2.In the formula bar, enter =C2*D2 and press Enter.
Notice that while Excel copies your formula down the column, it doesn't use structured references. If, for example, you add a column between the existing columns C and D, you'd have to revise your formula.
When you create an Excel table, Excel creates a default table name (Table1, Table2, and so on), but you can change the table name to make it more meaningful.
1.Select any cell in the table to show the Table Tools > Design tab on the ribbon.
2.Type the name you want in the Table Name box, and press Enter.
In our example data, we used the name DeptSales.
Use the following rules for table names:
•Use valid characters Always start a name with a letter, an underscore character (_), or a backslash (\). Use letters, numbers, periods, and underscore characters for the rest of the name. You can’t use "C", "c", "R", or "r" for the name, because they’re already designated as a shortcut for selecting the column or row for the active cell when you enter them in the Name or Go To box.
You can also enter or change structured references manually in the formula but to do that, it will help to understand structured reference syntax. Let’s go over the following formula example:
=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])
This formula has the following structured reference components:
•Table name: DeptSales is a custom table name. It references the table data, without any header or total rows. You can use a default table name, such as Table1, or change it to use a custom name.
•Column specifier: [Sales Amount] and [Commission Amount] are column specifiers that use the names of the columns they represent. They reference the column data, without any column header or total row. Always enclose specifiers in brackets as shown.
•Item specifier: [#Totals] and [#Data] are special item specifiers that refer to specific portions of the table, such as the total row.
For more flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.
To refer to specific portions of a table, such as just the totals row, you can use any of the following special item specifiers in your structured references.
When you create a calculated column, you often 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 Commission Amount, that calculates the amount of commission in dollars, you can use the following formulas:
The general rule to follow is this: If you’re 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.
Here are some ways to use structured references.
Consider the following when you work with structured references.
•Use 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.
•Decide whether to generate structured references for tables in semi-selections 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 File > Options > Formulas > Working with formulas dialog.
•Use workbooks with external links to Excel tables in other workbooks If a workbook contains an external link to an Excel table in another workbook, that linked source workbook must be open in Excel to avoid #REF! errors in the destination workbook that contains the links. If you open the destination workbook first and #REF! errors appear, they will be resolved if you then open the source workbook. If you open the source workbook first, you should see no error codes.
•Convert 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 absolute A1 style references. When you convert a range to a table, Excel doesn’t automatically change any cell references of this range to their equivalent structured references.
•Turn off column headers You can toggle table column headers on and off from the table Design tab > Header Row. If you turn off table column headers, structured references that use column names aren’t affected, and you can still use them in formulas. Structured references that refer directly to the table headers (e.g. =DeptSales[[#Headers],[%Commission]]) will result in #REF.
You can always ask an expert in the Excel Tech Community or get support in Communities.
Go to Table Tools > Design > Properties > Table Name. On a Mac, go to the Table tab > Table Name. Highlight the table name and enter a new name. Tips: All of your tables will be shown in the Address bar, which appears to the left of the Formula bar.
Aug 28, 2024 · This guide entails a whole list of stellar methods to change the column names in your Excel sheet each of which is explained step-by-step. Naming columns in Microsoft Excel can help you with referencing them later around your sheet or workbook.
Feb 26, 2024 · Quick Steps. Click a row number to highlight the entire row. Right-click the first cell and click Insert. Double-click the new cell and enter a name. Press Tab to enter a name for the next cell. Method 1. Adding Column Headers. Download Article. 1. Open a workbook in Microsoft Excel. You can use an existing project or create a new spreadsheet.
Some Things to Keep in Mind When Renaming Excel Tables. Method 1: Rename Excel Table Using the Table Name Option. One straightforward method to rename a Table is by using the Table Name option available in the design tab.
