_apostilapdf_temp_134 124 317-61_Excel 2016 - Modulo II (online)

Prévia do material em texto

Excel 2016 - Módulo II
(online)
Excel 2016 - Módulo II 
(online)
Cód.: TE 1824_0_EAD
SumárioCréditos
Excel 2016 - Módulo II 
(online)
Coordenação Geral
Marcia M. Rosa
Coordenação Editorial
Henrique Thomaz Bruscagin
Supervisão de Desenvolvimento 
Digital
Alexandre Hideki Chicaoka
Produção, Gravação, Edição de Vídeo 
e Finalização 
Edcléia Souza de Lima
Xandros Luiz de Oliveira Almeida 
(Impacta Produtora)
Este material é uma nova obra derivada da seguinte obra original, produzida por 
Monte Everest Participações e Empreendimentos Ltda. em Mai/2016:
Excel 2016 – Módulo II
Autoria: Fernando de Carvalho Navarro
Sobre o instrutor do curso: 
Fernando de Carvalho Navarro é consultor e instrutor em TI especializado no 
pacote Microsoft Office, e profissional certificado Microsoft Office Specialist Expert. 
Possui formação em engenharia e ministra cursos de tecnologia da informação 
na Impacta Certificação e Treinamento desde 2011. Também é autor de material 
didático e publicações sobre aplicativos Microsoft Office.
Copyright © MONTE EVEREST PARTICIPAÇÕES E EMPREENDIMENTOS LTDA.
Todos os direitos autorais reservados. Este manual não pode ser copiado, fotocopiado, reproduzido, 
traduzido ou convertido em qualquer forma eletrônica, ou legível por qualquer meio, em parte ou 
no todo, sem a aprovação prévia, por escrito, da Monte Everest Participações e Empreendimentos 
Ltda., estando o contrafator sujeito a responder por crime de Violação de Direito Autoral, conforme 
o art.184 do Código Penal Brasileiro, além de responder por Perdas e Danos. Todos os logotipos 
e marcas utilizados neste material pertencem às suas respectivas empresas.
“As marcas registradas e os nomes comerciais citados nesta obra, mesmo que não sejam assim identificados, pertencem 
aos seus respectivos proprietários nos termos das leis, convenções e diretrizes nacionais e internacionais.”
Roteirização
Fernando de Carvalho Navarro
Curso ministrado por
Fernando de Carvalho Navarro
Edição e Revisão final
Fernanda Monteiro Laneri
Marcos Cesar dos Santos Silva
Diagramação
Bruno de Oliveira Santos
Edição nº 1 | 1824_0_EAD
março/ 2017
5
Sumário
Apresentação ................................................................................................................................ 12
Aulas 1 a 4 - Referências e fórmulas ................................................................................. 13
1.1. Introdução ......................................................................................14 
1.2. Referências de células .....................................................................16
1.2.1. Referências na mesma planilha .......................................................16 
1.2.2. Referências a células de outras planilhas .........................................17 
1.2.3. Referências a células de outras pastas de trabalho ..........................19 
1.2.4. Gerenciando vínculos (links) ............................................................25
1.3. Intervalos nomeados .......................................................................30
1.3.1. Criando intervalos nomeados ..........................................................31 
1.3.2. Usando o nome criado ....................................................................33 
1.3.3. Gerenciando nomes ........................................................................36
1.4. Auditoria de fórmulas .....................................................................36
1.4.1. Rastreando precedentes ..................................................................38 
1.4.2. Rastreando dependentes .................................................................40 
1.4.3. Exibindo todas as fórmulas da planilha ...........................................42 
1.4.4. Detectando e resolvendo erros em fórmulas ...................................43 
1.4.5. Janela de inspeção ..........................................................................54
1.5. Opções de cálculo ...........................................................................57 
Pontos principais ................................................................................................59 
Teste seus conhecimentos...................................................................................................... 61
Mãos à obra! .................................................................................................................................. 65
Aulas 5 a 9 - Funções (Parte 1) ............................................................................................. 71
1.1. Introdução ......................................................................................72 
1.2. Funções matemáticas e trigonométricas ..........................................74
1.2.1. Função ABS .....................................................................................74 
1.2.2. Função INT ......................................................................................75 
1.2.3. Função ARRED.................................................................................76 
1.2.4. Função TRUNCAR ............................................................................77 
1.2.5. Função POTÊNCIA ...........................................................................77 
1.2.6. Função RAIZ ....................................................................................78 
1.2.7. Função ALEATÓRIO .........................................................................79 
1.2.8. Função ALEATÓRIOENTRE ...............................................................80 
1.2.9. Outras funções ...............................................................................80
1.3. Funções estatísticas ........................................................................80
1.3.1. Funções estatísticas básicas ............................................................81 
1.3.2. Funções MAIOR e MENOR ................................................................84 
1.3.3. Função SUBTOTAL ...........................................................................85 
1.3.4. Funções estatísticas condicionais ....................................................88
1.4. Funções de banco de dados ............................................................100
1.4.1. Sintaxe e argumentos .....................................................................100 
1.4.2. Intervalos ........................................................................................101 
1.4.3. Funções ..........................................................................................102
1.5. Funções de data e hora ...................................................................105
1.5.1. Números seriais ..............................................................................105 
1.5.2. Função HOJE ...................................................................................107 
6
Excel 2016 - Módulo II (online) Sumário
1.5.3. Função AGORA ................................................................................107 
1.5.4. Cálculos com datas .........................................................................107 
1.5.5. Cálculos com horas .........................................................................109 
1.5.6. Função DATA ..................................................................................115 
1.5.7. Funções DIA, MÊS e ANO .................................................................116 
1.5.8. Função DIA.DA.SEMANA ..................................................................117 
1.5.9. Função DATA.VALOR .......................................................................118 
1.5.10. Função VALOR.TEMPO .....................................................................118 
1.5.11. Função FIMMÊS ...............................................................................119 
1.5.12. Função DATAM ...............................................................................1201.5.13. Função NÚMSEMANAISO ..................................................................121 
1.5.14. Funções para dias úteis ...................................................................121 
1.5.15. Formatação para datas e horas .......................................................126
Pontos principais ................................................................................................128 
Teste seus conhecimentos...................................................................................................... 129
Aulas 10 a 13 - Funções (Parte 2) ....................................................................................... 133
1.1. Funções lógicas ..............................................................................134
1.1.1. Função SE .......................................................................................134 
1.1.2. Funções auxiliares ..........................................................................135 
1.1.3. Funções lógicas aninhadas ..............................................................137 
1.1.4. Função SES ......................................................................................140 
1.1.5. Função PARÂMETRO ........................................................................141 
1.1.6. Função SEERRO ...............................................................................144 
1.1.7. Função SENÃODISP ..........................................................................144 
1.1.8. Funções de informação ...................................................................145
1.2. Funções de procura e referência ......................................................146
1.2.1. Função PROCV ................................................................................146 
1.2.2. Função PROCH ................................................................................150 
1.2.3. Função ÍNDICE ................................................................................153 
1.2.4. Função CORRESP .............................................................................154 
1.2.5. Funções ÍNDICE e CORRESP usadas em conjunto .............................156 
1.2.6. Função DESLOC ...............................................................................158 
1.2.7. Função HIPERLINK ...........................................................................165
1.3. Funções de texto ............................................................................168
1.3.1. Maiúsculas e minúsculas .................................................................168 
1.3.2. Concatenação de texto ....................................................................170 
1.3.3. Formatação de texto .......................................................................175 
1.3.4. Tratamento de caracteres ................................................................177 
1.3.5. Localização e substituição de texto .................................................181 
1.3.6. Extração de texto ............................................................................186
1.4. Funções financeiras ........................................................................189
1.4.1. Função VP .......................................................................................191 
1.4.2. Função VF .......................................................................................191 
1.4.3. Função PGTO ..................................................................................192 
1.4.4. Função TAXA ..................................................................................193 
1.4.5. Função NPER ...................................................................................193
Pontos principais ................................................................................................195 
Teste seus conhecimentos...................................................................................................... 197
Mãos à obra! .................................................................................................................................. 201
7
Excel 2016 - Módulo II (online) Sumário
Aulas 14 a 16 - Organização de dados ............................................................................. 209
1.1. Introdução ......................................................................................210 
1.2. Classificação e filtragem .................................................................212
1.2.1. Classificação de dados ....................................................................212 
1.2.2. Filtragem de dados .........................................................................213
1.3. Estrutura de tópicos ........................................................................220
1.3.1. Agrupando itens manualmente .......................................................221 
1.3.2. Recolhendo e expandindo os agrupamentos ...................................222 
1.3.3. Desagrupando itens ........................................................................223 
1.3.4. Criando agrupamentos automáticos ................................................223 
1.3.5. Opções de agrupamento .................................................................224
1.3.6. Subtotais ........................................................................................226
1.4. Tabelas ...........................................................................................230
1.4.1. Criando uma tabela .........................................................................231 
1.4.2. Selecionando células na tabela ........................................................233 
1.4.3. Inserindo dados na tabela ...............................................................234 
1.4.4. Nomeando a tabela .........................................................................235 
1.4.5. Redimensionando a tabela ..............................................................236 
1.4.6. Modificando o estilo de tabela ........................................................236 
1.4.7. Alterando as opções de linhas e colunas .........................................237 
1.4.8. Escrevendo fórmulas na tabela ........................................................238 
1.4.9. Convertendo a tabela de volta para intervalo comum ......................242 
1.4.10. Removendo duplicatas ....................................................................243 
1.4.11. Segmentação de dados ...................................................................244 
1.4.12. Tabelas e gráficos ...........................................................................246
Pontos principais ................................................................................................247 
Teste seus conhecimentos...................................................................................................... 249
Mãos à obra! .................................................................................................................................. 253
Aula 17 - Importação de dados ............................................................................................ 259
1.1. Introdução ......................................................................................260
1.2. Importando arquivos de texto .........................................................261
1.2.1. Texto delimitado .............................................................................262
1.2.2. Texto com largura fixa ....................................................................267
1.3. Importando tabelas do Access.........................................................272
1.4. Importando dados XML ...................................................................276
1.5. Gerenciando as conexões da pasta de trabalho ...............................279
1.5.1. Adicionando uma conexão ..............................................................2811.5.2. Propriedades das conexões .............................................................282
Pontos principais ................................................................................................284
Teste seus conhecimentos...................................................................................................... 287
Mãos à obra! .................................................................................................................................. 291
8
Excel 2016 - Módulo II (online) Sumário
Aula 18 - Tabelas dinâmicas e gráficos dinâmicos ..................................................... 293
1.1. Introdução ......................................................................................294 
1.2. Criando uma tabela dinâmica ..........................................................297
1.2.1. Montagem manual ..........................................................................297 
1.2.2. Tabelas dinâmicas recomendadas ...................................................300
1.3. Modificando uma tabela dinâmica ...................................................301
1.3.1. Modificando o layout ......................................................................301 
1.3.2. Formatando os valores ....................................................................302 
1.3.3. Filtrando dados ...............................................................................303
1.4. Ferramentas de tabela dinâmica ......................................................305 
1.5. Formatando a tabela dinâmica ........................................................305
1.5.1. Modificando o layout ......................................................................306 
1.5.2. Opções de estilo de tabela dinâmica ...............................................310 
1.5.3. Estilos de tabela dinâmica ...............................................................310
1.6. Analisando dados ...........................................................................311
1.6.1. Opções de tabela dinâmica .............................................................311 
1.6.2. Atualizando dados ..........................................................................315 
1.6.3. Controlando a exibição de elementos da tabela ..............................316 
1.6.4. Trabalhando com o campo ativo .....................................................316 
1.6.5. Agrupando dados ...........................................................................319 
1.6.6. Filtrando dados ...............................................................................325 
1.6.7. Ações na tabela dinâmica ................................................................335 
1.6.8. Cálculos nas tabelas dinâmicas .......................................................337
1.7. Relacionamento entre tabelas .........................................................344
1.7.1. Conceito de banco de dados relacional ...........................................344 
1.7.2. Criando um relacionamento entre tabelas .......................................347 
1.7.3. Criando uma tabela dinâmica pelo modelo de dados .......................348
1.8. Gráficos dinâmicos .........................................................................351
1.8.1. Criando um gráfico dinâmico ..........................................................351 
1.8.2. Interagindo com o gráfico dinâmico ................................................353 
1.8.3. Modificando o gráfico dinâmico ......................................................354
Pontos principais ................................................................................................357 
Teste seus conhecimentos...................................................................................................... 359
Mãos à obra! .................................................................................................................................. 363
9
Excel 2016 - Módulo II (online) Sumário
Aulas 19 a 22 - Ferramentas de dados ............................................................................. 371
1.1. Introdução ......................................................................................372 
1.2. Formatação condicional ..................................................................372
1.2.1. Tipos de regras de formatação condicional .....................................373 
1.2.2. Formatos predefinidos ....................................................................374 
1.2.3. Formatação condicional baseada em fórmula ..................................382 
1.2.4. Gerenciando regras de formatação condicional ...............................384
1.3. Texto para colunas .........................................................................385
1.3.1. Texto delimitado .............................................................................386 
1.3.2. Largura fixa ....................................................................................388
1.4. Preenchimento relâmpago ...............................................................390 
1.5. Remover duplicatas .........................................................................394 
1.6. Validação de dados .........................................................................396
1.6.1. Aplicando uma validação de dados..................................................396 
1.6.2. Mais exemplos ................................................................................401 
1.6.3. Removendo uma validação de dados ...............................................404 
1.6.4. Localizando células com validação de dados ...................................404 
1.6.5. Localizando dados inválidos ............................................................405
1.7. Consolidação de dados ...................................................................407 
Pontos principais ................................................................................................414 
Teste seus conhecimentos...................................................................................................... 417
Mãos à obra! .................................................................................................................................. 421
Aulas 23 a 27 - Análise de dados ........................................................................................ 427
1.1. Introdução ......................................................................................428 
1.2. Gerenciador de cenários ..................................................................428
1.2.1. Adicionando cenários ......................................................................430 
1.2.2. Exibindo cenários ...........................................................................434 
1.2.3. Excluindo cenários ..........................................................................434 
1.2.4. Editando cenários ...........................................................................435 
1.2.5. Mesclando cenários .........................................................................436 
1.2.6. Resumindo cenários ........................................................................437 
1.2.7. Mostrando cenários de forma alternativa .........................................438
1.3. Tabela de dados..............................................................................439
1.3.1. Criando uma tabela de dados com uma variável ..............................442 
1.3.2. Criando uma tabela de dados com duas variáveis ............................444
1.4. Atingir meta ....................................................................................446 
1.5. Solver .............................................................................................448 
1.6. Planilha de previsão ........................................................................449 
Pontos principais ................................................................................................461Teste seus conhecimentos...................................................................................................... 463
Mãos à obra! .................................................................................................................................. 469
10
Excel 2016 - Módulo II (online) Sumário
Aula 28 - Controles de formulário ...................................................................................... 473
1.1. Introdução ......................................................................................474
1.1.1. Guia Desenvolvedor ........................................................................474 
1.1.2. Formulários ....................................................................................475 
1.1.3. Controles ........................................................................................476
1.2. Inserindo e editando controles ........................................................479
1.2.1. Selecionando controles ...................................................................480 
1.2.2. Dimensionando e posicionando controles .......................................480 
1.2.3. Duplicando controles ......................................................................482 
1.2.4. Organizando controles ....................................................................483 
1.2.5. Editando o texto de um controle .....................................................483 
1.2.6. Excluindo controles .........................................................................484
1.3. Propriedades dos controles .............................................................484 
1.4. Aplicabilidade dos controles ...........................................................485
1.4.1. Caixa de grupo ...............................................................................485 
1.4.2. Caixa de seleção .............................................................................486 
1.4.3. Botão de opção ...............................................................................490 
1.4.4. Caixa de listagem ...........................................................................494 
1.4.5. Caixa de combinação ......................................................................497 
1.4.6. Barra de rolagem ............................................................................499 
1.4.7. Botão de rotação .............................................................................502 
1.4.8. Botão ..............................................................................................506 
1.4.9. Rótulo .............................................................................................508
Pontos principais ................................................................................................509 
Teste seus conhecimentos...................................................................................................... 511
Mãos à obra! .................................................................................................................................. 515
Aula 29 - Macros ......................................................................................................................... 519
1.1. Introdução ......................................................................................520
1.1.1. O que são macros ...........................................................................520
1.1.2. Ferramentas de macro ....................................................................520
1.1.3. Tipos de arquivos ...........................................................................521
1.1.4. Lógica de programação ...................................................................522
1.2. Gravando macros ............................................................................523
1.3. Referências absolutas e relativas .....................................................527
1.4. Gerenciando macros .......................................................................530
1.5. Maneiras de executar as macros .....................................................531
1.5.1. Pela barra de ferramentas de acesso rápido ....................................532
1.5.2. Através de objetos gráficos .............................................................534
1.5.2.1. Controles de formulário ..................................................................534
1.5.2.2. Formas geométricas ........................................................................535
1.5.2.3. Fotos, ícones e ilustrações ..............................................................535
1.6. Visualizando e editando macros ......................................................535
1.7. Aninhando macros ..........................................................................538
1.8. Boas práticas e orientações .............................................................539
1.9. Exemplo de aplicação......................................................................541
1.9.1. Desenhando os botões ....................................................................542
1.9.2. Gravando a macro Limpar Dados .....................................................543
1.9.3. Gravando a macro Gravar Dados .....................................................545
1.9.4. Aninhando as macros ......................................................................548
Pontos principais ................................................................................................550
Teste seus conhecimentos...................................................................................................... 551
Mãos à obra! .................................................................................................................................. 555
11
Excel 2016 - Módulo II (online) Sumário
Aula 30 - Proteção de planilhas e pastas .......................................................................... 563
1.1. Introdução ......................................................................................564 
1.2. Protegendo planilhas ......................................................................565
1.2.1. Células bloqueadas .........................................................................567 
1.2.2. Fórmulas ocultas .............................................................................568
1.3. Protegendo a estrutura das pastas de trabalho ................................569 
1.4. Protegendo intervalos .....................................................................570 
1.5. Protegendo a pasta de trabalho .......................................................574 
1.6. Criptografando a pasta de trabalho .................................................575 
1.7. Informações da pasta de trabalho ...................................................576
1.7.1. Propriedades ...................................................................................577 
1.7.2. Proteger Pasta de Trabalho .............................................................580 
1.7.3. Inspecionar Pasta de Trabalho .........................................................581
1.7.3.1. Inspecionar Documento ..................................................................582 
1.7.3.2. Verificar Acessibilidade ...................................................................583
1.7.4. Gerenciar Pasta de Trabalho ............................................................584 
1.7.5. Opções de Exibição do Navegador ..................................................585
Pontos principais ................................................................................................586 
Teste seus conhecimentos...................................................................................................... 587
Mãos à obra! ..................................................................................................................................591
12
SumárioApresentação
Bem-vindo!
É um prazer tê-lo como aluno do nosso curso online de Excel 2016 - Módulo II. 
Este curso é ideal para você que deseja aprimorar suas habilidades com técnicas 
avançadas para a criação de planilhas eletrônicas mais sofisticadas.
Você aprenderá a criar referências, usar intervalos nomeados, auditar fórmulas, 
organizar dados, trabalhar com tabelas dinâmicas e importar dados. Aprenderá, 
também, a trabalhar com funções e análise de dados, dando funcionalidade 
profissional ao resultado, além de conhecer diversas maneiras de proteger seus 
arquivos e conteúdo interno contra alterações. Dentre as ferramentas avançadas, 
aprenderá a automatizar tarefas repetitivas com o uso do gravador de macros e a 
dar interatividade às planilhas com os controles de formulário. Bom aprendizado!
Como estudar?
Videoaulas sobre os assuntos que você precisa saber no módulo II de 
Excel 2016.
Parte teórica, com mais exemplos e detalhes para você que quer se 
aprofundar no assunto da videoaula.
Exercícios para você pôr à prova o que aprendeu.
Material de apoio para você testar os exemplos e exercícios das aulas.
Assista! Estude!
Material de 
Apoio!
Pratique!
Aulas 1 a 4 - 
Referências e fórmulas
Esta Leitura Complementar refere-se ao conteúdo das Aulas 1 a 4.
 9 Referências de células;
 9 Intervalos nomeados;
 9 Auditoria de fórmulas;
 9 Opções de cálculo.
