20 Excel revelations

To work with documentation Excel is one of the best tools! But, unfortunately, not everyone can use the program to its full potential. The multifunctional system allows not only to competently combine work objects, but also to cut and hide individual data. And also there are several secrets that simplify and speed up the entire process of working with documents.

Just one click and all cells are selected

Simultaneous pressing Ctrl + A - works not only in relation to Word, but also to Excel. By pressing these two keys, you select all cells. The same principle applies when you click the button located in the corner of the Excel sheet.

Fast transition from one file to another

When several documents are open at the same time, the transition through them is carried out quickly and easily by pressing Ctrl + Tab... By the same maneuver, you can work with other Windows documents, including moving in browser tabs.

In just a few seconds you will open all the necessary documents at the same time

Excel files can be opened either individually or all at once, which are required at the moment. To open the necessary books, they should be highlight and click on Enter.

How do I add new buttons to the Quick Access Toolbar?

Usually there are 3 buttons in this place. You can add buttons exactly in the amount in which you need them, and you do not need to resort to some tricks.

So, step by step instructions for inserting a new button. We go to:

file;
parameters;
quick access panel.

Now you can choose the buttons that you need for convenient work.

How to add empty rows or columns to a table?

Many people do this: select a blank cell, copy and paste it. This is correct, but only if you need to insert only 1 element. And if the need is significantly higher? Yes, for a long time. But you can significantly speed up the process by selecting the number of columns you need and inserting where you need it. The program will automatically offer to shift the cells, you choose the direction of the shift that is optimal for you.

Adding a diagonal line in cells

The need for this line arises for various reasons. To add a line, click on the button with borders and select "Other borders".

Ultra-fast blank cell removal

Empty cells not only take up space on the working page in vain, but really irritate the eyes, distracting from the main objects. Sometimes it’s impossible to concentrate. And to remove unused space manually is not enough nerves. In general, we click on the icon "Data", Then "Filter"... After that, an arrow will appear above each field, pointing down. By clicking on this arrow, you go to the menu, which, in fact, removes unnecessary blank lines.

How to copy and move data quickly?

"Fast" is the key word, since almost everyone can copy and move information. But, quickly - not everyone is given.

For high-speed movement of information of any form in the document, you need to select important objects and move the cursor over the border. When the pointer changes, you can safely transfer items to the selected location. Use the button to copy Ctrl.

Advanced Search

Why do you need advanced search functionality at all? We cannot always remember some information in its exact formulation. That is, we can forget one number in the phone number, the letter in the name of the document, and so on. In this case, symbols are excellent helpers:

«?» - it is used when 1 character is forgotten;
«*» - if you forgot several values.

The indicated icons are placed instead of forgotten characters, for example: 09? 0896301.

But if you are looking for exactly "?" or "*", and not forgotten values, then before these symbols you need to put «~».

Copying unique objects

What does unique mean? Let's say you work with personnel, and you have a column that contains information about each specific person, for example, his date of birth. And you need to highlight this particular information. To do this, select the required column, click "Advanced" (the icon adjacent to the "Filter"). Next: we indicate the desired range, that is, the one that will be copied, and the range where - to paste the copied element. At the end of the procedure, put a tick.

High speed navigation

There is nothing special to be wise. Ctrl + up or down arrow, depending on the place of travel. This key combination will take you to the extreme points of the document in a second.

Sample

For questioning or creating surveys in which a narrow group of people takes part, for example, women aged "... from and to ...", then the following method is suitable.

Go to "Data"where we choose "Data validation"... Next, we determine the required parameters. If the respondent does not meet the specified conditions, then he will receive a message about incorrect information.

Combine text quickly

You no longer have to bother with a whole chain of numerous actions to combine cells. For quick merging, the cell to which the text will be attached is highlighted and the symbol is pressed «=», and before subsequent cells - «&».

How do I turn a column into a row?

It may seem like a daunting mathematical task, but this opinion is erroneous. In principle, the function is not used as often in work as, suppose the previous ones, but any situation is no exception.

To convert a column to a row, first copy the cells that need to be converted. Then right-click in the desired place by selecting Paste Special.

Privacy: hide data in Excel

Don't want someone to have access to your information? Correct too. By pressing "Format", displayed Hide or Show, you need to choose the appropriate option.

The case applies to all textual information in the document, namely:

"Strochn" - lowercase;
"Prrisn" - uppercase;
"Propnach" - uppercase first letter in each word.

How do I put zeros at the beginning of a numeric value?

Anyone who has tried to write numbers in Excel, starting from "0", knows that the program automatically removes "0". To keep "0" in front of the rest of the digits, first we put «’», and then «0»... Even if there is a lot of "0", the apostrophe is placed only 1 time before the first "0".

About the right mouse button

Right button - a tricky "spy" thing. V lower right corner of the sheet you can track all sorts of information, and by clicking there with the right mouse button, you can put away or add required lines.