PowerPivot Window: Home Tab

Article
04/16/2014

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

This section provides Help on the Home 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 Home tab enables you to add new data, copy and paste data from Excel and other applications, apply formatting, and sort and filter data.

Gemini_HomeTabWithCallouts

Note

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 Home Tab

The options on the Home tab are arranged in groups.

Clipboard

This group enables you to copy and paste data into the current PowerPivot workbook.

Paste. Paste data from the Clipboard into a new table in your PowerPivot window.
Paste Append. Add data from the Clipboard at the end of an existing table in the PowerPivot window.
Paste Replace. Use the data in the Clipboard to replace the data in an existing table in the PowerPivot workbook.
Copy. Copy selected data from the workbook to the Clipboard.

You can copy table-like data from external applications and paste it into a PowerPivot workbook. The data that you paste from the Clipboard must be in HTML format, such as data that is copied from Excel or Word. For more information, see Copy and Paste Data to PowerPivot.

Get External Data

This group enables you to connect to external data sources and import data from those sources. For information about supported data sources, see Data Sources Supported in PowerPivot Workbooks.

From Database. Connect to SQL Server, Microsoft Access, and SQL Server Analysis Services cubes, as well as PowerPivot workbooks that have been published to SharePoint. For more information, see Import Data from a Database and Import Data from Analysis Services or PowerPivot. Connect to other relational sources by clicking From Other Sources.
From Report. Connect to a data feed that a Reporting Services report makes available. For more information, see Import Data from a Reporting Services Report.
From Data Feeds. Connect to data feeds that are generated from online data sources. For more information, see Import Data from a Data Feed.
From Text. Get data from text files, such as comma-delimited and tab-delimited files.
From Other Sources. Get data from other sources, such as Excel workbooks, and the following databases: SQL Azure, SQL Server Parallel Data Warehouse, DB2, Informix, Oracle, Sybase, and Teradata.
Refresh. Refresh one or more data sources used in the current workbook.

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.

PivotTable

The PivotTable button enables you to insert into the Excel workbook one or more PivotTables that are based on data in the PowerPivot window. For more information, see Create a PivotTable or PivotChart Report.

Formatting

This group enables you to format data in columns and work with data types.

Data type. Displays the current data type of the selected column. Click the dropdown arrow to view a list of data types and change the data type. If the data type you choose is incompatible with the column contents, an error is displayed.
Format. Displays the data type of the currently selected column. If other formatting options are available, click the dropdown arrow to select a new format.
Apply currency format. Click to display the Currency Format dialog box and specify the currency format and currency symbol.
Apply percentage format. Click to display all numbers in the currently selected column as percentages.
Thousands separator. Click to display a thousands separator on all numbers in the currently selected column. To undo, press Ctrl-Z.
Increase decimal and Decrease decimal. Click to increase or decrease the number of decimal places that are displayed for a number. These options do not change the value or increase its precision; they only affect the display format.

For more information about data types and formatting, see Set the Data Type of a Column.

Sort and Filter

The group enables you to choose the values that are displayed in a table by applying filters and by sorting.

Sort Smallest to Largest and Sort A to Z. Click to sort from smallest to largest. If you are sorting numbers, the column will sort from low numbers to high numbers. If you are sorting text, then the column will sort from A to Z.
Sort Largest to Smallest and Sort Z to A. Click to sort from largest to smallest. If you are sorting numbers, the column will sort from high numbers to low numbers. If you are sorting text, the column will sort from Z to A.
Clear Sort. Click to cancel sort and display the column in its natural order, meaning the order that the data was imported.
Clear All Filters. Click to remove filters and view all rows in the table. This option is available only when filters have been applied to at least one column.

Note

When you click this button, it removes all filters. To selectively clear filters, right-click the column header of the column that has the filter, select Filter, and then select Clear Filter From <column name>.

For more information, see Filter and Sort Data in PowerPivot.

Measures

This group enables you to easily create a few basic measures and to create KPIs based off of measures.

AutoSum. Select a column, and then click AutoSum or one of the other functions from the drop-down menu. The measure appears below the column on the Calculation Area.
Create KPI. Click a measure in the Calculation Area, and then click Create KPI on the Home tab. The Key Performance Indicator (KPI) dialog box appears.

View

This group enables you to change how you view your tables and columns.

Data View. This is the default column-based view in the PowerPivot window.
Diagram View. This view enables you to see your tables in a visual diagram. This view is ideal for creating and managing relationships and hierarchies.
Show Hidden. Click this view to show any objects that are hidden from client tools.
Calculation Area. Click this button to show or hide the Calculation Area, which is displayed beneath the columns in Data View only.