Através de regras de formatação condicional, podemos destacar números individuais simultaneamente em uma sequência de dados contida em uma planilha do Excel. Trocando em miúdos, podemos usar células que receberão valores individuais que serão destacados em outro intervalo de acordo com uma formatação predefinida.
Apesar de esse método possuir um teor apenas visual, ele é útil aliado a outras ferramentas, como a classificação de dados através de filtros de cores.
Neste artigo, você aprenderá a como criar regras para destaque de números individuais em um intervalo de dados, em uma coluna inteira e também abrangendo toda a linha onde está localizada a célula com o valor destacado. Veremos também algumas dicas importantes sobre como corrigir eventuais erros de condições em regras que analisam células vazias.
Versão do Excel utilizada na aula: Microsoft Excel 365 [versão 2109]
Versão do Excel utilizada neste tutorial: Microsoft Excel 365 [versão 2109]
Destacando células individuais
Antes de tudo, precisamos definir um conjunto de células que servirão de referência para a base de nossa pesquisa de valores. No exemplo a seguir, usaremos um intervalo de seis células: de H1 até M1.
Assim, toda vez que qualquer número for digitado em qualquer uma dessas células, ele deverá ser destacado em nossa base de dados dentro da coluna de filiais, que é o tipo de dado que iremos formatar nesse exemplo.
Se você já é bom conhecedor de técnicas de formatação condicional, pode iniciar a formulação de sua regra de modo mais abrangente, mas, como é de costume, prefiro iniciar a explicação usando apenas uma única célula de um intervalo e depois ensino a como expandir tanto a regra quanto a abrangência para as demais células ou intervalos. Isso facilita o aprendizado lógico dessas regras. Portanto, vamos seguir essa metodologia.
Se pretendemos pesquisar as filiais, devemos inserir a formatação condicional nas células onde estão contidas as tais filiais. Portanto, selecione a primeira célula desse intervalo de dados (B2). Na guia Página Inicial, dentro do grupo Estilos, clique em Formatação Condicional e depois em Nova Regra.
No tipo de regra, selecione a última opção: Usar uma fórmula para determinar quais células devem ser formatadas. Esse tipo de regra permite que criemos fórmulas personalizadas para serem usadas como parâmetro.
Em seguida, no campo de descrição da regra, digitaremos nossa fórmula, como no exemplo a seguir.
Analisemos a fórmula usada nesse caso.
=OU($B$2=$H$1:$M$1)
A função OU retorna dois possíveis valores lógicos: verdadeiro ou falso. Se qualquer um dos valores a serem comparados com outro for igual a esse outro valor, a função retornará um resultado verdadeiro. Note que, aqui, estamos comparando o conteúdo da célula B2 com todo o intervalo que vai de H1 até M1. Se o valor inserido em B2 for igual a QUALQUER um dos seis valores inseridos nas seis células a seguir, então o resultado será verdadeiro, portanto o Excel destacará qualquer um desses valores em nosso intervalo de dados.
Ao selecionar diretamente as células na montagem da fórmula, o Excel, por padrão, aplica o trancamento ($) a essas células, de modo que não haja referência relativa aplicada às demais células dessa mesma coluna. No próximo tópico aprenderemos a como editar esse detalhe.
Agora que a lógica da regra está aplicada, vamos definir a formatação que deve ser condicionada a esse caso. Para isso, clique então no botão Formatar.
Você pode aplicar todas as formatações que achar necessárias para essa condição, usando todas as ferramentas presentes nas guias da janela Formatar Células.
No nosso exemplo, vamos apenas trocar a cor de preenchimento da célula para um tom de amarelo.
Assim que finalizar suas configurações, clique em OK nas duas janelas para confirmar.
Note que a regra funciona para a primeira célula, mas não para as demais. O número 10 (presente no intervalo de valores a serem destacados) realmente recebe nossa formatação, mas os demais números 10 não, nem mesmo os outros dois valores (5 e 8).
Isso ocorreu por dois motivos: selecionamos apenas a célula B2 para criar uma regra de formatação condicional e não especificamos o intervalo que deve ser analisado para que se receba o destaque da formatação (afinal, por enquanto, o Excel entende que apenas a célula B2 deve ser analisada e que apenas ela deve ser formatada).
Expandindo o intervalo de análise e a abrangência de formatação da regra
Vamos “consertar” esse detalhe. Como a célula B2 é a única que contém nossa regra, mantenha-a selecionada e clique novamente no botão Formatação Condicional. Dentro das opções, selecione Gerenciar Regras, já que nossa intenção é editar uma regra pronta.
Todas as regras que abrangem a célula selecionada serão mostradas no gerenciador de regras. Selecione a regra que acabamos de criar e clique no botão Editar Regra (ou opte por dar um duplo clique diretamente na regra).
=OU($B2=$H$1:$M$1)
O primeiro passo é editar nossa fórmula. $B$2 indica que o Excel analisará apenas a célula B2, mesmo que nosso intervalo de abrangência seja maior. Dessa forma, ele não analisará as demais células da coluna B, como é nossa intenção.
Para corrigir esse “problema”, basta que retiremos o cifrão ($) da representação da linha, de modo que a linha passe a não ser mais trancada. Assim, o Excel sempre observará a coluna B (porque é nela que estão nossas filiais), mas não apenas a linha 2, e sim todas as linhas da coluna B, ou seja, todas as células pertencentes a ela.
Corrigindo esse detalhe, basta pressionar OK para retornamos ao gerenciador de regras.
O segundo passo é alterar a abrangência dessa regra. Note que, ao lado da regra, você encontra um campo chamado Aplica-se a. Esse campo mostra ao Excel qual é o intervalo de dados que deve ser formatado no caso de uma regra vir a ser verdadeira. Como selecionamos apenas a célula B2 no momento de montar nossa regra, ela é a única que recebe nossa formatação. Mas nosso intento é que um intervalo maior de células da coluna B seja analisado.
Por exemplo, você pode usar a notação B2:B100 para que o intervalo da célula B2 até a B100 seja analisado e formatado.
Se preferir, pode analisar a coluna toda, usando a notação B:B, por exemplo.
Agora sim nossa regra funciona, pois analisa todas as células da coluna B para compará-las com o intervalo de valores a serem destacados e formata, consequentemente, toda a coluna B.
Corrigindo a formatação de células vazias
Apesar de ter funcionado, ainda temos um pequeno problema em nossa planilha: note que as células vazias da coluna B também recebem nossa formatação.
Entender por que isso ocorre não é algo difícil. Ora, o Excel está formatando todos os valores que correspondem a QUALQUER um dos valores preenchidos no intervalo H1:M1.
Se deixarmos qualquer célula desse intervalo vazia, isso significa que o valor “vazio” é parte da validação, portanto as células vazias correspondem a esse “valor”. Sendo assim, elas também serão formatadas.
Há algumas alternativas para corrigirmos essa análise. Uma delas (que também pode ser útil em outras situações no seu dia a dia) é criar uma nova regra que impeça a formatação das células vazias da coluna B.
Vamos usar a mesma metodologia da regra anterior: aplicar a formatação em uma célula e expandi-la para as demais. No entanto, já vamos editando essa regra no meio do caminho.
Com a célula B2 selecionada (onde nosso intervalo de dados é iniciado), clique novamente em Formatação Condicional e em Nova Regra. O tipo de regra será o mesmo: baseado em uma fórmula.
=$B2=””
Essa regra analisará as células da coluna B (é necessário retirar o trancamento da linha, assim como fizemos na regra anterior) para determinar se elas contêm o valor “vazio” (que é representado por duas aspas duplas no Excel).
Ainda que haja uma regra pronta no Excel para destacar células vazias (dentro da seção Formatar apenas células que contenham), ela não nos servirá quando redirecionarmos a formatação para a linha inteira, por isso estamos criando nossa própria fórmula para isso.
Em seguida, basta pressionar OK sem mesmo definir qualquer tipo de formatação, porque nosso propósito aqui é justamente NÃO aplicar formatações a células vazias. Todavia, se preferir, você pode definir uma formatação para células vazias, como o preenchimento branco, por exemplo.
Em seguida, precisamos modificar a abrangência da regra. Com a célula B2 selecionada, basta acionar novamente o gerenciador de regras. No campo de abrangência, podemos definir um intervalo específico ou uma coluna toda. No caso a seguir, usaremos a coluna B completa (B:B).
Outra opção a ser modificada é a interrupção das regras posteriores. Aqui temos duas regras que são satisfeitas pelas células analisadas: tanto a regra para células vazias como a regra principal analisam e formatam células vazias. Por padrão, o Excel executa as regras na ordem em que elas são listadas no gerenciador. Sendo assim, ao executar a primeira, o Excel não aplicará nenhuma formatação às células vazias, mas, ao executar a segunda, o Excel continuará aplicando a formatação de preenchimento amarelo às células vazias. Ou seja, a última regra se sobrepõe à anterior quando as duas possuem testagens idênticas.
Podemos, nesse caso, inverter a ordem das regras, deixando a regra principal no topo, mas há uma alternativa bastante útil nativa do próprio Excel: a opção Parar se Verdadeiro, localizada à direita de cada regra listada. Quando marcada, o Excel analisa a regra e, sendo ela verdadeira, interrompe a análise, não executando as demais regras. Desse modo, caso as células sejam vazias, o Excel aplicará a formatação nessas células (no nosso exemplo, formatação nenhuma) e não executará a regra seguinte. Caso não sejam vazias (o mesmo que FALSO), o Excel então executará a regra seguinte normalmente.
Tudo certo. Agora as células vazias deixam de receber a formatação da regra principal.
Destacando a linha completa da célula formatada
Em vez de destacar apenas a célula da coluna B, podemos destacar todas as células que façam parte da linha onde se encontra a célula destacada, o que envolveria o destaque também das informações contidas nas outras colunas.
O único elemento que precisamos alterar em nossa regra é a abrangência, visto que o teste para a regra continua o mesmo: o número da filial, restrito apenas à coluna B.
Ao abrir nossa regra para edição, basta que, no campo Aplica-se a, alteremos a abrangência para o intervalo determinado ou para quantas colunas forem necessárias. Em nosso caso, os dados se encontram da coluna A até a coluna E. A notação para abranger todo esse intervalo é A:E.
Modificando a quantidade de colunas a serem abrangidas pela regra, conseguimos destacar todas as células de uma mesma linha.
Mas lembre-se de que nossa regra de formatação de células vazias também precisa ser alterada, para que sua abrangência também alcance esse novo intervalo de colunas.
É esse o motivo pelo qual usamos uma fórmula para testar as células vazias, e não a opção nativa do Excel. Podemos alterar a abrangência dessa regra, mas nunca a condição, porque a única coluna que precisa ser analisada para a verificação de células vazias é a B. Se usássemos a opção Vazias das regras prontas de formatação condicional, o Excel testaria todas as células dessas colunas, deixando em branco até mesmo as células que pertencessem à linha com a célula da coluna B preenchida e destacada.
De forma mais visualizável, ocorreria esse tipo de erro:
De modo alternativo, em vez de usar uma fórmula para células em branco, você poderia interverter a ordem das regras. Tudo depende, naturalmente, de como sua regra será adaptada.
Combinando a formatação de valores únicos com filtros de cores
Um dos resultados de se destacar valores usando esse tipo de formatação é a possibilidade de combinar os valores formatados a alguns filtros. Por exemplo, em vez de procurar quatro números diferentes em um filtro numérico, podemos realizar o destaque por cor desses quatro números e aplicar um filtro de cor única.
No exemplo a seguir, usando a mesma planilha, vamos destacar as filiais 4, 6, 10 e 1.
Ao aplicar um filtro em nossa planilha, podemos optar pelo filtro de cor na coluna B (das filiais), em vez de um filtro numérico para quatro números distintos, já que os quatro agora pertencem a uma categoria única: com preenchimento amarelo.
Todos os números destacados agora foram filtrados pela característica em comum entre eles: a cor de preenchimento.
- Você não precisa necessariamente trabalhar com números inteiros usando essa formatação; é possível realizar esse tipo de destaque com números decimais, porcentagem, data, hora e até mesmo texto.
- A abrangência não precisa se estender unicamente de um ponto inicial a um ponto final. Para abranger colunas intercaladas, você pode realizar notações intercaladas. Por exemplo, A:C;F:K estenderia a abrangência de uma regra de formatação condicional para as colunas de A a C e de F a K, ignorando as colunas D e E.
- Você pode criar destaques por exceção. Em vez de usar o sinal de igual (=) no teste de sua regra, pode usar o sinal de diferente (<>). Assim, todos os valores serão destacados, EXCETO aqueles que você informar nas células de referência.
