20 tips for those who know nothing about Excel and want to learn the basics

Excel is Microsoft's spreadsheet editor and allows you to create spreadsheets, charts and facilitate a series of operations.

It is no secret that mastery of the program can be the differential for approval in a job vacancy. That's why brings together 20 tips for those who don't know anything about Excel and want to learn the basics.

Find out what are the main shortcuts and formulas, how to create charts and apply filters to a worksheet. Also see how simple it is to sort content in ascending or alphabetical order, hide data, freeze rows and columns, and more.

1. What is Excel?

What Excel is a spreadsheet editor that had its first edition released in 1985, curiously, for the Macintosh OS system, from rival Apple. Since then, it has received a series of updates.

Its main function, however, remains the same. The program was created to help organize data and facilitate calculations, from the simplest to the most complex.

The software allows creating rules that are self-applied in predefined cells, without the need for the user to perform each operation manually.

From the information given, it also creates graphs automatically, which makes it easier to visualize the data. To understand a little better how all this happens, it is necessary to know the “anatomy” of Excel.

Each page has a limit of 1.048.576 rows and 16.384 columns. The intersections between the rows and columns are called cells (there are 2.147.483.648 in all).

Rows are defined by numbers, columns by letters. That is, the cells in the first row are A1, B1, C1, and so on. The cells in the first column are A1, A2, A3, etc.

When clicking on a cell, its name is displayed above it, next to the formula bar. In this bar, the formulas used in each cell are written, if any. In the next topic we will cover the basic formulas.

2. Formulas

Before we list the main formulas for those who want to learn the basics of Excel, we need to explain how they work.

Microsoft's official Excel tutorial explains that a function is a command created that takes a value or values, performs a certain calculation and returns a result. Formulas are used to apply these functions in a practical way. They can span cells, cell ranges, or numbers.

It is important to remember that every formula starts with the symbol of = and then it may vary depending on the desired function. The result is displayed when pressing the key Enter.

In the example above, we show the multiplication of the value of cell B3 by that of C3. Thus, in D3 we insert the function = (B3 * C3). when pressing the key Enter, we got the result 125 (25 times 5).

Clarified how they work, check below the main formulas that a beginner in Excel should know.

In the following formula examples, the letters C represent cells supposedly used in each function:

Want to delve deeper into the subject? Check out 30+ Excel formulas to master spreadsheets.

3. Add new rows and columns

Often, when we build a table, we need to insert new information between data that has already been added. And for that, it may be necessary to include one or a few rows and columns.

To do so, right-click on the cell in which you want to include the row just above or the column just to the left. In the menu that opens, go to insert and then choose entire line ou entire column. confirm in OK.

If you want to insert more rows or columns in sequence, just press the key F4 repeatedly, according to the amount you want.

If you want to delete a row or column, right-click on a cell that is part of it. so go on Delete and choose full line ou entire column.

4. Select multiple cells (and one-click selection)

Need to select multiple cells at once to perform an operation? It is possible to do this in a few ways. If the cells are in sequence, click on the first one, press Shift and then click on the last one. All cells in the range will be selected.

However, if the objective is to use randomly placed cells, click on one of them. Then press the key Ctrl and click on all the cells you want to use. keep the Ctrl always pressed, until you click on the last cell.

Need all the cells in the worksheet? Click on the button located in the left corner of the screen, shown in the image below.

5. Transpose data from a row to a column (and vice versa)

Sometimes we need to use data from a row in a column or vice versa. It would be a lot of work if we had to copy and paste cell to cell, wouldn't it? However, there is a very simple way to do the procedure.

To do this, select all the cells you need and copy (Ctrl + C). Then, select the cells where you should transpose this information and right-click. In the paste options that appear, click Transport ().

You can do the process with just one row or column or, if you want, with an entire table.

6. Hide data

Prefer to leave some confidential information “hidden”? No problem. To do this, select the cells that need to hide the information, as explained in item 4.

Then click on the down arrow symbol, located next to the number, shown in the image below. In the box that opens, in the tab Number, choose the category Personalised. In Market, type it ;;; and conclude in OK.

Immediately, the values ​​will disappear from the report, but will continue to be counted.

If you want to redisplay them, select the cells and click the down arrow symbol again. on the tab Number, go this time, in the category General. After confirming in OK, the numbers will be displayed again.

7. Insert values ​​that start with zero

