Como usar formatação condicional

Se você já teve algum contato com o Excel e já conhece algumas de suas funcionalidades mais básicas (caso contrário veja o artigo sobre o básico do Excel clicando aqui), já deve ter montado uma planilha e mexido em cores, fontes, bordas dentre outras mudanças visuais para melhorar a aparência e apresentação de seus dados.

Mas que tal fazer estas alterações de forma automática com base no que é exibido na célula? É pra isso que existe a formatação condicional, que você vai aprender agora.

Existe uma série de formas diferentes de fazer essas configurações e como de costume vou começar pelas mais simples até as mais avançadas. Vamos a elas.

Para todos os exemplos

Todas as configurações possíveis para a formatação condicional são acessadas em um mesmo menu no Excel.

Este menu pode ser acessado na aba Página Inicial no menu principal do Excel e clicando no botão Formatação Condicional. Como mostra a imagem.

Para todos os exemplos vou utilizar a mesma tabela abaixo, que podem dizer que seja de um estudo estatístico onde foram feitos 3 testes com cada participante, e se obteve uma pontuação que poderia variar de 0 a 10 pontos.

Além disso temos alguns dados pessoais de cada um.

Formatação condicional pré-configurada

Existem algumas formatações condicionais que já vêm pré configuradas pelo Excel, o que acelera o trabalho e em muitos casos já da conta do recado sem que seja necessário perder um pouco mais de tempo configurando o modo que a formatação deve funcionar.

A primeira configuração que faremos é, pintar de amarelo todos os participantes que tem idade superior a 45 anos. O excel tem essa formatação condicional pronto.

O que temos que fazer é:

Selecionar a área que queremos colocar a formatação.
Clicar no botão formatação condicional.
Realçar regras de célula.
É maior do que…

Ao selecionar estas opções uma nova janela será aberta, onde você poderá editar algumas opções. Veja.

No primeiro campo inseri o valor 45 pois quero pintar todos aqueles que tem idade superior a este valor. E no segundo campo escolhi a opção amarelo. Antes mesmo de apertar o OK já é possivel ver as cores pintadas na planilhas.

Se alterarmos a idade de algum dos participantes automaticamente a cor da célula irá se adequar a condição de ser maior ou não do que 45.

Podemos colocar mais de uma formatação em um mesmo intervalo de células, para exemplificar agora vou utilizar outra formatação pré-pronta e pintar de vermelho aqueles que tem idade inferior a média de idades.

Tenho que:

Selecionar a área que queremos colocar a formatação.
Clicar no botão formatação condicional.
Regras de primeiros/últimos
Abaixo da média…

Uma janela semelhante a mostrada no exemplo anterior é exibida, contendo somente a opção de cor, selecionei a vermelha. Veja o resultado.

O Excel automaticamente calculou a média de idade e pintou de vermelho somente aqueles que estavam abaixo dessa média. qualquer alteração que for feita na base a média vai ser recalculada automaticamente e as células recoloridas.

O funcionamento das duas primeiras opções de formatação condicional a Realçar regra das células e a Regras de primeiros/últimos são bem semelhantes. Basicamente seleciona-se a área, escolhe a opção e se ajusta um parâmetro ou outro e está feito. Simples assim.

Os outros 3 grupos de formatação pré-pronta são ainda mais simples, pois não pedem nenhuma configuração extra. Basta selecionar a área e a formatação desejada e está pronto. O resultado é exibido só de passar o mouse sobre as opções. Veja.

Na imagem acima aqueles que apresentaram as maiores pontuações no 1º Teste apresentam uma barra colorida em azul proporcionalmente maior do que aqueles que tiveram um pontuação mais baixa.

Nesta outra as células foram pintadas indo do verde ao vermelho. Aquelas com o menor valor ficaram mais próximas do vermelho, e as com maior valor mais próximas do verde.

Editando, excluindo e criando formatações condicionais

As formatações pré-prontas, como você viu, são bem simples de serem utilizadas, porém podem haver casos em que elas não suprem todas as necessidades do usuário, e também não apresentam todas as formatações possíveis que o usuário pode querer dar a sua formatação. Vamos ver como editar ou remover uma formatação feita utilizando uma formatação pré-pronta, e também ver como criar uma formatação desde o início.

Como editar formatação condicional existente

Vou primeiro utilizar aquela em que pintei de amarelo os participantes com mais de 45 anos. Vou querer mudar esta formatação, ao invés de maiores de 45 anos quero aqueles que tiverem forem maiores ou iguais a 50 anos, e também quero pintar de alguma cor específica que não havia nas opções da formatação pré-pronta, além de deixar o texto em negrito.

O que preciso fazer é:

Selecionar a área que queremos colocar a formatação.
Clicar no botão formatação condicional.
Gerenciar Regras (Última Opção da lista)

Ao fazer isso uma janela será exibida mostrando as formatações condicionais existentes no intervalo selecionado.

