Formatação Condicional no Excel (Guia Completo com Exemplos)
Se você desejar aprender e dominar a ferramenta de Formatação Condicional no Excel, então esse guia é para você!
A formatação condicional é um dos recursos mais simples, porém poderosos, do Excel. E como o próprio nome sugere, você pode usar a formatação condicional no Excel quando desejar realçar células que atendam a uma condição específica.
Essa ferramenta permite adicionar rapidamente uma formatação em conjunto de dados para análise visual. Você pode criar mapas de calor, exibir ícones de aumento / redução e muito mais. Se interessou pelo assunto? Então confira os detalhes a seguir:
Neste tutorial, mostraremos sete exemplos incríveis de uso de formatação condicional no Excel:
- Destacando Rapidamente as Duplicatas
- Realce células com valor maior / menor que um número
- Realçar Primeiros/Últimos 10 (10%)
- Destacando com erros e vazias
- Criando mapas de calor
- Realçar linhas alternadas
- Pesquisar e destacar dados usando formatação condicional
1.Destacando Rapidamente as Duplicatas
A formatação condicional no Excel pode ser usada para identificar duplicatas em um conjunto de dados. Abaixo estão os passos para você poder fazer isso:
1. Selecione o conjunto de dados no qual você deseja destacar duplicatas.
2. Acesse a guia Página Inicial > Formatação Condicional > Destacar Regras da Célula > Valores Duplicados.
3. Na caixa de diálogo ‘Valores Duplicados’, verifique se a opção Duplicados está selecionada na lista suspensa à esquerda. Você pode especificar a formatação a ser aplicada usando a lista suspensa à direita. Existem algumas formatações existentes que você pode usar ou poderá criar sua própria formatação usando a opção Formato Personalizado.
4. Clique em OK para finalizar.
Isso irá aplicar uma formatação em todas as células que têm uma duplicata no conjunto de dados selecionado. Seu conjunto de dados pode estar em uma única coluna, em várias colunas ou em um intervalo aleatório de células.
Confira também: Como excluir valores repetidos no Excel
2. Realce 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 que um valor especificado. Por exemplo, destacar todas as células com valor de vendas inferior a R$100,00 ou destacar células com notas menores que o mínimo para aprovação.
Abaixo estão os passos para você fazer isso:
1. Selecione o conjunto de dados inteiro.
2. Acesse a guia Página Inicial > Formatação Condicional > Destacar Regras da Célula > É Maior que… / É Menor que…
3. Com base na opção selecionada (maior ou menor que), uma caixa de diálogo será aberta. Digamos que você selecionou a opção “Maior que”. Na caixa de diálogo, insira o número no campo à esquerda. A intenção é destacar as células que possuem um número maior que esse número especificado.
4. Clique em OK para finalizar.
Isso irá realçar todas as células com valores maiores que 5 em um conjunto de dados.
Observação: Se você deseja destacar valores maiores que igual a 5, aplique 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 um valor especificado.
3. Realçar Primeiros/Últimos 10 (10%)
A formatação condicional no Excel pode identificar rapidamente os 10 principais itens ou os 10% superiores de um conjunto de dados. Isso pode ser útil em situações em que você deseja ver rapidamente os principais candidatos por pontuações ou valores de transações principais nas vendas, por exemplo.
Da mesma forma, você também pode identificar rapidamente os 10 itens inferiores ou os últimos 10% em um conjunto de dados. Abaixo estão os passos para fazer isso:
1. Selecione o conjunto de dados inteiro.
2. Acesse a guia Página Inicial > Formatação Condicional > Regras de Primeiros/Últimos > 10 Primeiros Itens (ou %) / 10 Últimos Items (ou %).
2. Com base no que você selecionar, ele abrirá a caixa de diálogo. Digamos que você selecionou os 10 Primeiros Itens e, neste caso, o Excel abrirá uma caixa de diálogo, conforme mostrado abaixo:
3. Especifique a formatação a ser aplicada às células que atendem à condição usando a lista suspensa à direita. Existem alguns formatos existentes que você pode usar ou criar o seu próprio formato usando a opção Formato Personalizado.
4. Clique em OK para finalizar.
Isso irá realçar os 10 principais itens no conjunto de dados selecionado. Observe que isso funciona apenas para células que possuem 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 / 10 itens inferiores, todas as células serão realçadas.
Abaixo estão alguns exemplos de como a formatação condicional funcionaria para cada regra:
4. Destacando com erros e vazias
Se você trabalha com muitos dados numéricos e cálculos no Excel, você conhece a importância de identificar e tratar células com erros ou em células 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 realçar rapidamente as células com erros ou em branco.
Suponha que tenhamos um conjunto de dados conforme mostrado abaixo:
Este conjunto de dados tem uma célula em branco (A4) e erros (A5 e A6). Abaixo estão os passos para destacar as células vazias ou com erros:
1. Selecione o conjunto de dados no qual você deseja destacar células e células em branco com erros.
2. Acesse a guia Página Inicial > Formatação Condicional > Nova Regra.
3. Na caixa de diálogo Nova Regra de Formatação, selecione Usar uma fórmula para determinar quais células devem ser formatadas.
4. Digite a seguinte fórmula no campo na seção ‘Editar a descrição da regra’:
=OU(ÉCÉL.VAZIA(A1);ÉERROS(A1))
A fórmula acima verifica todas as células para duas condições – se está em branco ou não e se tem um erro ou não. Se alguma das condições for VERDADEIRO ela retornará VERDADEIRO.
Defina o formato que você deseja aplicar às células que estão em branco ou com erros. Para fazer isso, clique no botão Formatar. Ele abrirá a caixa de diálogo “Formatar Células”, na qual você poderá especificar o formato.
Clique em OK para finalizar.
Isso irá realçar todas as células que estão em branco ou apresentam erros.
Observação: Você não precisa usar todo o intervalo A1:A7 na fórmula na formatação condicional. A fórmula acima mencionada utiliza apenas A1. Quando você aplica essa fórmula a todo o intervalo, o Excel verifica uma célula por vez e ajusta a referência. Por exemplo, quando ele verifica A1, ele usa a fórmula =OU(ÉCÉL.VAZIA(A1);ÉERROS(A1)). Quando ele verifica a célula A2, ele usa a fórmula=OU(ÉCÉL.VAZIA(A1);ÉERROS(A2)). Ajusta automaticamente a referência (pois são referências relativas) dependendo de qual célula está sendo analisada. Então 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ó.
5. Criando mapas de calor
Um mapa de calor é uma representação visual de 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 em verde com uma mudança para a cor vermelha quando o valor diminui. Veja o exemplo mostrado abaixo:
O conjunto de dados acima possui valores entre 1 e 100. As células são destacadas com base no valor. 100 obtém a cor verde, 1 obtém a cor vermelha.
Abaixo estão as etapas para criar mapas de calor usando formatação condicional no Excel.
- Selecione o conjunto de dados.
- Acesse a guia Página Inicial > Formatação Condicional > Escalas de Cor 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 selecionado. Existem vários gradientes de cores que você pode escolher. Se você não estiver satisfeito com as opções de cores existentes, poderá selecionar a opção ‘Mais Regras…” e especificar a cor desejada.
Dica: De maneira semelhante, você também pode aplicar conjuntos de Barras de Dados e Conjuntos de Ícones
6. Realçar linhas alternadas
Se você pretende destacar linhas alternadas para aumentar a legibilidade dos dados, então a ferramenta de formatação condicional também pode te ajudar nessa tarefa. Esse efeito também é conhecido como linhas de zebra e pode ser muito útil se você for imprimir a sua planilha.
No Excel existem duas maneiras de criar essas linhas de zebra. A maneira mais rápida é converter seus dados tabulares em uma tabela do Excel e aplicar automaticamente uma cor para alternar linhas.
Outra maneira é usar a formatação condicional.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
A seguir estão as etapas para destacar linhas alternadas usando a formatação condicional no Excel.
1. Selecione o conjunto de dados. No exemplo acima, selecione A2:C13 (exclua o cabeçalho). Se você quiser incluir o cabeçalho também, selecione o conjunto de dados inteiro.
2. Acesse a guia Página Inicial > Formatação Condicional > Nova Regra…
3. Na caixa de diálogo, selecione a opção ‘Usar uma fórmula para determinar quais células serão formatadas‘.
4. Digite a seguinte fórmula no campo na seção “Formatar valores em que esta fórmula é verdadeira”:
=ÉIMPAR(LIN())
A fórmula acima verifica todas as células e, se o número de uma célula for ímpar, ela retornará VERDADEIRO. O formato condicional especificado seria aplicado a toda a célula que retornasse VERDADEIRO.
5. Clique em OK para finalizar.
Você pode usar a mesma técnica em muitos casos. Neste caso, tudo o que você precisa fazer é usar a fórmula relevante na formatação condicional. Abaixo estão alguns exemplos:
- Realce linhas pares alternadas: = ÉPAR(LIN())
- Realce linhas de adição alternativas: =ÉIMPAR(LIN())
- Destaque a cada 3ª linha: = MOD(LIN();3)=0
7. Pesquisar e destacar dados usando formatação condicional
Essa é uma dica um pouco avançada quando se trata de usar a formatação condicional no Excel.
Suponha que você tenha um conjunto de dados, conforme mostrado abaixo, com Nome dos produtos, Vendedor e Região. A ideia é digitar uma busca na célula C2 e, se ela corresponder aos dados em qualquer célula, isso deve ser destacado. Algo como mostrado abaixo:
A seguir estão as etapas para criar essa funcionalidade de pesquisa e destaque:
1. Selecione o conjunto de dados.
2. Acesse a guia Página Inicial > Formatação Condicional > Nova Regra…
3. Na caixa de diálogo, selecione a opção ‘Usar uma fórmula para determinar quais células serão formatadas‘.
4. Digite a seguinte fórmula no campo na seção “Formatar valores em que esta fórmula é verdadeira”:
=E($B$2<>””;$B$2=A5)
5. Defina uma formatação que você deseja aplicar às células que estão em branco ou com erros. Para fazer isso, clique no botão ‘Formatar’. Ele abrirá a caixa de diálogo “Formatar Células”, na qual você poderá especificar a formatação.
6. Clique em OK para finalizar.
Pronto! Agora, quando você digitar qualquer coisa na célula B2 e apertar enter, o Excel irá destacar todas as células correspondentes.
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 a palavras “Sul”na célula B2. Neste caso, o Excel avaliará a fórmula para cada célula.
A fórmula retornará VERDADEIRO para uma célula quando duas condições forem atendidas:
- A célula B2 não está vazia.
- O conteúdo da célula B2 corresponde exatamente ao conteúdo da célula na tabela.
Deste modo, todas as células que contém o texto “Sul” são destacadas.
Como remover formatação condicional no Excel
Uma vez 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.
Por ser volátil, ela pode deixar a sua pasta de trabalho do Excel lenta e até mesmo pesada.
Se você precisa remover a formatação condicional em uma planilha, siga os passos abaixo:
1. Selecione as células das quais você deseja remover a formatação condicional.
2. Acesse a guia Página Inicial > Formatação Condicional > Limpar Regras -> Limpar Regras das Células Selecionadas. Se você deseja remover a formatação condicional da planilha inteira, selecione Limpar regras da Planilha inteira.
Dicas importantes sobre formatação condicional no Excel
- A Formatação condicional é um recurso volátil, podendo levar a uma pasta de trabalho lenta. Use somente quando necessário.
- Quando você copia e cola as células que contêm formatação condicional, a formatação condicional também é copiada.
- Se você aplicar várias regras ao mesmo conjunto de células, todas as regras permanecerão ativas. No caso de qualquer sobreposição, a regra aplicada por último é dada preferência. Você pode, no entanto, alterar a ordem alterando a ordem na caixa de diálogo Gerenciar Regras.
Você também poderá gostar das seguintes dicas de Excel:
- 10 dicas simples para deixar suas planilhas mais bonitas
- 27 habilidades de Excel para impressionar seu chefe
- Tabela dinâmica no Excel – Um tutorial passo a passo
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!