By default, Excel disregards leading zeros in a number and therefore suppresses them. If you want the zero to be kept in the cell, you need to perform a simple trick. Just add an apostrophe (') before the number, as '002345 and they will be kept.

8. Freeze headers and columns

In cases of very long worksheets, it can be interesting to keep the header information always visible, even if you scroll. For this, there is the cell freeze function, which works for both rows and columns.

First, go to the tab Exibição from Excel and click Freeze panels. Then choose to pin the first row and first column (Freeze panels), only the first row or only the first column.

9. Organize contents in alphabetical or ascending order

Organizing the information in a table in ascending or alphabetical order is very simple and can make your life easier. Simply select all items in the column and right-click on one of the selected cells.

In the menu that opens, place the mouse pointer over the option to rank. click on Sort from smallest to largest to place in ascending or alphabetical order. If you want to display in descending order or from Z to A, choose Sort from largest to smallest.

10. Information in the status bar

If you notice, when selecting some cells at the same time, results are displayed in the bar located at the bottom of the worksheet. By default, it instantly shows the numerical average, the number of selected cells and the sum of those cells. All this without having to enter any formula.

If you want to exclude or include other information in the status bar, click on it with the right mouse button. Enabled options are marked with a check sign next to them. To activate or deactivate, just click on the item.

11. Formatting brush

The formatting brush is a very useful tool to save time and effort. Present in other programs of the Office package, the feature allows you to quickly copy the type of font, color, size, among other characteristics.

To do so, just select the cell you want to copy the pattern and click on the brush symbol, available on the Homepage. Then click on the cell you want to format in the same way. If you want to apply it to multiple cells at the same time, just drag your mouse over them as soon as you click on the first one.

12. Duplicate worksheet

Anyone who wants to replicate data or formatting from a table to another tab can do so without copying and pasting all the cells. To do so, just right-click on the tab name and, in the menu that opens, click on move or copy.

Then check the box next to create a copy and check if the selected tab is the one you want. Then confirm in OK. A copy identical to the first worksheet will be made and will be located in the tab on the side.

13. Protect spreadsheet with password

Have a shared spreadsheet with important data and don't want anyone to change it without your knowledge? Know that it is possible to protect them with a password. Just right-click on the tab name at the bottom of the screen.

In the menu that appears, go to protect sheet. Then set the password to unlock the sheet, and below, choose the actions that third parties will be able to perform on the document. confirm in OK. Then, repeat the defined password and, once again, go to OK.

To unprotect the worksheet, go to the tab Review and click unprotect sheet, located in the top menu. Enter the previously defined password and confirm with OK.

14. Include filters

Those who have a very extensive spreadsheet may have difficulties finding some information inserted in it. There are filters for that. To apply, select the cell corresponding to the top of the column you want to filter. If you want, you can choose multiple cells.

On the flap DATA, select the option Filter. Then arrows will appear in the cells. Click on them and select only the items you want to display in your table. You can also search by name or value.

15. Configure decimal numbers

It is possible that when you include a decimal number, it will appear with more or less places after the comma than you want.

To solve the problem, select the cells that contain the numbers and click Increase Decimal Places, just above Issues. This to show more numbers after the comma.

If you want to reduce the number of numbers, go to Decrease Decimal Places. Click on the icons as many times as the number of houses you want to include or exclude.

16. Find duplicate content

Need to find duplicate spreadsheet contents? Don't worry. First, select the cells you want to check. on the tab Home, Click Conditional Formatting.

Then go to Highlight Cell Rules and, in the menu that appears, choose Duplicate Values. In the box that opens, choose the formatting you want to give the values ​​that appear duplicates, so that they stand out.

It can be, for example, light red fill and dark red lettering. After setting, confirm in OK.

17. Adjusting rows and columns

You can set the height of rows and width of columns by default. To do this, select the cells you want to adjust and, in the Homepage, patch me Format.

Choose the item Line height ou column width. Then enter the number corresponding to the height or width and confirm in OK. To give you an idea, the default size for a column is 8,43 and for a row it is 15.

18. Print only the desired area

Printing an Excel file may not be as easy as it sounds. If the table or chart is not selected, it is possible that all cells, even unused ones, will be printed, wasting sheets and more sheets.

To adjust the print area, select the cells to be printed. then go to File and then in Print. In Definitions, opt for print selections and confirm in Print.

19. Making graphics

Excel makes data visualization easier by turning tables into charts. The process is quite simple and teaches you how to do it in the tutorial How to Graph in Excel on PC and Mobile.

20. Shortcuts

Several Excel features can be quickly executed using keyboard shortcuts. Check out some that can help a lot of newbies to the program.

recommends:

• How to Create PivotTable in Excel and Google Sheets
• Word shortcut keys: check out the most useful options of the program