MS Excel - Tips and Tricks for the Beginner
Working in MS Excel seems boring and complicated to many people, 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, without a 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.
AutoComplete | MS Excel
This feature is very useful if you need to fill out sample information with 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: we write the date in one cell, and then simply "drag" it to the desired date. To do this, put the cursor on 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.
Format of cells | MS Excel
Do not forget about the very convenient function "Format cells", select the area you want to change, right-click, select "Format Cells", open 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.
Number of characters in the text | MS Excel
How often we need to set certain requirements for the number of characters in the text or line, 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.
Attaching cells | MS Excel
If your Excel document is rather long and you need to scroll it 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 the zone of visibility - no 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 select Window → Lock areas . To cancel the docking, click Window → Uncheck the areas .
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 a mouse, but with Ctrl + PageDown to move 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, hiding 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. The 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.
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 .
Automatic height and width alignment
In order not to manually adjust the height and width of cells, you can use a simple way: select the desired area, on the menu bar, click successively: 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.
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 must be some data from sheet A. To do this, place the cursor in the cell on sheet B, where we place 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 must appear in the correct cell on sheet B.
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 there will be our file. In this situation it is very convenient to edit large files, especially to copy something.
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 http://f1cd.ru or to write the site name from www , for example, www.f1cd.ru. If you need to make a link to a file on your computer, we do the following: set the cursor to the cell where we plan to place the link to the file, right click the mouse button - select Hyperlink from 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 the files inside your PC.
In order to delete a hyperlink, you need to move the cursor to the cell in which the hyperlink is located, click on it with the right mouse button, and in the opened menu select the item Delete hyperlink