Para entender este tópico, é necessário entender os conceitos de definir nomes para células ou intervalos de células, e o conceito de intervalo dinâmico.
Para resumir, é possível criar um nome que se refere a uma única célula ou um intervalo fixo ou variável de células. Chamamos este intervalo variável de células de Intervalo Dinâmico (ou Dynamic Range).
Todo intervalo em Excel pode ser expresso como Célula1:Célula2, como por exemplo:

=SOMA(A2:A10)

Notemos que é clara a definição da primeira célula e da última célula do intervalo. Observar o dois-pontos, que separa as duas células informadas, é essencial para o entendimento deste conceito. Agora a parte mais importante é entendermos que, qualquer função do Excel que retorne uma referência a uma célula pode ser usada ao invés de células fixas, tanto antes quanto depois do dois-pontos. Vamos utilizar duas funções ÍNDICE() para retornar uma célula cada e assim compor este endereçamento de uma forma bem diferente.

=SOMA(ÍNDICE($A:$A;1;1):ÍNDICE($A:$A;10;1))

Nota Importante:
A função ÍNDICE() é muito simples de entender. Os parâmetros são:
Matriz (uma tabela qualquer, com número qualquer de linhas e colunas)
Linha (o número da linha)
Coluna (o número da coluna)

Ou seja, dada uma tabela qualquer, retorne a célula da linha e coluna que está sendo pedida.

Ao escrever a fórmula:
=SOMA(ÍNDICE($A:$A;2;1):ÍNDICE($A:$A;10;1))

A matriz é a coluna $A:$A, e a célula é a da linha 2 e coluna 1 deste intervalo. Ou seja, Ter-se-á a célula $A$2 como resultado, e assim, quando o Excel resolve este função, é como escrever $A$2 na própria célula.

Até aqui, nada está dinâmico. Vamos então agora brincar com aquele 2, e com aquele 10.

Para facilitar este exemplo, vamos fixar a primeira célula em A2, pois é a célula fixa aonde nosso intervalo dinâmico sempre começará:
=SOMA($A$2:ÍNDICE(A:A;10;1))

No exemplo acima estamos dizendo que o intervalo deve começar em A2 e terminar na célula da linha 10 e coluna 1 do intervalo A:A, ou seja, terminar na célula A10.
Porém, e se a nossa lista cresce com o tempo, e a quantidade de itens for cada vez maior? Caso isso aconteça, a fórmula acima não atende mais a necessidade, pois não incluiria os outros itens, inseridos posteriormente na tabela.
Por isso, deveremos utilizar uma função, no lugar do número 10, que contará quantas células preenchidas há na coluna da soma, ou em outra coluna que se tenha garantido que todas as células estejam preenchidas. Ficará assim:

=SOMA($A$2:ÍNDICE(A:A;CONT.VALORES(A:A);1))

Assim, temos uma matriz de um número variável de linhas dentro da função soma, e atingimos o resultado de intervalo dinâmico utilizando a função ÍNDICE().

Claro que a partir daqui, podemos expandir o conceito e definir nomes (Ctrl+F3) com esta notação para indicar intervalos dinâmicos dentro de um nome que será de mais fácil utilização.

Exemplo de uma tabela com número dinâmico de linhas:
1) Ctrl+F3
2) Nome: Tabela
3) Refere-se à: =$A$2:ÍNDICE($J:$J;CONT.VALORES(A:A);1)
4) Ok

Daqui em diante, quando desejar-se fazer um PROCV() por exemplo, pode-se usar o nome da Tabela cujo conteúdo é variável de acordo com o número de linhas da mesma. Estamos criando um nome que referencia uma tabela com “efeito sanfona vertical”.

Exemplo 1: =PROCV(“JOÃO”;$A$2:ÍNDICE($J:$J;CONT.VALORES(A:A);1);6;0)
ou
Exemplo 2: =PROCV(“JOÃO”;Tabela;6;0)

Notas Importantes:
1) É importante a coluna utilizada na contagem de valores estar completamente preenchida;
2) Pode-se utilizar as funções DESLOC() ao invés de função ÍNDICE();
a. =SOMA($A$2:DESLOC($A$1;CONT.VALORES($A:$A)-1;0;1;1))
b. =SOMA(INDIRETO("A2"):DESLOC($A$1;CONT.VALORES($A:$A)-1;0;1;1))
3) Pode-se utilizar as funções INDIRETO() ao invés de função ÍNDICE();
a. =SOMA($A$2:INDIRETO("A10"))
b. =SOMA($A$2:INDIRETO("A" & CONT.VALORES($A:$A)))
4) Pode-se utilizar início e fim do intervalo como células dinâmicas. Ou seja, Você pode ter =ÍNDICE(...):ÍNDICE(...);
5) O que há antes do dois-pontos deve ser o equivalente a uma célula e o que há depois do dois-pontos deve ser equivalente a uma célula (diferentes ou não);
6) O dois-pontos é como um operador de cálculo, assim como o sinal de adição (+) ou subtração (-). Entender que o uso mais comum dele é entre duas células indicando um intervalo é base para entender que é possível utilizar funções cujos resultados são células e o dois-pontos no meio.