About PivotTable lists that were published from Microsoft Excel
Microsoft Excel PivotTable reports have some capabilities that are not retained when a report is saved or published as a PivotTable list. As a result, you might want to use another design program to make additional changes after publishing your PivotTable list.
The following table summarizes the Excel PivotTable report features that are affected when you publish it as a PivotTable list.
| Feature | Result on a Web page |
|---|---|
| Calculated fields | Formulas are not retained. Changed to total fields that use the default summary function for the field (Sum, Count, Min, or Max). |
| Calculated items | Not retained. |
| Summary functions | Only data fields summarized with Sum, Count, Min, or Max are retained. Data fields that use other summary functions are lost. |
| Custom calculations | Not retained. The data field using the custom calculation is lost. |
| Character and cell formatting | Not retained. Changes you make to fonts, text size, text color, and cell backgrounds are lost. |
| Custom subtotals | Not retained. If you change the summary function in Excel, the subtotal or grand total reverts to the summary function of the total field in the PivotTable list on the Web. |
| Number formats | Partially retained. Number formats that you apply to PivotTable fields in Excel are retained, but number formats that you apply to individual cells are not retained.
If you use a custom number format or a number format that's not supported by PivotTable lists on the Web, the numbers appear with the same number format as the Excel PivotTable report. If you subsequently change the number format for the field in the PivotTable list, you cannot return the field to the custom number format. |
| Asterisks that mark totals | Even if you turned off the asterisks marking totals in Excel, the asterisks are displayed in the PivotTable list on the Web. |
| Print settings | Not retained. The Excel settings to repeat PivotTable row and column labels on each page, repeat items labels after page breaks, and set page breaks between sections are lost. |
| Grouped items in fields | Partially retained. Grouped items in date fields are retained, but any selected items and numeric items that are grouped in Excel are no longer grouped in the PivotTable list on the Web, and group fields from Excel are not retained. |
| Custom sort order | Not retained. Instead, the data is in the order in which it is retrieved from the source database. |
| Indented formats | Not retained. Fields are in the same positions, but the character and cell formatting are not retained. Blank rows between item groups are lost. |
| Subtotals displayed at the top of item groups | Not retained. Subtotals appear at the bottom of each group. |
| Top/bottom items AutoShow | Not retained. All items are displayed. |
| Customized error values and empty cell values | Not retained. Errors and empty cells are displayed as blank cells. |
| Page fields in rows or columns | Not retained. Page fields become filter fields, which are always displayed across the top. |
| Password settings | Data in password-protected worksheets and workbooks cannot be saved or published as a Web page. To publish data that's protected, you must use a blank (or no) password. If you save your database password with the query in Excel, users must enter the password when they open the Web page in the browser. |
| Background refresh, retrieving data for each item in a page field individually | Not retained. The PivotTable list on the Web is always refreshed in the foreground, and all data for filter fields is retrieved in one operation. |
| Changes to Excel source data | Not available. To redefine the range of Excel source data or change the query to select different external data, you must make the change in Excel and republish. |
| Offline cube file data sources | You cannot reconnect to the original server database from the published PivotTable list, or make changes to the content of the offline cube file from the PivotTable list. If you need to perform either of these operations, you can do so in Excel and then republish to the PivotTable list. |