Usar Formatação Condicional no Excel (O Guia Final + Exemplos)

Formatação Condicional é uma das características mais simples mas poderosas das folhas de cálculo do Excel.

Como o nome sugere, pode usar a formatação condicional no Excel quando quiser realçar células que satisfaçam uma condição especificada.

Dá-lhe a capacidade de adicionar rapidamente uma camada de análise visual sobre o seu conjunto de dados. Pode criar mapas de calor, mostrar ícones de aumento/diminuição, bolhas de Harvey, e muito mais usando formatação condicional no Excel.

Usar formatação condicional no Excel (Exemplos)

Neste tutorial, mostrar-lhe-ei sete exemplos surpreendentes de uso de formatação condicional no Excel:

• Identificar rapidamente duplicados usando formatação condicional no Excel.
• Destacar Células com Valor Maior/Menos que um Número num Conjunto de Dados.
• Destacar Valores Top/Bottom 10 (ou 10%) num Conjunto de Dados.
• Destacar Erros/Borrão usando Formatação Condicional no Excel.
• Criar Mapas de Calor usando Formatação Condicional no Excel.
• Destacar Cada Nona Linha/Coluna usando Formatação Condicional.
• Pesquisar e Destacar usando Formatação Condicional no Excel.

1. Identificar rapidamente duplicados

Formatação condicional no Excel pode ser usada para identificar duplicados num conjunto de dados.

Aqui está como se pode fazer isto:

• Seleccionar o conjunto de dados no qual se pretende destacar duplicados./li>>li> Ir para casa -> Formatação condicional -> Destacar Regras de Células -> Valores Duplicados.
• Na caixa de diálogo Duplicar Valores, certifique-se de que Duplicar é seleccionado no menu da esquerda. Pode especificar o formato a ser aplicado utilizando o menu suspenso à direita. Existem alguns formatos existentes que pode usar, ou especificar o seu próprio formato usando a opção Formato Personalizado.
• Click OK.

Esta opção destacaria instantaneamente todas as células que têm um duplicado no conjunto de dados seleccionado. O seu conjunto de dados pode estar numa única coluna, múltiplas colunas, ou num intervalo não contíguo de células.

See Also: The Ultimate Guide to Find and Remove Duplicates in Excel.

2. Destacar células com valor maior/menos que um número

P>Pode usar a formatação condicional no Excel para destacar rapidamente células que contenham valores maiores/menos que um valor especificado. Por exemplo, destacar todas as células com valor de venda inferior a 100 milhões, ou destacar células com marcas inferiores ao limiar de passagem.

Aqui estão os passos para o fazer:

• Seleccionar todo o conjunto de dados.

li> Ir para casa -> Formatação condicional -> Destacar as Regras das Células -> Maior do que… / Less Than..

• Com base na opção que seleccionar (maior ou menor que), abriria uma caixa de diálogo. Digamos, selecciona a opção “Maior do que”. Na caixa de diálogo, introduza o número no campo da esquerda. A intenção é destacar as células que têm um número superior a este número especificado.

• Especificar o formato a ser aplicado às células que preenchem a condição, utilizando o drop-down à direita. Existem alguns formatos existentes que pode usar, ou especificar o seu próprio formato usando a opção Formato Personalizado.
• Click OK.

Isto destacaria instantaneamente todas as células com valores superiores a 5 num conjunto de dados.Nota: Se desejar destacar valores superiores a 5, deverá aplicar novamente a formatação condicional com o critério “Igual a”.

O mesmo processo pode ser seguido para destacar células com um valor inferior a um valor especificado.

3. Destacando Top/Bottom 10 (ou 10%)

Formatação condicional no Excel pode identificar rapidamente os 10 itens superiores ou 10% superiores a partir de um conjunto de dados. Isto pode ser útil em situações em que se pretenda ver rapidamente os candidatos de topo por pontuações ou valores de negócio de topo nos dados de vendas.

Simplesmente, também se pode identificar rapidamente os 10 itens de topo ou 10% de fundo num conjunto de dados.

Aqui estão os passos para o fazer:

• Selecionar todo o conjunto de dados.

li>Ir para casa -> Formatação condicional ->Regras superiores/inferiores -> 10 itens superiores (ou %) / 10 itens inferiores (ou %).

• Baseado no que seleccionar, abrirá a caixa de diálogo. Digamos que seleccionou Top 10 items, então abrirá uma caixa de diálogo como mostrado abaixo:
• Especifique o formato a ser aplicado às células que satisfazem a condição, utilizando o drop-down à direita. Existem alguns formatos existentes que pode usar, ou especificar o seu próprio formato usando a opção Formato Personalizado.
• Click OK.

