contador web Saltar al contenido

10 advanced Excel tips and tricks | Productivity

Excel spreadsheets are a great option for comparing and operating data and its variants in rows and columns. However, this Office suite tool also has excellent features for converting units, analyzing economic scenarios or even filtering, selecting and editing multiple items. These functions, which are not known by many basic users of the program, can make life easier for those who use spreadsheets at work or on a daily basis. Check out ten short tutorials on advanced tricks and tips from Microsof Excel.

Seven Excel functions that few people know

How to create charts in Microsoft Excel

How to create charts in Microsoft Excel

Want to buy cell phones, TV and other discounted products? Meet Compare TechTudo

With the convenient Find feature, Microsoft Excel, like other Office programs, can quickly find items and terms in a document. However, in order to edit multiple data simultaneously, the application allows you to select all.

Step 1. Check the entire table with the data you want to search for and select. Then, on the Home page tab, choose the Find and Select option and check Find …;

With the data selected, press on the controls on the tab Home page highlighted Photo: Reproduo / Daniel RibeiroWith the data selected, press on the controls on the tab Home page highlighted Photo: Reproduo / Daniel Ribeiro

With the data selected, press on the controls on the tab Home page highlighted Photo: Reproduo / Daniel Ribeiro

Step 2. In the Find and replace window, type the term you want to find and press Find all;

Enter the item you want to search for and press Find all Photo: Reproduo / Daniel RibeiroEnter the item you want to search for and press Find all Photo: Reproduo / Daniel Ribeiro

Enter the item you want to search for and press Find all Photo: Reproduo / Daniel Ribeiro

Step 3. Select one of the displayed items and press on the Ctrl + A keyboard to select all the results simultaneously. Then press close the marked cells will remain selected;

Click on an item in the results and Ctrl + A to select all Photo: Reproduo / Daniel RibeiroClick on an item in the results and Ctrl + A to select all Photo: Reproduo / Daniel Ribeiro

Click on an item in the results and Ctrl + A to select all Photo: Reproduo / Daniel Ribeiro

Step 4. Through this selection it is possible to delete, highlight or copy all selected items, and at the same time.

With the advanced selection you can edit or delete all items found Photo: Reproduo / Daniel RibeiroWith the advanced selection you can edit or delete all items found Photo: Reproduo / Daniel Ribeiro

With the advanced selection you can edit or delete all items found Photo: Reproduo / Daniel Ribeiro

Excel's transposition feature allows you to convert columns to rows and vice versa. However, with the advanced transposition, the user uses a formula, linking the data of the transposed table and causing any modification of the items to be replicated.

Step 1. Select the number of cells and columns equivalent to the number of columns and cells you want to transpose for example: if a table is 2X5, select cells in the format 5X2;

Mark a number of cells equivalent to the spreadsheet that will be transposed Photo: Reproduction / Daniel RibeiroMark a number of cells equivalent to the spreadsheet that will be transposed Photo: Reproduction / Daniel Ribeiro

Mark a number of cells equivalent to the spreadsheet that will be transposed Photo: Reproduction / Daniel Ribeiro

Step 2. Still with the cells marked, write the function TRANSPOR (X: Y) where X is the first cell in the upper left corner of the table, and Y is the last cell in the lower right corner;

Then write TRANSPOR (X: Y), with X and Y indicating the beginning and end of the table that will be transposed Photo: Reproduo / Daniel RibeiroThen write TRANSPOR (X: Y), with X and Y indicating the beginning and end of the table that will be transposed Photo: Reproduo / Daniel Ribeiro

Then write TRANSPOR (X: Y), with X and Y indicating the beginning and end of the table that will be transposed Photo: Reproduo / Daniel Ribeiro

Step 3. The table will be transposed by the function, setting the original values. However, any change in the values ??in the original table will immediately change the transposed items.

The transposed table will have fixed values ??and reflect any changes in the original table Photo: Reproduo / Daniel RibeiroThe transposed table will have fixed values ??and reflect any changes in the original table Photo: Reproduo / Daniel Ribeiro

The transposed table will have fixed values ??and reflect any changes in the original table Photo: Reproduo / Daniel Ribeiro

Excel has a special filter for tables identified in your spreadsheet. The segmentation of data presents special macros with the items of the table, presenting results in an excellent visual layout.

Step 1. Select the cells that will be identified as a table and, on the Insert tab, choose the Table option;

Mark the cells and click on the highlighted commands to insert a table Photo: Reproduo / Daniel RibeiroMark the cells and click on the highlighted commands to insert a table Photo: Reproduo / Daniel Ribeiro

