Filtrando dados no Excel – fácil e completo

O comando Filtro do Excel pode ajudar a filtrar dados em um intervalo ou tabela para exibir apenas as informações necessárias e ocultar o restante. Você pode utilizar os operadores internos para filtrar facilmente números, textos ou datas, como filtrar todos os números maiores ou iguais a um determinado valor, filtrar textos que começam, terminam ou contêm um caractere ou palavra específica, ou exibir apenas linhas em que a data de vencimento seja anterior ou posterior a uma data específica, entre outros. Após filtrar os dados em um intervalo ou tabela, se os dados forem alterados, você pode reaplicar o filtro para atualizar as informações ou limpar o filtro para exibir todos os dados.

Neste tutorial, vamos demonstrar como adicionar, usar ou remover filtros no Excel. Além disso, mostraremos como aprimorar o recurso de filtro para lidar com situações mais complexas no Excel.

1. Como adicionar filtro no Excel

Para filtrar dados em um intervalo ou tabela, primeiro é necessário adicionar o filtro aos seus dados. Esta seção apresenta3 maneiras de adicionar filtro no Excel.

1.1 Comando Filtro na guia Dados

Selecione qualquer célula no intervalo ou tabela onde deseja adicionar o filtro, clique em "Dados" > "Filtro".

1.2 Comando Filtro na guia Página Inicial

Selecione qualquer célula no intervalo ou tabela onde deseja adicionar o filtro, clique em "Página Inicial" > "Classificar e Filtrar" > "Filtro".

1.3 Adicionar filtro com atalho

Selecione qualquer célula no intervalo ou tabela onde deseja adicionar o filtro e pressione as teclas "Ctrl" + "Shift" + "L".

Após realizar uma das operações acima, você verá que setas de lista suspensa são adicionadas aos cabeçalhos das colunas das células selecionadas.

2. Como aplicar filtro no Excel (um ou vários critérios)

Depois de adicionar o filtro, é necessário aplicá-lo manualmente. Esta seção mostra como aplicar filtro em uma ou mais colunas no Excel.

2.1 Aplicar filtro em uma coluna (um critério)

Se você deseja aplicar o filtro apenas em uma coluna, como filtrar dados na coluna C conforme mostrado na imagem abaixo, vá até essa coluna e siga os passos a seguir.

1. 1) Clique na seta de lista suspensa no cabeçalho da coluna.
2. 2) Especifique a condição de filtro conforme necessário.
3. 3) Clique no botão "OK" para iniciar a filtragem. Veja a imagem:
   

Agora o filtro está aplicado à coluna C. Todos os dados que atendem aos critérios de filtro serão exibidos e o restante ficará oculto.

Após aplicar o filtro, você verá que a seta de lista suspensa se transforma em um ícone de filtro .

É muito prático que, ao passar o cursor sobre o ícone de filtro, os critérios de filtro especificados sejam exibidos como uma dica de tela, conforme mostrado na imagem abaixo. Portanto, se esquecer os critérios definidos, basta passar o cursor sobre o ícone de filtro.

2.2 Aplicar filtro com múltiplos critérios em várias colunas (múltiplos critérios)

2.2.1 Aplicar filtro com múltiplos critérios em várias colunas uma por uma

Se quiser aplicar filtro em várias colunas com múltiplos critérios, basta repetir o método acima para cada coluna individualmente.

Após aplicar o filtro em várias colunas, você verá que as setas de lista suspensa nas colunas filtradas se transformam em ícones de filtro.

2.2.2 Aplicar simultaneamente filtro com múltiplos critérios em várias colunas

Com o método acima, é necessário aplicar o filtro em cada coluna separadamente, e o ponto principal é que esse método só suporta critérios "E" (AND). Aqui apresentamos métodos para aplicar o filtro em várias colunas simultaneamente, suportando tanto critérios "E" quanto "OU" (OR).

Suponha que você tenha uma tabela de dados como mostrado na imagem abaixo e queira filtrar dados de várias colunas com múltiplos critérios: "Produto = AAA-1" e "Pedido >80", ou "Preço Total >10000". Experimente um dos métodos a seguir para realizar essa tarefa.

2.2.2.1 Aplicar filtro em várias colunas com a função Filtro Avançado

A função Filtro Avançado pode ajudar a resolver esse problema. Siga o passo a passo abaixo.

1. Primeiro, crie os critérios na planilha conforme mostrado na imagem abaixo.

Nota: Para critérios E (AND), coloque os valores dos critérios em células diferentes da mesma linha. Para critérios OU (OR), coloque o valor em outra linha.

2. Clique em "Dados" > "Avançado" para ativar a função "Filtro Avançado".

3. Na caixa de diálogo "Filtro Avançado", configure conforme abaixo.

Agora as colunas são filtradas simultaneamente com base nos critérios definidos, conforme mostrado na imagem abaixo.

2.2.2.2 Aplicar facilmente filtro em várias colunas com uma ferramenta incrível

Como os critérios de filtro "E" e "OU" não são fáceis de gerenciar no método anterior, recomendamos o recurso "Super Filtro" do "Kutools para Excel". Com esse recurso, é possível aplicar filtro em várias colunas com critérios E e OU de forma simples no Excel.

1. Após instalar o Kutools para Excel, clique em "Kutools Plus" > "Super Filtro".

