Ten tips and tricks to master Excel | Managers

Microsoft Excel spreadsheet editor with powerful features for organizing and relating data. However, the unattractive look of its rows and columns, as well as the amount of commands and shortcuts on the interface, can make users a little afraid to explore the application.

With sophisticated commands for moving data and formulas or highlighting information, Excel hides excellent tools for working. Check out 10 tips to help edit spreadsheets and better understand the potential of this tool.

Five sites with free online Excel courses

How to create charts in Microsoft Excel

How to create charts in Microsoft Excel

1. Transform columns into rows (and vice versa)

With a simpler look and focused on displaying data, Excel edits spreadsheets made up of rows and columns. However, it is sometimes necessary to convert the vertical formatting of this arrangement to horizontal, or the other way around.

Step 1. This feature works with the program download area. Select the row or column you want to transform and click Copy or press Ctrl + C. Then, go to the special options for Paste and choose the option Transpose;

1 of 23
Select and copy the column or row you want to convert and choose the Transpose option. Photo: Daniel Ribeiro

Select and copy the column or row you want to convert and choose the Transpose option. Photo: Daniel Ribeiro

Step 2. To complete the transformation, it is necessary to transfer the copied data to a spreadsheet with empty cells and with the vertical or horizontal space necessary to arrange the row or column;

2 of 23
Paste the transposition into a spreadsheet with enough space for the entire row or column Photo: Daniel Ribeiro

Paste the transposition into a spreadsheet with enough space for the entire row or column Photo: Daniel Ribeiro

2. Freeze headers and columns

Despite having a matrix of headers and columns identified by numbers and letters, very long spreadsheets in Excel may require the display of titles and classifications. For this reason, the program has a specific tool to fix the display of the first row or column, without changing the formatting of the rest of the spreadsheet.

Step 1. In the View tab, click the Freeze Panels alternative to fix the preview of the header and the first column of the spreadsheet, simultaneously;

3 of 23
Select the highlighted options to freeze the first column and the header Photo: Daniel Ribeiro

Select the highlighted options to freeze the first column and the header Photo: Daniel Ribeiro

Step 2. To remove this view option, click on the Unfreeze Panels option. It is also possible to fix only one of the alternatives in Freeze Top Line or Freeze First Column;

4 of 23
In Freeze Panels it is possible to undo the visualization or specify the display of the first row or column Photo: Daniel Ribeiro

In Freeze Panels it is possible to undo the visualization or specify the display of the first row or column Photo: Daniel Ribeiro

Like Microsoft Word, Excel allows you to insert comments in your cells, helping to highlight notes or curiosities in the spreadsheet. However, this feature is not to be confused with spreadsheet data, presenting itself as an automatic display text box.

Step 1. In the spreadsheet, right-click on the cell in which you want to write a note or observation and select the Insert Comment option;

5 of 23
Right-click on the cell and choose Insert Comment Photo: Daniel Ribeiro

Right-click on the cell and choose Insert Comment Photo: Daniel Ribeiro

Step 2. Write your comment in the text box that will be displayed, pointing out the authorship of the Excel user. Once this operation is completed, the comment will be indicated by a discreet red sign and display the annotation whenever the mouse is over the cell;

6 of 23
The comment will "float" over the spreadsheet whenever the mouse is over your cell Photo: Daniel Ribeiro

The comment will "float" over the spreadsheet whenever the mouse is over your cell Photo: Daniel Ribeiro

Excel, like other Microsoft Office programs, has several features activated by shortcut keys. However, in addition to these commands, the user also needs to know some tips on data entry in the cells.

Step 1. The spreadsheet editor has sophisticated data identification systems to check if the numbers are currencies, dates, times, etc. However, this configuration can hinder the filling of cells. So, always write before numbers to turn off automatic identification and keep the value in the format you want;

7 of 23
Type before number mainly if they start with 0 Photo: Daniel Ribeiro

Type before number mainly if they start with 0 Photo: Daniel Ribeiro

Step 2. Pressing Enter in Excel finishes editing the cell and moves to the bottom one. In this way, very long sentences can be compressed or hidden by the spreadsheet settings. To write longer texts and skip lines within the cell, press Ctrl + Enter, increasing the size of the space and preventing the words from being hidden;

8 of 23
Press Ctrl + Enter to skip lines within the cell and avoid automatic hiding Photo: Daniel Ribeiro

Press Ctrl + Enter to skip lines within the cell and avoid automatic hiding Photo: Daniel Ribeiro

Step 3. Excel has a special shortcut to replicate the content of the cells, be it text, number or formulas. Press Ctrl + D or Ctrl + R to copy and duplicate the cell data just above or just left, respectively;

9 of 23
Ctrl + D duplicates the content of the cell above and Ctrl + R copies the data from the left cell Photo: Daniel Ribeiro

Ctrl + D duplicates the content of the cell above and Ctrl + R copies the data from the left cell Photo: Daniel Ribeiro

Step 4. Despite not having many resources for text editing, the program has formulas that process data from one or more cells. The formulas = MAISCULA (XX) and = MINSCULA (XX), with XX indicating the position of a cell, automatically transform from specific fields, saving the user's time;

10 of 23
The formulas = MAISCULA and = MINSCULA transform the text of specific cells Photo: Daniel Ribeiro

The formulas = MAISCULA and = MINSCULA transform the text of specific cells Photo: Daniel Ribeiro

Printing spreadsheets in Excel can be a little complicated, as the program does not prioritize the display of margins or page breaks. Thus, there are ways to view the area to be printed or to specify cells for printing.

