The moment you filter a dataset in Excel, you expect the Pivot Table that follows to respect that view. It doesn't. That disconnect isn't just annoying; it's a fundamental misunderstanding of how these two features interact. The user who posted this question isn't missing a button or a setting. They've hit a genuine design quirk, and the solution requires stepping back from the spreadsheet itself.
Here's what's actually happening: a Pivot Table doesn't read your filtered worksheet. It reads the underlying data source, and unless you've structured that source to exclude hidden rows, the Pivot Table will include everything. The filter you applied to the sheet is a temporary lens, not a permanent change to the data. So when you drag fields into the Pivot Table, you're pulling from the full dataset, hidden rows and all. That's why your totals don't match your filtered view. It feels like a bug, but it's really a feature of how Pivot Tables are designed to operate independently.
The practical fix is to change your data source, not your filter. You can convert your filtered range into an Excel Table, then create the Pivot Table from that Table. Or, more reliably, use a dynamic named range that expands and contracts with your filtered data. Another approach is to use Power Query to load the filtered data as a separate query, then build your Pivot Table from that. Each of these methods forces the Pivot Table to see only what you want it to see. It's an extra step, but it aligns the tool with your intent rather than fighting against it.
What this means for you is that the problem isn't your skill level. It's that you're expecting a level of intelligence from the software that hasn't been built into that workflow yet. The good news is that the workaround is straightforward once you know the logic. You don't need to abandon your filtered view or recreate your data manually. You just need to give the Pivot Table a source that matches the story you're trying to tell. That's the difference between fighting your tools and using them well. Next time you hit this wall, remember: the filter is for your eyes, but the Pivot Table needs a source that's already shaped for the answer you're after.