Em seguida, o painel "Super Filtro" será exibido no lado direito da planilha.

Por padrão, dois grupos de critérios em branco são adicionados com relação OU entre eles no painel "Super Filtro". E a relação entre os critérios do mesmo grupo é E. Você pode alterar a relação entre diferentes grupos conforme necessário.

2. No painel "Super Filtro", configure os critérios de filtro conforme abaixo.

2.1) Marque a caixa "Especificado", clique no botão para selecionar o intervalo ou tabela original que será filtrado;

2.2) Na lista suspensa "Relacionamento", escolha "Ou";

3.3) Clique na primeira linha em branco do primeiro grupo e especifique os critérios conforme necessário;

Dicas: A primeira lista suspensa é para os cabeçalhos das colunas, a segunda para tipos de filtro (você pode escolher "Texto", "Número", "Data", "Ano", "Formato de texto" etc.), a terceira para tipos de critérios e a última caixa de texto para o valor do critério.

No exemplo acima, aqui escolho "Produto" > "Texto" > "Igual a" separadamente nas três listas suspensas e digito "AAA-1" na caixa de texto. Veja a imagem:

2.4) Continue criando os demais critérios, e o critério "Ou" precisa ser criado em um novo grupo. Como mostrado na imagem abaixo, todos os critérios foram criados. Você pode excluir o critério em branco dos grupos.

2.5) Clique no botão "Filtrar" para iniciar a filtragem.

Agora apenas os dados correspondentes são exibidos no intervalo de dados original e o restante fica oculto. Veja a imagem:

Dicas: Com esse recurso prático, você pode adicionar mais critérios em um grupo, adicionar mais grupos, salvar as configurações atuais de filtro como um cenário para uso futuro e muito mais. É uma ferramenta indispensável que economiza tempo e aumenta a eficiência do trabalho.

Clique para saber mais sobre esse recurso.

3. Como usar o filtro no Excel

Nesta seção, você aprenderá como usar o comando de filtro para filtrar diferentes tipos de dados, como texto, números, datas e formatos.

3.1 Filtrar valores de texto

3.1.1Filtrar células de texto com critérios específicos (começa com, termina com, contém, etc.)

Na verdade, o operador de filtro interno – "Filtros de Texto" oferece vários critérios úteis para filtrar textos facilmente. Suponha que você queira filtrar células que começam com um caractere específico, como J, siga os passos abaixo para realizar essa tarefa.

1. Adicione um filtro ao cabeçalho da coluna do intervalo de dados original. Clique para saber como adicionar um filtro ao cabeçalho da coluna.

2. Clique na seta de lista suspensa na célula do cabeçalho para abrir o menu de filtro.

3. Clique em "Filtros de Texto" > "Começa Com".

4. Na caixa de diálogo "Filtro Automático Personalizado", digite o caractere específico (aqui digito J) na caixa de texto e clique em "OK".

Dicas: Você pode adicionar outro critério de relacionamento "E" ou "Ou" conforme necessário.

Agora todas as células que começam com o caractere J são exibidas na coluna D, conforme mostrado na imagem abaixo.

3.1.2 Filtrar diferenciando maiúsculas de minúsculas

Parece fácil filtrar células de texto com critérios específicos usando o operador de filtro interno. No entanto, como a função Filtro não diferencia maiúsculas de minúsculas, como realizar um filtro sensível a maiúsculas/minúsculas no Excel? Esta seção mostra métodos para alcançar esse objetivo.

3.1.2.1 Filtrar determinado texto diferenciando maiúsculas de minúsculas com fórmula e o comando Filtro

Suponha que você queira filtrar todas as ocorrências em maiúsculas de um determinado texto, como “TEXT TOOLS” na coluna B, siga os passos abaixo.

1. Crie uma coluna auxiliar ao lado do intervalo de dados original (aqui escolho a coluna D como auxiliar). Digite a fórmula abaixo na segunda célula e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para obter os demais resultados.

=EXACT(B2,UPPER(B2))

Nota: Esta fórmula ajuda a identificar células em maiúsculas e minúsculas. Se uma célula contiver apenas caracteres em maiúsculas, o resultado será "TRUE"; caso contrário, será "FALSE".

2. Selecione as colunas B e D (a coluna C será selecionada, não se preocupe), clique em "Dados" > "Filtro" para adicionar filtros a elas.

3. Vá para a coluna B (a coluna que contém os textos a serem filtrados) e configure conforme abaixo.

Agora apenas as ocorrências em maiúsculas e minúsculas de “text tools” são exibidas na coluna B.

4. Clique na seta de lista suspensa na coluna D, desmarque a caixa "Selecionar Tudo", marque a caixa ao lado do item "TRUE" e clique no botão "OK".

Em seguida, todas as ocorrências em maiúsculas do texto “text tools” na coluna B são filtradas, conforme mostrado na imagem abaixo.

3.1.2.2 Filtrar facilmente células diferenciando maiúsculas de minúsculas com uma ferramenta incrível

Se você deseja filtrar apenas textos totalmente em maiúsculas ou minúsculas em uma coluna, recomendamos o recurso "Filtro Especial" do "Kutools para Excel". Com esse recurso, todos os textos em maiúsculas ou minúsculas serão filtrados facilmente com apenas alguns cliques.

