PowerPivot Window: Design Tab
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
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.
Add. Click to add a new column at the rightmost side of the current table. For more information, see Calculated Columns.
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.
Freeze and Unfreeze. Click to keep the current column visible while you scroll to another area of the worksheet. For more information, see Freeze Columns.
Width. Click to display the Column Width dialog box, which lets you enter the width of a column you selected.
Click the Insert Function button to open the Insert Function dialog box and add a new column with a specific DAX function.
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 Different Ways to Update Data in PowerPivot.
Recalculation means updating columns and tables in your workbook that contain formulas. For more information, see Recalculate 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 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.
Mark as Date Table
Click the Mark as Date Table button to open the Mark as Date Table Dialog Box, used to enable dedicated date filtering in reports.
Click Undo or Redo as appropriate. If an action cannot be undone or redone, the option will not be available.
The following topics provide Help for the dialog boxes that are available from the Design tab.