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

MS Excel - Tips and Tips for a Newbie

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

Calculator | MS Excel

Sometimes when working with Excel, it is easier to do some calculations not in a spreadsheet, but on a calculator, but if it is not displayed in the quick launch panel, then it can be run for a long time through the Start button. Let's display the calculator button on the Excel toolbar.

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

Work in Excel - tricks and tips.

Autocomplete | MS Excel

This function is very convenient if you need to fill in a sample information of many cells. For example, make a schedule and make all the days of the year. Manually it will take a lot of time. We make it easier: write the date in one cell, and then just "drag" to the desired date. To do this, move the cursor to the cell with the date, move the cursor to the lower right corner, a small cross will appear there, dragging it down to the desired date.

Work in Excel - tricks and tips.

Cell Format | MS Excel

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

Work in Excel - tricks and tips.

Number of characters in the text | MS Excel

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

Work in Excel - tricks and tips.

Attaching Cells | MS Excel

If you have an Excel document is quite long, and you need to scroll to view it, then it will be very convenient to fix the top lines ("table header"), and to scroll down the document, they will be in view - you will not need to return to the top of the document and see what this cell means. The attachment is simple: we place the cursor in the line that we want to freeze, in the toolbar, select Window → Lock areas . To cancel the pinning, click Window → Unpinned areas .

Work in Excel - tricks and tips.

Fast switching 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 a 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 in the file without deleting, to hide some data - calculations, text, etc. need to hide the data in the cell. To do this, select what you want to hide - it can be either a single cell or several rows and columns. Click the right mouse button - in the menu that opens, select Cell format , the Number tab, select All formats , on the right and in the Type field put three semicolons ";;;" . Click OK. Data will only be visible in the value entry line, and that is, if the courses 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 commas, only now you need to remove them from the Type field.

Work in Excel - tricks and tips.

Make a paragraph in the cell | MS Excel

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

Work in Excel - tricks and tips.

Automatic alignment of the height and width of the cell

In order not to manually adjust the height and width of the cells, you can use the straightforward method: select the desired area, on the menu bar, press successively: Format → Line → Automatic height selection. So we leveled the heights. Similarly, we do for alignment by width - select the working area, on the menu bar Format → Column → Auto Width . In my opinion, quite simply.

Work in Excel - tricks and tips.

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. On Sheet B there should be a part of the data from Sheet A. To do this, put the cursor in a cell on Sheet B, where we put the data for Sheet A, put a sign equal to "=" , go to Sheet A, put the cursor in the cell whose data will be used on Sheet B, press Enter. The data should appear in the desired cell on sheet B.

Work in Excel - tricks and tips.

Two screens in Excel | MS Excel

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

Work in Excel - tricks and tips.

Hyperlinks | MS Excel

In an Excel document, you can put hyperlinks to both individual sites and computer files. In order to make a hyperlink to the site, it is enough to enter its name, starting with the http: // prefix, for example or write the name of the site starting with www , for example, If you need to make a link to a file on your computer, then do the following: set the cursor in the cell where we plan to place the link to the file, right-click - select the Hyperlink item in the opened menu, and select the file to be referenced in the window that opens .

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

Work in Excel - tricks and tips.

In order to remove a hyperlink, you must hover the cursor on the cell in which the hyperlink is located, right-click on it, and in the opened menu select the item Delete Hyperlink