MS Excel - Tips and Tricks for a Newbie
Working in MS Excel seems boring and complicated to many, but this is far from the case. Excel can be convenient if you know some secrets that we will tell you. Most likely, many of you will find something familiar, but, no doubt, the material will be useful to many. So:
Calculator | MS Excel
Sometimes when working with Excel it is easier to do some calculations not on the spreadsheet, but on the calculator, but if it is not displayed in the quick launch panel, then through the "Start" it will run for a rather long time. Let's display the calculator button on the Excel toolbar.
In the top menu, select: View → Toolbar → Settings . The Settings dialog box opens, go to the Commands tab. Now, in the Categories list, select Tools. Scroll down the list of Commands , you will see the calculator icon (next to it will be the text inscription "Other"). While holding the left mouse button, drag this icon from the list to the toolbar. Now you can simply click on this icon to start the calculator.
AutoFill | MS Excel
This function is very convenient if you need to fill out a lot of cells with typical information. For example, make a schedule and enter all the days of the year. This will take a lot of time manually. We make it simpler: 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 lower right corner, a small cross will appear there, drag it down to the desired date.
Cell Format | MS Excel
Do not forget about the very convenient function "Format Cells", for this we select the area that you want to change, right-click, select "Format Cells", a window opens in which you can select various options for cells - set a specific format (monetary, numeric, percentage, etc.), change alignment, font, border, fill, etc.
The 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 line, for example, when submitting an advertisement or composing 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 parentheses write the cell itself, in which we count the characters, it looks something like this: = DLSTR (cell) click Enter
Cell Attachment | MS Excel
If your Excel document is quite long, and you need to use scrolling to view it, it will be very convenient to fix the top lines ("table heading"), and when scrolling down the document, they will be in visibility range - you will not need to return to the top of the document and look what this cell means. Attachment is done simply: we place the cursor in under the line that we want to pin, in the toolbar, select Window → Pin areas . To unpin, click Window → Unpin regions .
Quickly switch between sheets | MS Excel
If your file has 2 or more sheets, and you need to constantly switch between them, you can switch them not with the mouse, but with the key combination Ctrl + PageDown to go to the next sheet, and Ctrl + PageUp to go back to the previous one.
Invisible data | MS Excel
Sometimes we need to hide some data in a file without deleting it - 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. We right-click - in the menu that opens, select the Format of cells , the Number tab, select All formats , on the right and type the field, put three semicolons ";;;" . Click OK. Data will be visible only in the input line of values, and then if the courses will stand on a hidden cell. To make the data visible, you need to redo the whole operation from the beginning of the selection to the input of semicolons, only now you need to remove them from the Type field.
Making a paragraph in a cell | MS Excel
Excel standard functions do not allow paragraphs to be put in one 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 .
Automatic alignment of cell height and width
In order not to manually adjust the height and width of the cells, you can use a simple way: select the desired area, click on the menu bar: Format → Line → Auto-fit. So we aligned the height. Similarly, we do it for alignment by width - select the workspace on the menu bar Format → Column → Auto-fit width . In my opinion, quite simple.
Cross Data Links | MS Excel
In large documents, sometimes you have to work with data from several sheets at once. Suppose 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 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. The data should appear in the desired cell on sheet B.
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 you pull this button down, a window will open in which our file will be. In this position, it is very convenient to edit large files, especially copy anything.
Hyperlinks | MS Excel
You can put hyperlinks in an Excel document, either to individual sites or to computer files. In order to make a hyperlink to a site, just enter its name, starting with the prefix http: // , for example http://f1cd.ru or write the name of the site starting with www , for example, www.f1cd.ru. If you need to make a link to a file on your computer, then do the following: place the cursor in the cell where we plan to place the link to the file, right-click - in the menu that opens, select the Hyperlink item, and in the window that opens, select the file that we are going to link to .
Attention! The link will not work if this file is opened on another computer, because Excel puts a link to the files inside your PC.
In order to delete a hyperlink, you need to hover over the cell in which the hyperlink is located, right-click on it, and in the menu that opens, select the Delete hyperlink item