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.
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 over the date cell, move the cursor to the lower right corner, a small cross will appear there, dragging it down to the desired date.
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.
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.
Attaching Cells | MS Excel
If you have an Excel document that is quite long and you need to scroll to view it, then it’s very convenient to fix the top lines ("table header"), and about scrolling the document down, 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 unpin, you need to click Window → Unpin regions .
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 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 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 we want to hide - it can be either a single cell or several rows and columns. We click with the right mouse button - in the opened menu, 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.
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 .
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 simple 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-fit width . In my opinion, quite simply.
Cross references to data | 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 a cell on Sheet B, where we put the data for 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 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 copying something.
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 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: set the cursor in the cell where we plan to place the link to the file, right-click - in the opened menu, select the Hyperlink item, and in the opened window select the file to which we are going to refer .
Attention! The link will not work if this file opens on another computer, since Excel puts a link to the files inside your PC.
In order to delete 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 Delete hyperlink item.