20 tricks that will make you an Excel guru

Every person who works at a computer will answer in the affirmative if one asks him if he can work in Excel. However, if you put the question differently and try to find out how good it is, the majority will be bewildered. And what there it is possible to be able especial? In fact, this program stores a lot of secrets, which you simply do not pay attention to. But if you learn about them, they will immediately become your favorite. So, in this article you will learn about twenty different techniques in "Excel", which everyone should know.

Allocating with just one click

In the upper left corner of the working field there is a button that no one usually presses. And it is very useful, because it allows you to single-click all the cells of the current document with one click.

Opening multiple files

Data processing is the main function of this program, but what if you have a dozen files, each of which you need to use? Do you have to open each of them separately? In fact, there is one trick that will allow you to cope with the task faster. Select all the files you need and press Enter - they all open without any conflicts between them.

Navigate between files

When you work with a large number of documents, it can be very annoying to switch between them manually. You can make your life easier with the Ctrl + Tab key combination, which automatically switches you to the next tab.

Menu items

Many believe that the "Save", "Forward" and "Back" are the only functions available in the program from the top menu. But you can add there any number of functions through a special configuration block - just keep in mind that only two shortcuts will be displayed - the rest will be hidden.

The diagonal line in the cell

Sometimes a cell needs to specify a diagonal line to separate the information that is inserted into it. But how to do that? All are able to set boundaries for cells, but if you click "More borders", you will get not only a diagonal line, but also other surprises.

Add multiple rows or columns

Everyone knows how to add a row or column of cells. But adding a few pieces individually is a real nightmare. Therefore, it's best to select the desired number of cells, which will correspond to the required number of rows or columns, and then repeat the standard operation.

Fast data transfer

What if you need to quickly move one column of data? To do this, move the cursor to the edge of the column until it takes the shape of a cross in the form of arrows, and then freely move the contents of the column.

Deleting empty cells

In the process of work very often there is a large number of empty cells that spoil the appearance of the document. You can delete them one at a time - or use the fastest way. To do this, you need to remove the selection from the "All" item in the filter and select the "Empty" item. It will instantly select all empty cells in the document that you can delete with one click.

Inaccurate search

Everyone knows how to use fast search, but few know that you can look for not exact matches, but results for one or more letters. To search by one letter, you need to enter a question mark in the search bar, and if you need to search in several letters - use an asterisk.

Creating a unique value

Many use filters, but almost never look in the advanced section. But with it you can set unique values for specific cells, if you need it for work or for other purposes.

Specification of information

Not everyone knows that you can impose restrictions on what information will be entered into the cell. For example, if you create a list of invited people for an event, and their age can range from 18 to 60 years (only whole numbers), you can set both of these parameters in the corresponding menu item "Data", and then other values will simply not be accepted .

Quick navigation

To quickly navigate the document page, you can use the Ctrl key combination with the arrows. If you use this combination, you will get to the extreme point of information cells.

Transformation

Sometimes it is very necessary to turn a column in a row or vice versa. In fact, it can be done very simply. To do this, you need to copy the necessary information, and then paste it through the menu, selecting "Convert" - and your series will be copied as a column.

How to hide information?

In "Excel" there is an opportunity to hide information, but you can do it different methods. You can click on the cell and select "Hide", but this will not give the best result. Therefore, it is recommended that you format cells in the Type format ;;; - it allows you to hide the information from prying eyes as much as possible.

Combining data from cells

If you have several cells, and you need to combine the information written in them into one, then you do not need to completely rewrite everything. Just set the function, in which the names of the desired cells, insert the "ampersand" icon - as a result, in the selected cell you will get all the data from the previous ones.

Change of lowercase to uppercase

If you need to make text from one cell completely written in uppercase letters, you do not need to completely retype it with the activated CapsLock function. You just need to use the UPPER function, and in brackets specify which cell to convert.

Data beginning with 0

In some cases, you may need to enter data into the cell that starts at 0. Unfortunately, by default, the program automatically removes this zero, leaving only an integer value. You can change the format of the cell, but this is a lengthy process, which is easier to replace with a single single quotation mark. If you paste it in front of your value, then zero will remain in place.

Autocorrection

Many people use autocorrection for their intended purpose, that is, they are not afraid to print with errors, because they know that the program will correct them. But few people think that you can use this function to simplify your task. If you have repeated long words, names and surnames, then you can add them to the list of words for autocorrection and spend on them writing a fraction of a second - and the program will bring them to the proper view, which you yourself ask once instead of a hundred.

More information

If you select a cell, information about it appears in the lower right corner. However, it is far from complete, but you can access absolutely all the data. To do this, you need to hover over the information received and right click on it - this will open a window that will contain absolutely all the data relating specifically to this cell.

Renaming a page

One of the simplest tricks, which for some reason practically no one uses. Very often you have to rename the pages in the document, and for this you click on the name with the right mouse button, then select the item "Rename", then change the name itself. But the process can be greatly simplified by using the same principle on the page as with the shortcuts and files in Windows. Double-click on the title of the page gives you the instant possibility to edit it and change it without delay.