1. Selecione o intervalo da coluna que deseja filtrar os textos e clique em "Kutools Plus" > "Filtro Especial" > "Filtro Especial".

2. Na caixa de diálogo "Filtro Especial", faça as configurações abaixo.

3. Em seguida, uma caixa de diálogo "Kutools para Excel" aparecerá informando quantas células foram encontradas e serão filtradas. Clique em "OK".

Agora todas as células em maiúsculas ou minúsculas são filtradas imediatamente, conforme mostrado na imagem abaixo.

Clique para saber mais sobre esse recurso.

3.1.3 Filtrar por comprimento do texto

Se você deseja filtrar células pelo comprimento do texto, por exemplo, filtrar células com comprimento igual a10 caracteres, o que fazer? Os três métodos abaixo podem ajudar.

3.1.3.1 Filtrar células pelo comprimento do texto com o comando Filtro

Na verdade, o comando Filtro possui um operador interno para resolver esse problema. Siga os passos abaixo.

1. Selecione o intervalo de células a ser filtrado (aqui selecionei B1:B27) e adicione um filtro a essa coluna clicando em "Dados" > "Filtro".

2. Clique na seta de lista suspensa no cabeçalho da coluna e depois em "Filtros de Texto" > "Filtro Personalizado". Veja a imagem:

3. Na caixa de diálogo "Filtro Automático Personalizado", selecione o critério como igual a, digite10 pontos de interrogação (?) como padrão na caixa de texto e clique no botão "OK".

Dicas: Esses10 pontos de interrogação indicam que será correspondido o texto cujo comprimento é10.

Agora todas as células cujo comprimento do texto é10 (incluindo espaços) são filtradas imediatamente.

3.1.3.2 Filtrar células pelo comprimento do texto com fórmula e o comando Filtro

Além disso, você pode usar a função NÚM.CARACT para calcular o comprimento do texto de cada célula e, em seguida, aplicar o comando Filtro para filtrar as células com o comprimento desejado com base no resultado calculado.

1. Crie uma coluna auxiliar ao lado do intervalo de dados original. Digite a fórmula abaixo e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para obter os demais resultados.

=LEN(B2)

Agora você obteve o comprimento do texto de cada célula na coluna especificada.

2. Selecione a coluna auxiliar (incluindo o cabeçalho), clique em "Dados" > "Filtro" para adicionar um filtro a ela.

3. Clique na seta de lista suspensa, desmarque a caixa "Selecionar Tudo" para desmarcar todos os itens e marque apenas a caixa ao lado do número10. Por fim, clique no botão "OK".

Agora todas as células cujo comprimento do texto é10 (incluindo espaços) são filtradas imediatamente.

3.1.3.3 Filtrar facilmente células pelo comprimento do texto com uma ferramenta incrível

Aqui recomendamos a utilidade "Filtro Especial" do "Kutools para Excel" para ajudar a filtrar facilmente células pelo comprimento do texto no Excel.

1. Selecione o intervalo da coluna que deseja filtrar as células com determinado comprimento de texto, clique em "Kutools Plus" > "Filtro Especial" > "Filtro Especial". Veja a imagem:

2. Na caixa de diálogo "Filtro Especial", configure conforme abaixo.

3. Uma caixa de diálogo "Kutools para Excel" aparecerá informando quantas células foram encontradas e serão filtradas. Clique em "OK" para continuar.

Em seguida, todas as células com comprimento de texto igual a10 são filtradas, conforme mostrado na imagem abaixo.

3.2 Filtrar valores numéricos

No Excel, também é muito fácil filtrar números usando o comando Filtros de Números.

Suponha que você queira filtrar células com números entre15000 e20000 em uma coluna (como a coluna C, conforme mostrado na imagem abaixo), siga os passos abaixo para realizar essa tarefa.

1. Selecione o intervalo da coluna que contém os números a serem filtrados, clique em "Dados" > "Filtro" para adicionar um filtro.

2. Após adicionar o filtro, configure conforme abaixo.

Dicas: Como quero filtrar células com números entre15000 e20000, aqui digito15000 e20000 separadamente nas caixas de texto.

Agora as células com números entre15000 e20000 são filtradas, conforme mostrado na imagem abaixo.

3.3 Filtrar datas

Por padrão, o recurso interno Filtros de Data oferece vários critérios comuns para filtrar datas. Como pode ver, não há opção interna para filtrar datas por dia da semana, finais de semana ou dias úteis. Esta seção ensina como realizar essas operações.

3.3.1 Filtrar datas por dia da semana ou finais de semana

Suponha que você tenha uma tabela de dados como mostrado na imagem abaixo e queira filtrar datas por dia da semana ou finais de semana. Aplique um dos métodos abaixo.

3.3.1.1 Filtrar datas por dia da semana ou finais de semana com fórmula e o comando Filtro

Nesta seção, você aplicará a função DIA.DA.SEMANA para calcular o dia da semana de cada data e, em seguida, aplicará o filtro para filtrar o dia da semana ou finais de semana conforme necessário.

1. Em uma célula em branco (D2 neste caso), digite a fórmula abaixo e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para aplicar a fórmula.

=WEEKDAY(A2)

Dicas:

