PowerPivot Window: Design Tab
This section provides Help on the Design tab in the PowerPivot window, including the dialog boxes that are available from this tab. For an overview of the user interface and instructions on how to open the PowerPivot window, see Take a Tour of the PowerPivot UI. The Design tab is where you can change table properties, create and manage relationships, and modify connections to existing data sources. You can also add columns, and change when column values are calculated.
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this section. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
Options on the Design Tab
The options on the Design tab are arranged in groups.
The Columns group enables you to create new columns and to change the way that columns are displayed. The View group on the Home tab has additional options. For more information, see PowerPivot Window: Home Tab.
Hide and Unhide. Click to display the Hide and Unhide Columns Dialog Box, which lets you control the columns that are hidden, and specify whether the columns are hidden in the PowerPivot window only or are also hidden in PivotTables associated with the table. For more information, see Hide or Freeze Columns.
Add. Click to add a new column at the rightmost side of the current table. For more information, see Create a Calculated Column.
Delete. Click to delete the currently selected column or columns. You cannot multi-select columns by using Ctrl+Click, but you can multi-select columns by clicking and dragging.
Click the Calculation Options button to control the way that the workbook performs recalculation of formulas.
Calculate Now. When workbook is set to manual calculation mode, click to perform a recalculation of the entire workbook.
Automatic Calculation Mode. The default enables automatic recalculation of formulas. Any changes to data in the workbook that would cause the result of any formula to change will trigger recalculation of the entire column that contains a formula.
Manual Calculation Mode. Turns off automatic recalculation. Click Calculate Now to recalculate formulas. We recommend that you recalculate and validate the workbook before saving.
Note that there are two similar but distinct operations within PowerPivot for Excel:
Data refresh means obtaining up-to-date data from external data sources. For more information, see Refreshing or Changing Imported Data.
Recalculation means updating columns and tables in your workbook that contain formulas. For more information, see Recalculating Formulas.
Click the Existing Connections button to display a list of existing connections used by this workbook. The Select an Existing Connection dialog box enables you to edit the connection properties, refresh data, or get more data. For more information, see Edit the Properties of an Existing Data Source.
The Relationships group enables you to create and manage relationships between tables in the PowerPivot workbook.
Create Relationship. Click to open the Create Relationship Dialog Box, which enables you to create a relationship between tables of data. A relationship establishes how the data in the two tables should be correlated.
Manage Relationships. Click to open the Manage Relationships Dialog Box, which enables you to view, edit, or delete existing relationships.
For more information about relationships, see Creating Relationships Between Tables.
Click the Table Properties button to open the Edit Table Properties Dialog Box, which enables you to view and modify the properties of tables. This applies only to tables that were imported, rather than pasted directly into the PowerPivot window.
Click Undo or Redo as appropriate. If an action cannot be undone or redone, the option will be unavailable.
The following topics provide Help for the dialog boxes that are available from the Design tab.