Excel Fill Tips

　　Custom sequence

Because when you fill in a table with data, it's often like consecutive months, the days of the week, the first quarter, or "A, B, C, ding ..." and so on, these data in a certain order in a very regular, so Excel has defined them into a number of sequences, when we "AutoFill", These sequences are automatically populated into the specified range of cells.

As in Excel, the name of each day of the week is defined as a sequence: Sun, Mon, Tue, Wed, Thu, Fri, Sat. When you fill in the Start cell with "Sun", you can use the AutoFill feature to cycle through the cells in the pre-populated area in the order of Sun, Mon, Tue, Wed, Thu, Fri, Sat, Sun ...

What are the sequences that Excel defines in addition to the "Week" sequence? Through the menu "Tools/Options ..." Open the Options dialog box, select the "Custom Series" tab, you will find them.

In fact, you can also build your own "custom sequence" according to your own needs, such as "Department A, Department b, Department c, Department D ...". The method is:

1. With the Tools/Options menu command, open the Options dialog box;

2. Switch to the Custom Series tab, click New Series in the Custom Series list box, and then enter a new sequence in the Input series list box, such as department A, Department b, Department c, Department D .... Each time you finish an item in the sequence, press the ENTER key once.

3. Click the Add button and the new sequence appears in the Custom Series list box.

4. Click the OK button to close the Options dialog box.

After that, other items, such as "department A," "Department b, Department c, Department D ...", and so on, can be entered with automatic padding as long as you have an entry in the cell input sequence.

　　Use of the Sequence dialog box

When you use the menu method for automatic padding, the Sequence dialog box opens. In addition to using the AutoFill option, you can apply additional options to fill a special sequence of numbers and dates.

1. For example, want to fill in a "column" of the area of 1, 3, 5, 7 ... of the linear series. The method is:

(1) Enter a value of 1 in the starting cell;

(2) Select the same column range of cells, including the starting cell, to fill the sequence;

(3) "Edit/fill/sequence ..." To open the "Sequence" dialog box;

(4) Select "Column" in the "sequence generation in" option, select "Arithmetic progression" in the "type" option, and "Step size" to 2;

(5) Click OK, then fill in the selected area with a 1, 3, 5, 7 ... of the linear series.

2. If you want to fill in the date series for the year increment, such as 1980-3-15, 1982-3-15, 1984-3-15, 1986-3-15 ..., the method is:

(1) Enter the date in the starting cell 1980-3-15;

(2) Select the same column range of cells, including the starting cell, to fill the sequence;

(3) "Edit/fill/sequence ..." To open the "Sequence" dialog box;

(4) Select "Column" in the "sequence generation in" option, and select "Date" in the type option;

(5) When the "Date unit" option is available, select "Year";

(6) "Step Size" set to 2;

(7) Click OK, then fill in a desired sequence of dates in the selected area.

In a similar way, you can populate a date series that has a decreasing year, and you can also populate a date series with a month, a date increment (subtract).

　　With the right mouse button, drag the fill handle to fill 　

　

As I said before, you can use the left mouse button to drag the fill handle to fill it automatically. In fact, with the right mouse button drag "fill handle" can also be filled, of course, more diverse forms of filling. The method is to fill in the starting cell data, point to the fill handle with the mouse, press the right mouse button on the same line (or in the same column) to the left, right (or up, down) after dragging to the predetermined cell, release the right button, then a shortcut menu pops up.

Click on the appropriate menu to complete the relevant operation. If you click "Copy Cell", you can use the start cell data to copy the fill in the range of the mouse drag.

Several other menu items

When the mouse points to the menu edit/fill, the submenu has a "sequence ..." in it, as well as "fill down", "Fill Right", "fill up", and "fill left" menu items. Their role is to populate the selected range of cells in the form of a copy.

For example, the role of "right padding" is similar to other menu items. With B2 as the starting cell, enter a date for 2004-3-15, and then select the range from B2 to the right containing several cells, with the menu command edit/fill/Right fill, then the data for the selected range cell is 2004-3-15.

a few special padding operations

There are several special filling operations that you can try and see what the effect is:

1. In the same column (row) Two adjacent cells, enter two numeric data respectively. Select them at the same time as the starting point, with the left mouse button to drag the fill handle up or down (in the row to the right, left) drag the fill, then there will be a linear series appear. This method has the effect of producing a linear series of data in date type, time type, character type with number and custom sequence.

2. While using the left mouse button to drag the fill handle to fill, hold down the CTRL key on the keyboard. You will find that different types of data will have different fill effects, please note the distinction.

　　--Auto Fill

AutoFill is an intelligent fill that directly utilizes existing cell data for data on cells or ranges of cells near it. You can use the menu and mouse to drag the fill handle.

　　1. Menu method

(1) Type data in the starting cell that you want to automatically fill, and select the range of cells (rows or columns) that includes the starting cell.

(2) With the menu: "Edit/Fill/series ...", Pop-up "sequence" dialog box.

(3) for horizontal automatic fill sequence, in the "sequence generated in" column select the "line" option, otherwise select the Column option button.

(4) Select Auto Fill in the Type option;

(5) Click "OK" button.

　　2. Use the mouse to drag the fill handle way

Drag the fill handle to the mouse is the easiest and most convenient way to fill the automatic. The fill handle is a small black block in the lower-right corner of the selected cell or range.

(1) Type the data in the starting cell and select the cell.

(2) Point the mouse pointer to the fill handle (the small black square in the lower right corner of the selection), and the pointer changes to a black cross shape.

(3) Press the left mouse button to fill the direction (column for up and down, the left and right) dragged, to meet the needs of the region.

　　3. Automatic Filling Summary:

(1) If the starting data is date type, the effect of the fill is to increase or decrease the date by days, such as 2004-3-14, 2004-3-15, 2004-3-16 ... ；

(2) If the starting data is a time type, the effect of the fill is that the time data of the cell increase or decrease by the number of hours, such as 3:34, 4:34, 5:34 ... ；

(3) If the starting data is the data in the defined "custom sequence", the effect of the fill is recycled in the order of the custom special sequence (for the "Custom Sequence" post);

(4) If the starting data is a character data containing Arabic numerals, the effect of the fill is the character copy, and the number in which the numbers increase or decrease, such as 1th, 2nd, 3rd ... , such as ABC1, ABC2, ABC3 ... , such as 2DF9, 2DF10, 2df11 ... (Note: If there are several separate numbers, the rightmost number increases or decreases);

(5) If the starting data is numeric, logical, or other character-type data that does not contain numbers, the effect of the fill is to copy the data from the starting cell;