Formatação condicional no Excel: instruções e uso

Use estas instruções para criar uma variedade de formatos

A formatação condicional no Microsoft Excel é uma ferramenta útil para destacar tabelas, relatórios ou células, destacar uma área ou várias colunas, analisar dados, esclarecer diferenças ou identificar tendências. Barras de dados, conjuntos de símbolos ou escalas coloridas são usados para isso. Dependendo das condições especificadas, a exibição das células é alterada ou o intervalo de células é formatado. Você pode criar o seu próprio para as inúmeras condições integradas.

A formatação condicional faz sentido e ajuda você

A formatação condicional no Excel pode ser aplicada de várias maneiras, seja em uma tabela do Excel, um intervalo específico de células ou um relatório de tabela dinâmica. Várias ações e modelos de formato estão disponíveis para você. A vantagem decisiva para usuários do Excel é que valores ou resultados especiais podem ser destacados por codificação de cores. Se um valor mudar, a formatação é ajustada automaticamente - dependendo das condições.

informação: A Tabela Dinâmica ("Tabela Dinâmica") é uma tabela especial com a qual uma grande quantidade de dados pode ser calculada, resumida e analisada. Além disso, você pode fazer comparações e identificar tendências nos dados do exame rapidamente.

Formatação condicional com regras existentes para números, conteúdo de texto ou datas

Se você formatar com um operador de comparação, as células podem ser encontradas mais facilmente. Você pode tornar os valores mais visíveis, por exemplo, maiores ou menores do que um valor definido ou certos valores que se encontram entre um limite superior e inferior. Isso significa que os dados desejados ou pesquisados são exibidos em cores. A formatação rápida ou a formatação avançada estão disponíveis para edição. Com a formatação estendida, você pode trabalhar com fórmulas, por exemplo, para marcar automaticamente uma coluna do Excel em cores.

Exemplos:

• Um funcionário do depósito gostaria de destacar todos os estoques com menos de 15 peças com um fundo colorido.
• Todas as cidades com mais de 150.000 habitantes devem ser marcadas em uma tabela.
• Uma empresa de manufatura deseja destacar em vermelho todos os produtos fabricados entre 1º de janeiro de 2022-2023 e 31 de janeiro de 2022-2023.

Formatação rápida:

Seleção de uma ou mais células.

Clique em “Iniciar” na guia.

Vá para a seta ao lado do botão "Formatação condicional" em "Estilo".

Clique em "Regras para destacar células".

Selecione o comando desejado, por exemplo B. maior que, menor que, entre, data, etc.

Insira os valores e selecione o formato.

Formatação estendida:

1. Selecione uma ou mais células em uma tabela ou relatório de tabela dinâmica.

2. Vá para a guia "Página inicial", no grupo Estilos, clique na seta ao lado de "Formatação condicional".

3. Clique em "Gerenciar regras": A caixa de diálogo "Gerenciador de regras de formatação condicional" é exibida.

4. Selecionar ação:

   • Clique em "Nova Regra": A caixa de diálogo "Nova Regra de Formatação" é exibida.
   • Para alterar um formato, certifique-se de que a planilha ou tabela correta esteja selecionada na caixa de listagem "Mostrar regra de formatação para".
   • No campo "Aplica-se a", vá para "Recolher caixa de diálogo"; em seguida, selecione o novo intervalo de células na planilha.
   • Clique no botão "Expandir caixa de diálogo".
   • Selecione a regra e clique em "Editar regra": A caixa de diálogo "Editar regra de formatação" é exibida.

5. Para alterar o intervalo de campos na área de valor "Aplicar regra a" de um relatório de tabela dinâmica, faça o seguinte:

   • Seleção: Clique em "Apenas estas células"
   • Campo correspondente: Clique em "Todas as células com os mesmos campos"
   • Campo de valor: Clique em "Todas as células"

6. Em "Selecionar tipo de regra", clique em "Formatar apenas células que contenham"

Agora você pode executar ações em "Editar descrição da regra" no campo da lista "Formatar células apenas com":

Formato por número, data ou hora:

Para fazer isso, selecione um valor de célula e operador de comparação e insira o número, data ou hora. exemplo: Para "Entre", insira 300 e 500 ou selecione "Igual a" e insira uma data.

Você pode usar uma fórmula para obter um número, data ou valor de tempo de volta.

Formato por texto:

Selecione "Texto específico" e "Operador relacional".

Insira o texto, por exemplo B. em "contém" a palavra "ouro" ou em "começa com" um prefixo que você deseja pesquisar.

Podem ser inseridos no máximo 255 caracteres.