Isto destacaria instantaneamente os 10 principais itens do conjunto de dados seleccionado. Note que isto funciona apenas para células que têm um valor numérico no conjunto de dados.

Também, se tiver menos de 10 células no conjunto de dados, e seleccionar as opções para destacar os 10 itens do Top 10/Bottom 10 Items, então todas as células seriam destacadas.

Aqui estão alguns exemplos de como a formatação condicional funcionaria:

h5>4. Destaque de erros/blanks

Se trabalhar com muitos dados numéricos e cálculos em Excel, saberá a importância de identificar e tratar as células que têm erros ou que estão em branco. Se estas células forem utilizadas em cálculos posteriores, poderá levar a resultados errados.

Formatação condicional em Excel pode ajudá-lo a identificar rapidamente e destacar células que têm erros ou que estão em branco.

P>Pomos um conjunto de dados como mostrado abaixo:



Este conjunto de dados tem uma célula em branco (A4) e erros (A5 e A6).

Existem passos para destacar as células que estão vazias ou com erros:

• Selecione o conjunto de dados no qual pretende destacar células em branco e células com erros.

li>Ir para Home -> Formatação condicional -> Nova regra.

• Na caixa de diálogo Nova regra de formatação seleccione Usar uma fórmula para determinar quais as células a formatar.
• Introduzir a seguinte fórmula no campo da secção ‘Editar a Descrição da Regra’:
  =OR(ISBLANK(A1),ISERROR(A1))
  • A fórmula acima verifica todas as células para duas condições – se está em branco ou não e se tem ou não um erro. Se alguma das condições for VERDADEIRA, devolve VERDADEIRA.
• Definir o formato que se pretende aplicar às células que estão em branco ou que têm erros. Para o fazer, clique no botão Formatar. Irá abrir a caixa de diálogo ‘Formatar células’, onde pode especificar o formato.
• Click Ok.

Isto destacaria instantaneamente todas as células que estão em branco ou que têm erros.

Nota: Não é necessário utilizar toda a gama A1:A7 na fórmula em formatação condicional. A fórmula acima mencionada utiliza apenas A1. Quando aplica esta fórmula a toda a gama, o Excel verifica uma célula de cada vez e ajusta a referência. Por exemplo, quando verifica A1, utiliza a fórmula =OR(ISBLANK(A1),ISERROR(A1)). Quando verifica a célula A2, utiliza então a fórmula =OR(ISBLANK(A2),ISERROR(A2)). Ajusta automaticamente a referência (uma vez que estas são referências relativas) dependendo da célula que está a ser analisada. Portanto, não é necessário escrever uma fórmula separada para cada célula. Excel é suficientemente inteligente para mudar a referência da célula por si só 🙂

See Also: Using IFERROR and ISERROR to handle errors in excel.

5. Criar Heat Maps

Um heat map é uma representação visual dos dados em que a cor representa o valor numa célula. Por exemplo, pode-se criar um mapa de calor onde uma célula com o valor mais elevado é colorida a verde e há uma mudança para a cor vermelha à medida que o valor diminui.

Algo como mostrado abaixo:

O conjunto de dados acima tem valores entre 1 e 100. As células são realçadas com base no valor nele contido. 100 obtém a cor verde, 1 obtém a cor vermelha.

Aqui estão os passos para criar mapas de calor usando formatação condicional no Excel.

• Selecione o conjunto de dados.
• Vá para casa -> Formatação condicional ->Escalas de cor, e escolha um dos esquemas de cor.

Assim que clicar no ícone do heatmap, aplicaria a formatação ao conjunto de dados. Há vários gradientes de cor que pode escolher. Se não estiver satisfeito com as opções de cor existentes, pode seleccionar mais regras e especificar a cor que pretende.

Nota: De modo semelhante, pode também aplicar os conjuntos de Bardo de Dados e Ícones.

6. Realce Cada Outra Linha/Coluna

P>Pode querer destacar linhas alternativas para aumentar a legibilidade dos dados.

Estas são chamadas linhas de zebra e podem ser especialmente úteis se estiver a imprimir os dados.

Agora há duas maneiras de criar estas linhas de zebra. A forma mais rápida é converter os seus dados tabulares numa Tabela Excel. Aplica automaticamente uma cor a linhas alternadas. Pode ler mais sobre isto aqui.

Outra forma é usando formatação condicional.

P>Ponha um conjunto de dados como mostrado abaixo:

p>Aqui estão os passos para destacar linhas alternativas usando formatação condicional no Excel.

• Selecione o conjunto de dados. No exemplo acima, seleccionar A2:C13 (que exclui o cabeçalho). Se desejar incluir também o cabeçalho, então seleccione todo o conjunto de dados.

li>Abrir a caixa de diálogo Formatação Condicional (Home-> Formatação Condicional> Nova Regra) .