14
Excel 2016 - Módulo II (online) 
1.1. Introdução
O Excel é, sem dúvida, o programa de computador mais usado para edição 
de planilhas eletrônicas. Isso se deve, na maior parte, às suas ferramentas 
poderosas de manipulação de dados, fórmulas e gráficos.
Você verá, nesta leitura, alguns detalhes sobre a edição de fórmulas no Excel, 
a criação de vínculos entre planilhas e pastas de trabalho e como evitar alguns 
problemas causados por fórmulas que perdem as referências de células.
Um arquivo do Excel é chamado de Pasta de Trabalho e é composto por 
folhas de planilha e folhas de gráfico. Cada planilha contém 16.384 colunas, 
identificadas por letras do alfabeto, partindo de A até XFD. Essa mesma planilha 
possui 1.048.576 linhas numeradas. O cruzamento de uma linha com uma 
coluna é chamado de célula.
A ilustração a seguir mostra as principais áreas da tela do Excel 2016:
15
Aulas 1 a 4
Referências e fórmulas
1. Barra de ferramentas de acesso rápido;
2. Guia selecionada da Faixa de Opções;
3. Guia não selecionada da Faixa de Opções;
4. Barra de Título;
5. Faixa de Opções;
6. Botões de controle da janela e da Faixa de Opções;
7. Célula ativa (cursor);
8. Ponteiro do mouse;
9. Caixa de Nomes;
10. Divisor móvel entre Caixa de Nomes e Barra de Fórmulas;
11. Botões de inserção e edição de fórmulas e funções;
12. Barra de Fórmulas;
13. Grupo de comandos na Faixa de Opções;
14. Botão iniciador de caixa de diálogo do grupo;
15. Títulos de colunas;
16. Títulos de linhas;
17. Botões de navegação para as planilhas;
18. Guia da planilha ativa;
19. Guia de planilha não ativa;
20. Botão para inserção de nova planilha;
21. Barra de Status (mensagens);
22. Divisor móvel entre as guias de planilha e a barra de rolagem;
23. Barra de rolagem horizontal;
24. Botões para diferentes modos de visualização e zoom;
25. Barra de rolagem vertical.
16
Excel 2016 - Módulo II (online) 
1.2. Referências de células
A seguir, veja como fazer referências a células em uma mesma planilha, 
referências a células de outras planilhas e de outras pastas de trabalho. 
1.2.1. Referências na mesma planilha
A célula é identificada por um endereço (ou referência), o qual combina a letra 
da coluna com o número da linha. Exemplos: célula A1, célula H20, célula 
XY1234, etc. Os endereços de células são usados para compor as fórmulas.
Existem três tipos de endereços de células em fórmulas:
 • Relativo: Neste tipo de endereço, nenhum elemento (linha ou coluna) está 
