A adição de formatação condicional no Excel permite aplicar diferentes opções de formatação a uma célula ou intervalo de células que atendem a condições específicas definidas por você. As opções de formatação só são aplicadas quando as células selecionadas atendem a essas condições definidas.
As opções de formatação que podem ser aplicadas incluem alterações de cor de fonte e de fundo, estilos de fonte, bordas de célula e adição de formatação de número aos dados.
Excel tem um número de opções internas para condições comumente usadas, como localizar números que são maiores ou menores que um determinado valor ou localizar números que estão acima ou abaixo do valor médio.
Além dessas opções predefinidas, também é possível criar regras de formatação condicional personalizadas usando fórmulas do Excel para testar as condições especificadas pelo usuário.
01 de 04Aplicando Múltiplas Regras no Excel
Mais de uma regra pode ser aplicada aos mesmos dados para testar condições diferentes. Por exemplo, os dados de orçamento podem ter condições definidas que aplicam alterações de formatação quando determinados níveis, como 50%, 75% e 100%, do orçamento total são gastos.
Em tais circunstâncias, o Excel primeiro determina se as várias regras entram em conflito e, em caso afirmativo, o programa segue uma ordem de precedência definida para determinar qual regra de formatação condicional é aplicada aos dados.
Encontrando dados que excedem 25% e 50% de aumento
No exemplo a seguir, duas regras de formatação condicional personalizadas serão aplicadas ao intervalo de células B2 para B5.
- A primeira regra verifica se os dados em células A2 para A5 é maior que o valor correspondente em B2 para B5 em mais de 25%.
- A segunda regra verifica se os mesmos dados A2: A5 excede 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 vai mudar.
- Para dados em que a diferença é maior que 25%, a cor do plano de fundo da célula mudará para verde.
- Se a diferença for maior que 50%, a cor do plano de fundo da célula mudará para vermelho.
As regras usadas para realizar essa tarefa serão inseridas usando a formatação condicional Nova regra de formatação caixa de diálogo. Comece inserindo os dados da amostra nas células A1 a C5, como visto na imagem acima
No terceiro passo do tutorial irá 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 para verificar a precisão das regras de formatação condicional.
02 de 04Definindo regras de formatação condicional
Como mencionado, as regras de formatação condicionais que verificam as duas condições serão inseridas usando a formatação condicional Nova regra de formatação caixa de diálogo. Vamos começar definindo nossa formatação condicional personalizada:
Formatação condicional para encontrar mais de 25% de aumento
= (A2-B2) / A2> 25%
- 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.
- De Estilo: caixa, selecione o Clássico opção.
- Na segunda lista suspensa, clique na última opçãoUse uma fórmula para determinar quais células devem ser formatadas.
- Digite o Fórmula fornecido acima no espaço fornecido.
- Clique no Formatar com: drop-down, em seguida, selecione o Preenchimento vermelho claro com texto vermelho escuro opção.
- Clique Está bem para fechar as caixas de diálogo e retornar à planilha.
- Neste ponto, a cor de fundo de células B3 e B5 deve ser verde.
Formatação condicional para encontrar mais de 50% de aumento
= (A2-B2) / A2> 50%
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.
- De Estilo: caixa, selecione o Clássico opção.
- Na segunda lista suspensa, clique na última opçãoUse uma fórmula para determinar quais células devem ser formatadas.
- Digite o Fórmula fornecido acima no espaço fornecido.
- Clique no Formatar com: drop-down, em seguida, selecione o Preenchimento verde claro com texto verde escuro opção.
- Clique Está bem para fechar as caixas de diálogo e retornar à planilha.
A cor de fundo de célula B3 ainda deve ser verde indicando que a diferença percentual entre os números em células A3 e B3 é maior que 25%, mas menor ou igual a 50%. A cor de fundo de célula B5 deve mudar para vermelho, indicando que a diferença percentual entre os números células A5 e B5 é maior que 50%.
03 de 04Verificando as regras de formatação condicional
Para verificar se as regras de formatação condicionais inseridas estão corretas, podemos inserir fórmulas nas células C2: C5 que calculará a diferença percentual exata entre os números nos intervalos A2: A5 e B2: B5.
= (A2-B2) / A2
- Clique em Célula C2 para torná-lo 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 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 de 30%, 25% e 60%.
As respostas nessas células mostram que as regras de formatação condicional criadas estão corretas, já que a diferença entre células A3 e B3 é maior que 25% e a diferença entre células A5 e B5 é maior que 50%.
Célula B4 não mudou de cor porque a diferença entre células A4 e B4 equivale a 25 por cento, e nossa regra de formatação condicional especificou que uma porcentagem maior que 25 por cento era necessária para que a cor do plano de fundo mudasse para verde.
04 de 04Ordem de Precedência para Formatação Condicional
Quando várias regras são aplicadas 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 escolhidas para cada regra não podem ser aplicadas aos mesmos dados.
No exemplo usado neste tutorial, as regras entram em conflito, pois ambas as regras usam a mesma opção de formatação - a de alterar a cor da célula de plano de fundo.
Na situação em que a segunda regra é verdadeira (a diferença no valor é maior que 50% entre duas células), a primeira regra (a diferença no valor sendo maior que 25%) também é verdadeira.
Ordem de Precedência do Excel
Como uma célula não pode ter um plano de fundo vermelho e verde ao mesmo tempo, o Excel precisa saber qual regra de formatação condicional deve ser aplicada.
Qual regra é aplicada é determinada pela ordem de precedência do Excel, que afirma que a regra que é mais alta na lista na caixa de diálogo Gerenciador de regras de formatação condicional tem precedência.
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 plano de fundo da célula B5 é alterada para vermelho.
Por padrão, novas regras são adicionadas ao topo da lista e, portanto, têm maior precedência. Para alterar a ordem de precedência, use o Para cima e para baixo botões de seta na caixa de diálogo, conforme identificado na imagem acima.
Aplicando Regras Não Conflitantes
Se duas ou mais regras de formatação condicionais não conflitarem, ambas serão aplicadas quando a condição que cada regra estiver 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 verde em vez de uma cor de plano de fundo verde, as duas regras de formatação condicional não entrariam em conflito, pois os dois formatos podem ser aplicados sem interferir no outro.
Como um resultado, célula B5 teria uma borda verde e uma cor de fundo vermelho, uma vez que a diferença entre os números em células A5 e B5 é maior que 25 e 50 por cento.
Formatação Condicional vs. Formatação Regular
No caso de conflitos entre as regras de formatação condicional e as opções de formatação aplicadas manualmente, a regra de formatação condicional sempre terá precedência e será aplicada em vez de quaisquer opções de formatação adicionadas manualmente.
Se uma cor de fundo amarela foi inicialmente aplicada células B2 para B5 no exemplo, uma vez que as regras de formatação condicionais foram adicionadas, somente células B2 e B4 ficaria amarelo. Porque as regras de formatação condicionais inseridas se aplicam a células B3 e B5, suas cores de fundo mudariam de amarelo para verde e vermelho, respectivamente.