• Na caixa de diálogo, seleccionar ‘Use a Formula to determine which cells to format’ caixa de diálogo.
• >Introduzir a seguinte fórmula no campo na secção ‘Editar a descrição da regra’:
  =ISODD(ROW())

• A fórmula acima verifica todas as células e se o número ROW de uma célula for ímpar, então devolve VERDADEIRO. O formato condicional especificado seria aplicado a todas as células que retornam TRUE.
• Configure o formato que pretende aplicar às células que estão em branco ou que têm erros. Para o fazer, clicar no botão Formatar. Irá abrir a caixa de diálogo ‘Formatar Células’, onde pode especificar o formato.
• Click OK.

É isso! As linhas alternativas no conjunto de dados serão realçadas.

Pode usar a mesma técnica em muitos casos. Tudo o que precisa de fazer é usar a fórmula relevante na formatação condicional. Aqui estão alguns exemplos:

• Luz forte alternar linhas pares: =ISEVEN(ROW())
• Highlight alternar adicionar linhas: =ISODD(ROW())
• Highlight a cada 3ª linha: =MOD(ROW(),3)=0

7. Pesquisar e destacar dados usando formatação condicional

Esta é uma utilização um pouco avançada da formatação condicional. Fá-lo-ia parecer uma estrela de rock do Excel.

P>Ponha um conjunto de dados como se mostra abaixo, com Nome do Produto, Representante de Vendas, e Geografia. A ideia é escrever uma string na célula C2, e se corresponder aos dados em qualquer célula(s), então isso deve ser realçado. Algo como mostrado abaixo:

Aqui estão os passos para criar esta funcionalidade de Pesquisa e Destaque:

• Selecionar o conjunto de dados.
• Ir para Home -> Formatação condicional -> Nova Regra (Atalho de Teclado – Alt + O + D).
• Na caixa de diálogo Nova regra de formatação, seleccione a opção ‘Usar uma fórmula para determinar quais as células a formatar’.
• Introduzir a seguinte fórmula no campo na secção ‘Editar a Descrição da Regra’:
  =AND($C$2<>””,$C$2=B5)
• Definir o formato que se pretende aplicar às células que estão em branco ou que têm erros. Para o fazer, clique no botão Formatar. Irá abrir a caixa de diálogo ‘Formatar células’, onde pode especificar o formato.
• Click OK.

É isso mesmo! Agora, quando introduzir qualquer coisa na célula C2 e carregar em enter, irá destacar todas as células correspondentes.

Como é que isto funciona?

A fórmula utilizada na formatação condicional avalia todas as células do conjunto de dados. Digamos que entra no Japão na célula C2. Agora o Excel avaliaria a fórmula para cada célula.

A fórmula retornaria VERDADEIRO para uma célula quando duas condições são cumpridas:

• Célula C2 não está vazia.
• O conteúdo da célula C2 corresponde exactamente ao conteúdo da célula no conjunto de dados.

Hence, todas as células que contêm o texto Japão são realçadas.

Download the Example File

P>Pode usar a mesma lógica, para criar variações tais como:

• Destacar a linha inteira em vez de uma célula.
• Realce mesmo quando há uma correspondência parcial.
• Realce as células/linhas à medida que escreve (dinâmico) .

Como remover formatação condicional no Excel

Após aplicação, a formatação condicional permanece no lugar, a menos que a remova manualmente. Como melhor prática, mantenha a formatação condicional aplicada apenas às células onde precisar.

P>Por ser volátil, pode levar a uma pasta de trabalho lenta do Excel.

Para remover a formatação condicional:

• Selecione as células das quais deseja remover a formatação condicional.
• Ir para Home -> Formatação Condicional -> Limpar Regras -> Limpar Regras das Células Seleccionadas.
  • Se desejar remover formatação condicional de toda a folha de trabalho, seleccione Clear Rules from Entire Sheet.

Coisas importantes a saber sobre Formatação condicional no Excel

• Formatação condicional em volátil. Pode levar a uma pasta de trabalho lenta. Utilize-a apenas quando necessário.
• Quando copia células de pasta que contêm formatação condicional, a formatação condicional também é copiada.
• Se aplicar várias regras no mesmo conjunto de células, todas as regras permanecem activas. No caso de qualquer sobreposição, é dada preferência à última regra aplicada. Pode, contudo, alterar a ordem alterando a ordem a partir da caixa de diálogo Gerir Regras.

Pode também gostar dos seguintes tutoriais do Excel:

• A Forma Correcta de Aplicar Formatação Condicional na Tabela Pivot.
• Encontrar e Remover Duplicações no Excel.
• O Guia Final para Utilizar Formatação Condicional nas Folhas do Google.