Mark the cells and click on the highlighted commands to insert a table Photo: Reproduo / Daniel Ribeiro

Step 2. In the window that will open, check the selection of the cells that make up the table, check the option My table has headers and confirm with OK;

In the Create Table window, check My table has headers and click OK Photo: Reproduo / Daniel RibeiroIn the Create Table window, check My table has headers and click OK Photo: Reproduo / Daniel Ribeiro

In the Create Table window, check My table has headers and click OK Photo: Reproduo / Daniel Ribeiro

Step 3. Then, on the Design tab, click on the Insert Data Segment option to activate the dynamic filtering system of the created table;

Created the table, press the Insert Data Segment button, in the Design tab Photo: Reproduo / Daniel RibeiroCreated the table, press the Insert Data Segment button, in the Design tab Photo: Reproduo / Daniel Ribeiro

Created the table, press the Insert Data Segment button, in the Design tab Photo: Reproduo / Daniel Ribeiro

Step 4. A window will open with options for the headings of the created table. Select as needed and press OK;

Choose the column headers you want to use Photo: Reproduo / Daniel RibeiroChoose the column headers you want to use Photo: Reproduo / Daniel Ribeiro

Choose the column headers you want to use Photo: Reproduo / Daniel Ribeiro

Step 5. Once the data segment is created, each option marked in the header windows will eliminate the incompatible alternatives with the others. Likewise, selected items will be shown in the table, while unmarked items will remain hidden.

Each marked item will be displayed in the table, while the others will be hidden Photo: Reproduo / Daniel RibeiroEach marked item will be displayed in the table, while the others will be hidden Photo: Reproduo / Daniel Ribeiro

Each marked item will be displayed in the table, while the others will be hidden Photo: Reproduo / Daniel Ribeiro

The presentation of data in Excel spreadsheets facilitates the visualization of different results. However, with the Scenario Manager feature it is possible to change variables and make reports with a scale from the best to the worst possible results.

Step 1. In a spreadsheet, select the variable values ??and, on the Data tab, click on the Hypothesis Test button and then on the alternative Scenario Manager …;

Mark the variable values ??in the table and click on the commands highlighted in the image Photo: Reproduo / Daniel RibeiroMark the variable values ??in the table and click on the commands highlighted in the image Photo: Reproduo / Daniel Ribeiro

Mark the variable values ??in the table and click on the commands highlighted in the image Photo: Reproduo / Daniel Ribeiro

Step 2. The Edit scenario window will open. Type a name in the Scenario Name field and click OK to continue configuring one of the possible scenarios;

Name the scenario and press OK Photo: Reproduction / Daniel RibeiroName the scenario and press OK Photo: Reproduction / Daniel Ribeiro

Name the scenario and press OK Photo: Reproduction / Daniel Ribeiro

Step 3. Then, change the values ??of each variable, according to the cells previously chosen, and finish with OK;

Continue editing the scenario by choosing the values ??for each variable and click OK Photo: Reproduction / Daniel RibeiroContinue editing the scenario by choosing the values ??for each variable and click OK Photo: Reproduction / Daniel Ribeiro

Continue editing the scenario by choosing the values ??for each variable and click OK Photo: Reproduction / Daniel Ribeiro

Step 4. Repeat the operation to add other scenarios and, when you are satisfied, select one of them, in the Scenario Manager window, press Show to view the scenario. The spreadsheet formulas will immediately incorporate and operate the scenario values;

Select a scenario and click on Show to view it, as well as the result with the formulas Foto: Reproduo / Daniel RibeiroSelecione um cenrio e clique em Mostrar para visualiz-lo, bem como o resultado com as frmulas  Foto: Reproduo/Daniel Ribeiro

Selecione um cenrio e clique em Mostrar para visualiz-lo, bem como o resultado com as frmulas Foto: Reproduo/Daniel Ribeiro

Passo 5. Ainda na janela Gerenciador de Cenrios, aperte o boto Resumir para fazer uma planilha especial com cada cenrio;

A alternativa Resumir cria uma planilha com os resultados de todos os cenrios  Foto: Reproduo/Daniel RibeiroA alternativa Resumir cria uma planilha com os resultados de todos os cenrios  Foto: Reproduo/Daniel Ribeiro

A alternativa Resumir cria uma planilha com os resultados de todos os cenrios Foto: Reproduo/Daniel Ribeiro

Passo 6. O Resumo do Cenrio ser criado em uma nova planilha e apresentar os cenrios criados de acordo com a configurao desejada.

O Excel criar o Resultado do Cenrio em uma nova planilha  Foto: Reproduo/Daniel RibeiroO Excel criar o Resultado do Cenrio em uma nova planilha  Foto: Reproduo/Daniel Ribeiro

