Structured references

Formulas with structured references do not operate with cells or range references, but with the names of the table and columns of the table, as well as the names of table areas.

The examples of regular and structured references:

Structured references can be used both in the table and outside of it.

If a formula with a structured reference is located directly in the table, you do not need to specify the name of the table in this reference. If the table name is specified in the reference for any reason, it is automatically deleted when you enter the formula. For example, the formula =SUM(Table1[Sales]) will be automatically replaced with =SUM([Sales]).

If a formula with a structured reference is located outside the table, the name of the table must be specified.

To find out the name of a table, select any cell in it. The table name will appear on the Toolbar, under the Table section.

To copy the table name, select it by clicking it.

Structured references can refer to the following data in a table:

The examples of the structured references are stated below.

Formulas with structured references are entered as usual.

To specify the name of a smart table in a formula, proceed with the following:

1.Start typing the table name. A drop-down list will show the names of all smart tables that are contained in the document.

2.Select the name of the desired table using the mouse or the keyboard in one of the following ways:

•Click the table name with the left mouse button.

•Select the table name using the ↓ and ↑ keys and press Enter.

The table name will be entered into the formula.

To enter the column name or a reference to the table area (for example, #Totals or @), do the following:

1.Enter the [ (left square bracket) symbol. The drop-down list will display the column names and the references to the table areas.

2.Select the desired element from the list using the mouse or the keyboard:

•Left-click the element name with the mouse.

•Select the element line using the ↓ and ↑ keys and press Enter.

The selected element will be added to the formula.

If the reference to the table data area (for example, [#Headers]) is entered correctly into a formula, it is highlighted in color, and the table highlights the range to which it refers (in this example, the header line).

"Smart" tables are displayed in MyOffice Spreadsheet the same way they were saved in the Excel. For example, if the result line was disabled in Excel, MyOffice Spreadsheet displays the table without the result line.

When working with a "smart" table, you can perform the following operations:

•Edit column names. Column names are automatically updated in the structured references of the table.

•Insert rows and columns between the existing rows and columns (but not at the end of the table).

•Copy "smart" tables within one document or from document to document, provided that both documents are open in MyOffice Spreadsheet. The copied table is automatically assigned a unique name. In all structured references, the name of the original table is automatically replaced with the name of the copied table.

•Select a table style from those available in the collection, enable or disable table areas (header and summary rows), and reset the style.

If you want to add a column where all cells contain the same formula to a table, create a calculated column. You can use any column in the table that does not contain data to create a calculated column.

To make a column calculated, type the desired formula in any cell of that column and press Enter.

All cells in the column will automatically be filled with the formula you entered.

The following rules apply to the calculated columns:

•If you edit a formula in any cell of a calculated column, the same formula editing is automatically performed in the remaining cells of that column.

•If the data in a column is partially deleted or regular data is entered instead of a formula, the column ceases to be calculated (formula changes cease to apply to the whole column).

•If the column consistency is restored manually (all cells contain the same formula again), the column becomes calculated again.

Smart tables are auto-expanded when you enter data in adjacent cells located in the column to the right of the smart table. If there is no result line in the table, auto-expansion is also performed when you enter data in the adjacent cells located in the row following the last table row.

Data can be entered:

•Manually.

•From the clipboard. The automatic expansion is performed when data is inserted to the left/bottom of the table or when data is inserted simultaneously in both the table and adjacent cells.

•By means of automatic filling of the cells where the reference cell is one or multiple cells of the smart table.

The automatic expansion of smart tables has the following limitations:

•The automatic expansion area should not contain any data.

•The data inserted does not overlap with another "smart" table.

When working with several smart tables on the same sheet, you can distinguish them by applying different styles and formatting. To do this, do the following:

1.Select a cell of the smart table. The  Smart Table section will open in the Sidebar.

2.In the Table Style field, select one of the most recently used styles or click to view all available styles in the collection. The collection contains 60 styles and 1 additional style which is intended to reset the formatting.

3.In the Display field, enable or disable the display of the header and total.

4.In the Alternate Colors field, enable or disable alternating dark and light shades for column and row colors.

You can reset a style applied to a table in several ways:

•By selecting the style intended for formatting reset. This style is labeled with the word "None" in the Table Style field.

•From the style collection window by clicking Clear at the bottom of this window.