•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Why does my second pivot table have more Total & Filters options than the first?
Our take
Have you ever noticed that different pivot tables in your spreadsheets can offer varying options under Totals & Filters? This discrepancy often stems from the underlying data sources and configurations you use. In your case, the second pivot table includes additional features that enhance data analysis, such as evaluating calculated members from OLAP servers. Understanding these differences can empower you to leverage all available options.
On one spreadsheet, I have a pivot table (with data from PQ) and in Pivot Table Options, under Totals & Filters, I can see these options:
Grand Totals
-Show grand totals for rows
-Show grand totals for columns
Filters
-Subtotal filtered page items
-Allow multiple filters per field
Sorting
-Use custom lists while sorting
In my other spreadsheet, I have another pivot table (with different, but similar data from PQ) and under the same tab I see this:
Grand Totals
-Show grand totals for rows
-Show grand totals for columns
Filters
-Include filtered items in totals
- Mark totals with \*
-Include filtered items in set totals
-Subtotal filtered page items
-Allow multiple filters per field
-Evaluate calculated members from OLAP servers in filters
Sorting
-Use custom lists while sorting
Why do I have more options available (bolded) in the second pivot table options, and is there a way to make these available in the first?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]
- pivot tables - exist something like filter by column?Good morning community. I don’t usually use pivot tables much, because I tend to prefer building my reports with filters and formulas, but my boss loves them. The problem is that many times he doesn’t even know exactly what he really wants. Here’s the issue. I have a table where data is divided into categories, for example: Column A – Primary Categories Column B – Secondary Categories Column C – Tertiary Categories From Column D onward… prices, with each column representing a different month. My boss wants a pivot table where he can filter by categories (I already have that done), but also be able to filter somehow by just one month, or several, or all of them, since he then wants to use that in a chart (this part is also already done). AND HE ONLY WANTS TO SEE THE CHART AND CONTROL THE DATA FROM THERE—in other words, he doesn’t want to have to go into the pivot table to edit it by adding or removing columns. So the question is… how can I (if it’s even possible directly from the pivot table) quickly change the columns I’m displaying with a button? That is, without having to manually edit the pivot table. So far, the solution I found was to create a “column” using formulas where I bring in the data with “HLOOKUP” and change the filter with a dropdown list linked to a macro that refreshes the pivot table every time its value changes. But I’d like to know if pivot tables have a more direct way to solve this. Thank you very much. submitted by /u/Potential-Aside-1712 [link] [comments]
- Pivot Table - can't see PivotTable Analyse, and can't see Pivot Table options when right clickingLarge Pivot table output: 25 columns x 250 rows. Using 20000 rows of input data from another sheet. Formula: GETPIVOTDATA still works Edit: Other Pivots on different sheets in the workbook still work. submitted by /u/SkatesUp [link] [comments]
- Sort a Pivot Table slicer by another columnI’ve got a relatively simple question compared to some of the other content here: For a project I’m working on I’ve had to convert from using Power Pivot and a Data Model to normal Tables/Pivot Tables without Power Pivot to accommodate some Mac users in my org. When rebuilding some of the pivot tables and slicers, one of my most used ones is a Fiscal Quarter & Year slicer/column which contains data formatted “FQ1’26”, “FQ2’26”, etc. The result of this is that the slicer orders the entries “FQ1’26”, “FQ1’27”, etc. I have what we can call an index column that is “20261”, “20262”, etc that was used in Power Pivot to sort the Fiscal Quarter & Year column - however I’ve struggled to find a way to replicate the functionality outside of Power Pivot. Is there a way in a normal Table/Slicer to sort the Slicer column by another data column? submitted by /u/gnartung [link] [comments]