Pivot Table Issues

How to Show and Filter Columns in an Excel Cube PivotTable

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to display and use column-filtering options in a PivotTable that is connected to a cube data source.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Working with advanced Excel PivotTables connected to an OLAP cube data source where standard filtering tools behave unpredictably.
Observed behavior: Column-filtering controls appear inconsistently depending on the PivotTable type, cube connection properties, field settings, and the selected report layout.

Before you start

Ensure your Excel workbook is saved and note down your cube connection credentials before modifying PivotTable field settings or report layouts.

Solution 1Recommended

Review PivotTable Field Settings and Layout Options

Adjusting the report layout and verifying the field settings can restore missing filter controls in a cube-connected PivotTable.

In Excel, PivotTables connected to an OLAP cube handle data differently than standard data ranges. If column filters are missing, it is often due to the report layout being set to Compact Form or specific field settings restricting the display of filter drop-downs.

Select the PivotTable

Click anywhere inside your cube-connected PivotTable to reveal the PivotTable Analyze and Design tabs on the Excel ribbon.

Change the Report Layout

Navigate to the Design tab, click on 'Report Layout', and select 'Show in Tabular Form' or 'Show in Outline Form'. This forces Excel to display individual column headers with their respective filter drop-downs.

Check Field Settings

Right-click the problematic column header and select 'Field Settings'. Navigate to the Layout & Print tab and ensure 'Show item labels in tabular form' is checked.

Test in a New Workbook

If the issue persists, copy a sample of your data or recreate the cube connection in a completely new, blank workbook to determine if the issue is file-specific corruption.

Data Source Limitations: Certain OLAP cube configurations defined on the server side may intentionally restrict filtering on specific hierarchies. If layout changes do not work, consult your database administrator.

Free Microsoft Office alternative

Try WPS Office for Standard PivotTable Reporting

If you are struggling with the complexities and inconsistent behaviors of OLAP cube connections in Microsoft Excel, consider using WPS Office for your standard data analysis. WPS Spreadsheet provides powerful, highly intuitive PivotTable features without the heavy complexity, making everyday data filtering and summarizing a breeze.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the raw data.
2. Insert a PivotTable: Select your data range, go to the Insert tab on the ribbon, and click 'PivotTable'.
3. Filter with Ease: Drag fields into the Columns and Rows areas. Click the drop-down arrows directly on the headers to instantly filter your data without worrying about complex cube settings.

Free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx) file formatsIntuitive PivotTable creation and seamless column filtering toolsCross-platform support for Windows, Mac, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why are filter drop-downs missing in my Excel PivotTable?

Filter drop-downs typically disappear if the PivotTable report layout is set to 'Compact Form' without headers, or if the specific OLAP cube connection restricts certain filtering capabilities. Switching your layout to 'Tabular Form' often resolves this.

Can I filter items directly in an OLAP cube PivotTable?

Yes, but filtering capabilities may be limited by the cube's predefined hierarchy and design. You typically filter by selecting the field drop-down directly or by inserting Slicers connected to the cube.

How do I share my cube-connected PivotTable for troubleshooting?

Create a sanitized copy of your workbook by removing sensitive data. Note that if the cube is hosted on a local server or requires specific permissions, other users will not be able to refresh or fully interact with the filters unless they also have network access to the data source.

Excel & Data Problems