Step 1. On the View tab, select the Page Break Display option to mark the print limits in blue dotted lines on the printout of each page of the entire spreadsheet;

11 of 23
Selecting the highlighted commands will limit the limits of each page to be displayed Photo: Daniel Ribeiro

Selecting the highlighted commands will limit the limits of each page to be displayed Photo: Daniel Ribeiro

Step 2. It is also possible to print only a part of the spreadsheet, previously selected. To do this, check the cells you want to print, click on File, then on the Print option. Then, in the Settings section, open the selection box and choose the Print Selection alternative;

12 of 23
Print specific parts of the text, marking them and choosing the option Print Selection Photo: Daniel Ribeiro

Print specific parts of the text, marking them and choosing the option Print Selection Photo: Daniel Ribeiro

6. Data restrictions for cells

Some spreadsheets need to be fed by different users who are unaware of the values ​​that must be entered in the cells. To coordinate work and avoid errors, Excel allows you to configure the data restriction for each cell.

Step 1. Select the cell you want to configure and, on the Data tab, click on the highlighted button and choose the option Data Validation …;

13 of 23
Mark the cell to be configured and click on the options marked in the figure Photo: Daniel Ribeiro

Mark the cell to be configured and click on the options marked in the figure Photo: Daniel Ribeiro

Step 2. A configuration box will appear. Write the data restriction specifications you want and, when finished, press OK to complete the cell customization;

14 of 23
Customize the cell restrictions in the Settings tab of the displayed window Photo: Daniel Ribeiro

Customize the cell restrictions in the Settings tab of the displayed window Photo: Daniel Ribeiro

Step 3. Data Validation also allows you to configure customizable input and error alert messages. To do this, click on the highlighted tabs and write the notification texts;

15 of 23
It is possible to create input messages and specific error alerts in the checked options Photo: Daniel Ribeiro

It is possible to create input messages and specific error alerts in the checked options Photo: Daniel Ribeiro

7 Hide / show column and rows

To help you better visualize and organize your spreadsheets, Microsoft Excel has a feature that allows you to hide rows and columns.

Step 1. Right-click on a column or row in the header or side guide of the spreadsheet editor and choose the Hide option. The entire column or row is hidden, leaving a small marker in place;

16 of 23
Press the right mouse button on the header or the first column and click Hide Photo: Daniel Ribeiro

Press the right mouse button on the header or the first column and click Hide Photo: Daniel Ribeiro

Step 2. To display hidden columns and rows again, press the right mouse button on the marker that indicates the hidden part and click Display;

17 of 23
Right-click on hidden rows or columns and choose View Photo: Daniel Ribeiro

Right-click on hidden rows or columns and choose View Photo: Daniel Ribeiro

8. Time and time shortcuts

Excel has shortcuts that insert the current date and time automatically, making it easier to fill cells in the spreadsheet. Likewise, the editor has formulas that count the passage of time between different days of the year.

Step 1. In an empty cell, pressing Ctrl +; It is possible to automatically insert the current date in the spreadsheet, while Ctrl + Shift +; add the exact time, according to the PC clock;

18 of 23
Ctrl +; and Ctrl + Shift +; add the date and time, respectively Photo: Daniel Ribeiro

Ctrl +; and Ctrl + Shift +; add the date and time, respectively Photo: Daniel Ribeiro

Step 2. The formula = NMSEMANA (XX), with XX indicating the cell that has a date, it is possible to specify the week of the year, according to the day. At the same time, the formula = DIAS360 (XX: YY), with XX and YY indicating two cells with different dates, has the number of days between periods;

19 of 23
The formulas = NMSEMANA and = DAYS360, count the week of the year the difference between dates, respectively Photo: Daniel Ribeiro

The formulas = NMSEMANA and = DAYS360, count the week of the year the difference between dates, respectively Photo: Daniel Ribeiro

Created to work with large volumes of data, Excel also has an automatic calculator that performs operations with the number of cells and the numbers entered in them.

Step 1. At the bottom of the spreadsheet editor, there is a Status Bar that serves as the area for automatic calculations. Whenever cells with numbers are selected, Excel automatically presents results on the sum and the mean, for example;

20 of 23
The Status Bar automatically displays the result of operations between selected cells Photo: Daniel Ribeiro

The Status Bar automatically displays the result of operations between selected cells Photo: Daniel Ribeiro

Step 2. Right-click on the Status Bar to configure the displayed information, which may include preferences and new mathematical operations;

21 of 23
Press the right mouse button on the Status Bar to configure it Photo: Daniel Ribeiro

Press the right mouse button on the Status Bar to configure it Photo: Daniel Ribeiro

Excel also has a sophisticated data filter to process the content in your spreadsheets. Usually disabled, this feature allows you to alphabetically sort the column, as well as search and display only cells with certain words and numbers;

Step 1. Select one or more columns and, on the Home page tab, click the Sort and Filter option. Press on Filter to activate the resource permanently;

22 of 23
With one or more columns marked, click on the highlighted options to activate the filter Photo: Daniel Ribeiro

With one or more columns marked, click on the highlighted options to activate the filter Photo: Daniel Ribeiro

Step 2. The Filter column will display an arrow with the specifications for this mechanism. Click on this button to choose the classification alternatives or the search and display of specific terms;

23 of 23
Select the Filter button to choose column sorting methods or the advanced search Photo: Daniel Ribeiro

Select the Filter button to choose column sorting methods or the advanced search Photo: Daniel Ribeiro

With these tips, you can enjoy even more functions not so well known in Excel.