Nota: Como pode ver, a fórmula retorna números de "1" a "7", que indicam o dia da semana de "Domingo" a "Sábado" (1 é para Domingo,7 para Sábado).

2. Selecione todos os resultados da fórmula (incluindo o cabeçalho), clique em "Dados" > "Filtro".

3. Clique na seta de lista suspensa e desmarque a caixa "Selecionar Tudo".

Em seguida, todas as células de finais de semana ou de determinado dia da semana são filtradas. Veja a imagem:

3.3.1.2 Filtrar facilmente datas por dia da semana ou finais de semana com uma ferramenta incrível

Se o método acima não for conveniente, recomendamos a utilidade "Filtro Especial" do "Kutools para Excel". Com esse recurso, é possível filtrar facilmente células contendo qualquer dia da semana com apenas alguns cliques.

1. Selecione as células com as datas que deseja filtrar por determinado dia da semana.

2. Clique em "Kutools Plus" > "Filtro Especial" > "Filtro Especial".

3. Na caixa de diálogo "Filtro Especial", configure conforme abaixo.

4. Em seguida, uma caixa de diálogo "Kutools para Excel" aparecerá informando quantas células foram encontradas e serão filtradas. Clique em "OK" para continuar.

Agora todas as células de finais de semana ou de qualquer dia da semana são filtradas.

3.3.2 Filtrar células por dias úteis com uma ferramenta incrível

Além de filtrar células por dia da semana ou finais de semana, a utilidade "Filtro Especial" do "Kutools para Excel" também pode ajudar a filtrar células por dias úteis.

1. Siga os mesmos passos acima para ativar a utilidade "Filtro Especial".

2. Na caixa de diálogo "Filtro Especial", faça as configurações abaixo.

3. Em seguida, uma caixa de diálogo "Kutools para Excel" aparecerá. Clique em "OK" para continuar.

Agora todas as células de dias úteis são filtradas.

3.4 Filtrar formatos

Normalmente, o Excel permite filtrar dados com base em critérios visuais, como cor da fonte, cor da célula ou conjuntos de ícones, conforme mostrado na imagem abaixo.

No entanto, se quiser filtrar dados com base em outros critérios visuais, como estilo da fonte (negrito, itálico), efeitos de fonte (tachado) ou células especiais (com fórmulas), o Excel não oferece suporte direto. Esta seção apresenta métodos para resolver esses problemas.

3.4.1 Filtrar por texto em negrito/itálico

Suponha que você queira filtrar dados por texto formatado em negrito ou itálico, conforme mostrado na imagem abaixo. Os métodos a seguir podem ajudar. Siga os passos abaixo.

3.4.1.1 Filtrar texto em negrito/itálico com fórmula e o comando Filtro

A combinação da fórmula Get.Cell e do comando Filtro pode ajudar a filtrar textos em negrito em um intervalo de coluna.

1. Clique em "Fórmulas" > "Definir Nome".

2. Na caixa de diálogo "Novo Nome", faça o seguinte:

=GET.CELL(20,$B2)

=GET.CELL(21,$B2)

Sintaxe da Fórmula:

=GET.CELL(type_num, reference)

Argumentos da Fórmula

3. Selecione uma célula em branco na mesma linha de B2, digite a fórmula abaixo e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para aplicar a fórmula.

=Filter_Bold_Cells

4. Selecione todas as células de resultado (incluindo o cabeçalho), clique em "Dados" > "Filtro".

5. Clique na seta de lista suspensa, marque apenas a caixa ao lado da opção "TRUE" e clique em "OK".

Em seguida, todas as células de texto em negrito ou itálico são filtradas. Veja a imagem:

3.4.1.2 Filtrar texto em negrito ou itálico com os comandos Localizar e Substituir e Filtro

Você também pode usar a combinação dos comandos Localizar e Substituir e Filtro para realizar essa tarefa.

1. Selecione o intervalo da coluna contendo as células de texto em negrito ou itálico que deseja filtrar e pressione as teclas "Ctrl" + "F".

2. Na caixa de diálogo "Localizar e Substituir", configure conforme abaixo.

3. Agora todas as células de texto em negrito ou itálico estão selecionadas no intervalo original; clique em "Página Inicial" > "Cor de Preenchimento" e escolha uma cor para as células selecionadas.

4. Selecione novamente todo o intervalo da coluna, clique em "Dados" > "Filtro" para adicionar um filtro.

5. Clique na seta de lista suspensa, selecione "Filtrar por Cor" e clique na cor de preenchimento especificada em "Filtrar por Cor da Célula". Veja a imagem:

Em seguida, todas as células de texto em negrito ou itálico são filtradas.

3.4.1.3 Filtrar facilmente texto em negrito ou itálico com uma ferramenta incrível

Como mencionado acima, a utilidade "Filtro Especial" do "Kutools para Excel" pode ajudar a filtrar facilmente diferenciando maiúsculas de minúsculas, por comprimento do texto e datas. Aqui, ensinamos como aplicar esse recurso para filtrar células de texto em negrito ou itálico no Excel.

1. Selecione o intervalo da coluna (incluindo o cabeçalho) que contém as células formatadas em negrito ou itálico que deseja filtrar.

2. Clique em "Kutools Plus" > "Filtro Especial" > "Filtrar Negrito / Filtrar Itálico". Veja a imagem:

