Filter and conditional formatting not working

Question

I am trying to troubleshoot an excel worksheet from another person. It is a database of contact information [name, company, title, email etc]

When I activate the Filter function [selecting, FILTER from the Data tab ribbon], the filter arrows appear at the right edge of all cells in row 1 [column headings].

But when I use the filter function to sort the database A-Z by email, the data is screwed up. The names no longer are associated with the correct email address. Why?

Also, trying to find duplicate emails, I use the Conditional formatting to highlight dups. The formatting rules show that the rule is active for that entire column, but it does not show the highlight format for actual dups.

Answer 1

Hi JackSheets!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

Oh sorry, I understand you are having an issue with the Filter function not working properly

There could be several reasons why the filter function is not working correctly:

1. Data is not structured properly: Ensure that the data is structured consistently, and there are no blank cells in the data range. Also, make sure that each column has the correct data type (e.g., text, numbers, dates).
2. Hidden rows or columns: Sometimes, hidden rows or columns can interfere with the filtering process. Ensure that all rows and columns are visible before applying the filter.
3. Mixed data types: Ensure that the data in each column is of the same data type. For example, if a column contains both text and numbers, the filter function may not work correctly.

Regarding the issue of finding duplicate emails, ensure that the conditional formatting rule is set up correctly. Make sure that the range for the rule includes only the column with the email addresses and not the entire worksheet.

Additionally, ensure that the formatting rule is set up to highlight duplicate values and not unique values.

Best Regards, Shakiru

Answer 2

I am trying to troubleshoot an excel worksheet from another person. It is a database of contact information [name, company, title, email etc]

When I activate the Filter function [selecting, FILTER from the Data tab ribbon], the filter arrows appear at the right edge of all cells in row 1 [column headings].

But when I use the filter function to sort the database A-Z by email, the data is screwed up. The names no longer are associated with the correct email address. Why?

Also, trying to find duplicate emails, I use the Conditional formatting to highlight dups. The formatting rules show that the rule is active for that entire column, but it does not show the highlight format for actual dups.

Ignore the conditional formatting question above. The "dups" in question had extra spaces at the ends - not perfect dups. All good for that function.