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 às condições específicas definidas. Definir essas condições pode ajudar a organizar sua planilha e facilitar a digitalização. 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 numérica aos dados.
O Excel possui opções internas para condições comumente usadas, como localizar números maiores ou menores que um valor específico ou localizar 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.
Aplicando 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 determinados 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 de precedência definida para determinar qual regra de formatação condicional aplicar aos dados.
Encontrando dados que excedem 25% e aumentam 50%
No exemplo a seguir, duas regras de formatação condicional personalizadas serão aplicadas ao intervalo de células B2 a B5.
- A primeira regra verifica se os dados em células A2:A5 são maiores que o valor correspondente em B2:B5 por mais de 25%.
- A segunda regra verifica se os mesmos dados em A2:A5 excedem o valor correspondente em B2:B5 em mais de 50%.
Como pode ser visto na imagem acima, se qualquer uma das condições acima for verdadeira, a cor de fundo da célula ou células no intervalo B1:B4 será alterada.
- 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 a caixa de diálogo Nova Regra de Formatação. Comece inserindo os dados da amostra em células A1 a C5 como visto na imagem acima.
Na parte final 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 significativo de 25% ou mais.
A função ficará assim:
=(A2-B2)/A2>25%
- Realçar células B2 a B5 na planilha.
- Clique na guia Início da ribbon.
- Clique no ícone Formatação Condicional na ribbon para abrir a lista suspensa.
-
Escolha Nova Regra para abrir a caixa de diálogo Nova Regra de Formatação.
- Em Selecione um tipo de regra, clique na última opção: Use uma fórmula para determinar quais células formatar.
- Digite a formula anotada acima no espaço abaixo Formatar valores onde esta fórmula é verdadeira:
- Clique no botão Format para abrir a caixa de diálogo. Clique na guia Preencher e escolha uma cor.
- Clique em OK para fechar as caixas de diálogo e retornar à planilha.
- A cor de fundo das células B3 e B5 deve mudar para a cor selecionada.
Agora, aplicaremos a formatação condicional para encontrar um aumento de 50% ou mais. A fórmula ficará assim:
- Repita os primeiros cinco passos acima.
- Digite a formula fornecida acima no espaço abaixo Formatar valores onde esta fórmula é verdadeira:
- Clique no botão Format para abrir a caixa de diálogo. Clique na guia Preencher e escolha uma cor diferente da que você escolheu no conjunto de etapas anterior.
- Clique em OK para fechar as caixas de diálogo e retornar à planilha.
A cor de fundo da célula B3 deve permanecer a mesma, indicando que a diferença percentual entre os números nas células A3 eB3 é maior que 25 por cento, mas menor ou igual a 50 por cento. A cor de fundo da célula B5 deve mudar para a nova cor selecionada, indicando que a diferença percentual entre os números nas células A5 e B5 é maior que 50%.
Verificando as 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 calcularão a diferença percentual exata entre os números nos intervalosA2:A5 e B2:B5.
A fórmula na célula C2 é assim:
=(A2-B2)/A2
- Clique na célula C2 para torná-la a célula ativa.
- Digite a fórmula acima e pressione a tecla Enter no teclado.
- A resposta 10% deve aparecer na célula C2, indicando que o número na célula A2 é 10% maior que o número na célula B2.
- Pode ser necessário alterar a formatação na célula C2 para exibir a resposta em porcentagem.
- 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 a C5 devem ser 30%, 25% e 60%.
As respostas nestas 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 25 por cento, e nossa regra de formatação condicional especificou que uma porcentagem maior que 25 por cento era necessária para a mudança da cor do plano de fundo.
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 estão em conflito. Regras conflitantes são aquelas em que as opções de formatação não podem ser aplicadas aos mesmos dados.
No nosso exemplo, as regras entram em conflito, pois ambas usam a mesma opção de formatação - alterando a cor do plano de fundo da célula.
Na situação em que a segunda regra é verdadeira (a diferença de valor é maior que 50 por cento entre duas células), então a primeira regra (a diferença de valor é maior que 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.
Como mostrado na imagem acima, a segunda regra usada neste tutorial é mais alta na lista e, portanto, tem precedência sobre a primeira regra. Como resultado, a cor de fundo da célula B5 é verde.
Por padrão, novas regras vão para o topo da lista; para alterar a ordem, use os botões de seta Para cima e Para baixo 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 formatar 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 conflito, pois ambos os formatos podem ser aplicados sem interferir 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.