3. Clique em "OK" na caixa de diálogo "Kutools para Excel" que aparecerá para continuar (essa caixa informa quantas células atendem aos critérios).

Agora todas as células de texto em negrito ou itálico são filtradas.

3.4.2 Filtrar por texto tachado

Suponha que você tenha recebido uma lista com pessoas marcadas com tachado e precise localizar todas as células tachadas por meio de filtro; os métodos abaixo podem ajudar.

3.4.2.1 Filtrar texto tachado com função definida pelo usuário e o comando Filtro

Você pode aplicar uma função definida pelo usuário para identificar células com texto tachado e, em seguida, usar o comando Filtro para filtrar todas as células tachadas com base nos resultados.

1. Pressione as teclas "Alt" + "F11" para abrir a janela "Microsoft Visual Basic for Applications".

2. Na janela "Microsoft Visual Basic for Applications", clique em "Inserir" > "Módulo". Em seguida, copie o código VBA abaixo para a janela de código.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Pressione as teclas "Alt" + "Q" para fechar a janela "Microsoft Visual Basic for Applications".

4. Selecione uma célula em branco (na mesma linha da célula que deseja calcular), digite a fórmula abaixo e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para aplicar a fórmula.

=HasStrike(B2)

Nota: Se a célula correspondente tiver efeito de fonte tachada, retorna "TRUE"; caso contrário, retorna "FALSE".

5. Selecione todas as células de resultado (incluindo o cabeçalho), clique em "Dados" > "Filtro".

6. Clique na seta de lista suspensa > marque apenas a caixa ao lado da opção "TRUE" > clique em "OK". Veja a imagem:

Agora você pode ver que todas as células com formato tachado são filtradas.

3.4.2.2 Filtrar facilmente texto tachado com uma ferramenta incrível

Com a utilidade "Filtro Especial" do "Kutools para Excel", todas as células com formato tachado podem ser filtradas diretamente com apenas alguns cliques.

1. Selecione o intervalo da coluna que deseja filtrar todas as células tachadas, clique em "Kutools Plus" > "Filtro Especial" > "Filtrar Tachado".

2. Em seguida, uma caixa de diálogo "Kutools para Excel" aparecerá informando quantas células foram qualificadas. Clique em "OK" para continuar.

Em seguida, todas as células com formato tachado são filtradas, conforme mostrado na imagem abaixo.

3.4.3 Filtrar por cor da fonte ou de fundo

Como mencionado no início desta seção sobre formatos de filtro, o Excel permite filtrar dados com base em critérios visuais, como cor da fonte, cor da célula ou conjuntos de ícones, usando o recurso interno. Esta seção demonstra como aplicar o recurso "Filtrar por Cor" para filtrar células por cor da fonte ou de fundo em detalhes. Além disso, recomendamos uma função de terceiros prática para ajudar a resolver esse problema.

3.4.3.1 Filtrar por uma cor de fonte ou de fundo com o comando Filtro

Você pode aplicar diretamente o recurso Filtrar por Cor do comando Filtro para filtrar células por uma cor específica de fonte ou de fundo no Excel.

1. Selecione o intervalo da coluna que deseja filtrar por cor da fonte ou de fundo e clique em "Dados" > "Filtro".

2. Clique na seta de lista suspensa > "Filtrar por Cor". Assim, todas as cores de célula e de fonte do intervalo da coluna atual serão listadas. Clicando em qualquer cor de célula ou de fonte, todas as células serão filtradas com base na cor selecionada.

3.4.3.2 Filtrar por várias cores de fundo com função definida pelo usuário e o comando Filtro

Se quiser filtrar células por várias cores de fundo, aplique o método abaixo.

Suponha que você queira filtrar todas as células com cores de fundo laranja e azul na coluna B, conforme mostrado na imagem abaixo. Primeiro, é necessário calcular o índice de cor dessas células.

1. Pressione as teclas "Alt" + "F11" simultaneamente.

2. Na janela "Microsoft Visual Basic for Applications", clique em "Inserir" > "Módulo". Em seguida, copie o código VBA abaixo para a janela de código.

Código VBA: Obter índice de cor de fundo da célula

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Pressione as teclas "Alt" + "Q" para fechar a janela "Microsoft Visual Basic for Applications".

4. Em uma nova coluna, digite um cabeçalho na primeira célula (essa célula deve estar na mesma linha do cabeçalho do intervalo original).

5. Selecione uma célula em branco ao lado do cabeçalho (aqui selecionei E2), digite a fórmula abaixo e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para aplicar a fórmula.

=GetColor(B2)

Nota: Se a célula não tiver cor de preenchimento, retorna -4142.

6. Selecione as células da coluna auxiliar (incluindo o cabeçalho), clique em "Dados" > "Filtro" para adicionar um filtro à coluna.

7. Clique na seta de lista suspensa para abrir a lista e configure conforme abaixo.

Agora as células são filtradas pelas cores de fundo especificadas, conforme mostrado na imagem abaixo.

3.4.3.3 Filtrar facilmente por cor da fonte ou de fundo com uma ferramenta incrível

