Forum Discussion

RandomPanda's avatar
RandomPanda
Brass Contributor
Oct 21, 2021

Pivot Table Default Filter

Found an old thread with https://support.office.com/en-us/article/Set-PivotTable-default-layout-options-efd8569c-f07a-43c1-9db2-4f2912a0f94e

showing setting defaults for Pivot Tables, but after a short time reviewing it looks to be more layout defaults rather than filter defaults. 

So, this is probably not an option - logically, absent data can't be filtered. But, is there a way to set filters to a specific value that I know will be in the data once I paste it into the table?

For example - a labor file is blank until I download and manipulate the data (clearing out blanks and adjusting any "N/A" fields). Once in the correct format and free of invalid data, I copy/paste into the pivot tables data set. I know that "Regular Worked Hours" will be in the data set and I have several pivot tables for different departments. Is there a way to set the default for each of these tables to only include Regular Worked Hours so all I need to do is refresh once the data is copied over to the table?

office 365

3 Replies

    • RandomPanda's avatar
      RandomPanda
      Brass Contributor
      Oct 21, 2021

      English? Example? Consider me new to Pivot Tables. I'm editing tables that were in place before I started. 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 21, 2021

        RandomPanda 

        Afraid that won't work then. If you already use cached PivotTables you can't convert them, need to build new PivotTables adding data to data model (check box at bottom left when you create PivotTable).  DAX basics are described here QuickStart: Learn DAX Basics in 30 Minutes (microsoft.com) but that's definitely not what you may copy/paste with small adjustments.

         

        As for the cached PivotTable I don't know how to predefine filters before filtered data is added, sorry.

Resources