fixo. Quando você copia uma célula contendo o endereço relativo em sua 
fórmula, o endereço é ajustado de acordo com a posição que será ocupada 
pela célula. Isso significa que, se a célula tiver a referência D3 em sua 
fórmula, quando você copiar essa célula para a linha de baixo, a referência 
passará a ser D4. Se copiar para a coluna da direita, a referência será E3;
 • Absoluto: É o tipo em que a linha e a coluna estão fixas, não sofrendo 
alteração quando a célula que contém a fórmula é copiada. O endereço 
absoluto tem um cifrão ($) antes da referência da linha e da coluna. Por 
exemplo, se uma célula tiver a referência $F$1 em sua fórmula e a mesma 
for copiada para outra célula, essa referência sempre será $F$1;
 • Misto: Neste tipo, somente a linha ou a coluna está fixa. O endereço misto 
tem um cifrão ($) antes da referência da linha ou da coluna. Por exemplo, 
em $G2, o cifrão fixa apenas a coluna. Já em G$2, o cifrão fixa apenas a 
linha.
17
Aulas 1 a 4
Referências e fórmulas
A ilustração a seguir mostra uma fórmula escrita inicialmente na célula B3. Note 
o que acontece com a referência D1 no final da fórmula, quando ela é copiada 
para a célula C6, em cada um dos tipos de referência: 
Para agilizar a digitação de uma fórmula, quando precisar alterar o 
tipo de referência da célula que você acabou de indicar, pressione 
a tecla F4. Cada acionamento de F4 mudará o tipo de referência. 
Exemplo: A1, $A$1, A$1 e $A1.
1.2.2. Referências a células de outras planilhas
Apesar de uma planilha poder conter grande quantidade de linhas e colunas, 
muitas vezes não faz sentido você realizar todo o trabalho somente em uma 
única folha. É para isso que o Excel permite a inserção de outras planilhas no 
arquivo.
A inclusão de uma referência de uma célula ou intervalo em uma fórmula é 
bastante fácil. É necessário, porém, incluir o nome da planilha, conforme a 
sintaxe genérica:
Nome_da_planilha!Intervalo
18
Excel 2016 - Módulo II (online) 
Quando o nome da planilha possui várias palavras, a sintaxe deverá incluir esse 
nome entre aspas simples:
'Nome da planilha'!Intervalo
Esse tipo de referência também é chamado de Referência 3D, pois considera 
uma pasta de trabalho como se fosse uma planilha tridimensional, permitindo 
cálculos e intervalos que percorrem várias planilhas.
Veja, a seguir, alguns exemplos de fórmulas com referências a outras planilhas 
da mesma pasta de trabalho: 
 • A fórmula abaixo simplesmente traz para a célula atual o conteúdo da célula 
C5 da planilha Vendas:
=Vendas!C5
 • A fórmula a seguir soma 3 células oriundas de 3 planilhas diferentes:
=Norte!H4 + Sul!G3 + Centro!F4
 • A próxima fórmula soma 2 valores de duas planilhas diferentes, nomeadas 
com mais de uma palavra:
='Filial Capital'!G2 + 'Filial Interior'!G2
 • A fórmula seguinte soma um intervalo que se encontra em outra planilha:
=SOMA(Planilha4!C3:C18)
 • A próxima fórmula soma todas as células B5, da planilha Janeiro até a 
planilha Dezembro:
=SOMA(Janeiro:Dezembro!B5)
 • Esta última fórmula soma um intervalo retangular de células desde a planilha 
