Excel Pivot Help!

[joropug]

I have a personal spreadsheet that features a pivot.

In 2021, I had no issues with it but when I add 2022 dates it causes a problem whereby whenever I enter a new value into my data, it is not selected in the pivot - Previously, everything but blanks was selected which is the desired behaviour and updated every time a new date was added.



My data: Each row is an item, if I put a date next to it it means I got that item. If I put no date next to it, it means I do not have that item:




Am I missing something obvious? All I need is for the pivot to select all of the dates, including new ones, but ignoring blanks.

[mmm-five]

Are you sure the previous behaviour was as you remember, as none of the ones I have will add new items to the filter list...I have to add them manually.

This is on both W10 and MacOS, and on multiple versions of Excel...although I can now only test this on my currently installed Office/Microsoft 365 on both OSes.

I say this, because I've been battling with the same issue for as far back as I can remember...where someone changes a field to a mis-spelled version of what I'm looking for, and it then causes a mis-count in the pivot, as it obviously hasn't been added in manually...because I got fed up with checking it for errors every time someone updated their section of data.

Edited by mmm-five on Monday 24th January 19:07

[joropug]

mmm-five said:

Are you sure the previous behaviour was as you remember, as none of the ones I have will add new items to the filter list...I have to add them manually.

This is on both W10 and MacOS, and on multiple versions of Excel...although I can now only test this on my currently installed Office/Microsoft 365 on both OSes.

I say this, because I've been battling with the same issue for as far back as I can remember...where someone changes a field to a mis-spelled version of what I'm looking for, and it then causes a mis-count in the pivot, as it obviously hasn't been added in manually...because I got fed up with checking it for errors every time someone updated their section of data.

Edited by mmm-five on Monday 24th January 19:07

Argh! I know that it worked but maybe I found another way around it completely inadvertently before, I just remembered I’ve saved loads of previous versions so will check my workings and report back.

All I did before was hit my refresh button and the totals would update fine.

[joropug]

Had a look on my phone but doesn’t display the same, looking at previous iterations I had it broken down by month but ultimately the same filter.



You’ll note that >06/12/2021 isn’t selected but the dates in scope are listed individually and selected.

I’m used to using pivots on static data to analyse but less so on tables I’m adding data to

[Some Gump]

Data in a table and pivot not seeing the lowest lines?

Did you put the pivot on the table rather than all the columns? Excel might not be looking all the way down the rows?

[joropug]

Some Gump said:

Data in a table and pivot not seeing the lowest lines?

Did you put the pivot on the table rather than all the columns? Excel might not be looking all the way down the rows?

Hmm I think it’s seeing the data as it’s available in the filter just isn’t selected by default.

I have 1000 rows of data but the pivot is looking at 2000 rows I.E a ton of blank rows too for my new data entry.

I’m wondering if I can record the macro button I have set up to navigate to the pivot select all data, untick blanks , then refresh .

[Planet Claire]

Why don't you convert your table of data into a table and when you add extra rows the pivot table will automatically pick it up, so you won't end up with rows of blank cells.

[Piersman2]

I usually cure this by manually reselecting the data in the pivot table via the menus.

[nickd01]

Don't you just want to 'clear' the filter?
It should filter out blanks automatically?

[RUSTILLDOWN]

[mmm-five]

I don't think this is about making the data refresh - which is simple - it's about getting the filter to automatically select any new dates when they appear after new data is added (either via a table or expanded data reference).

[boiler2003]

The values in a filter get updated when the Pivot table is refreshed, but additional values are not selected because you manually select multiple values in the filter.

If you include the Date field as a row in the pivot table, you can put a formula as a filter (click the small triangle next to the row header), and select all dates greater than say 1/1/2021, which will automatically include those rows when the pivot table is refreshed.

[RUSTILLDOWN]

[mmm-five]

RUSTILLDOWN said:

Found it on the Windows version (and verified it works as intended - thanks for the tip), but doesn't seem to exist on the Mac version - looks like another feature that MS have 'forgotten' about for Mac users.

Just have to ensure you're on the right field when you select it, as it's on a per-field basis.

Edited by mmm-five on Tuesday 25th January 12:32

[boiler2003]

Thanks Rustilldown, you learn something new every day and it does work with fields as both rows and filters.

[joropug]

Thank you I’ll try this when I get home !! Have a windows laptop with OneDrive so I can access the doc on there , might apply to the Mac version then too if I don’t have the selection.

Strangely enough I switched to the mac in July and I wonder if it was a hangover from the windows version of the document - the pivot was the same one as I used on windows but, recently I have deleted it and started again due to unrelated issues so have perhaps lost that selection.

[joropug]

Yep missing on my iMac - Ridiculous - Hopefully the reason it worked before was because I selected it on Windows either by default or by chance.

I think the reason it messed up in 2022 is my original pivot had a monthly breakdown whereas my new one just has a running total.

[paulrockliffe]

Planet Claire said:

Why don't you convert your table of data into a table and when you add extra rows the pivot table will automatically pick it up, so you won't end up with rows of blank cells.

This isn't the issue. The problem is that he's used the filter on the Pivot itself to remove blanks. Excel doesn't do this as a filter, "All values except blanks". The filter it applies is, "= 2021, 2020, 2019" etc. Then when you add new data in 2022 that is not covered by the filter, so is also filtered out.

OP - this behaviour is one of my top 10 complaints in Excel. I once had to recreate someone crap spreadsheet properly and had all sorts of issues checking my proper spreadsheet was returning the data correctly because this issue had screwed up the reference spreadsheet.

The solution is to do the filter somewhere more appropriate. Load your data into Power Query, then filter it in there. I *think* the same filtering method in there will give you an "All except blanks" filter, but if it doesn't, you have the whole M language at your disposal to define the filter correctly.

Load the resulting Query to the Data Model and redraw your Pivot Table.

https://www.youtube.com/watch?v=4M5VinpEBmg

To access the table in the existing Workbook the Source for the Query is Excel.CurrentWorkbnook()

https://exceloffthegrid.com/power-query-import-dat...

[joropug]

paulrockliffe said:

This isn't the issue. The problem is that he's used the filter on the Pivot itself to remove blanks. Excel doesn't do this as a filter, "All values except blanks". The filter it applies is, "= 2021, 2020, 2019" etc. Then when you add new data in 2022 that is not covered by the filter, so is also filtered out.

OP - this behaviour is one of my top 10 complaints in Excel. I once had to recreate someone crap spreadsheet properly and had all sorts of issues checking my proper spreadsheet was returning the data correctly because this issue had screwed up the reference spreadsheet.

The solution is to do the filter somewhere more appropriate. Load your data into Power Query, then filter it in there. I *think* the same filtering method in there will give you an "All except blanks" filter, but if it doesn't, you have the whole M language at your disposal to define the filter correctly.

Load the resulting Query to the Data Model and redraw your Pivot Table.

https://www.youtube.com/watch?v=4M5VinpEBmg

To access the table in the existing Workbook the Source for the Query is Excel.CurrentWorkbnook()

https://exceloffthegrid.com/power-query-import-dat...

Thanks for the advice, what I'm going to do as a luddite workaround is add an invisible column to my data with an if statement that says if cell D1 is populated = Yes

Then I can set the pivot filter up to ignore all No instead of each individual date, to give me the same result without additional data entry.

[paulrockliffe]

joropug said:

Thanks for the advice, what I'm going to do as a luddite workaround is add an invisible column to my data with an if statement that says if cell D1 is populated = Yes

Then I can set the pivot filter up to ignore all No instead of each individual date, to give me the same result without additional data entry.

I approve.