Formatação condicional no Excel: o guia definitivo com exemplos

A formatação condicional é um dos recursos mais simples, mas poderosos, das planilhas do Excel.

Como o nome sugere, você pode usar a formatação condicional no Excel quando quiser destacar células que atendem a uma condição especificada.

Ele oferece a capacidade de adicionar rapidamente uma camada de análise visual ao seu conjunto de dados. Você pode criar mapas de calor, mostrar ícones crescentes / decrescentes, bolhas Harvey e muito mais usando a formatação condicional no Excel.

Usando a formatação condicional no Excel (exemplos)

Neste tutorial, vou mostrar sete exemplos incríveis de uso de formatação condicional no Excel:

• Identifique Duplicados rapidamente usando a Formatação Condicional no Excel.
• Realce células com valor maior / menor que um número em um conjunto de dados.
• Destacando 10 valores superiores / inferiores (ou 10%) em um conjunto de dados.
• Destacando erros / espaços em branco usando a formatação condicional no Excel.
• Criação de mapas de calor usando formatação condicional no Excel.
• Destaque cada enésima linha / coluna usando a formatação condicional.
• Pesquise e destaque usando a Formatação Condicional no Excel.

1. Identifique Duplicados Rapidamente

A formatação condicional no Excel pode ser usada para identificar duplicatas em um conjunto de dados.

Aqui está como você pode fazer isso:

• Selecione o conjunto de dados no qual deseja destacar duplicatas.
• Vá para Home -> Formatação Condicional -> Regras de Destacamento de Células -> Valores Duplicados.
• Na caixa de diálogo Valores Duplicados, certifique-se de que Duplicar esteja selecionado no menu suspenso à esquerda. Você pode especificar o formato a ser aplicado usando o menu suspenso à direita. Existem alguns formatos que você pode usar ou especificar seu próprio formato usando a opção Formato personalizado.
• Clique OK.

Isso destacaria instantaneamente todas as células que têm uma duplicata no conjunto de dados selecionado. Seu conjunto de dados pode estar em uma única coluna, várias colunas ou em um intervalo não contíguo de células.

Veja também: O guia definitivo para localizar e remover duplicatas no Excel.

2. Destacar células com valor maior / menor que um número

Você pode usar a formatação condicional no Excel para destacar rapidamente as células que contêm valores maiores / menores do que um valor especificado. Por exemplo, destacando todas as células com valor de vendas inferior a 100 milhões ou destacando células com marcas menores que o limite de aprovação.

Aqui estão as etapas para fazer isso:

• Selecione todo o conjunto de dados.
• Vá para Home -> Formatação Condicional -> Regras de Destacamento de Células -> Maior que … / Menor que …
• Com base na opção selecionada (maior ou menor que), uma caixa de diálogo seria aberta. Digamos que você selecione a opção ‘Maior que’. Na caixa de diálogo, digite o número no campo à esquerda. A intenção é destacar as células que possuem um número maior do que o número especificado.

• Especifique o formato a ser aplicado às células que atendem à condição usando o menu suspenso à direita. Existem alguns formatos que você pode usar ou especificar seu próprio formato usando a opção Formato personalizado.
• Clique OK.

Isso destacaria instantaneamente todas as células com valores maiores que 5 em um conjunto de dados.Nota: Se você deseja destacar valores maiores que iguais a 5, você deve aplicar a formatação condicional novamente com o critério “Igual a”.

O mesmo processo pode ser seguido para destacar células com um valor menor que os valores especificados.

3. Destacando 10% superior / inferior (ou 10%)

A formatação condicional no Excel pode identificar rapidamente os 10 principais itens ou os 10% principais de um conjunto de dados. Isso pode ser útil em situações em que você deseja ver rapidamente os principais candidatos por pontuação ou valores de negócios principais nos dados de vendas.

Da mesma forma, você também pode identificar rapidamente os 10 itens inferiores ou os 10% inferiores em um conjunto de dados.

Aqui estão as etapas para fazer isso:

• Selecione todo o conjunto de dados.
• Vá para Página inicial -> Formatação condicional -> Regras superiores / inferiores -> 10 itens principais (ou%) / 10 itens inferiores (ou%).
• Com base no que você selecionar, a caixa de diálogo será aberta. Digamos que você selecionou os 10 itens principais e, em seguida, abriria uma caixa de diálogo conforme mostrado abaixo:
• Especifique o formato a ser aplicado às células que atendem à condição usando o menu suspenso à direita. Existem alguns formatos que você pode usar ou especificar seu próprio formato usando a opção Formato personalizado.
• Clique OK.