JAN até a planilha DEZ:
=SOMA(JAN:DEZ!B3:H8)
19
Aulas 1 a 4
Referências e fórmulas
Os dois últimos exemplos ilustram bem o porquê do termo 3D. De fato, o 
Excel se comporta como uma planilha tridimensional, ou um “cubo” de células, 
localizadas por sua linha, coluna e planilha. Sendo assim, você consegue efetuar 
cálculos “através” das planilhas.
1.2.3. Referências a células de outras pastas de trabalho
Uma fórmula com referência a outra pasta de trabalho, ou seja, com uma 
referência externa, possui a seguinte sintaxe genérica:
='caminho_do_arquivo[nome_do_arquivo]nome_da_planilha'!intervalo
Em que:
 • caminho_do_arquivo é o caminho completo que indica a localização do 
arquivo. Antes do caminho, você deve abrir aspas simples (');
 • nome_do_arquivo é o nome do arquivo onde a referência se encontra. Deve 
ser seguido pela extensão (.xlsx ou .xlsm, por exemplo) e ser informado 
entre colchetes [ ];
 • nome_da_planilha é o nome da planilha onde a referência se encontra, 
seguidodo fechamento das aspas simples (') e de um ponto de exclamação 
(!);
 • intervalo é o endereço da célula ou intervalo de células que serão 
referenciados na fórmula.
Se o caminho não for conhecido, você pode localizar o arquivo usando 
o Windows Explorer no Windows 7 ou o Explorador de Arquivos 
no Windows 8 em diante. O caminho completo aparecerá na caixa 
Endereço. 
20
Excel 2016 - Módulo II (online) 
Você pode digitar o caminho completo para fazer referência à célula, mas é mais 
simples criar as fórmulas clicando nas células referenciadas com os arquivos 
abertos, deixando o Excel inserir o caminho completo.
Se a pasta referenciada estiver aberta, apenas o nome do arquivo 
aparecerá na referência da fórmula. Porém, se a pasta referenciada 
estiver fechada, o caminho completo do arquivo será adicionado à 
fórmula.
Veja, a seguir, alguns exemplos de fórmulas com referências a outras pastas de 
trabalho: 
 • A fórmula a seguir simplesmente traz para a célula atual o conteúdo da 
célula C5 da planilha Janeiro, da pasta de trabalho aberta Vendas.xlsx:
=[Vendas.xlsx]Janeiro!$C$5
 • A mesma situação anterior, porém a pasta Vendas.xlsx está fechada e salva 
em um disco da rede:
='L:\Clientes Novos\Capital\[Vendas.xlsx]Janeiro'!$C$5
 • A próxima fórmula soma a célula D2 da planilha Janeiro, da pasta de 
trabalho Vendas Filial Capital.xlsx, com a célula D2 da planilha Janeiro, 
da pasta de trabalho Vendas Filial Capital.xlsx. Todos os arquivos estão 
abertos:
='[Vendas Filial Capital.xlsx]Janeiro'!$D$2 + '[Vendas Filial In-
terior.xlsx]Janeiro'!$D$2
 • Esta última fórmula traz, para a célula que a contém, a soma de um intervalo 
localizado na planilha Janeiro, na pasta de trabalho aberta Vendas Filial 
Interior.xlsx:
=SOMA('[Vendas Filial Interior.xlsx]Janeiro'!$D$5:$D$15)
21
Aulas 1 a 4
Referências e fórmulas
Uma maneira rápida de alternar pelos aplicativos ou janelas abertas é pelo 
teclado, conforme os atalhos a seguir:
Atalho Ação
ALT + TAB
Alterna por todos os programas e janelas em 
execução no Windows.
ALT + SHIFT + TAB
O mesmo que o atalho anterior, porém em 
direção oposta.
CTRL + TAB
ou 
CTRL + F6
Alterna por todas as janelas do programa 
atual, no caso, o Excel. Não passa pelas janelas 
abertas por outros programas no Windows.
CTRL + SHIFT + TAB
ou 
CTRL + SHIFT + F6
O mesmo que o atalho anterior, porém em 
direção oposta.
Para que você possa acompanhar a prática de criação de fórmulas com vínculos 
a outros arquivos, faça as seguintes atividades:
1. Feche eventuais arquivos abertos no Excel;
2. Na pasta referente às Aulas 1 a 4, localize e abra 5 arquivos:
 • Relatório de Vendas - TOTAL.xlsx;
 • Relatório de Vendas - Região Norte.xlsx;
 • Relatório de Vendas - Região Sul.xlsx;
 • Relatório de Vendas - Região Leste.xlsx;
 • Relatório de Vendas - Região Oeste.xlsx.
22
Excel 2016 - Módulo II (online) 
3. Use o atalho CTRL + TAB para alternar entre eles e, depois, retorne ao arquivo 
Relatório de Vendas - TOTAL.xlsx: 
4. Na célula C4, você escreverá uma fórmula que resgata o valor do total para a 
região Norte do arquivo correspondente. Para começar, digite o sinal de igual 
(=):
23
Aulas 1 a 4
Referências e fórmulas
5. Pelo atalho CTRL + TAB, alterne até que o arquivo Relatório de Vendas - 
Região Norte.xlsx apareça;
6. Clique na célula F10 que contém o total: 
7. Tecle ENTER para encerrar a fórmula e retornar à célula C4 do arquivo Relatório 
de Vendas - TOTAL.xlsx: 
24
Excel 2016 - Módulo II (online) 
8. Repita a operação com as outras 3 regiões. No final, o arquivo de totais ficará 
como a imagem adiante (já havia um gráfico criado dentro dele):
9. Clique na célula C4 e observe a fórmula, a qual busca um valor que se encontra 
na célula F10 em outro arquivo aberto: 
='[Relatório de Vendas - Região Norte.xlsx]Norte'!$F$10
10. Feche os outros 4 arquivos. Mantenha aberto somente o arquivo de totais;
11. Veja novamente a fórmula em C4 e perceba que, agora, a referência ao 
nome do arquivo inclui todo o caminho onde ele está salvo (isso vai depender 
de onde você salvou os seus arquivos de curso): 
='C:\Users\Treinamento\Documents\Capítulo 1\[Relatório de Vendas 
- Região Norte.xlsx]Norte'!$F$10
12. Salve o arquivo e feche-o também.
25
Aulas 1 a 4
Referências e fórmulas
1.2.4. Gerenciando vínculos (links)
Quando você cria uma fórmula que aponta para um intervalo em outro arquivo, 
o nome desse arquivo é parte integrante da fórmula. Há, portanto, um vínculo 
(link) entre o arquivo que contém a fórmula e o arquivo referenciado.
Quando você abre um arquivo que contém vínculos com outros através de 
fórmulas, você verá uma faixa amarela na parte superior da planilha:
Esse AVISO DE SEGURANÇA apenas informa que os vínculos existem, porém, 
estão desabilitados. Clique no botão Habilitar Conteúdo para atualizar as 
fórmulas que buscam valores em outras pastas de trabalho.
Os vínculos representam uma boa ferramenta para que você não precise duplicar 
informações entre pastas e planilhas. Contudo, problemas podem surgir quando 
os arquivos referenciados são movidos, excluídos ou renomeados.
Para que você possa acompanhar as explicações, use os mesmos 5 arquivos da 
pasta de exercícios referente às Aulas 1 a 4. Siga as atividades:
1. Feche todos os arquivos do Excel;
26
Excel 2016 - Módulo II (online) 
2. Pelo Windows Explorer ou pelo Explorador de Arquivos, navegue até a pasta 
onde estão salvos os arquivos referentes às Aulas 1 a 4 e selecione o arquivo 
Relatório de Vendas - Região Sul.xlsx: 
3. Clique mais uma vez sobre o nome do arquivo (ou use o atalho F2) para 
editá-lo. Adicione o algarismo 2 no final do nome: 
4. Tecle ENTER para efetivar a mudança de nome: 
27
Aulas 1 a 4
Referências e fórmulas
5. Aplique um duplo-clique no arquivo Relatório de Vendas – TOTAL.xlsx para 
abrir o arquivo que possui as fórmulas com vínculos. Surgirá uma caixa de 
mensagens, indicando a presença de vínculos: 
6. Clique em Atualizar para que o Excel busque os valores dos vínculos e 
atualize as fórmulas;
Como um dos arquivos foi renomeado, o Excel não conseguiu encontrá-lo como 
esperado na pasta. Surge, portanto, uma outra caixa de mensagem que informa 
exatamente isso: 
Você tem, agora, a opção de Continuar sem que os vínculos sejam atualizados, 
ou Editar Vínculos... para resolver o problema, conforme detalhado a seguir.
7. Para gerenciar os vínculos de uma pasta de trabalho do Excel, na caixa de 
mensagem anterior, clique em Editar Vínculos... Alternativamente, pela faixa 
de opções, guia Dados, grupo Conexões, clique em Editar Links:
28
Excel 2016 - Módulo II (online) 
A caixa de diálogo Editar vínculos oferece uma maneira prática de gerenciar os 
links do arquivo, principalmente aqueles que geraram erros:
Essa caixa exibe uma lista com o nome dos arquivos vinculados à pasta de 
trabalho atual. Para executar qualquer operação com o vínculo, basta selecionar 
o nome do arquivo e clicar em um dos botões à direita, descritos a seguir:
Botão Descrição
Atualizar valores
Atualiza os valores a qualquer momento, principalmente 
se você não tiver optado pela atualização do vínculo na 
abertura do arquivo. 
Alterar Fonte
Redireciona o vínculo para outro arquivo. É útil quando o 
arquivo vinculado originalmente foi renomeado ou movido 
para outra pasta. O vínculo sempre é direcionado para 
a planilha com o mesmo nome e a mesma referência às 
células da planilha atualmente vinculada. Se a pasta de 
trabalho do novo vínculo não possuir uma planilha com 
o mesmo nome, será exibida uma caixa com as planilhas 
dessa pasta, para que você possa escolher a planilha do 
novo vínculo. 
Abrir Fonte Abre o arquivo selecionado na lista de vínculos.
Quebrar vínculo
Remove o vínculo da fórmula, mantendo apenas o resultado 
final. 
Verificar status Verifica o estado atual da fórmula que contém a referência. 
29
Aulas 1 a 4
Referências e fórmulas
Botão Descrição
Prompt de 
inicialização
Abre uma caixa de diálogo de configuração paradefinir a 
ação do Excel quando um arquivo for inicializado:
Agora, retomando o exemplo em uso, prossiga com as etapas para corrigir o 
link quebrado:
1. Na exibição da caixa de mensagem que indica que o vínculo não pôde ser 
atualizado, clique no botão Editar Vínculos;
2. Selecione, na lista, o arquivo Relatório de Vendas – Região Sul.xlsx, que 
apareceu com uma indicação de erro (sim, pois ele foi renomeado);
3. Clique no botão Alterar Fonte para exibir a janela Alterar Fonte. Localize o 
arquivo Relatório de Vendas – Região Sul2.xlsx e clique em Abrir:
30
Excel 2016 - Módulo II (online) 
4. Verifique agora que o arquivo não apresenta mais a indicação de erro porque 
o vínculo foi redirecionado para um arquivo existente:
5. Feche a caixa Editar vínculos.
1.3. Intervalos nomeados
Determinadas células e intervalos importantes e muito usados na planilha 
podem receber um nome. Esse nome será usado diretamente nas fórmulas e 
facilita muito a leitura dessa fórmula. Além disso, nomes podem ser usados em 
qualquer planilha da pasta de trabalho. Existem algumas maneiras para criar 
e usar esses nomes, mas, basicamente, você verá tudo o que precisa na guia 
Fórmulas, grupo Nomes Definidos:
Regras para definição de um nome de célula ou intervalo:
 • Deve começar por uma letra. O restante pode conter letras, letras acentuadas, 