Sem dúvida, é fácil aplicar o recurso interno Filtrar por Cor para filtrar células por cor da fonte ou de fundo. No entanto, a desvantagem é que a caixa de lista suspensa cobre o conteúdo dos dados originais, dificultando a visualização dos dados ao escolher uma cor. Para evitar esse problema, recomendamos a utilidade "Filtro Especial" do "Kutools para Excel".

1. Selecione o intervalo da coluna que deseja filtrar por cor da fonte ou de fundo e clique em "Kutools Plus" > "Filtro Especial" > "Filtro Especial".

2. Na caixa de diálogo "Filtro Especial", configure conforme abaixo.

2.1) O endereço das células do intervalo selecionado é exibido na caixa "Intervalo";

2.2) Na seção "Regras de Filtro", selecione a opção "Formato";

2.3) Escolha "Cor de Fundo" ou "Cor da Fonte" na lista suspensa;

2.4) Clique no ícone do conta-gotas ;

2.5) Na caixa de diálogo "Filtrar células especiais" que abrir, selecione uma célula com a cor de fonte ou de fundo que deseja filtrar e clique em "OK".

2.6) Ao retornar à caixa de diálogo "Filtro Especial", a cor da fonte ou de fundo da célula selecionada será exibida na caixa de texto (você pode modificar a cor se necessário), clique em "OK" para iniciar a filtragem.

Em seguida, todas as células com a cor de fonte ou de fundo especificada no intervalo selecionado são filtradas.

3.4.4 Filtrar células com fórmulas

Se você tem uma lista longa de dados que contém valores reais e fórmulas e precisa filtrar apenas as células com fórmulas, o que fazer? Esta seção apresenta dois métodos para isso.

3.4.4.1 Filtrar células com fórmulas com função definida pelo usuário e o comando Filtro

Primeiro, é necessário identificar todas as células com fórmulas na lista usando uma função definida pelo usuário e, em seguida, aplicar o comando Filtro para filtrar as células com fórmulas com base nos resultados.

1. Pressione as teclas "Alt" + "F11" para abrir a janela "Microsoft Visual Basic for Applications".

2. Na janela "Microsoft Visual Basic for Applications", clique em "Inserir" > "Módulo" e copie o código VBA abaixo para a janela de código.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Pressione as teclas "Alt" + "Q" para fechar a janela "Microsoft Visual Basic for Applications".

4. Selecione uma célula em branco, que deve estar na mesma linha da célula que deseja verificar se é uma célula de fórmula, digite a fórmula abaixo e pressione "Enter". Selecione a célula com o resultado e arraste a "Alça de Preenchimento Automático" para baixo para aplicar a fórmula.

=HasFormula(C2)

Como mostrado na imagem acima, os resultados são "FALSE" e "TRUE", indicando que, se a célula correspondente for uma célula de fórmula, retorna TRUE; caso contrário, retorna FALSE.

5. Selecione as células de resultado (incluindo o cabeçalho), clique em "Dados" > "Filtro" para adicionar um filtro.

6. Clique na seta de lista suspensa, marque apenas a caixa ao lado de "TRUE" e clique em "OK".

Em seguida, todas as células de fórmula são filtradas.

3.4.4.2 Filtrar facilmente células de fórmula com uma ferramenta incrível

Aqui demonstramos a utilidade "Filtro Especial" do "Kutools para Excel" para ajudar a filtrar facilmente células de fórmula em uma lista com apenas alguns cliques.

1. Selecione a lista que deseja filtrar todas as células de fórmula e clique em "Kutools Plus" > "Filtro Especial" > "Filtrar Fórmula".

2. Uma caixa de diálogo "Kutools para Excel" aparecerá informando quantas células foram qualificadas. Clique em "OK" para continuar.

Em seguida, todas as células de fórmula são filtradas, conforme mostrado na imagem abaixo.

Além disso, você pode usar o recurso "Filtro Especial" do "Kutools para Excel" para filtrar facilmente células com outros formatos, como:

Filtrar todas as células com comentários, clique para saber mais...

Filtrar todas as células mescladas com base em determinado valor, clique para saber mais...

3.5 Filtrar com caractere curinga

Às vezes, você pode esquecer o critério exato de pesquisa ao filtrar. Nesse caso, recomendamos o uso de caracteres curinga.

Existem apenas3 caracteres curinga no Excel:

Veja como usar caracteres curinga na filtragem.

Supondo que você precise filtrar todas as células que terminam com Market na coluna B, conforme mostrado na imagem abaixo, siga os passos abaixo.

1. Primeiro, crie um intervalo de critérios. Digite um cabeçalho igual ao cabeçalho da coluna original e, em seguida, digite o critério de filtro na célula abaixo. Veja a imagem:

2. Clique em "Dados" > "Avançado".

3. Na caixa de diálogo "Filtro Avançado" que abrir, configure conforme abaixo.

Em seguida, todas as células que terminam com Market são filtradas. Veja a imagem:

O uso dos caracteres curinga * e ~ na filtragem é o mesmo das operações acima.

3.6 Filtrar com caixa de pesquisa integrada

Se você estiver usando o Excel2010 ou versões posteriores, notará que há uma caixa de pesquisa integrada no Filtro do Excel. Esta seção demonstra como usar essa caixa de pesquisa para filtrar dados no Excel.

Como mostrado na imagem abaixo, você deseja filtrar todas as células que contêm “Market”; a caixa de pesquisa pode ajudar a realizar isso facilmente.

