Often, when filling out a table, you have to type the same text. Excel’s auto-complete feature helps speed up this process considerably. If the system determines that the typed part of the text matches what was entered earlier in another cell, it will substitute the missing part and highlight it in black (Fig. 2). You can agree with the proposal and move on to filling in the next cell by clicking, or continue typing the desired text, ignoring the selection (if the first few letters match) .
Rice. 2 – Autocomplete when entering text
The autocomplete mechanism is useful in cases where you need to enter some sequence of data into cells. Suppose you need to fill a row or column with a sequence of numbers, each of which is greater (or less) than the previous one by a certain amount. To avoid doing this manually, you can do the following :
1. It is necessary to type in two adjacent cells the first two values from a series of numbers so that Excel can determine their difference.
2. Select both cells.
3. Move the mouse pointer over the marker located in the lower right corner of the selection frame. In this case, it will take the form of a black plus.
4. Click and, holding down the mouse button, drag the frame until the final value appears on the tooltip near the mouse pointer, which will be placed in the last cell of the row. You can drag the frame in any direction.
5. Release the mouse button so that the range of covered cells is filled.
The AutoComplete feature is very useful when copying a formula to a range of cells. With AutoComplete, you can enter formulas into cells in a matter of seconds. You need to type only the first formula, and then by dragging the frame for the bottom marker copy it to the entire row .
In this case, the addresses of the cells in the formulas will be automatically replaced with the necessary ones (by analogy with the first formula).
45. I think everyone knows such a trick in Excel, how to auto -fill cells by dragging a cross with the mouse? If not yet, I’ll tell you more. Let’s say you want to fill a row or column with the days of the week (Monday, Tuesday, etc.) . What are you doing for this? That’s right, you manually enter all these days in each cell. But Excel has a great opportunity to simplify this process. To perform such an operation, you only need to fill in the first cell. We write in it – Monday. Now select this cell and move the mouse cursor to the lower right corner of the cell. The cursor will take the form of a black cross (Fig. 1) .
As soon as the cursor has become a cross, press the left mouse button and hold it down and drag it down (if you need to fill in the rows) or to the right (if you need to fill in the columns) by the required number of cells. Now all the cells we captured are filled with the days of the week. And not one Monday, but in order (Fig. 2) .
But that’s not all. If instead of the left mouse button, hold down the right mouse button and stretch, then upon completion, Excel will display a menu in which you will be prompted to select a filling method: Copy cells , Fill , Fill only formats , Fill only values , Fill by days , Fill by weekdays , Fill by Months , Fill in by Years , Linear Approximation , Exponential Approximation , Progression – see Figure 3 . Gray font indicates inactive menu items – those that cannot be applied to selected cells. Choose the desired item and admire the result.
But that’s not all. Along with the built-in autocomplete lists in Excel, you can create your own lists. For example, you often fill in the table header with the words: Date, Article, Price, Amount . You can enter them every time or copy them from somewhere, but you can do it differently. If you are using:
· Excel 2003 , then go to Tools – Options – Tab ” Lists “ ;
· Excel 2007 – Menu – Excel Options – Basic tab – button ” Change lists “ ;
· Excel 2010 – File – Options – Advanced tab – button ” Change Lists… “ .
As a result, you have something like this (Fig. 4)
Select the item NEW LIST – put the cursor in the Elements of the list field and enter the names of the columns there, separated by commas, as shown in Fig . 4. Click Add .
You can also use the ” Import list from cells ” field. Activate the selection box by clicking in it with the mouse. Select the range of cells with values from which we want to create a list. Click Import. A new list of the values of the specified cells will appear in the Lists field.
Now it remains to check in action. We write the word Date in any cell and extend it as described above. Excel filled in the rest of the columns for us with values from the list we just created ourselves. You can change and delete the lists you created, add new ones.