Isso destacaria instantaneamente os 10 principais itens no conjunto de dados selecionado. Observe que isso funciona apenas para células que contêm um valor numérico.

Além disso, se você tiver menos de 10 células no conjunto de dados e selecionar as opções para destacar os 10 itens principais / os 10 itens inferiores, todas as células serão destacadas.

Aqui estão alguns exemplos de como a formatação condicional funcionaria:

4. Destacando erros / espaços em branco

Se você trabalha com muitos dados numéricos e cálculos no Excel, sabe a importância de identificar e tratar células que têm erros ou estão em branco. Se essas células forem usadas em cálculos adicionais, isso pode levar a resultados errôneos.

A Formatação Condicional no Excel pode ajudá-lo a identificar e destacar rapidamente as células que contêm erros ou estão em branco.

Suponha que temos um conjunto de dados conforme mostrado abaixo:

Este conjunto de dados possui uma célula em branco (A4) e erros (A5 e A6).

Aqui estão as etapas para destacar as células que estão vazias ou contêm erros:

• Selecione o conjunto de dados no qual deseja destacar células em branco e células com erros.
• Vá para Home -> Formatação Condicional -> Nova Regra.
• Na caixa de diálogo Nova regra de formatação, selecione Usar uma fórmula para determinar quais células formatar.
• Insira a seguinte fórmula no campo da seção "Editar a descrição da regra":
  = OU (ISBLANK (A1), ISERROR (A1))
  • A fórmula acima verifica todas as células em busca de duas condições - se está em branco ou não e se tem um erro ou não. Se qualquer uma das condições for TRUE, ele retornará TRUE.
• Defina o formato que deseja aplicar às células em branco ou com erros. Para fazer isso, clique no botão Formatar. Isso abrirá a caixa de diálogo ‘Formatar células’, onde você pode especificar o formato.
• Clique OK.

Isso destacaria instantaneamente todas as células que estão em branco ou contêm erros.

Observação: Você não precisa usar todo o intervalo A1: A7 na fórmula na formatação condicional. A fórmula mencionada acima usa apenas A1. Quando você aplica esta fórmula a todo o intervalo, o Excel verifica uma célula por vez e ajusta a referência. Por exemplo, quando verifica A1, ele usa a fórmula = OR (ISBLANK (A1), ISERROR (A1)). Ao verificar a célula A2, ele usa a fórmula = OR (ISBLANK (A2), ISERROR (A2)). Ele ajusta automaticamente a referência (já que são referências relativas) dependendo de qual célula está sendo analisada. Portanto, você não precisa escrever uma fórmula separada para cada célula. O Excel é inteligente o suficiente para alterar a referência da célula por si só 🙂

Veja também: Usando IFERROR e ISERROR para lidar com erros no Excel.

5. Criação de mapas de calor

Um mapa de calor é uma representação visual dos dados em que a cor representa o valor em uma célula. Por exemplo, você pode criar um mapa de calor onde uma célula com o valor mais alto é colorida de verde e há uma mudança para a cor vermelha conforme o valor diminui.

Algo conforme mostrado abaixo:

O conjunto de dados acima tem valores entre 1 e 100. As células são destacadas com base no valor nele. 100 fica com a cor verde, 1 fica com a cor vermelha.

Aqui estão as etapas para criar mapas de calor usando formatação condicional no Excel.

• Selecione o conjunto de dados.
• Vá para Página inicial -> Formatação condicional -> Escalas de cores e escolha um dos esquemas de cores.

Assim que você clicar no ícone do mapa de calor, ele aplicará a formatação ao conjunto de dados. Existem vários gradientes de cor que você pode escolher. Se não estiver satisfeito com as opções de cores existentes, você pode selecionar mais regras e especificar a cor desejada.

Nota: De maneira semelhante, você também pode aplicar conjuntos de Bardo de Dados e Ícones.

6. Destaque todas as outras linhas / colunas

Você pode querer destacar linhas alternativas para aumentar a legibilidade dos dados.

Elas são chamadas de linhas zebra e podem ser especialmente úteis se você estiver imprimindo os dados.

Agora, existem duas maneiras de criar essas linhas de zebra. A maneira mais rápida é converter seus dados tabulares em uma tabela do Excel. Ele aplicou automaticamente uma cor a linhas alternadas. Você pode ler mais sobre isso aqui.

Outra maneira é usar a formatação condicional.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Aqui estão as etapas para destacar linhas alternativas usando a formatação condicional no Excel.