números, símbolos e alguns caracteres especiais;
 • O Excel não diferencia maiúsculas de minúsculas;
31
Aulas 1 a 4
Referências e fórmulas
 • Não pode conter espaços. Use o caractere “_” para simular um separador de 
palavras;
 • Máximo de 255 caracteres;
 • Não use nomes que se pareçam com endereços de células. Exemplos: X25, 
ANO2018, TRI1, etc.;
 • Não use nomes reservados pelo Excel, como por exemplo: Criterios, Area_
de_extracao, Area_de_impressao, entre outros.
Além de intervalos nomeados, você também pode criar constantes e fórmulas 
nomeadas.
1.3.1. Criando intervalos nomeados
Para criar um ou mais intervalos nomeados, use uma das seguintes opções.
1.3.1.1. Pelo botão Definir Nome
Abre a caixa Novo Nome, onde você pode definir um nome para o intervalo, 
um comentário opcional e o local de referência, o qual pode ser também uma 
fórmula:
32
Excel 2016 - Módulo II (online) 
1.3.1.2. Pela Caixa de Nomes
Digite o nome desejado diretamente na Caixa de Nome, à esquerda da Barra de 
Fórmulas e tecle ENTER:
1.3.1.3. Pelo botão Criar a partir da Seleção
Permite a criação rápida de vários nomes de uma vez, tomando como base 
os textos que se encontram na primeira linha e primeira coluna do intervalo 
selecionado na planilha:
Todos os nomes foram criados de uma única vez. No exemplo anterior, cada 
título de linha na coluna esquerda (Janeiro, Fevereiro, etc.) corresponde a 
um intervalo horizontal com 3 valores naquela linha. Cada título de coluna na 
primeira linha (Filial SP, Filial RJ, Filial MG) corresponde a um intervalo vertical 
com 12 valores naquela coluna. Em particular, o nome que se encontra no canto 
superior esquerdo (Unidades) se refere a todos os 36 valores que se encontram 
abaixo e à direita dessa célula.
33
Aulas 1 a 4
Referências e fórmulas
1.3.2. Usando o nome criado
Após a criação do nome, usá-lo em fórmulas é bastante simples: sempre que 
precisar digitar o endereço da célula ou intervalo, use o nome no lugar. Ao 
começar a digitar, você verá uma lista com funções e intervalos nomeados por 
você:
Para selecionar o nome desejado mais facilmente, use as setas do teclado para 
localizá-lo e, em seguida, pressione TAB para terminar sua inserção na fórmula. 
Alternativamente, você pode aplicar um duplo-clique no nome que aparece na 
lista.
Outra forma prática para inserir um nome em uma fórmula que você está 
digitando é usar a caixa Colar nome, acionada pela tecla F3:
34
Excel 2016 - Módulo II (online) 
Ao selecionar o nome e clicar em OK, ele aparece digitado na fórmula e você 
poderá dar continuidade a ela, caso existam outros termos:
A caixa Colar nome funciona, inclusive, na maioria das caixas de 
diálogo do Excel, quando você precisa fornecer uma referência a um 
intervalo.
O nome criado, por padrão, é uma referência absoluta. Ou seja, ele sempre se 
refere ao mesmo intervalo, mesmo que a fórmula seja movida ou copiada:
35
Aulas 1 a 4
Referências e fórmulas
Para localizar uma célula nomeada ou intervalo nomeado em sua pasta de 
trabalho do Excel, pressione a tecla F5 ou, na guia Página Inicial, grupo Edição, 
clique no botão Localizar e Selecionar e escolha Ir para... Depois, selecione o 
nome desejado e clique em OK:
Alternativamente, clique na seta � da Caixa de nome e selecione o nome 
desejado: 
36
Excel 2016 - Módulo II (online) 
1.3.3. Gerenciando nomes
O Gerenciador de Nomes (atalho CTRL + F3) é o local que centraliza a relação 
de todos os nomes criados no arquivo. Nele também é possível criar um novo 
nome, editar um nome existente ou excluir um nome que não será mais usado:
1.4. Auditoria de fórmulas
Quando a planilha aumenta de tamanho ou fica mais complexa, existe a chance 
de você cometer erros matemáticos ou de lógica em suas fórmulas. Com a 
finalidade de visualizar o “fluxo” dos cálculos ou dos erros é que existem 
ferramentas de auditoria que auxiliam o processo. Elas criarão rastreadores, ou 
setas, que identificam as células envolvidas nos cálculos.
Observe as células e a fórmula do exemplo a seguir:
37
Aulas 1 a 4
Referências e fórmulas
Os rastreadores auxiliam na identificação de três tipos de células:
 • Precedentes: São células que são referenciadas por uma fórmula em outra 
célula. Por exemplo, se a célula C4 contiver a fórmula =C2-C2*C3, as células 
C2 e C3 são precedentes da célula C4 (fornecem valores para C4);
 • Dependentes: Contêm fórmulas que se referem a outras células. Por 
exemplo, se a célula C4 contiver a fórmula =C2-C2*C3, a célula C4 é 
dependente das células C2 e C3 (precisa dos valores de C2 e C3);
 • Erros: São as células que contêm algum tipo de erro e que são diretamente 