1. Selecione o intervalo da coluna que deseja filtrar os dados, clique em "Dados" > "Filtro" para adicionar um filtro.

2. Clique na seta de lista suspensa, digite “Market” na caixa de pesquisa e clique em "OK".

Você verá que todos os textos correspondentes são listados em tempo real enquanto digita na caixa de pesquisa.

Em seguida, todas as células que contêm “Market” são filtradas, conforme mostrado na imagem abaixo.

4. Copiar apenas dados visíveis (ignorar dados ocultos ou filtrados)

Copiar apenas dados visíveis com teclas de atalho

Você pode usar teclas de atalho para selecionar apenas as células visíveis e, em seguida, copiá-las e colá-las manualmente no local desejado.

1. Selecione o intervalo que deseja copiar apenas as células visíveis. Tanto as células visíveis quanto as ocultas serão selecionadas nesta etapa.

2. Pressione as teclas "Alt" e ";" ao mesmo tempo. Agora apenas as células visíveis estão selecionadas.

3. Pressione as teclas "Ctrl" + "C" para copiar as células selecionadas e depois "Ctrl" + "V" para colá-las.

Copiar facilmente apenas dados visíveis com uma ferramenta incrível

Aqui apresentamos o recurso "Colar visível" do "Kutools para Excel" para copiar facilmente apenas dados visíveis no Excel. Além disso, com esse recurso, é possível copiar e colar valores apenas nas células visíveis de um intervalo filtrado.

1. Selecione o intervalo filtrado que deseja copiar apenas as células visíveis, depois clique em "Kutools" > "Intervalo" > "Colar visível" > "Tudo / Colar somente valores".

Para células de fórmula, escolha "Tudo" para copiar tanto o resultado quanto as fórmulas, ou "Colar somente valores" para copiar apenas os valores reais.

2. Na caixa de diálogo "Colar no intervalo visível" que aparecer, escolha uma célula em branco para colar as células copiadas e clique em "OK".

Em seguida, apenas as células visíveis do intervalo filtrado selecionado são copiadas e coladas em um novo local.

Nota: Se o intervalo de destino estiver filtrado, os valores selecionados serão colados apenas nas células visíveis.

5. Excluir linhas ocultas ou visíveis após filtrar

Para uma lista filtrada, pode ser necessário excluir as linhas ocultas para manter apenas os dados visíveis. Nesta seção, você aprenderá três métodos para excluir linhas ocultas ou visíveis em uma lista filtrada no Excel.

Excluir todas as linhas ocultas da planilha atual com código VBA

O código VBA abaixo pode ajudar a excluir todas as linhas ocultas da planilha atual no Excel.

Nota: Este VBA remove não apenas as linhas ocultas na lista filtrada, mas também as linhas ocultas manualmente.

1. Na planilha que contém as linhas ocultas que deseja remover, pressione as teclas "Alt" + "F11" simultaneamente para abrir a janela "Microsoft Visual Basic for Applications".

2. Na janela "Microsoft Visual Basic for Applications", clique em "Inserir" > "Módulo" e copie o código VBA abaixo para a janela do módulo.

Código VBA: Excluir todas as linhas ocultas da planilha atual

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Pressione a tecla "F5" para executar o código.

4. Em seguida, uma caixa de diálogo "Kutools para Excel" aparecerá informando quantas linhas ocultas foram removidas. Clique em "OK" para fechar.

Agora todas as linhas ocultas (incluindo as ocultas automaticamente e manualmente) foram removidas.

Excluir linhas visíveis após filtrar com o recurso Ir Para

Se quiser remover apenas as linhas visíveis em um determinado intervalo, o recurso "Ir Para" pode ajudar.

1. Selecione o intervalo filtrado do qual deseja remover as linhas visíveis e pressione a tecla "F5" para abrir a caixa de diálogo "Ir Para".

2. Na caixa de diálogo "Ir Para", clique no botão "Especial".

3. Na caixa de diálogo "Ir Para Especial", selecione a opção "Apenas células visíveis" e clique em "OK".

4. Agora todas as células visíveis estão selecionadas. Clique com o botão direito no intervalo selecionado e clique em "Excluir Linha" no menu de contexto.

Em seguida, todas as células visíveis são excluídas.

Excluir facilmente linhas ocultas ou visíveis após filtrar com uma ferramenta incrível

Os métodos acima são trabalhosos e demorados. Recomendamos o recurso "Excluir Linhas (Colunas) Ocultas (Visíveis)" do "Kutools para Excel". Com esse recurso, você pode excluir facilmente linhas ocultas ou visíveis não apenas em um intervalo selecionado, mas também na planilha atual, em várias planilhas selecionadas ou em todo o workbook. Siga os passos abaixo.

1. Selecione o intervalo do qual deseja remover todas as linhas ocultas ou visíveis.

Notas:

2. Clique em "Kutools" > "Excluir" > "Excluir Linhas (Colunas) Ocultas (Visíveis)".

3. Na caixa de diálogo "Excluir Linhas (Colunas) Ocultas (Visíveis)", configure conforme abaixo.

4. Em seguida, todas as linhas visíveis ou ocultas são excluídas de uma vez. Ao mesmo tempo, uma caixa de diálogo aparecerá informando o número de linhas excluídas. Clique em "OK" para concluir toda a operação.

