Uma das limitações mais frustrantes do Excel convencional é que referências fixas não acompanham quando os dados crescem. Você cria um gráfico de vendas com os dados de janeiro a junho, e em julho precisa editar manualmente o intervalo de dados do gráfico para incluir o novo mês. Cria uma validação de dados com uma lista de produtos e quando adiciona um produto novo ele não aparece no menu suspenso. A função DESLOC no Excel resolve exatamente esse problema ao permitir a criação de intervalos que se expandem automaticamente conforme novos dados são inseridos. Neste artigo iremos mostrar como usar o DESLOC para criar intervalos dinâmicos em gráficos, listas e validações de dados.
Intervalos nomeados dinâmicos com DESLOC: o conceito base
Um intervalo nomeado dinâmico é um nome definido no Excel (como “Vendas” ou “ListaProdutos”) que não aponta para um intervalo fixo de células, mas para um intervalo calculado por uma fórmula. Quando os dados crescem, a fórmula recalcula e o intervalo nomeado se expande automaticamente para incluir os novos dados. Qualquer elemento que referencie esse nome — um gráfico, uma validação de dados, uma fórmula — recebe automaticamente a versão atualizada do intervalo.
Para criar um intervalo nomeado dinâmico, vá em Fórmulas, Gerenciador de Nomes, e clique em Novo. No campo Nome, dê um nome descritivo como “VendasDinamico”. No campo Refere-se a, insira a fórmula DESLOC: =DESLOC(Planilha1!$A$2; 0; 0; CONT.VALORES(Planilha1!$A:$A)-1; 1). Essa fórmula parte de A2 (o primeiro dado abaixo do cabeçalho na linha 1), não se desloca (lins=0 e cols=0), tem altura igual ao número de células preenchidas na coluna A menos 1 (o cabeçalho) e largura de 1 coluna. O resultado é sempre o intervalo exato com todos os dados da coluna A, sem o cabeçalho, independentemente de quantas linhas têm dados.
Esse intervalo nomeado dinâmico pode agora ser usado em qualquer lugar do Excel onde você normalmente escreveria um intervalo fixo. Em uma fórmula: =SOMA(VendasDinamico). Em uma validação de dados: selecione a célula, vá em Dados, Validação de Dados, escolha Lista e no campo Fonte escreva =VendasDinamico. Em um gráfico: edite a série e use =NomeDoArquivo!VendasDinamico como fonte de dados. Em todos esses casos, quando um novo valor é adicionado à lista, o intervalo dinâmico se expande automaticamente e o elemento que o referencia reflete a mudança sem nenhuma ação manual.
Gráficos que crescem automaticamente com DESLOC
Gráficos com intervalos dinâmicos são um dos casos de uso mais valorizados do DESLOC no Excel. Imagine um gráfico de vendas mensais que você atualiza todo mês com um novo dado — sem intervalos dinâmicos, toda atualização exige entrar nas configurações do gráfico e expandir manualmente o intervalo de dados. Com o DESLOC, você adiciona o novo mês na tabela e o gráfico se atualiza automaticamente.
Para criar esse gráfico dinâmico, você precisa de dois intervalos nomeados: um para os valores (as vendas) e um para os rótulos do eixo (os meses). Crie o intervalo nomeado “Meses” com a fórmula: =DESLOC(Planilha1!$A$2; 0; 0; CONT.VALORES(Planilha1!$A:$A)-1; 1). Crie o intervalo “Vendas” com a mesma estrutura mas apontando para a coluna B: =DESLOC(Planilha1!$B$2; 0; 0; CONT.VALORES(Planilha1!$B:$B)-1; 1). Crie o gráfico com os dados existentes, depois edite a série de dados e substitua as referências fixas pelos nomes dinâmicos: no campo Valores da série use =NomeArquivo!Vendas, e no campo Rótulos do eixo use =NomeArquivo!Meses.
Agora, cada vez que você adiciona um novo mês e o valor de vendas correspondente nas colunas A e B, os intervalos nomeados expandem automaticamente e o gráfico inclui o novo ponto de dados sem nenhuma edição adicional. Esse comportamento transforma o processo de atualização mensal de relatórios com gráficos — o que antes levava minutos de edição manual passa a ser apenas digitar os novos dados. Para relatórios que são compartilhados com outras pessoas, a eliminação dessa etapa de manutenção também elimina o risco de esquecer de atualizar o gráfico quando os dados mudam.
Validação de dados dinâmica com DESLOC: listas que crescem sem precisar reeditar
A validação de dados com lista é um dos recursos mais usados para controlar o preenchimento de planilhas compartilhadas — aqueles menus suspensos que limitam os valores que podem ser inseridos em uma célula. O problema com as listas fixas de validação é que quando um novo item precisa ser adicionado à lista, você precisa editar a validação de cada célula que usa aquela lista — um processo trabalhoso quando são muitas células. Com o DESLOC e um intervalo nomeado dinâmico, você adiciona o novo item à lista de referência e automaticamente ele passa a aparecer em todos os menus suspensos que referenciam aquele intervalo.
Para implementar, crie uma aba dedicada para as listas de referência (nomeie como “Listas” ou “Configurações”) e coloque os itens de cada lista em colunas separadas com cabeçalho. Para uma lista de categorias de produtos na coluna A dessa aba, crie um intervalo nomeado dinâmico: =DESLOC(Listas!$A$2; 0; 0; CONT.VALORES(Listas!$A:$A)-1; 1). Na validação de dados das células que precisam usar essa lista, escolha “Lista” e no campo Fonte use =NomeDaLista. Para adicionar uma nova categoria, basta digitar na aba Listas — o intervalo se expande e a nova categoria aparece em todos os menus suspensos automaticamente.
Uma melhoria importante para listas de validação dinâmicas é manter os itens em ordem alfabética na aba de referência. Como o DESLOC inclui os dados na ordem em que estão na planilha, se você sempre adiciona novos itens no final da lista eles aparecem desordenados no menu suspenso. Duas abordagens resolvem isso: ou você sempre insere os novos itens na posição correta na lista de referência (respeitando a ordem), ou usa o Power Query para criar uma versão ordenada da lista que serve como fonte da validação. Para listas que raramente mudam, a inserção manual em posição correta é suficiente. Para listas que crescem frequentemente, o Power Query automatiza a ordenação.
DESLOC para dashboards com seleção dinâmica de período
Um uso muito sofisticado do DESLOC em dashboards é a criação de visualizações que mudam conforme o usuário seleciona um período diferente — sem nenhuma macro ou VBA. A ideia é: o usuário escolhe um ano ou um mês em uma célula de controle, e o DESLOC calcula dinamicamente quais dados mostrar no gráfico com base nessa seleção.
Para implementar um seletor de ano em um gráfico de vendas mensais, você precisa de uma tabela com os dados organizados por ano e mês, e uma célula de controle onde o usuário digita o ano desejado. O DESLOC calcula a posição das vendas daquele ano na tabela: =DESLOC(TabelaVendas; (ano_selecionado – primeiro_ano) * 12; 0; 12; 1). Aqui, o deslocamento em linhas é calculado como a diferença entre o ano selecionado e o primeiro ano da tabela multiplicada por 12 (os meses de cada ano), posicionando o início do intervalo exatamente nos dados do ano escolhido. A altura 12 garante que todos os 12 meses daquele ano são incluídos.
Com esse DESLOC como fonte de dados do gráfico (via intervalo nomeado dinâmico), mudar o valor na célula de controle do ano muda instantaneamente os dados exibidos no gráfico. Para um dashboard executivo apresentado em reuniões, esse comportamento interativo sem macros é extremamente valioso — qualquer pessoa pode navegar pelos dados de anos diferentes com um único clique na célula de seleção, sem precisar de conhecimento técnico sobre como o mecanismo funciona por baixo.
Se você curtiu esse artigo onde mostramos como usar o DESLOC no Excel para criar intervalos dinâmicos em gráficos e validações, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.