Click OK to close the PivotTable Options dialog box.This change will hide the filter buttons, and the Row Labels and Column Labels captions.Next, remove the check mark from Display Field Captions and Filter Drop Downs.This change will hide the Expand/Collapse buttons to the left of the outer Row Labels and Column Labels.In the Display section, remove the check mark from Show Expand/Collapse Buttons. It took me six years, but I finally convinced the Excel team that a lot of people prefer Tabular layout for pivot tables to the Compact layout that became.It returns the correct answers and behaves as expected but I am surprised that it is sending MDX queries to the server. The SSAS server is 2019 and Excel is 2016. I have a relatively small 57MB tabular model that I would like to query with Excel Pivot table connections.

Right-click a cell in the pivot table and, in the pop up menu, click PivotTable Options. Querying Tabular Models with Excel Pivot Tables.You can change the pivot table options to hide some of the buttons and captions in the pivot table. There are filter buttons on the Column heading, and on the Row Labels heading.Īt the left of the date, there is an expand/collapse button showing.

If you’re sharing an Excel pivot table with colleagues who aren’t too skilled in Excel, you might want to hide some of the pivot table buttons and labels before you send it.įor example, in the pivot table shown below, the sales are summarized by city and product.

Tabular pivot table