ou indiretamente referenciadas em uma fórmula, fazendo com que o erro 
apareça nessa fórmula.
As ferramentas de auditoria são encontradas na faixa de opções, guia Fórmulas, 
grupo Auditoria de Fórmulas:
O rastreamento produz uma série de setas azuis para as células precedentes 
e dependentes. O rastreamento de células com erro produz setas vermelhas. 
Esse “fluxo” identifica a direção do cálculo: de onde vêm e para onde vão os 
valores. As setas também são impressas com a planilha, porém não são salvas.
Para acompanhar o aprendizado desta seção, abra o arquivo Auditoria de 
Fórmulas.xlsx, disponível em sua pasta de exercícios referente às Aulas 1 a 4.
38
Excel 2016 - Módulo II (online) 
A pasta de trabalho possui três planilhas. As duas primeiras, Norte e Sul, 
contêm dados de várias categorias de produtos de exportação em milhares de 
toneladas. A terceira planilha, Brasil, é um relatório total que traz a soma das 
duas planilhas anteriores.
1.4.1. Rastreando precedentes
Realize as seguintes atividades para compreender o rastreamento de células 
precedentes sobre as planilhas de exemplo:
1. Na planilha Norte, clique na célula P8, que contém o total anual das categorias;
39
Aulas 1 a 4
Referências e fórmulas
2. Na guia Fórmulas, grupo Auditoria de Fórmulas, clique no botão Rastrear 
Precedentes para descobrir quais células geraram valores para esse total. As 
setas e os pontos azuis indicam que as células H8 (total no 1º semestre) e O8 
(total no 2º semestre) participaram do total anual em P8:
3. Clique mais uma vez no botão Rastrear Precedentes. Agora, as setas indicam 
que cada total semestral somou valores das 4 células acima dele: 
4. Clique mais uma vez no botão Rastrear Precedentes. Cada uma das 4 células 
nas colunas H e O realizam somas das 6 células à esquerda dela; 
5. Clique mais uma vez no botão Rastrear Precedentes. Você ouvirá um bipe. 
Desta vez, não haverá mais setas porque o rastreamento chegou ao final. Não 
há mais células precedentes àsjá marcadas: 
40
Excel 2016 - Módulo II (online) 
6. Clique na seta � do botão Remover Setas e selecione o comando Remover 
Setas Precedentes: 
7. O último rastreamento de precedentes foi removido. Clique diretamente 
dentro do botão Remover Setas para limpar todos os rastreamentos da planilha.
1.4.2. Rastreando dependentes
Realize as seguintes atividades para compreender o rastreamento de células 
dependentes sobre as planilhas de exemplo:
1. Novamente, na planilha Norte, clique agora na célula B5;
2. Na guia Fórmulas, grupo Auditoria de Fórmulas, clique no botão Rastrear 
Dependentes para descobrir quais células dependem de B5. Neste momento, 
os totais em B8 e H5 usaram diretamente o valor de B5. Note também um ícone 
de planilha e uma linha tracejada preta com uma seta apontando para ele. Isso 
indica que uma célula em outra planilha também usa o valor de B5: 
41
Aulas 1 a 4
Referências e fórmulas
3. Para descobrir as planilhas dependentes, aplique um duplo-clique na linha 
tracejada para abrir a caixa Ir para: 
4. Selecione o local na lista de intervalos e clique em OK para que o Excel ative 
essa célula. Portanto, a célula B5 da planilha Brasil também usa o valor de B5 da 
planilha Norte. Esta célula contém uma fórmula que faz a soma entre planilhas: 
5. Retorne à planilha Norte e permaneça na célula B5;
6. Clique mais uma vez em Rastrear Dependentes. Desta vez, as setas 
se estendem, mostrando que o total do 1º semestre em H8 também usa 
indiretamente o valor de B5, bem como o total anual em P5: 
42
Excel 2016 - Módulo II (online) 
7. Clique mais uma vez em Rastrear Dependentes. A célula B5 também 
influencia o total geral em P8, bem como os totais na planilha Brasil:
8. Clique mais uma vez em Rastrear Dependentes. Desta vez, você ouvirá um 
bipe para sinalizar que não há mais continuidade nesse rastreamento;
9. Clique na seta � do botão Remover Setas e selecione o comando Remover 
Setas Dependentes: 
10. O último rastreamento de dependentes foi removido. Clique diretamente 
dentro do botão Remover Setas para limpar todos os rastreamentos da planilha.
1.4.3. Exibindo todas as fórmulas da planilha
Para ver a fórmula digitada na célula, você pode clicar nela e observar o conteúdo 
na barra de fórmulas ou editá-la dando um duplo clique nela ou pela tecla F2.
43
Aulas 1 a 4
Referências e fórmulas
O botão Mostrar Fórmulas no grupo Auditoria de Fórmulas oferece um meio 
de visualizar todas as fórmulas da planilha, simultaneamente:
Enquanto as fórmulas estão sendo exibidas, as células não mostram formatação 
numérica; apenas o que foi originalmente digitado nelas. As colunas ficarão 
com uma largura maior.
Para retornar a planilha à visualização normal, clique novamente no botão 
Mostrar Fórmulas para desativá-lo.
1.4.4. Detectando e resolvendo erros em fórmulas
Erros podem ser resultantes de digitação de valores incorretos, fórmulas com 
argumentos não adequados ou resultado de cálculos matemáticos em situações 
específicas.
O Excel pode mostrar, nas células da planilha, os seguintes erros:
Erro Descrição
#DIV/0! Resultado de uma divisão por zero (0).
#REF!
Uma fórmula contém uma referência de célula que não é válida. 
Geralmente, acontece quando uma fórmula faz referência a células 
que foram excluídas ou substituídas por outros dados. 
#NÚM!
Uma fórmula ou função contém valores numéricos que não são 
válidos.
#VALOR!
Uma fórmula inclui células que possuem dados diferentes do tipo 
esperado (exemplo: texto em vez de número).
#NOME?
O Excel não reconhece nome de intervalo ou de função dentro de 
uma fórmula.
#N/D
Não é propriamente um erro, mas indica que um valor procurado 
em uma tabela não pode ser encontrado.
44
Excel 2016 - Módulo II (online) 
No exemplo usado neste tópico, imagine que a planilha Norte possui um erro 
na célula B5 (você pode provocar um erro em B5 teclando a fórmula =1/0).
Observe que o erro se propaga em todas as células dependentes de B5, inclusive, 
na planilha Brasil:
O Excel oferece alguns mecanismos para ajudar você a encontrar a origem do 
erro e também algumas formas de resolvê-lo.
1. Na guia Fórmulas, grupo Auditoria de Fórmulas, clique no botão Verificação 
de Erros, ou na seta � ao lado dele e na opção Verificação de Erros:
A caixa de diálogo Verificação de erros aparece, exibindo botões ou opções 
diferentes para cada situação de erro:
45
Aulas 1 a 4
Referências e fórmulas
Em que:
Botão Descrição
Ajuda sobre 
este erro
Exibe um artigo de ajuda do Excel sobre o erro em 
questão.
Rastrear erro
Opção exibida caso o erro seja de um valor de retorno 
de uma fórmula. As células que deram origem ao erro 
serão apontadas por setas vermelhas.
Mostrar etapas 
de cálculo...
Opção exibida quando ocorre um erro durante o 
cálculo de uma fórmula.
Ignorar erro Ignora o erro e não faz alteração alguma na célula.
Editar na barra 
de fórmulas
Possibilita a edição da fórmula na barra de fórmulas.
Opções...
Exibe a guia Fórmulas, dentro das Opções do Excel, 
permitindo a configuração de diversos parâmetros 
em relação às fórmulas.
Anterior Seleciona a célula anterior, que contenha algum erro.
Avançar
Avança para a próxima célula que contenha algum 
erro.
Retomar Retorna ao início do ciclo de verificação de erros.
1.4.4.1. Rastreando erros
Para determinar a origem do erro em uma fórmula, usando a pasta de trabalho 
do exemplo, na planilha Norte:
1. Clique na célula P8, que contém o total geral;
2. Na guia Fórmulas, grupo Auditoria de Fórmulas, clique na seta � ao lado 
do botão Verificação de erros e selecione o comando Rastrear Erro: 
46
Excel 2016 - Módulo II (online) 
3. Observe, na planilha, que o Excel identificou a origem do erro como sendo 
a célula B5 e esta foi selecionada. Além disso, traçou uma sequência de setas 
vermelhas para mostrar o fluxo dos cálculos. A barra de fórmulas mostra o que 
estava digitado na célula B5 e que causou o erro: 
4. Em B5, digite o valor 173 e tecle ENTER para corrigir o erro. Note agora que 
as setas existentes ficaram azuis, indicando que se trata de um rastreamento 
normal de células dependentes e não mais um erro;
5. Clique no botão Remover Setas para limpar os rastreamentos na planilha: 
6. Salve o arquivo e feche-o em seguida. Lembre-se que as setas são desligadas 
no fechamento do arquivo.
1.4.4.2. Avaliando fórmulas
Independentemente da fórmula conter algum erro, você pode ver como ela é 
resolvida pelo Excel, passo a passo. Isso é útil para resolver problemas com 
a lógica na hora de elaborar uma fórmula. Pode ser que você tenha usado 
parênteses no lugar errado ou se esquecido de algum termo.
47
Aulas 1 a 4
Referências e fórmulas
Para você praticar a resolução de fórmulas passo a passo, abra o arquivo 
Avaliação de Fórmulas.xlsx, disponível na pasta referente às Aulas 1 a 4: 
A Planilha1 contém uma relação de produtos e preços nas colunas B e C. Serão 
compradas várias unidades desses produtos (célula F2). Sobre o total, será 
aplicado um desconto (célula F4) e adicionado o frete no final (célula F6).
Escreva, na célula F8, a fórmula que realiza o cálculo completo, incluindo todos 
os valores citados:
Retorne à célula F8 e realize as seguintes atividades para resolver o cálculo 
passo a passo:
48
Excel 2016 - Módulo II (online) 
1. Na guia Fórmulas, grupo Auditoria de Fórmulas, clique no botão Avaliar 
fórmula para abrir a caixa Avaliar fórmula:
2. A caixa Avaliação mostra a fórmula original, conforme digitada na célula F8. 
A parte sublinhada indica o termo que será executado em seguida. Clique no 
botão Avaliar: 
3. O termo sublinhado foi resolvido e o resultado do cálculo 3358 foi deixado 
no lugar, em itálico, para sinalizar um termo já calculado. Agora, a referência F2 
está sublinhada. Será o próximo valor a ser calculado. Clique no botão Avaliar: 
4. O conteúdo da célula F2, o valor 3, é colocado na expressão e ficou em 
itálico. O próximo termo a ser calculado será 3358*3, que aparece sublinhado. 
Clique no botão Avaliar;49
Aulas 1 a 4
Referências e fórmulas
5. Prossiga com o restante dos cálculos, clicando no botão Avaliar para cada 
etapa, até chegar no resultado final: 
6. Se desejar refazer a sequência, clique no botão Reiniciar;
7. Clique em Fechar quando terminar de avaliar a fórmula.
Quando o termo que será avaliado for uma referência de célula, a caixa Avaliar 
fórmula habilitará o botão Depuração total. Ao clicar nele, você verá outra 
caixa, mostrando o conteúdo da célula referenciada e que pode ser outra 
fórmula, permitido que você também a execute passo a passo:
Para calcular o termo e retornar à depuração completa em uma única expressão, 
clique no botão Depuração circular.
50
Excel 2016 - Módulo II (online) 
1.4.4.3. Usando o botão de verificação de erros
Quando o Excel percebe que uma fórmula está diferente das fórmulas vizinhas, 
ou parece haver algum tipo de inconsistência, a célula que contém essa 
fórmula ficará com uma marca verde em seu canto superior esquerdo. Isso não 
necessariamente indica um erro, mas merece atenção.
Suponha que uma planilha contenha informações de valores de 4 bimestres e 
um total:
A fórmula com o total na célula E2 possui uma pequena marca triangular verde 
em seu canto superior esquerdo. É indicação de que há algo inconsistente na 
fórmula.
Quando você clica na célula que contém a fórmula, verá um pequeno botão 
aparecendo no lado esquerdo dela:
Esse botão apareceu porque, supostamente, a soma deveria ser dos 4 bimestres. 
A fórmula mostra que foram somados apenas os 3 primeiros. Faltou o último 
valor.
51
Aulas 1 a 4
Referências e fórmulas
Ao clicar no botão, você verá um menu de contexto, mostrando como primeiro 
item o tipo de erro: A fórmula omite células adjacentes: 
Com os itens seguintes, você pode decidir o que fazer:
Opção Descrição
Atualizar fórmula 
para incluir 
células
Efetivamente corrige a fórmula para incluir a célula 
que falta ao intervalo da soma. Feita a correção, a 
marca na célula desaparece. Note que esta opção varia 
conforme o tipo de erro.
Ajuda sobre este 
erro
Abre a janela de ajuda do Excel com artigos relacionados 
ao erro em questão.
Ignorar erro Ignora o erro apenas para a célula em questão.
Editar na barra 
de fórmulas
Inicia o modo de edição da fórmula com o cursor 
na barra de fórmulas para que você possa fazer 
manualmente a correção.
Opções de 
Verificação de 
Erros...
Abre a caixa de Opções do Excel com o item Fórmulas 
selecionado e suas opções de configuração abertas. 
Na seção Verificação de Erros, você pode desligar 
as marcas nas células ou mesmo trocar a cor dessas 
marcas.
1.4.4.4. Referências circulares
Referências circulares são indicações na pasta de trabalho quando há problemas 
com fórmulas que referenciam a própria célula que contém a fórmula, direta ou 
indiretamente.
52
Excel 2016 - Módulo II (online) 
Veja o seguinte exemplo:
Na célula M3, ao escrever a fórmula que soma todos os valores da linha 3, 
acidentalmente, você incluiu a própria célula que contém a função de SOMA:
Ao teclar ENTER para confirmar a fórmula, aparece a seguinte caixa de mensagem:
Quando isto acontece, o Excel não realiza os cálculos na planilha. Observe que 
o total ficou zerado:
53
Aulas 1 a 4
Referências e fórmulas
A barra de status, no canto inferior esquerdo da tela, mostra a indicação 
Referências Circulares: M3. Provavelmente, a célula mencionada (M3) seja a 
responsável pelo erro:
Se essa indicação for suficiente para que você faça a correção, basta editar a 
fórmula e, ao teclar ENTER, a indicação desaparece e o resultado é calculado:
Há situações um pouco mais complexas, como o exemplo a seguir:
Em que:
 • A célula B2 contém a fórmula =2*F2;
 • A célula F2 contém a fórmula =D4/5;
 • A célula D4 continha o número 100, gerando os resultados 40 e 20 nas 
