Start Free Trial

asked on

Excel filter will not find all the dates

One user has an issue with excel filters on her PC. She is running Win 10 with Office 2016. When opening a file she needs to be able to filter by date and view only one or two months of the years data. Selecting the filter options and drilling in the selection menus appears normal as the row is selected, the years appear, selecting the + on the year 2017 however only displays Jan and Feb underneath. All the months show for year 2016. The file is large, but not that big, There are 67k rows, but only 4 cols.
The same file opens and filter works properly on any other machine in the building whether running office 2010, 2013 or 2016.
I have repeatedly cleared the filters in Excel, restarted Excel and rebooted the PC. The problem occurs when another user is logged in on the same PC. Manually copying all the 2017 rows to a new spreadsheet also yields the same results.

Does this happen on other computers that are also win10 with Office 2016?

It doesn't sound user dependent, it might mean that there is something in Excel that is corrupted and will need to be repaired. You can go to the Control Panel -> Add/ Remove Programs and repair Office from there. I believe that requires a reboot when done.

What I would do is have her uncheck filter. Then select entire sheet go back to the data tab and select filter again. See if that helps.

ASKER

Thanks for the quick response. It did not help. Any other ideas?

is the date column formatted as a DATE or just general?

ASKER

It is formatted as date. I also tried shortcut 3 to change date format and ensured the dates were all changing. That tells me Excel understands it really is a date instead of a number.

Again, this same file works as expected on any other machine, so I am thinking some obscure excel option.

Sorry to ask the dumb question, but is this person placing the filter at the top of the columns where the title is?

ASKER

I have tried it at the location with the titles which is a few rows down, and at the head of the column.

Did you try repairing Office?

I would try what Mike said and also try rebooting and deleting any files located in the location below.

C:\Users\<user>\AppData\Local\Microsoft\Office\15.0\OfficeFileCache

ASKER

I will give those a try in just a few. Is there any place to tell Excel to reset all filters/views/etc to factory default?

You would probably have to do a quick repair of office. See if that helps you.

AutoFilter has a limit that it will show only the first 10,000 unique items. Depending on how many years worth of data you are looking at, it may be worth checking. This doesn't explain why it works OK on some machines and not others though.

If your 67k rows are individual days or less than 6 entries per day, it could make for more than 10k unique dates.

Does the Autofilter list show entries other than the months? If so these could be text representations of dates rather than true dates. Even if formatted as a date, the cell content could still be text.

ASKER

Quick repair and full repair had no effect. Also deleted cache files, but no joy.
@ Rob this sounds plausible, but does not explain why it works OK on another PC.

ASKER

The situation is changed a bit. It IS only doing this on excel 2016. The only other person in the office with 2016 did not understand what I was asking him to look for when opening the file(s).

That at least narrows the search.

Can you provide a screen shot of the non-working filter and a screen shot of the working filter? So we can compare what they look like.

ASKER

Attached are a couple quick phone snapshots. Both have the same file opened, one is using Office 2010, the other Office 2016.

I spoke with the other person in the office running the 2016 version, and he insists that when I asked him the first time he was able to view/sort all the months from 2017, and also noted that his PC greeted him with that warming message "We have new updates for you!" just the other day. The person who initially reported the problem tells me she has not had a problem working with these reports until sometime around the second week in July. I uninstalled any updates the came in July, (one at a time) but did not restore functionality.

I have brought in one of my laptops to troubleshoot this without kicking people off their desk. Once I loaded Office 2016, it too has the same problem.
IMG_1974.JPG
IMG_1975.JPG

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Removing any updates failed to cure the problem. Going to M$ft for a $upport ticket.

When you do find a solution please post it here. This one is baffling!

Try this instead... Note: This is different than what I posted in my previous comment. Just select the column and rows that need to be filtered here not the whole sheet.

1. Remove AutoFilter from the sheet. All rows should become visible and the AutoFilter drop-down arrows disappear from the heading row.
2. Select the whole range you want the AutoFilter to apply to, from the heading row to the very last row of data, and include all relevant columns.
3. Apply AutoFilter to the sheet again. AutoFilter drop-down arrows should re-appear on heading row.
4. Try your particular filter again and the relevant rows should now be hidden.

ASKER

Thanks Ed, but that did not work either. The non-Date formatted information appeared as expected, but the selection window still only has two months under 2017.
This is looking more like bug land every hour.

ASKER

Just for fun, here is one of the report files sanitized for your protection.
SCE-SDGE-NP15-PGE-SP15-PNode-Hourly.xlsx

Well, this one is so large I cannot open the file. It tells me not enough memory.

Weird. I had to save the file then open it that way or otherwise I get out of memory. I have loads of disk space and RAM.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I will pass that to the client. It works. There still is an issue that I think MSFT needs to fix. This should appear in the easy selection windows like it does on every other version of excel. Besides, if it is a bug I get my $$ back.

I will leave this open and post what comes up.

Additional comments still welcomed.

I think its 2016 since I am using the same version and have the same issue.

Another thing I found is if I filter the other columns first, 2017 dates will appear if I filter the date column last.

I also have the same issue with Excel 2016

I just created a Pivot Table on the sample data supplied and there are only 565 unique dates with 120 entries against each, 1 per hour, 5 nodes; 5 * 24 = 120. Therefore I don't believe it is the 10k issue that I mentioned. That is supposed to be 10k unique values.

Well I thought it was 10k unique items, MS Office page (link below) does not mention "unique". Strangely enough the last date that it will show in the dropdown is 20 Feb 17. That date spans rows 9989 to 10012, so I would hazard a guess that it is stopping at row 10005 as the headers are in row 4, therefore data starts in row 5; 10k entries gives row 10005.

Excel specifications and limits

ASKER

@ Rob MSFT tech now has a copy of the file and is supposed to call me in the next 8 hours. I don't think the 10K limit is the answer since it does not affect operation on any previous version of Excel.

10k limit has been in place since 2007 if not before

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

@Rob that actually works. I had to remove the filter first then I was able to see the option. Once I unchecked group dates I was able to see all the dates.

ASKER

Working with MSFT. During the events, running an office repair, Office got corrupted and had to be re-installed. Build 16.0 came back up, and the file worked. Ran updates from inside Excel, and after update completed, it would not show the months again.

Looks like the problem is found.

MSFT now has this in thier capable hands. I will leave this open to post the fix when it is ..... available?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks to all participants. That's what makes this forum great.
Problem finally isolated to MSFT bug that came with a patch. The next patch will patch the patch.

ASKER

Addendum - Once the Excel version had been backed down, MSFT updates came in the next day and "fixed" it for me again. GPO was set to prohibit updates. Windows had all the "defer" boxes checked. I even went into the XML file to disable office updates. Still, down they came like clockwork.
Here is what I found.
https://social.technet.microsoft.com/Forums/en-US/56fd5175-f595-4b0f-8e5f-ff53d2803028/disable-automatic-updates-2016-ctr?forum=Office2016setupdeploy
So MSFT now installs scheduled tasks to circumvent all the sysadmin tools we need to keep our networks running.

Just a few more years and I can retire......

Wow! go MSFT way to make life so much more bearable for all of us poor peons.

Sounds like a major pain! Hopefully, their patch for their patch actually fixes it so you don't have to worry about whether it will work each day when MSFT forces the install of a scheduled task.