Os valores podem ser retornados por meio de fórmulas. Para fazer isso, a fórmula deve começar com (=). Fórmulas inválidas não são formatadas. É aconselhável testar primeiro a fórmula para erros.

Formato por data:

• Selecione a data e a data de comparação, por exemplo B. Ontem, próxima semana ou este mês

Em seguida: Para especificar um formato, clique em "Formatar", a caixa de diálogo "Formatar células" aparecerá. Selecione o formato de número desejado, formato de preenchimento, moldura ou fonte, clique em "OK".

É possível escolher vários formatos. Você pode ver quais formatos foram selecionados no campo "Visualização".

Formatação condicional com bancos de dados, esquemas de cores e conjuntos de símbolos

Os conteúdos e valores nas células podem ser atualizados visualmente com bancos de dados, escalas de cores ou conjuntos de símbolos e tornados mais atraentes e claros. Se os valores em um intervalo de células devem ser comparados uns com os outros, isso é mais fácil com esta formatação condicional.

Formatar células com barras de dados

As barras de dados facilitam a comparação de células entre si. O valor na célula é indicado pelo comprimento da barra. Quanto mais alto for o valor, mais longa será a barra de dados, quanto mais baixo for o valor, mais curta será a barra. Isso permite que valores altos ou baixos sejam determinados rapidamente.

Formatação rápida:

Marque a área desejada (célula, tabela ou planilha inteira).

Na guia Página inicial, clique no botão Formatação condicional.

Vá em "barra de dados" e selecione um recheio.

Formatar células com engastes coloridos

As escalas de cores ajudam a classificar diferentes informações de dados com mais facilidade e podem comparar rapidamente seus conteúdos. Dependendo da tonalidade da cor, valores altos ou baixos são exibidos. Você pode formatar células com uma escala de 2 ou 3 cores.

Escala de 2 cores

Formatação rápida:

Selecione célula, tabela ou folha inteira.

Na guia Página inicial, clique em Formatação condicional.

Vá em "escala de cores" e clique no formato de escala de 2 cores desejado. A cor superior representa os valores mais altos, a cor inferior representa os valores mais baixos.

Escala de 3 cores

A terceira cor permite a visualização dos valores médios - além da representação colorida dos valores altos e baixos como na escala de 2 cores.

Formatação rápida:

Selecione célula, tabela ou folha inteira.

Na guia Página inicial, clique em Formatação condicional.

Vá em "escala de cores" e clique no formato de escala de 3 cores desejado. A cor superior representa os valores mais altos, a cor inferior representa os valores mais baixos.

Formatar células com conjuntos de símbolos

Com um conjunto de símbolos, os dados podem ser visualizados e classificados em categorias separadas. Um símbolo representa um determinado intervalo de valores. A formatação condicional no Excel oferece símbolos de diferentes cores: setas, pontos, cruzes, pontos de exclamação e muito mais.

Formatação rápida:

Selecione célula, tabela ou folha inteira.

Na guia Página inicial, clique em Formatação condicional.

Vá para "Conjuntos de símbolos" e selecione o conjunto de símbolos desejado

Crie você mesmo regras de formatação

Se você não conseguir encontrar uma opção adequada na formatação condicional oferecida pelo Excel, poderá criar facilmente regras individuais para formatação.

Marque o intervalo de células a ser formatado condicionalmente.

Vá para "Formatação condicional" na guia "Página inicial".

Selecione "Nova regra" e confirme com "OK".

Se você não conseguir encontrar a opção de que precisa para sua própria regra de formatação, uma fórmula lógica pode ajudar. Por exemplo, você pode usar uma fórmula para colorir automaticamente células do Excel assim que o conteúdo estiver disponível. Por outro lado, as células vazias podem ser formatadas condicionalmente.

Exemplo: Aplicar uma fórmula permite comparar dados fora do intervalo selecionado. Isso inclui até dados que estão em uma planilha diferente na mesma pasta de trabalho. Ou você pode comparar valores com um resultado retornado por uma função. A fórmula deve ser lógica e retornar "verdadeiro" ou "falso".

Ao realçar células diretamente na planilha, você pode inserir referências de células em uma fórmula. Estas são referências de células absolutas. No entanto, o Excel também pode ajustar as referências de célula para cada célula na área selecionada. Se desejar, selecione referências de células relativas.

Instruções passo a passo para criar uma regra

1. Na guia "Página inicial", grupo "Estilos", vá até a seta ao lado de "Formatação condicional" e clique em "Gerenciar regras". A caixa de diálogo Gerenciador de regras de formatação condicional é exibida.