Aqui é possível ver qual o tipo de formatação que está sendo utilizada, qual o resultado final que é dado a célula editada e em qual intervalo de células ele está ativo.

Basta selecionar a opção que queremos editar e clicar em Editar Regra.

Outra janela será exibida, onde podemos configurar toda nossa regra.

Na primeira metade desta janela (Selecione um tipo de regra) podemos selecionar o tipo de formatação que queremos utilizar. Todas as formatações pré-prontas podem ser encontradas aqui, mas com uma gama maior de opções e com um pouco mais de trabalho de ser feita. Por hora como não quero alterar o tipo de formatação não vou mexer em nada da parte superior.

Na segunda metade (Edite a descrição da regra) é onde faremos as alterações.

O primeiro campo deixarei inalterado, Valor da Célula, pois quero que a formatação ocorra de acordo com o número que estiver na célula.

No segundo campo, é maior do que, terei de alterar para é maior ou igual a.

No terceiro campo, =45, irei alterar para 50 (não é necessário inserir o sinal de =, o Excel fará isso depois automaticamente.

Assim, até o momento as configurações ficaram:

Agora só me resta alterar as cores e deixar em negrito. Para isso clico no botão Formatar que aparece logo à direita da visualização da formatação escolhida na imagem acima. Outra janela será exibida, e nela 4 abas.

Na primeira é possível editar a formatação do texto ou número exibido na célua.

Na segunda é possível alterar a fonte, colocar negrito, sublinhar, etc.

Na terceira é possível alterar a borda da célula.

E na quarta e última, é possível alterar a cor de preenchimento da célula.

Neste caso alterei apenas a 2º aba, selecionando o negrito. E a 4º aba selecionando a cor azul para o fundo da célula.

Veja o resultado.

Como Excluir formatação condicional

Existem três formas de excluir uma formatação condicional feita.

Você pode excluir somente a formatação desejada, excluir todas as formatações de um intervalo selecionado ou excluir todas as formatações da planilha.

Para excluir uma formação específica, começe como se fosse editar uma formatação (exibido no tópico anterior) até chegar na imagem abaixo.

Agora basta selecionar a formatação condicional que deseja excluir e clicar no botão Excluir Formatação que aparece na parte superior.

As duas outras formas aparecem na imagem abaixo, quando você abre o menu de Formatação Condicional.

Como criar formatação condicional

Vamos agora criar uma formatação de farol com base na média obtida nos três testes. Sendo que aqueles com nota de 0 a 5 terão sinal vermelho; maiores ou iguais que 5 e menores que 8 terão sinal amarelo e; maiores ou iguais que 8 terão sinal verde.

Para criar a regra vou em:

Selecionar a área que queremos colocar a formatação.
Clicar no botão formatação condicional.
Nova Regra.

Aparecerá a mesma janela que é exibida quando vamos editar uma formatação existente.

As opções que utilizaremos serão como na imagem abaixo.

Na metade superior da janela foi selecionado a primeira opção, formatar todas as células com base em seus respectivos valores. E na parte inferior foi selecionada a opção Conjunto de ícones, foi seleciona o conjunto desejado, Qual ícone representaria qual dado, o valor dos dados e o tipo.

Veja o resultado.

Criando uma regra de formatação condicional com base em outra célula

Você já viu como criar uma formatação condicional pré-pronta, como criar uma formatação normal desde o início, como editar uma formatação existente e como removê-las.

Porém todas as regras que vimos analisavam e alteravam a formatação da mesma célula, isto é, a formatação do intervalo A1:A10, por exemplo, era feito com base nos valores exibidos neste mesmo intervalo.

Mas existe ainda a possibilidade de alterar a formatação do intervalo A1:A10 com base nos dados exibidos em D1:D10, por exemplo.

Vamos agora criar uma formatação que irá pintar de amarelo toda a linha da tabela sempre que o participante teve uma pontuação média superior ou igual a 7. Para deixar a tabela mais limpa foram removidos todas as formatações exibidas anteriormente, mas não haveria necessidade de ser feito.

Veja na imagem como esta formatação é feita.

Inicialmente o processo é o mesmo que o feito para criar uma regra normal (veja no tópico anterior). Assim selecionei toda a tabela, pois a regra se aplicará a toda ela.

Aqui, na parte superior da janela selecionamos a última opção, Usar uma fórmula…

E escrevi a fórmula =$H3>=7.

Onde:

O símbolo $ fixa a coluna H enquanto que a linha 3 não é fixa. Funciona da seguinte forma: cada uma das células da tabela olhará para a célula que estiver na coluna H pois esta foi fixada com o sinal $ na sua frente na mesma linha que a célula estiver, pois a linha não foi fixada. Por exemplo, o dado que está na célula B5 olhará para o valor que estiver na célula H5 para verificar se esta é maior ou igual a sete, caso seja a célula B5 se pintará de amarelo.

E o sinal >=7 indica ser maior ou igual a 7.

Veja o resultado.