7 Excel Formula replication Advanced Tips

1, thousands of lines of formula replication.

Put the cursor in the lower right corner, and when it turns black cross, double-click the left mouse button.

　　2. Copy formulas across blank lines

If the next column has a blank row, double-clicking the copy cannot be copied to the bottom, at which point you can select the bottom cell and press the ctrl+shift+ up arrow to select the area that contains the formula. Finally press Ctrl+d to complete the entire column copy of the formula.

　　3, cross-file formula replication

When you copy a formula from workbook 1 to a workbook 2 o'clock, the formula references the data in workbook 1 when you paste it into workbook 2 when the formula references another worksheet data in workbook 1. You only need to look for "[*]" to replace it with NULL.

　4, VLOOKUP function formula copy

When VLOOKUP needs to find multiple columns of data, because the 3rd parameter does not automatically change, you need to change one after another. At this point you only need to use the column function to produce a variable number sequence as the VLOOKUP 3rd parameter.

=vlookup ($A $B $ $E $6,column (B1), 0)

5, interlaced copy reference formula

The column A in the chart below is the original data, we need to set the formula in the D column to take the value of column A, we just need to set a formula every 3 rows, =a1,=a2,=a3,=a4 ... The result of the extraction is shown in column D.

Operation Steps:

Step 1: Enter the character A1 in the D1, and then select D1:d3 to copy down, as shown in the following figure.

Step 2:ctrl+f opens the Find replacement window, looks for: A, replaces the =a, and completes the formula setup.

　　7. Merge cell formula copy

If the merged cells in a column do not contain the same number of cells, they cannot be copied directly. You only need to select the area, and then enter the formula in the edit bar and press CTRL + ENTER to complete the entry.