2. As seguintes ações são possíveis:

   • Se você deseja adicionar um formato condicional, vá para "Nova regra".
   • Altere um formato condicional:
   • • Verifique se você está na planilha correta.
     • Primeiro, em Aplica-se a, clique em Recolher a caixa de diálogo. Em seguida, selecione o novo intervalo de células e vá para "Expandir caixa de diálogo".
     • Selecione a regra desejada, clique em "Editar regra". Aparece "Editar regra de formatação".

3. Em "Selecionar tipo de regra" clique em "Usar fórmula para determinar as células a serem formatadas".

   • Em "Editar descrição da regra", insira uma fórmula em "Formatar valores para os quais esta regra é verdadeira". A fórmula começa com (=) e deve retornar “verdadeiro” (1) ou “falso” (0).
   • Clique em Formatar para exibir a caixa de diálogo Formatar células.
   • Agora selecione o número, fonte, formato de preenchimento etc. que deve ser usado se o valor da célula for verdadeiro e confirme com "OK". Os formatos selecionados são exibidos em "Visualização".

Por exemplo, a fórmula CONT.SE retorna valores lógicos. Se esta fórmula for usada, valores duplicados podem ser determinados ou o conteúdo que ocorre em uma determinada frequência pode ser destacado em cores. Desta forma, itens exclusivos também podem ser encontrados e destacados em cores. Outras fórmulas que podem ser usadas para formatação condicional são

• REST: para que as cores das linhas alternadas possam ser definidas automaticamente
• IDÊNTICO: Com esta fórmula, os dados que você está procurando podem ser marcados em uma tabela do Excel que corresponde a um valor especificado
• ISERROR: marca automaticamente células vazias na tabela do Excel ou faz com que os valores de erro desapareçam da pasta de trabalho
• NÃO e ISNUMBER: formata todas as células do Excel que não contêm números
• MAX: o maior valor da célula é destacado na tabela
• MIN: permite a formatação condicional do menor valor na lista do Excel para que o mínimo possa ser visto de relance
• ESQUERDA: dependendo de uma sequência de caracteres no início, as células do Excel são formatadas automaticamente e recebem uma cor de texto uniforme
• GRANDE e ESQUERDA: permite que uma célula com novo conteúdo seja colorida
• DIREITO: as células podem ser formatadas com base no último caractere da string
• HOJE: Com isso, os compromissos podem ser formatados em cores usando o método do semáforo ou podem ser destacados os valores de data que estarão em breve atualizados
• MÊS ou ANO: permite dividir a lista do Excel em meses ou anos; Com YEAR, os valores da data do ano atual também podem ser marcados e os valores anuais podem ser destacados
• OU e WEEKDAY: destaca os valores de acordo com o dia da semana em cores

Exemplo de criação de uma regra para verificações AND e OR:

Para este exemplo, dois valores são usados para formatação condicional. Se a primeira regra não se aplica, a segunda regra se aplica automaticamente.

Primeira regra: Um comprador de imóvel residencial deseja verificar se a compra planejada de um imóvel está de acordo com seu orçamento. Ele pode trazer até € 80.000 de seus próprios fundos como entrada e servir uma prestação mensal de empréstimo de no máximo € 1.200. Se o adiantamento e a parcela do empréstimo atenderem a esses requisitos, as células correspondentes serão marcadas em verde.

Segunda regra: Se o adiantamento ou a prestação não atenderem aos requisitos, as células correspondentes serão marcadas em vermelho.

A fórmula para criar a primeira regra usando a cor verde é a seguinte:

= E (SE ($ B $ 4 <= 80000,1); SE (ABS ($ B $ 5) <= 1200,1))

A fórmula para criar a segunda regra usando a cor vermelha é a seguinte:

= OU (SE ($ B $ 4 = 1200,1))

Transferir e excluir formatação condicional

A formatação condicional pode ser copiada e aplicada a outros dados ou a novos dados na planilha ou excluída novamente.

Transferir formatação condicional

Para copiar a formatação condicional e colá-la em outras células ou intervalos de células, use o botão "Transferir formato".

Selecione as células com a formatação condicional que deseja copiar e colar em outras células e clique nelas.

Agora vá para "Iniciar" e depois "Formato de transferência". O cursor aparece como um pincel.

Você pode adicionar formatação condicional a uma célula ou intervalo de células movendo o pincel sobre a célula.

Com ESC você termina o uso do pincel.

Você ainda pode usar o pincel para inserir a formatação condicional clicando duas vezes em "Transferir Formato".

Como alternativa, a formatação condicional pode ser copiada usando os atalhos do teclado e colada em outra célula do Excel.

Limpar formatação condicional