células B2 e F2 respectivamente;
 • Na célula D4, foi inserida a fórmula =B2+10. 
54
Excel 2016 - Módulo II (online) 
A situação causou uma referência circular e o Excel posicionou automaticamente 
setas azuis rastreadoras para você visualizar esse ciclo fechado.
Nessa situação, na guia Fórmulas, grupo Auditoria de Fórmulas, clique na 
seta � do botão Verificação de Erros e, depois, em Referências Circulares, 
para ver quais células foram envolvidas nesses cálculos: 
Acesse cada célula envolvida na referência circular para verificar onde pode 
estar o erro e faça a correção até a indicação desaparecer na barra de status.
Como as referências circulares podem acontecer também entre 
planilhas da mesma pasta de trabalho e até entre arquivos diferentes, 
assim que ocorrer a primeira indicação de referência circular, corrija 
imediatamente a fórmula que você acabou de digitar. É sempre mais 
fácil corrigir uma fórmula na hora do erro, do que deixar isso se 
propagar até se tornar algo complexo demais.
1.4.5. Janela de inspeção
A Janela de inspeção permite que você monitore valores em células específicas 
que podem estar em outras planilhas. É uma boa ferramenta para acompanhar 
mudanças entre várias planilhas, sem que você tenha que ficar alternando entre 
elas.
55
Aulas 1 a 4
Referências e fórmulas
Para ativar a Janela de inspeção, na guia Fórmulas, grupo Auditoria de Fórmulas, 
clique no botão Janela de inspeção:
A janela começa flutuante na planilha, mas pode ser movimentada (use a barra 
de título) ou “ancorada” em um dos 4 lados da planilha.
Para acompanhar a explicação deste tópico, abra novamente a pasta de trabalho 
Auditoria de Fórmulas.xlsx, disponível em sua pasta de exercícios referente 
às Aulas 1 a 4 e ative a planilha Brasil. Suponha que você deseje monitorar as 
mudanças de valores nos totais semestrais e anuais da linha 8 (H8, O8, P8), 
enquanto experimenta valores nas planilhas Norte e Sul:
Siga as etapas para praticar o uso da Janela de inspeção:
1. Arraste a janela para um local que não atrapalhe a visualização da tabela 
ou para a parte superior da planilha, até que ela fique encaixada e use toda a 
largura disponível;
2. Na planilha Brasil, selecione as 3 células H8, O8 e P8;
56
Excel 2016 - Módulo II (online) 
3. Na Janela de inspeção, clique no botão Adicionar inspeção de variáveis 
para inserir a informação das 3 células na janela:
4. Clique em OK para incluir as 3 células:
5. Se desejar, redimensione as colunas da Janela de inspeção para visualizar 
melhor as informações;
6. Vá até a planilha Norte e, na célula B4, digite o número 300;
7. Observe, na Janela de inspeção, a alteração na célula H8 (total do 1º semestre) 
e P8 (total anual):
8. Vá até a planilha Sul e, na célula K6, digite o número 50;
57
Aulas 1 a 4
Referências e fórmulas
9. Observe, na Janela de inspeção, a alteração na célula O8 (total do 2º semestre) 
e P8 (total anual):
10. As informações de inspeções de variáveis ficam salvas junto com o arquivo. 
Para remover uma ou mais células inspecionadas, selecione-as na Janela de 
inspeção e clique no botão Excluir inspeção de variáveis:
11. Quando terminar de trabalhar com a Janela de inspeção, feche-a, clicando 
no “X” no canto superior direito dela.
1.5. Opções de cálculo
Na guia Fórmulas, grupo Cálculo, você encontrará alguns botões e comandos 
que controlam os cálculos nas planilhas do arquivo:
58
Excel 2016 - Módulo II (online) 
As opções existentes no grupo Cálculo são as seguintes:
Botão/Comando Descrição
Opções de 
Cálculo
Permite a escolha da forma de atualização dos cálculos 
no arquivo aberto:
 • Automático: Atualiza todas as fórmulas de todas as 
planilhas quando há qualquer alteração de conteúdo 
no Excel;
 • Automático Exceto em Tabelas de Dados: Atualiza 
todas as planilhas, porém não interfere em tabelas de 
dados;
 • Manual: Não atualiza as fórmulas quando há 
mudanças nas células do arquivo. 
Calcular Agora
Força o Excel a recalcular todas as fórmulas de todas as 
planilhas do arquivo ativo. 
Calcular Planilha
Força o Excel a recalcular somente as fórmulas da 
planilha ativa.
59
Aulas 1 a 4
Referências e fórmulas
Pontos principaisAtente para os tópicos a seguir. Eles devem ser estudados com muita 
atenção, pois representam os pontos mais importantes da leitura.
 • A célula é identificada por um endereço (ou referência), o qual combina a 