O Excel criar o Resultado do Cenrio em uma nova planilha Foto: Reproduo/Daniel Ribeiro

O Excel conta com um enorme banco de dados de converso para valores e medidas de temperatura, peso, tamanho etc. Para acessar e utilizar este recurso basta inserir a funo CONVERTER, convertendo automaticamente os valores.

Passo 1. Digite a frmula =CONVERTER(X; (sem aspas) para exibir a tabela de valores de converso do Excel sendo que X equivale a clula com o valor original que ser escolhido. importante digitar ; aps identificar a clula para abrir a tabela de valores (na imagem, por exemplo, foi escolhido nmeros em litros);

Escreva a funo =CONVERTER(X; e escolha a categoria de medida que ser convertida  Foto: Reproduo/Daniel RibeiroEscreva a funo =CONVERTER(X; e escolha a categoria de medida que ser convertida  Foto: Reproduo/Daniel Ribeiro

Escreva a funo =CONVERTER(X; e escolha a categoria de medida que ser convertida Foto: Reproduo/Daniel Ribeiro

Passo 2. Em seguida, com a formula =CONVERTER(X;Y; sendo Y a medida selecionada escolha em uma segunda tabela, compatvel com a primeira, a frmula para o qual o valor ser convertido;

Aps digitar o segundo ; de =CONVERTER(X;Y;, escolha a medida em que o valor ser convertido  Foto: Reproduo/Daniel RibeiroAps digitar o segundo ; de =CONVERTER(X;Y;, escolha a medida em que o valor ser convertido  Foto: Reproduo/Daniel Ribeiro

Aps digitar o segundo ; de =CONVERTER(X;Y;, escolha a medida em que o valor ser convertido Foto: Reproduo/Daniel Ribeiro

Passo 3. A clula com a frmula passar a converter automaticamente qualquer valor determinado no primeiro campo, podendo ser replicada e ter medidas alteradas em outras clulas.

O campo com a frmula alterar automaticamente os valores da clula previamente escolhido  Foto: Reproduo/Daniel RibeiroO campo com a frmula alterar automaticamente os valores da clula previamente escolhido  Foto: Reproduo/Daniel Ribeiro

O campo com a frmula alterar automaticamente os valores da clula previamente escolhido Foto: Reproduo/Daniel Ribeiro

6. Clulas com contedo invisvel

Com recursos para ocultar colunas inteiras de planilhas, o Microsoft Excel tambm oferece um truque para esconder valores de determinadas clulas.

Passo 1. Clique com o boto direito do mouse sobre a clula que ter a visualizao ocultada e, em seguida, escolha a alternativa Formatar clulas…;

Aperte o boto direito do mouse sobre a clula e escolha Formatar clula...  Foto: Reproduo/Daniel RibeiroAperte o boto direito do mouse sobre a clula e escolha Formatar clula...  Foto: Reproduo/Daniel Ribeiro

Aperte o boto direito do mouse sobre a clula e escolha Formatar clula… Foto: Reproduo/Daniel Ribeiro

Passo 2. Na janela Formatar clula, na aba Nmero, escolha a opo Personalizado e, no campo de texto, escreva ;;; (sem aspas);

Selecione Personalizado e digite ;;; no campo de texto  Foto: Reproduo/Daniel RibeiroSelecione Personalizado e digite ;;; no campo de texto  Foto: Reproduo/Daniel Ribeiro

Selecione Personalizado e digite ;;; no campo de texto Foto: Reproduo/Daniel Ribeiro

Passo 3. O contedo da clula ser ocultado da visualizao normal da planilha, exibindo-a como um campo em branco. Porm ser possvel verificar o seu valor ao consultar a barra de frmulas do Excel.

Os valores da clula passaro a ser exibidos somente na barra de frmulas  Foto: Reproduo/Daniel RibeiroOs valores da clula passaro a ser exibidos somente na barra de frmulas  Foto: Reproduo/Daniel Ribeiro

Os valores da clula passaro a ser exibidos somente na barra de frmulas Foto: Reproduo/Daniel Ribeiro

O Microsoft Excel tem um pacote de recursos para fazer anlise de dados estatsticos mais refinados e especficos. Porm, esta ferramenta se encontra disponvel na configurao original do programa.

Passo 1. Para ativar o pacote de Anlise de Dados, clique na aba Arquivos e, em seguida, na alternativa Opes;

Na aba Arquivo, aperte em Opes  Foto: Reproduo/Daniel RibeiroNa aba Arquivo, aperte em Opes  Foto: Reproduo/Daniel Ribeiro

Na aba Arquivo, aperte em Opes Foto: Reproduo/Daniel Ribeiro

Passo 2. Ser aberta uma janela com vrias opes de configurao do Excel. Na parte Suplementos, escolha a opo Suplementos do Excel, na caixa de seleo da parte inferior da interface, e conclua em Ir…;

Em Suplementos, escolhe Suplementos do Excel e clique em Ir...  Foto: Reproduo/Daniel RibeiroEm Suplementos, escolhe Suplementos do Excel e clique em Ir...  Foto: Reproduo/Daniel Ribeiro

Em Suplementos, escolhe Suplementos do Excel e clique em Ir… Foto: Reproduo/Daniel Ribeiro

Passo 3. Na janela Suplementos, marque a alternativa Ferramentas de anlise e aperte OK;

Em seguida, marque Ferramenta de anlise e clique OK  Foto: Reproduo/Daniel RibeiroEm seguida, marque Ferramenta de anlise e clique OK  Foto: Reproduo/Daniel Ribeiro

Em seguida, marque Ferramenta de anlise e clique OK Foto: Reproduo/Daniel Ribeiro

Passo 4. As opes de Anlise de Dados podero ser acessadas atravs de um boto na aba Dados, bastando escolher uma das vrias alternativas para iniciar a configurao dos recursos de estatstica.

Na aba Dados estar o boto Anlise de Dados com os sofisticados recursos estatsticos do Excel  Foto: Reproduo/Daniel RibeiroNa aba Dados estar o boto Anlise de Dados com os sofisticados recursos estatsticos do Excel  Foto: Reproduo/Daniel Ribeiro

Na aba Dados estar o boto Anlise de Dados com os sofisticados recursos estatsticos do Excel Foto: Reproduo/Daniel Ribeiro

8. Remover clulas em branco

Um truque prtico e fcil para editar e padronizar planilhas de Excel a remoo de clulas em branco. Apesar de no existir um recurso para fazer exatamente esta operao, a alternativa para a adequao bem til e verstil.

Passo 1. Em uma planilha com clulas em branco, selecione suas colunas e digite o comando Ctrl + G para abrir o recurso Ir para e clique em Especial…;

Selecione as colunas da planilha, abra Ir para com Ctrl + G e clique em Especial...  Foto: Reproduo/Daniel RibeiroSelecione as colunas da planilha, abra Ir para com Ctrl + G e clique em Especial...  Foto: Reproduo/Daniel Ribeiro

Selecione as colunas da planilha, abra Ir para com Ctrl + G e clique em Especial… Foto: Reproduo/Daniel Ribeiro

Passo 2. Na janela Ir para especial, selecione a opo Em branco e aperte OK para selecionar todas as clulas vazias na seleo da planilha;

Escolha Em branco e clique OK para selecionar todas as clulas vazias  Foto: Reproduo/Daniel RibeiroEscolha Em branco e clique OK para selecionar todas as clulas vazias  Foto: Reproduo/Daniel Ribeiro

Escolha Em branco e clique OK para selecionar todas as clulas vazias Foto: Reproduo/Daniel Ribeiro

Passo 3. Em seguida, na aba Pgina Inicial, clique no comando Excluir e selecione a opo Excluir Clulas…. Na caixa de seleo que ser aberta, escolha a opo Destacar clulas para cima para manter a unidade da planilha com campos contnuos.

Na aba Pgina Inicial, clique Excluir, Excluir Clulas... e  Destacar clulas para cima   Foto: Reproduo/Daniel RibeiroNa aba Pgina Inicial, clique Excluir, Excluir Clulas... e  Destacar clulas para cima   Foto: Reproduo/Daniel Ribeiro

Na aba Pgina Inicial, clique Excluir, Excluir Clulas… e Destacar clulas para cima Foto: Reproduo/Daniel Ribeiro

Com um j sofisticado sistema de filtro para planilhas, o Excel tambm conta com o recurso de Filtro Avanado que permite formar uma nova tabela com itens extrados da planilha original.

Passo 1. Na aba Dados, selecione Avanado para ativar a janela de configurao do Filtro Avanado. importante tambm copiar o cabealho da planilha para no misturar clulas e j determinar o termo que ser filtrado;

Copie o cabealho da planilha com o termo de filtro, e na aba Dados, clique em Avanado  Foto: Reproduo/Daniel RibeiroCopie o cabealho da planilha com o termo de filtro, e na aba Dados, clique em Avanado  Foto: Reproduo/Daniel Ribeiro

Copie o cabealho da planilha com o termo de filtro, e na aba Dados, clique em Avanado Foto: Reproduo/Daniel Ribeiro

Passo 2. Na janela Filtro Avanado, determine como Intervalo da lista toda a planilha que ser utilizada;

Marque toda a planilha como Intervalo da lista  Foto: Reproduo/Daniel RibeiroMarque toda a planilha como Intervalo da lista  Foto: Reproduo/Daniel Ribeiro

Marque toda a planilha como Intervalo da lista Foto: Reproduo/Daniel Ribeiro

Passo 3. Em seguida, escolha o cabealho e o termo de filtragem para o campo Intervalo de critrios;

Marque o cabealho e o termo de filtro como Intervalo de critrios  Foto: Reproduo/Daniel RibeiroMarque o cabealho e o termo de filtro como Intervalo de critrios  Foto: Reproduo/Daniel Ribeiro

Marque o cabealho e o termo de filtro como Intervalo de critrios Foto: Reproduo/Daniel Ribeiro

Passo 4. Para concluir o Filtro Avanado, marque a opo Copiar para um novo local e, logo abaixo, escolha o campo em Copiar para: e aperte OK;

Marque Copiar para um novo local e escolha a clula em Copiar para  Foto: Reproduo/Daniel RibeiroMarque Copiar para um novo local e escolha a clula em Copiar para  Foto: Reproduo/Daniel Ribeiro

Marque Copiar para um novo local e escolha a clula em Copiar para Foto: Reproduo/Daniel Ribeiro

Passo 5. O Filtro Avanado formar uma nova tabela s com as linhas que contenham o termo da filtragem

Uma nova planilha ser exibida no campo determinado pelo Filtro Avanado  Foto: Reproduo/Daniel RibeiroUma nova planilha ser exibida no campo determinado pelo Filtro Avanado  Foto: Reproduo/Daniel Ribeiro

Uma nova planilha ser exibida no campo determinado pelo Filtro Avanado Foto: Reproduo/Daniel Ribeiro

Passo 6. Da mesma forma, possvel combinar mais de dois termos e utilizar valores e indicadores numricos para aprimorar a filtragem.

O Filtro Avanado permite combinar termos e utilizar valores com indicadores numricos   Foto: Reproduo/Daniel RibeiroO Filtro Avanado permite combinar termos e utilizar valores com indicadores numricos   Foto: Reproduo/Daniel Ribeiro

O Filtro Avanado permite combinar termos e utilizar valores com indicadores numricos Foto: Reproduo/Daniel Ribeiro

10. Funo DIATRABALHOTOTAL

O Excel tem vrios recursos para contabilizar dias e horas de trabalho, mas com a frmula DIATRABALHOTOTAL possvel configurar tambm feriados e folgas.

Passo 1. A frmula =DIATRABALHOTOTAL(A;B) (sem aspas) faz a contabilizao automtica do total de dias de trabalho sendo A a clula com a data do primeiro dia, e B o campo com a data do ltimo dia;

Escreva =DIATRABALHOTOTAL(A;B), com A e B indicando, respectivamente, o primeiro e o ltimo dia de trabalho  Foto: Reproduo/Daniel RibeiroEscreva =DIATRABALHOTOTAL(A;B), com A e B indicando, respectivamente, o primeiro e o ltimo dia de trabalho  Foto: Reproduo/Daniel Ribeiro

Escreva =DIATRABALHOTOTAL(A;B), com A e B indicando, respectivamente, o primeiro e o ltimo dia de trabalho Foto: Reproduo/Daniel Ribeiro

Passo 2. Porm, esta funo conta com uma terceira variante que, alm de verificar se a data cai em um final de semana, j subtrai do montante total os dias de trabalho. Para continuar a frmula, digite =DIATRABALHOTOTAL(A;B;C:D) com C e D indicando a primeira e a ltima clula dos feriados ou folgas;

Com =DIATRABALHOTOTAL(A;B;C:D), o intervalo C:D subtrai feriados da soma total  Foto: Reproduo/Daniel RibeiroCom =DIATRABALHOTOTAL(A;B;C:D), o intervalo C:D subtrai feriados da soma total  Foto: Reproduo/Daniel Ribeiro

Com =DIATRABALHOTOTAL(A;B;C:D), o intervalo C:D subtrai feriados da soma total Foto: Reproduo/Daniel Ribeiro

Passo 3. A funo ir apresentar o resultado automaticamente sempre que alguma de suas variantes for alterada.

A formula apresentar imediatamente os resultados   Foto: Reproduo/Daniel RibeiroA formula apresentar imediatamente os resultados   Foto: Reproduo/Daniel Ribeiro

A formula apresentar imediatamente os resultados Foto: Reproduo/Daniel Ribeiro