This page has been robot translated, sorry for typos if any. Original content here.

MS Excel - Tips and Tricks for the Beginner

Working in MS Excel seems boring and complex to many, but this is far from being the case. Excel can be handy if you know some of the secrets that we will tell you. Most likely, many of you will find something familiar, but, no doubt, the material is useful to many. So:

Calculator | MS Excel

Sometimes when working with Excel, it's easier to make some calculations not in a spreadsheet, but on a calculator, but if it is not displayed in the Quick Launch bar, it will run long enough through the "Start". Let's display the calculator button on the Excel toolbar.

In the upper menu select: View → Toolbar → Settings . The Setup dialog box opens, click the Commands tab. Now, in the Categories list, click Tools. Scroll down the Commands list, you will see the calculator icon (next to the text label "Other"). Hold down the left mouse button, drag this icon from the list to the toolbar. Now you can just click on this icon to start the calculator.

Work in Excel - tips and tricks.

AutoComplete | MS Excel

This feature is very useful if you need to fill out the sample information with many cells. For example, make a schedule and make all the days of the year. Manually it will take a long time. We do it simply: we write the date in one cell, and then simply "drag it" to the desired date. To do this, move the cursor to the cell with the date, move the cursor to the right bottom corner, there will appear a small cross, pull it down until the desired date.

Work in Excel - tips and tricks.

Format of cells | MS Excel

Do not forget about the very convenient function "Format Cells", to do this, select the area that you want to change, right-click, select "Format Cells", opens a window in which you can select different options for cells - specify a specific format (monetary, numeric, percentage, etc.), change alignment, font, border, fill, etc.

Work in Excel - tips and tricks.

Number of characters in the text | MS Excel

How often do we need to set certain requirements for the number of characters in a text or string, for example, when submitting an advertisement or composing text. To do this, do the following: in the cell where you need to display the number of characters put the "=" sign and set the formula "DLLSTR" , then in the brackets we write the cell in which we count the symbols, it looks something like this: = DLRT (cell) click Enter.

Work in Excel - tips and tricks.

Attaching cells | MS Excel

If your Excel document is rather long and you need to scroll to view it, it will be very convenient to fix the top lines ("table header"), and about scrolling the document down, they will be in sight - you will not need to return to the top of the document and to look, what does this cell stand for. The attachment is done simply: we place the cursor under the line that we want to fix, in the toolbar we select Window → Lock areas . To cancel the docking, click Window → Uncheck the areas .

Work in Excel - tips and tricks.

Quick switch between sheets | MS Excel

If there are 2 or more sheets in your file, and you need to constantly switch between them, you can switch them not with the mouse, but with the Ctrl + PageDown key combination to go to the next sheet, and Ctrl + PageUp to return to the previous one.

Invisible data | MS Excel

Sometimes we need to delete the file, hide some data - calculations, text, etc. you need to hide the data in the cell. To do this, select what we want to hide - it can be either a single cell or several rows and columns. Right-click - in the opened menu select the Format of cells , the Number tab, select All formats , on the right and in the Type field put three semicolon characters ";;;" . Click OK. Data will only be visible in the value entry line, and then, if the course will be on a hidden cell. To make the data visible, you need to re-do the whole operation from the beginning of the selection to entering the semicolons, only now you need to remove them from the Type field.

Work in Excel - tips and tricks.

Making a paragraph in a cell | MS Excel

Standard Excel functions do not allow you to put paragraphs in one cell. As soon as we press Ente r, the cursor moves to another line. There is an exit - every time you need to start the sentence in the cell with a new line - press Alt + Enter .

Work in Excel - tips and tricks.

Automatic height and width alignment

In order not to manually adjust the height and width of cells, you can use a simple method: select the desired area, on the menu bar, press: Format → Line → Auto-fit height. So we leveled the height. Similarly we do for alignment on width - we select a working area, on the menu bar Format → Column → Auto-fit width . In my opinion, it's quite simple.

Work in Excel - tips and tricks.

Cross-references to data | MS Excel

In large documents, sometimes you have to work with data from several sheets at once. Suppose that we have 2 sheets: Sheet A and Sheet B. A sheet of data from sheet A must be present on sheet B. To do this, place the cursor in the cell on sheet B, where we put the data of sheet A, put the sign equal to "=" , go to sheet A, put the cursor in the cell whose data will be used on sheet B, press Enter. Data should appear in the correct cell on sheet B.

Work in Excel - tips and tricks.

Two screens in Exel | MS Excel

On the right, above the top button on the scroll bar is another small button. On it, the mouse changes the cursor. If this button is pulled down, the window will open, in which our file will be. In this situation it is very convenient to edit large files, especially to copy something.

Work in Excel - tips and tricks.

Hyperlinks | MS Excel

In the Excel document, you can place hyperlinks, both on separate sites and on computer files. In order to make a hyperlink to the site it is enough to enter its name beginning with the prefix http: // , for example or to write the site name from www , for example, If you want to link to a file on your computer, we do the following: place the cursor in the cell where we plan to place the link to the file, right click on the mouse - select Hyperlink in the opened menu and select the file on which we are going to refer .

Attention! The link will not work if this file is opened on another computer, since Excel puts a link to files inside your PC.

Work in Excel - tips and tricks.

In order to remove a hyperlink, you need to move the cursor to the cell in which the hyperlink is located, right-click on the hyperlink, and select Delete hyperlink in the opened menu