Formatação Condicional com Fórmula: 7 Exemplos Práticos

agosto 18, 2021agosto 18, 2021 admin 0 comentários

Você sabia que é possível usar a formatação condicional com fórmula no Excel? Se ainda não conhece essa possibilidade e deseja aprender um pouco mais deste recurso, então este artigo é para você!

Certamente você já utilizou a formatação condicional em suas planilhas. Seja para formatar um valor negativo em vermelho, destacar valores duplicados, entre outros.

O Excel fornece ferramentas de formatação que permitem adicionar formatos rápidos de acordo com a necessidade do usuário. No entanto, se você deseja formatar alguma célula de acordo com alguma condição específica, o ideal é usar a opção de formatação condicional com fórmulas.

E para que você possa aprender um pouco mais sobre esta opção é só conferir o artigo de hoje, onde separamos alguns exemplos simples didáticos.

Quando usar formatação condicional com fórmula?

No Excel, as formatações condicionais predefinidas, tem como objetivo principal, formatar células com base em seus próprios valores. Mas se você precisa aplicar uma formatação condicional com base em outra célula ou formatar uma linha inteira com base no valor de uma única célula, será necessário usar as fórmulas do Excel dentro da formatação.

Para entender como este tipo de formatação funciona, confira os exemplos a seguir.

Exemplo 1: Como criar uma regra para formatação condicional com fórmula

No primeiro exemplo, vamos criar uma regra simples para que você possa compreender como este tipo de formatação funciona.

Para este exemplo, vamos usar uma tabela contendo valores de 1 a 15. Agora, suponha que precisamos formatar os valores que sejam maiores que 5. Para este tipo de formatação, você poderá usar as formatações condicionais predefinidas do Excel, mas neste exemplo, vamos usar as fórmulas para entender como ela funciona.

Confira o passo a passo:

1. Primeiro selecione todos os valores da tabela que você deseja formatar e vá até a guia Página Inicial > Formatação Condicional > Nova Regra:

2. Note que a janela de “Nova Regra de Formatação” irá abrir, nela você irá selecionar a opção “Usar uma fórmula para determinar quais células devem ser formatadas”:

No campo, “Formatar valores em que está fórmula é verdadeira”, insira a fórmula, que neste caso, é =A2>5 e formate conforme desejar (escolhendo a cor da fonte, fundo, entre outros) clicando no botão “Formatar”, confira a imagem:

Note que, estamos dizendo para o Excel, formatar todos os valores que forem maiores do que 5, através da fórmula, confira o resultado:

Exemplo 2: Fórmulas para comparar valores (números e texto)

Agora que você já sabe como e onde criar as suas fórmulas para formatação condicional, podemos ir para um exemplo mais prático onde queremos comparar valores de duas células.

Neste exemplo, vamos usar uma tabela contendo alguns itens, a quantidade em estoque e as regiões onde o produto é vendido. Deste modo, precisamos que o Excel destaque a linha onde a quantidade em estoque seja maior que 5 e o produto seja comercializado em todo o Brasil.

Para isso, vamos precisar utilizar a função E em nossa fórmula.

1. O primeiro passo, será selecionar os dados a serem formatados na tabela. Vá até Página Inicial > Formatação Condicional > Nova Regra > Selecione a opção Usar uma fórmula para determinar quais células devem ser formatas

2. Na opção “Usar uma fórmula para determinar quais células devem ser formatadas”, insira a seguinte fórmula:

=E($B2>0;$C2=”Brasil”)

Esta fórmula irá formatar as células se o valor na coluna B for maior do que 5 e se o valor na coluna C for igual à Brasil. Sendo assim, se ambos os critérios forem verdadeiros, a linha será formatada. Confira a imagem abaixo:

3. Após inserir a fórmula e escolher a formatação desejada clique em Ok e confira o resultado:

Note que foram pintadas apenas as linhas que atenderam aos dois critérios inseridos na fórmula, ou seja, o estoque ser maior que 5 e o produto ser vendido para o Brasil.

Exemplo 3: Formatar células com a função DIA DA SEMANA

Neste exemplo, vamos usar uma tabela com algumas datas e queremos que o Excel marque de vermelho todas as datas que forem referentes aos finais de semana. Deste modo, toda data que cair em um sábado ou domingo, será destacada em vermelho.

1. O primeiro passo será selecionar todos os dados a serem formatados (neste exemplo será o intervalo A2:A16).

2. Vá até Página Inicial > Formatação Condicional > Nova Regra > Selecione a opção Usar uma fórmula para determinar quais células devem ser formatas, veja a imagem abaixo:

3. No campo “Formatar valores em que esta fórmula é verdadeira”, insira a seguinte fórmula:

=DIA.DA.SEMANA($A2;2)>5

4. Formate com a cor vermelha e clique em OK.

No exemplo acima, nós usamos a função DIA.DA.SEMAMA para retornar um valor entre 1 e 7 correspondente ao dia da semana. Então, construímos um teste com essa função para saber se o dia é final de semana ou não.

Para entender melhor, coloque a função em uma coluna auxiliar e observe os resultados que a função irá trazer:

Note que a função, irá mostrar como VERDADEIRO todas as datas correspondentes ao sábado e domingo. A formatação condicional com fórmula funciona da mesma maneira.

Importante: Note que há apenas um $ na fórmula após a coluna A. Isso porque, a condição deve sempre “ler” o valor do dia. Para mais detalhes, confira o artigo sobre Referências de células: Relativas, Absolutas e Mistas para saber como usar o $ em uma fórmula.