É feita uma distinção entre um processo de exclusão em uma planilha e um processo de exclusão em um intervalo de células.

Apagar em uma planilha ou em toda a planilha

Vá para "Formatação condicional" na guia "Página inicial".

Em seguida, clique em "excluir regras"

Em seguida, clique em "Excluir regras na planilha inteira".

Como alternativa, você pode excluir a formatação condicional usando a caixa de diálogo "Selecionar conteúdo".

Excluir no intervalo de células

Realce as células que contêm a formatação condicional desejada.

Clique no botão "Lente de aumento da análise de leitura" (o botão está localizado na parte inferior direita ao lado dos dados selecionados).

Vá para "Excluir formato".

Encontre e exclua os mesmos formatos condicionais em toda a planilha

Clique na célula com o formato condicional apropriado.

Na guia "Página inicial", clique na seta ao lado de "Localizar e selecionar" e, em seguida, clique em "Selecionar conteúdo".

Clique em "Formatos condicionais".

Em "Verificação de dados", vá para "Mesmo" (isso selecionará todas as células com a mesma formatação condicional).

Na guia "Iniciar", vá em "Formatação condicional", depois em "Excluir regras" e "Excluir regras nas células selecionadas".

Formatação condicional no relatório de tabela dinâmica

Com um relatório de tabela dinâmica, os usuários podem resumir rapidamente grandes quantidades de dados. Intervalo de células, planilha do Excel ou relatório de tabela dinâmica - a formatação condicional funciona basicamente da mesma forma em qualquer lugar. Apenas no relatório de tabela dinâmica existem alguns recursos especiais que você deve conhecer:

• Alguns formatos condicionais não funcionam em campos no intervalo de valores de um relatório de tabela dinâmica.
• Se você alterar o layout do relatório de tabela dinâmica de alguma forma, a formatação condicional persistirá até que os campos nos dados subjacentes sejam removidos.
• A formatação condicional para campos no intervalo de valores pode ser baseada na hierarquia de dados. O intervalo dessa formatação condicional é determinado pelo próximo nível inferior ou próximo superior da hierarquia em linhas para uma ou mais colunas ou em colunas para uma ou mais linhas.
• O intervalo de formatação condicional para campos no intervalo de valores pode ser definido usando três métodos:

• • seleção
  • Campo correspondente
  • Campo de valor

O método padrão é a seleção.

Definição de área por seleção

Vá para "Aplicar regra de formatação a".

Clique na caixa de diálogo Nova regra de formatação ou na caixa de diálogo Editar regra de formatação.

Este método é adequado para uma série contígua de campos no intervalo de valores, por ex. B. levantamento de dados para uma região, bem como para uma série não contígua de campos na faixa de valor, como dados de levantamento para diferentes regiões em diferentes níveis da hierarquia de dados.

Definição da área através de um campo correspondente

Este método funciona se você tiver

• deseja evitar ter que selecionar muitos itens não relacionados
• deseja formatar condicionalmente vários campos no intervalo de valores para um nível de hierarquia de dados
• Deseja excluir resultados parciais

Definição de área através de um campo de valor

Este método funciona se você tiver

• deseja evitar ter que selecionar muitos itens não relacionados
• deseja formatar condicionalmente vários campos no intervalo de valores para todos os níveis de hierarquia
• Deseja incluir totais e resultados parciais.

Conclusão: a formatação condicional no Excel torna mais fácil controlar os dados

Se você deseja destacar determinados valores de dados em sua tabela do Excel em cores, a formatação condicional no Excel é ideal. Você tem duas opções para aplicar a formatação condicional. Você pode usar as regras fornecidas pelo Excel para destacar o conteúdo do texto, valores numéricos ou datas em cores. Ou você mesmo pode definir uma regra com várias condições sob as quais os valores relevantes devem ser marcados. Você pode comparar os dados de maneira fácil e eficaz usando barras de dados, barras de cores e conjuntos de símbolos. Se as opções de design não forem mais necessárias, você pode removê-las com alguns cliques.

Mais artigos sobre formatação condicional

• Como gerar setas automaticamente em listas do Excel
• Exibir valores de tempo negativos em tabelas do Excel
• Percentuais negativos de cor vermelha
• Reconhecer porcentagens negativas em um piscar de olhos
• Formate os números com um formato personalizado em cores
• Formatar células do Excel que não possuem o valor especificado
• Armazene células automaticamente com os formatos de moeda correspondentes
• Marque as células em cores usando uma função macro
• Use a formatação condicional e destaque os dados que são maiores ou iguais à terceira maior célula
• Valores de verdade reversa