Excel 2010 vs Excel 2016 - Filter stops working

Geeta2014

New Member
Joined
Aug 9, 2014
Messages
16
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
if i open file in 2010, filter works [file was likely created using 2010 excel]
same exact file - I open using Excel 2016, filter does not work.

the column has h:mm formatting and is dependent on a calculation. the result in the cells throughout the column are very large negatives [16 decimal places]. any ideas anyone? FYI - I am an experienced Excel user and do have multiple workarounds - so please do not provide a workaround. I am looking to identify the reason why this works in an older Version of Excel but not in a newer version. Has anyone come across this? if so, do you know specifically how the two version differ? my instinct is it is a microsoft issue.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What specifically do you mean when you say that the filter doesn't work? The dropdowns don't function at all?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
filter does not work
What are the symptoms of this?
- Filter drop-down arrow is unresponsive?
- Filter drop-down works but incorrect values are returned by the filter?
- Excel crashes?
- Something else?

Any chance you could upload such a file (with any sensitive data removed or disguised) to say Dropbox or OneDrive & provide a link here so we can see if we can replicate/investigate the issue?

You would need to explain what/how you are trying to filter.
 

Geeta2014

New Member
Joined
Aug 9, 2014
Messages
16
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
What specifically do you mean when you say that the filter doesn't work? The dropdowns don't function at all?

that's correct. on the file, the column has a Data > filter. On 2010, that dropdown filter works perfectly. Exact file, I open in 2016 Excel, and a hover prompt says 'showing all' , and drop down filter is disabled.
 

Geeta2014

New Member
Joined
Aug 9, 2014
Messages
16
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

What are the symptoms of this?

- Filter drop-down arrow is unresponsive? - Main symptom: When i click on the drop down filte arrow, prompt displays that says "showing all" in 2016. But, I know there is more information in each cell of column so this is an incorrect message. And, i am unable to see the data

- Filter drop-down works but incorrect values are returned by the filter? - No. The Data filter upside down arrow is still there [same as 2010] but the numbers do not show in the cells of the column.
- Excel crashes? - No. Excel does not crash.
- Something else? - My guess is YES!! lol

Additonal information:

The data contained in the column where the filter functionality is completely disable in 2016 has some unique characteristics
- column is formatted to hour/minute.
- the cells contains information derived from minus-ing a cell that's formatted as Date from another cell formatted as Date
- there are four conditional formatting rules in this specific column [ideally, in 03, only 3 rules were handled. in 2010, this capability is expanded]. In 2016, should be better than 2010.

Any chance you could upload such a file (with any sensitive data removed or disguised) to say Dropbox or OneDrive & provide a link here so we can see if we can replicate/investigate the issue? Not a chance !

You would need to explain what/how you are trying to filter. - I seriously believe this doesn't matter - the simple question is why does a file containing a h:mm column open and work in MS Excel 2010 but does not display the filter correctly in MS 2016.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
Not sure if it is still applicable as I have not had the issue for quite some time, but it might be worth trying Rory's suggestion quoted by HansV here

Also, can you get access to another machine with Excel 2016 to see if the issue is replicated there?
 

Geeta2014

New Member
Joined
Aug 9, 2014
Messages
16
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

Not sure if it is still applicable as I have not had the issue for quite some time, but it might be worth trying Rory's suggestion quoted by HansV here

Also, can you get access to another machine with Excel 2016 to see if the issue is replicated there?
Issue on multiple machines. I saw the linked thread - but I was hoping someone had the details as to the resolution of that thread by HansV
 

Geeta2014

New Member
Joined
Aug 9, 2014
Messages
16
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
one other thing I didn't mention : the result in teh column is calculated and the amount is large negative numbers formatted to h:mm
 

Geeta2014

New Member
Joined
Aug 9, 2014
Messages
16
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
I think it has something to do with the negative h:mm formatted - that the negatives are better handled in 2010 than they are in 2016 (i know, we are supposed to be getting better service from Microsoft not devolving service lol).. when i get a chance, i am going to set up a non-sensitive file on my home pc with all the criteria, then check it with eXCLE 2010 Vs excel 2019 vs Excel 2016 [Just dont know when]. driving me nuts.

Also, this new Power Pivot [as of 2013} - does anyone know whether disabling any option under this would affect negative numbers handling? [Wild out of box question, I know}
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,142
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top