• Selecione o conjunto de dados. No exemplo acima, selecione A2: C13 (que exclui o cabeçalho). Se você quiser incluir o cabeçalho também, selecione todo o conjunto de dados.
• Abra a caixa de diálogo Formatação condicional (Página inicial-> Formatação condicional-> Nova regra) [Atalho de teclado - Alt + O + D].
• Na caixa de diálogo, selecione "Usar uma fórmula para determinar quais células formatar" na caixa de diálogo.
• Insira a seguinte fórmula no campo da seção "Editar a descrição da regra":
  = ISODD (LINHA ())

• A fórmula acima verifica todas as células e, se o número ROW de uma célula for ímpar, retorna VERDADEIRO. O formato condicional especificado seria aplicado a todas as células que retornassem TRUE.
• Defina o formato que deseja aplicar às células em branco ou com erros. Para fazer isso, clique no botão Formatar. Isso abrirá a caixa de diálogo ‘Formatar células’, onde você pode especificar o formato.
• Clique OK.

É isso! As linhas alternativas no conjunto de dados serão destacadas.

Você pode usar a mesma técnica em muitos casos. Tudo o que você precisa fazer é usar a fórmula relevante na formatação condicional. aqui estão alguns exemplos:

• Destacar linhas pares alternadas: = ISEVEN (ROW ())
• Destacar linhas de adição alternativas: = ISODD (ROW ())
• Destaque a cada 3ª linha: = MOD (ROW (), 3) = 0

7. Pesquise e destaque dados usando formatação condicional

Este é um uso um pouco avançado de formatação condicional. Isso faria você parecer uma estrela do rock do Excel.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo, com o nome dos produtos, representante de vendas e geografia. A ideia é digitar uma string na célula C2 e, se ela corresponder aos dados de alguma (s) célula (s), então isso deve ser destacado. Algo conforme mostrado abaixo:

Aqui estão as etapas para criar essa funcionalidade de pesquisa e destaque:

• Selecione o conjunto de dados.
• Vá para Home -> Formatação Condicional -> Nova Regra (Atalho de teclado - Alt + O + D).
• Na caixa de diálogo Nova regra de formatação, selecione a opção ‘Usar uma fórmula para determinar quais células formatar’.
• Insira a seguinte fórmula no campo da seção "Editar a descrição da regra":
  = E ($ C $ 2 ””, $ C $ 2 = B5)
• Defina o formato que deseja aplicar às células em branco ou com erros. Para fazer isso, clique no botão Formatar. Isso abrirá a caixa de diálogo ‘Formatar células’, onde você pode especificar o formato.
• Clique OK.

É isso! Agora, quando você inserir qualquer coisa na célula C2 e pressionar Enter, todas as células correspondentes serão destacadas.

Como é que isso funciona?

A fórmula usada na formatação condicional avalia todas as células no conjunto de dados. Digamos que você insira o Japão na célula C2. Agora o Excel avaliaria a fórmula para cada célula.

A fórmula retornaria TRUE para uma célula quando duas condições fossem atendidas:

• A célula C2 não está vazia.
• O conteúdo da célula C2 corresponde exatamente ao conteúdo da célula no conjunto de dados.

Portanto, todas as células que contêm o texto Japão são destacadas.

Baixe o arquivo de exemplo

Você pode usar a mesma lógica para criar variações como:

• Realce a linha inteira em vez de uma célula.
• Destaque mesmo quando houver uma correspondência parcial.
• Destaque as células / linhas conforme você digita (dinâmico) [Você vai adorar esse truque :)].

Como remover formatação condicional no Excel

Depois de aplicada, a formatação condicional permanece em vigor, a menos que você a remova manualmente. Como prática recomendada, mantenha a formatação condicional aplicada apenas às células em que você precisa dela.

Por ser volátil, pode levar a uma pasta de trabalho do Excel lenta.

Para remover a formatação condicional:

• Selecione as células das quais deseja remover a formatação condicional.
• Vá para Página inicial -> Formatação condicional -> Limpar regras -> Limpar regras das células selecionadas.
  • Se você deseja remover a formatação condicional de toda a planilha, selecione Limpar regras da planilha inteira.

Coisas importantes que você deve saber sobre a Formatação Condicional no Excel

• Formatação condicional em volátil. Isso pode levar a uma pasta de trabalho lenta. Use-o apenas quando necessário.
• Quando você copia células de colagem que contêm formatação condicional, a formatação condicional também é copiada.
• Se você aplicar várias regras no mesmo conjunto de células, todas as regras permanecerão ativas. Em caso de sobreposição, a regra aplicada por último tem preferência. Você pode, no entanto, alterar o pedido alterando-o na caixa de diálogo Gerenciar regras.