Hide columns and tables in Power Pivot

When using a data model for a Power View or PivotTable report, you can hide entire tables or individual columns so that only relevant items appear in the field list. This is particularly useful when you have redundant tables or columns. For example, if you add "Product Category Name" to the Products table, you might not want the Product Category table to show up in the fields list.

You can also hide calculated fields. For example, if you have advanced calculations that include other calculations that you'd rather not expose, you can hide the intermediary calculations from the fields list.

1. Start Power Pivot in Microsoft Excel add-in and open a Power Pivot window.

2. To hide an entire table, right-click the tab that contains the table and choose Hide from Client Tools.

3. To hide individual columns, open the table for which you are hiding a column, right-click the column, and click Hide from Client Tools. You can hide multiple columns at a time by holding down the Ctrl key or the Shift key.

4. To hide a calculated column, make sure the calculation area is visible, right-click the field, and click Hide from Client Tools.

The column is grayed out to reflect that it is hidden to reporting clients that consume the model. Hidden columns are grayed out in the model to indicate their state, but remain visible in the Data View so that you can continue to work with them.

Hiding a column does not remove it from the model. A hidden column can still be used as a sort column in a table. It can also be referenced in formulas that define calculated columns or calculated fields. The ability to hide columns is not intended to be used for data security, only to simplify and shorten the list of columns visible to reports that use them.

Hiding tables and columns that are already in use

If you hide tables or columns that are actively used in a report layout (for example, hiding all of the columns in a Date table after using Calendar Year as a slicer in Excel), the report layout is preserved. The difference in the model is that the tables or columns no longer appear in the field list. You cannot use the now-hidden columns when making further changes to the report layout. Using the previous example as an illustration, if you delete the Calendar Year slicer, you won't be able to recreate that slicer because Calendar Year is no longer visible in the field list.

Working with hidden tables and columns

Visibility in Data View varies depending on whether Show Hidden is enabled. This option, which appears in the View area of the Home tab, is on by default. When enabled, columns and tables that you've hidden remain visible in Data view.

The Power Pivot window includes a Find button that lets you search for columns or expressions by name. If you use Find to locate specific columns, be aware that it will not find any objects that you've previously hidden unless Show Hidden is enabled.

Notes: Video copyrights:

• Olympics Dataset © Guardian News & Media Ltd.

• Flag images courtesy of CIA Factbook (cia.gov)

• Population data courtesy of UNData (data.un.org ) on Microsoft Azure Marketplace.

• Olympic Sport Pictograms by Thadius856 and Parutakupiu, distributed according to the license on Wikimedia Commons (creativecommons.org/licenses/by-sa/3.0/)

Top of Page