Como usar a formatação condicional no Excel

Neste artigo

Adicionar formatação condicional no Excel permite que você aplique diferentes opções de formatação a uma célula, ou intervalo de células, que atendam a condições específicas definidas por você. Definir essas condições pode ajudar a organizar sua planilha e torná-la mais fácil de digitalizar. As opções de formatação que você pode usar incluem alterações de fonte e cor de fundo, estilos de fonte, bordas de células e adição de formatação de número aos dados. O Excel tem opções internas para as condições comumente usadas, como encontrar números maiores ou menores que um determinado valor ou encontrar números acima ou abaixo do valor médio. Além dessas opções predefinidas, você também pode criar regras de formatação condicional personalizadas usando fórmulas do Excel. Estas instruções se aplicam ao Excel 2019, 2016, 2013, 2010 e Excel para Microsoft 365.

Aplicação de várias condições no Excel

Você pode aplicar mais de uma regra aos mesmos dados para testar diferentes condições. Por exemplo, os dados de orçamento podem ter condições definidas que aplicam alterações de formatação quando certos níveis de gastos são alcançados, como 50%, 75% e 100% do orçamento total.

Em tais circunstâncias, o Excel primeiro determina se as várias regras estão em conflito e, em caso afirmativo, o programa segue uma ordem definida de precedência para determinar qual regra de formatação condicional aplicar aos dados.

Encontrando Dados que Superam aumentos de 25% e 50%

No exemplo a seguir, duas regras de formatação condicional personalizada serão aplicadas ao intervalo de células B2 para B5.

A primeira regra verifica se os dados em células A2: A5 é maior do que o valor correspondente em B2: B5 em mais de 25%.
A segunda regra verifica se os mesmos dados em A2: A5 excede o valor correspondente em B2: B5 em mais de 50%.

Como pode ser visto na imagem acima, se alguma das condições acima for verdadeira, a cor de fundo da célula ou células no intervalo B1: B4 vai mudar.

Para dados em que a diferença é superior a 25%, a cor de fundo da célula mudará para verde.
Se a diferença for maior que 50%, a cor de fundo da célula mudará para vermelho.

As regras usadas para realizar esta tarefa serão inseridas usando o Nova regra de formatação caixa de diálogo. Comece inserindo os dados de amostra em células A1 para C5 como visto na imagem acima. Na final parte do tutorial, vamos adicionar fórmulas às células C2: C4 que mostram a diferença percentual exata entre os valores nas células A2: A5 e B2: B5; isso nos permitirá verificar a precisão das regras de formatação condicional.

Definindo regras de formatação condicional

Primeiro, aplicaremos a formatação condicional para encontrar um aumento de 25% ou mais significativo.

A função será semelhante a esta: = (A2-B2) / A2> 25%

type = “código”>

Realçar células B2 para B5 na planilha.
Clique no Aba inicial do fita.
Clique no Formatação condicional ícone no fita para abrir o menu suspenso.
Escolher Nova regra para abrir o Nova regra de formatação caixa de diálogo.
Sob Selecione um tipo de regra, clique na última opção: Use uma fórmula para determinar quais células formatar.
Digite o Fórmula anotado acima no espaço abaixo Formate os valores onde esta fórmula for verdadeira:
Clique no Formato botão para abrir a caixa de diálogo. Clique no Aba de preenchimento e escolha uma cor.
Clique OK para fechar as caixas de diálogo e retornar à planilha.
A cor de fundo de células B3 e B5 deve mudar para a cor que você selecionou.

Agora, aplicaremos a formatação condicional para encontrar um aumento de 50 por cento ou mais. A fórmula será semelhante a esta:

Repita as cinco primeiras etapas acima.
Digite o Fórmula fornecido acima no espaço abaixo Formate os valores onde esta fórmula for verdadeira:
Clique no Formato botão para abrir a caixa de diálogo. Clique no Aba de preenchimento e escolha uma cor diferente da que você escolheu no conjunto de etapas anterior.
Clique OK para fechar as caixas de diálogo e retornar à planilha.

A cor de fundo de célula B3 deve permanecer o mesmo, indicando que a diferença percentual entre os números em células A3 e B3 é maior que 25 por cento, mas menor ou igual a 50 por cento. A cor de fundo de célula B5 deve mudar para a nova cor que você selecionou, indicando que a diferença percentual entre os números em células A5 e B5 é maior que 50 por cento.

Verificando regras de formatação condicional

Para verificar se as regras de formatação condicional inseridas estão corretas, podemos inserir fórmulas nas células C2: C5 que irá calcular a diferença percentual exata entre os números nos intervalos A2: A5 e B2: B5.

A fórmula na célula C2 é semelhante a esta: = (A2-B2) / A2

type = “código”>

Clique em célula C2 para torná-la a célula ativa.
Digite a fórmula acima e pressione o botão Entrar tecla no teclado.
A resposta 10% deve aparecer em célula C2, indicando que o número em célula A2 é 10% maior do que o número em célula B2.
Pode ser necessário alterar a formatação em célula C2 para exibir a resposta como um percentual.
Use o alça de preenchimento para copiar a fórmula de célula C2 para células C3 para C5.
As respostas para células C3 para C5 deve ser 30%, 25% e 60%.

As respostas nessas células mostram que as regras de formatação condicional são precisas, pois a diferença entre células A3 e B3 é maior que 25 por cento, e a diferença entre células A5 e B5 é maior que 50 por cento.

Célula B4 não mudou de cor porque a diferença entre células A4 e B4 é igual a 25 por cento, e nossa regra de formatação condicional especifica que uma porcentagem maior que 25 por cento é necessária para que a cor do plano de fundo seja alterada.

Ordem de precedência para formatação condicional

Quando você aplica várias regras ao mesmo intervalo de dados, o Excel primeiro determina se as regras entram em conflito. Regras conflitantes são aquelas em que as opções de formatação não podem ser aplicadas aos mesmos dados.

Em nosso exemplo, as regras são conflitantes, pois ambas usam a mesma opção de formatação – alterando a cor da célula de fundo. Na situação em que a segunda regra é verdadeira (a diferença no valor é superior a 50 por cento entre duas células), então a primeira regra (a diferença no valor é superior a 25 por cento) também é verdadeira. Como uma célula não pode ter dois fundos de cores diferentes ao mesmo tempo, o Excel precisa saber qual regra de formatação condicional deve ser aplicada. A ordem de precedência do Excel indica que a regra mais alta na lista da caixa de diálogo Gerenciador de regras de formatação condicional é aplicada primeiro. Conforme mostrado na imagem acima, a segunda regra usada neste tutorial está no topo da lista e, portanto, tem precedência sobre a primeira regra. Como resultado, a cor de fundo de célula B5 é verde. Por padrão, as novas regras vão para o topo da lista; para alterar o pedido, use o Para cima e para baixo botões de seta na caixa de diálogo.

Aplicação de regras não conflitantes

Se duas ou mais regras de formatação condicional não entrarem em conflito, ambas serão aplicadas quando a condição que cada regra está testando se tornar verdadeira. Se a primeira regra de formatação condicional em nosso exemplo formatou o intervalo de células B2: B5 com uma borda laranja em vez de uma cor de fundo laranja, as duas regras de formatação condicional não entrariam em conflito, pois os dois formatos podem ser aplicados sem interferir um no outro.

Formatação Condicional vs. Formatação Regular

No caso de conflitos entre regras de formatação condicional e opções de formatação aplicadas manualmente, a regra de formatação condicional sempre tem precedência e será aplicada em vez de quaisquer opções de formatação adicionadas manualmente.