6. Filtrar em várias planilhas

Normalmente, é fácil filtrar dados em uma planilha. Nesta seção, você aprenderá como filtrar dados usando os mesmos critérios em várias planilhas que possuem estrutura de dados comum.

Supondo que um workbook contenha três planilhas como mostrado na imagem abaixo e você queira filtrar dados nessas três planilhas ao mesmo tempo com o mesmo critério “Produto = KTE”, o código VBA abaixo pode ajudar.

1. Pressione as teclas "Alt" + "F11" para abrir a janela "Microsoft Visual Basic for Applications".

2. Na janela "Microsoft Visual Basic for Applications", clique em "Inserir" > "Módulo" e copie o código VBA abaixo para a janela do módulo.

Código VBA: Filtrar dados em várias planilhas ao mesmo tempo

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Nota: A linha “Xws.Range(“A1”.AutoFilter1, “=KTE”)” no código indica que você filtrará dados na coluna A com o critério =KTE, e o número1 é o número da coluna A. Você pode alterá-los conforme necessário. Por exemplo, para filtrar todos os números maiores que500 na coluna B, altere essa linha para “Xws.Range(“B1”.AutoFilter2, “>500”)”.

3. Pressione a tecla "F5" para executar o código.

As colunas especificadas agora são filtradas simultaneamente em todas as planilhas do workbook atual. Veja os resultados abaixo.

7. Reaplicar filtro após alterar dados

Às vezes, você pode ter feito alterações em um intervalo filtrado. No entanto, o resultado do filtro permanece o mesmo, independentemente das alterações feitas no intervalo (veja a imagem abaixo). Esta seção mostra dois métodos para reaplicar manual ou automaticamente o filtro no intervalo atual, incluindo as alterações feitas.

Reaplicar filtro manualmente com o comando Reaplicar

O Excel possui o recurso interno "Reaplicar" para reaplicar manualmente o filtro. Siga os passos abaixo.

Clique em "Dados" > "Reaplicar" para reaplicar o filtro na planilha atual.

Em seguida, você verá que o intervalo filtrado foi reaplicado para incluir as alterações feitas.

Reaplicar filtro automaticamente com código VBA

Se a lista filtrada precisar ser alterada com frequência, será necessário clicar repetidamente para aplicar o recurso Reaplicar. Aqui fornecemos um código VBA para reaplicar automaticamente um filtro em tempo real quando os dados forem alterados.

1. Na planilha que contém o filtro que deseja reaplicar automaticamente, clique com o botão direito na guia da planilha e selecione "Exibir Código".

2. Na janela "Microsoft Visual Basic for Applications" que abrir, copie o código VBA abaixo para a janela de código.

Código VBA: Reaplicar filtro automaticamente ao alterar dados

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Nota: No código, “Sheet2” é o nome da planilha atual. Você pode alterá-lo conforme necessário.

3. Pressione as teclas "Alt" + "Q" para fechar a janela "Microsoft Visual Basic for Applications".

A partir de agora, ao alterar dados na lista filtrada, o resultado filtrado será ajustado dinamicamente. Veja a animação abaixo.

8. Limpar ou remover filtro

Aprendemos como adicionar, aplicar e usar o filtro acima. Agora vamos aprender como limpar ou remover o filtro no Excel.

8.1 Limpar filtro de uma coluna

Após aplicar o filtro em uma coluna, se quiser limpá-lo, clique no ícone de filtro e depois em "Limpar Filtro de “Nome do Cabeçalho”" no menu suspenso.

8.2 Limpar todos os filtros de uma planilha

Se aplicou filtro em várias colunas e deseja limpar todos ao mesmo tempo, clique em "Dados" > "Limpar".

Em seguida, todos os filtros são limpos, conforme mostrado na imagem abaixo.

8.3 Limpar filtros de todas as planilhas no workbook atual

Suponha que você tenha aplicado filtros em várias planilhas de um workbook e queira limpar todos esses filtros de uma vez. O código VBA abaixo pode ajudar.

1. Abra o workbook do qual deseja limpar todos os filtros e pressione as teclas "Alt" + "F11" simultaneamente.

2. Na janela "Microsoft Visual Basic for Applications" que abrir, clique em "Inserir" > "Módulo" e copie o código VBA abaixo para a janela do módulo.

Código VBA: Limpar filtros de todas as planilhas no workbook atual

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Pressione a tecla "F5" para executar o código. Em seguida, todos os filtros são limpos de todas as planilhas do workbook atual.

8.4 Remover todos os filtros de uma planilha

Os métodos acima apenas limpam o status do filtro, mas o recurso de filtro permanece ativo na planilha. Se quiser remover todos os filtros de uma planilha, experimente os métodos abaixo.

Remover todos os filtros de uma planilha desativando o Filtro

Clique em "Dados" > "Filtro" para desativar o recurso (o botão Filtro não estará mais destacado).

Remover todos os filtros de uma planilha com atalho

Além disso, você pode usar o atalho para remover todos os filtros de uma planilha.

Na planilha que contém os filtros que deseja remover, pressione as teclas "Ctrl" + "Shift" + "L" simultaneamente.

Todos os filtros da planilha atual são removidos imediatamente.