letra da coluna com o número da linha. Exemplos: célula A1, célula H20, 
célula XY1324, etc. Os endereços de células são usados para compor as 
fórmulas, as quais usam seus valores para calcular um resultado;
 • Há 3 tipos de endereços de células: o endereço relativo (A1) sempre é 
ajustado quando a fórmula é copiada; o endereço absoluto ($A$1) nunca 
é ajustado quando a fórmula é copiada; o endereço misto ($A1, A$1) tem 
apenas a coluna ou apenas a linha ajustada durante a movimentação da 
fórmula;
 • É possível você usar nas suas fórmulas valores que estão em outras planilhas 
do mesmo arquivo ou até em outras pastas de trabalho do Excel. Essas 
referências externas também são chamadas de links;
 • Nomear uma célula ou intervalo se torna prático e útil porque facilita a 
leitura das fórmulas e os nomes podem ser usados em qualquer planilha 
da pasta de trabalho. Você encontra as ferramentas para nomes na guia 
Fórmulas, grupo Nomes Definidos. A principal ferramenta é o Gerenciador 
de Nomes;
 • Ferramentas de auditoria de fórmulas permitem que você investigue a 
lógica empregada nas fórmulas e veja o fluxo dos cálculos através de setas 
de rastreamento. Células precedentes são referenciadas por uma fórmula 
em outra célula. Células dependentes contêm fórmulas que se referem a 
outras células;
 • Com as ferramentas de auditoria você também pode rastrear erros, 
acompanhar as mudanças em um conjunto selecionado de células variáveis 
e até mesmo executar os cálculos do Excel, visualizando-os em etapas.
60
Excel 2016 - Módulo II (online) 
Aulas 1 a 4 -
Referências 
e fórmulas
 Teste seus conhecimentos
Estes testes referem-se ao conteúdo das Aulas 1 a 4.
62
Excel 2016 - Módulo II (online) 
1. Uma célula em uma planilha possui a seguinte fórmula: 
=(B2+C2)/D$2. Se essa fórmula for copiada duas linhas para 
baixo, como ela ficará?
2. Em uma planilha, foi inserida uma fórmula que possui 
uma referência a outra pasta de trabalho, também aberta. 
Após a montagem da fórmula, a pasta referenciada foi 
fechada. Qual a alternativa correta com relação à fórmula 
com a referência externa?
 ☐ a) =(B4+C4)/$D$2
 ☐ b) =(B4+C4)/D$2
 ☐ c) =(B4+C4)/D$4
 ☐ d) =(B2+C2)/D$2
 ☐ e) Nenhuma das alternativas anteriores está correta.
 ☐ a) A fórmula mostra o caminho completo do arquivo 
fechado (disco, pasta e nome do arquivo).
 ☐ b) A fórmula mostra apenas o nome do arquivo fechado.
 ☐ c) A fórmula resulta em um erro de referência #REF!.
 ☐ d) Não é possível criar fórmulas que apontam para 
intervalos de arquivos que estão fechados.
 ☐ e) Nenhuma das alternativas anteriores está correta.
63
Aulas 1 a 4
Referências e fórmulas
3. Quando estiver atribuindo um nome a um intervalo, qual 
das alternativas a seguir deve ser considerada?
4. Para que servem as ferramentas de Auditoria de Fórmulas?
 ☐ a) O nome deve conter obrigatoriamente letras e números e 
pode iniciar com uma letra ou com um número.
 ☐ b) O nome pode conter espaços.
 ☐ c) Você pode usar nomes que se parecem com intervalos 
(Exemplos: A1, ANO2018) e nomes que já são reservados 
pelo Excel em algumas situações (exemplos: Area_de_
extracao, Area_de_impressao).
 ☐ d) O Excel não diferencia maiúsculas de minúsculas.
 ☐ e) Todas as alternativas anteriores estão corretas.
 ☐ a) Visualizar o fluxo do cálculo com setas rastreadoras, 
localizar erros e avaliar fórmulas passo a passo.
 ☐ b) Classificar e filtrar um banco de dados de forma correta.
 ☐ c) Verificar se todas as fórmulas foram iniciadas com o 
sinal de igual (=) e se possuem funções com os argumentos 
corretos.
 ☐ d) Garantir que os intervalos nomeados foram criados com as 
regras corretas para definição dos nomes.
 ☐ e) Nenhuma das alternativas anteriores está correta.
64
Excel 2016 - Módulo II (online) 
5. Para que serve o Gerenciador de Nomes?
 ☐ a) Criar um novo intervalo nomeado.
 ☐ b) Editar um intervalo nomeado existente.
 ☐ c) Excluir um intervalo nomeado.
 ☐ d) Visualizar todos os nomes existentes no arquivo.
 ☐ e) Todas as alternativas anteriores estão corretas.
Aulas 1 a 4 -
Referências e 
fórmulas
 Mãos à obra!
Este laboratório refere-se ao conteúdo das Aulas 1 a 4.
66
Excel 2016 - Módulo II (online) 
Estes exercícios complementam o aprendizado. Cada exercício contém as 
instruções necessárias para você completar as atividades, bem como menção 
aos arquivos que devem ser abertos, caso necessário.
Para realizar as atividades práticas deste laboratório, abra as pastas de 
trabalho mencionadas nas atividades a seguir.
Laboratório 1 
A – Fazendo referências de células, intervalos nomeados, edição de links e 
auditoria de fórmulas
Abra a pasta de trabalho Exercícios de Laboratório - Capítulo 1.xlsx. Na 
planilha Semestre 1, observe a tabela com 6 categorias de produtos de uma 
cadeia de lojas com 5 filiais:
As células que você usará para criar fórmulas e referências já estão todas 
devidamente formatadas.
67
Aulas 1 a 4
Referências e fórmulas
Há 6 outras planilhas, uma para cada mês, nomeadas como Jan, Fev, etc., as 
quais contêm, cada uma, informações de vendas dessas 6 categorias por cada 
uma das 5 filiais:
Abra também a pasta de trabalho Cotações de Moedas.xlsx. Suponha que essa 
pasta ficará em um local de sua rede corporativa e será de responsabilidade 
de um colaborador a atualização diária das cotações de 4 moedas, conforme 
consta na Planilha1:
Várias pastas de trabalho da empresa usam essas cotações. É necessário que 
elas fiquem centralizadas em um único arquivo para que as atualizações sejam 
propagadas automaticamente nos outros arquivos que dependem dessas 
cotações.
68
Excel 2016 - Módulo II (online) 
Realize as seguintes atividades para praticar referências de células, intervalos 
nomeados, edição de links e auditoria de fórmulas:
1. Alterne para a pasta Exercícios de Laboratório - Capítulo 1.xlsx e vá até a 
planilha Semestre 1;
2. Na célula C5, escreva a fórmula que totaliza as vendas dos 6 meses e das 5 
filiais (é o total geral do 1º semestre para cada categoria de produtos);
3. Calcule o total na célula C11;
4. Na célula D2, crie uma referência externa à pasta Cotações de Moedas.xlsx, 
célula C4 (cotação do Dólar Americano);
5. Na célula E2, crie uma referência externa à pasta Cotações de Moedas.xlsx, 
célula C6 (cotação do Euro);
6. Feche a pasta de trabalho Cotações de Moedas.xlsx e observe como ficaram 
as referências externas em D2 e E2, agora mostrando o caminho completo de 
onde ela foi salva;
7. Nomeie a célula D2 como Dólar e a célula E2 como Euro;
8. Acione o Gerenciador de Nomes para observar os dois nomes criados. Feche 
o gerenciador em seguida;
9. Na coluna D, crie as fórmulas para converter os valores em Reais da coluna 
C para Dólar. Use o nome criado. Totalize na célula D11;
10. Na coluna E, crie as fórmulas para converter os valores em Reais da coluna 
C para Euro. Use o nome criado. Totalize na célula E11;
69
Aulas 1 a 4
Referências e fórmulas
11. Na coluna F, calcule a participação (em %) de cada uma das 6 categorias, em 
relação ao total em C11. Totalize na célula F11: 
12. Salve e feche a pasta Exercícios de Laboratório - Capítulo 1.xlsx;
13. Pelo Windows Explorer (ou Explorador de Arquivos), vá até a pasta referente 
às Aulas 1 a 4 e altere o nome do arquivo Cotações de Moedas.xlsx para 
Índices Financeiros.xlsx;
14. Abra o arquivo que você acabou de renomear e faça a atualização de valores 
do Dólar e do Euro (se desejar, consulte os índices reais em sites específicos). 
Salve e feche o arquivo em seguida;
15. Abra novamente a pasta de trabalho do Excel Exercícios de Laboratório - 
Capítulo 1.xlsx. Na abertura, habilite o conteúdo dos vínculos externos. Uma 
mensagem informará que alguns vínculos não puderam ser atualizados. Edite 
os vínculos e corrija