Exemplo 4: Destacar linhas que contenham determinada informação

Utilizando a função LOCALIZAR na formatação condicional você poderá destacar linhas que contenham determinada palavra. Esta função faz a busca da correspondência parcial, não diferenciando letras maiúsculas de minúsculas.

Por exemplo, vamos usar a fórmula, para formatar linhas que contenham a palavra “São Paulo”. Neste caso, usaremos o exemplo da tabela contendo os Produtos, Estoque e Regiões. Conforme imagem abaixo:

1. Selecione as informações a serem formatadas e vá até a opção de formatação com fórmulas, no campo de fórmula, insira:

=LOCALIZAR(“São Paulo”;$C2)>0

2. Feito isso, formate da cor desejada e está pronto:

Exemplo 5: Formatação condicional com a função SE

A função SE também é muito útil quando se pretende formatar determina célula, linha ou coluna. E neste exemplo, vamos usá-la para formatar o status de um checklist.

Neste caso, imagine que você tenha uma tabela com as suas tarefas diárias e o status de cada uma. O que queremos é que quando as tarefas forem marcadas como concluídas, então a coluna com o status seja colorida.

Para realizar esta formatação, basta selecionar as informações a serem formatadas e inserir na barra de fórmulas da formatação condicional, a seguinte fórmula:

=SE(CONT.VALORES($B$2:$B$6)=CONT.VALORES($A$2:$A$6);VERDADEIRO;FALSO)

Aqui, usamos a função SE para criar uma condição, e a condição é que quando a contagem de “Concluída” no intervalo B2:B6 for igual à contagem de tarefas no intervalo A32:A6, então toda a coluna de status será formatada. Confira o exemplo:

Observe que a coluna será colorida, apenas quando todos os status estiverem concluídos.

Exemplo 6: Formatação condicional usando várias condições

Em alguns casos, é preciso levar em consideração várias condições para a formatação. Sendo assim, apenas após todas as condições ou uma das condições serem atendidas é que a formatação condicional será aplicada.

Para entendermos melhor, vamos usar uma tabela com os meses do ano e as temperaturas. Na tabela, queremos usar a formatação condicional para colorir as temperaturas se elas forem maiores ou iguais a 28ºC e menores que 34ºC. Confira o exemplo:

1. Primeiro vamos selecionar a coluna de temperatura, ir até a opção opção “Usar uma fórmula para determinar quais células devem ser formatas” e inserir a fórmula:

=SE(E(B2>=28;B2<34);VERDADEIRO;FALSO)

Nesta fórmula estamos basicamente, dizendo para que sejam formatadas apenas as células que tiverem valores maiores ou iguais a 28 e menores que 34. Confira o resultado:

Exemplo 7: Criando checklist com formatação condicional

Neste último exemplo, vamos criar um checklist, onde precisamos que quando marcarmos a tarefa como feita, a tarefa seja sublinhada. Para isto, o primeiro passo será criar as caixas de seleção.

1. Vá até a guia Desenvolvedor > Inserir > Caixa de Seleção:

2. Insira as caixinhas em todos os itens, lembre-se que você deve vinculá-la à alguma célula, no caso escolhemos as células onde elas estão, coluna B, para o vínculo não aparecer, formatamos a fonte de branco.

Caso tenha dúvidas em como inserir as caixas de seleção recomendamos a leitura deste artigo: Caixa de Seleção no Excel: Como criar?

3. Em seguida basta selecionar as células onde colocaremos a formatação, no exemplo, escolhemos as células A2:A6. Vá até o item “Usar uma fórmula para determinar quais células devem ser formatas” e insira a fórmula:

=SE(B2=VERDADEIRO;VERDADEIRO;FALSO)

4. Clique no botão “Formatar” e formate a fonte com a opção “Tachado” e clique em OK:

Na fórmula, basicamente estamos dizendo para o Excel, que se a caixa de seleção estiver seleciona, então será VERDADEIRO, neste caso, ele irá formatar o item correspondente na coluna A. Para que você possa compreender melhor, veja a imagem abaixo, onde os vínculos estão visíveis:

Para que as informações do vínculo não apareçam, basta deixar a fonte na cor branca, confira o resultado final:

Informações importantes sobre a formatação condicional com fórmulas

Em alguns momentos a sua regra de formatação condicional pode não funcionar conforme o esperado e o motivo para isso pode ser algum pequeno erro que não foi visto durante o processo. Neste caso, algumas causas comuns são:

1. A a referência de célula está sendo usada de maneira errada. Lembre-se de sempre usar referências relativas e absolutas no local adequado. Neste artigo você poderá conhecer mais sobre a referência de célula.

2. O intervalo está incorreto. Verifique se sua regra de formatação condicional se aplica ao intervalo correto de células. Sempre selecione todas as células ou linhas que deseja formatar, mas não inclua cabeçalhos.

3. Fórmulas muito grandes e complexas. Se você usou uma fórmula muito complexa e está dando erro. A dica é dividir as fórmulas em elementos simples e verifique cada função individualmente. Neste caso, vale também como dica, colocar a fórmula em uma célula do Excel para ver se ela irá trazer o resultado esperado.

Baixe aqui a planilha com os exemplos apresentados neste tutorial

Você também poderá gostar das seguintes dicas de Excel:

E então, o que achou da dica? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!