How to Skip Cells When Dragging in Excel: 4 Easy Methods

Steps:

=OFFSET($B$5,(ROW(B4)-4)*2,0)

(ROW(B4)-4) → In here the ROW function returns the row number 4. Thus the output stands (4-4) = “0”
=OFFSET($B$5,(ROW(B4)-4)*2,0)→ The OFFSET function returns output based on a cell reference ($B$5), and then (2,0) commanding 2 rows down and 0 stands for staying in the same column.

Combine OFFSET and ROW Functions to Skip Cells When Dragging

The Enter button and drag down the “fill handle” to fill all the cells.

Get the names in a new column by dragging and skipping their sales volume from the previous column.

Combine OFFSET and ROW Functions to Skip Cells When Dragging

=INDEX($B$5:$B$14,ROWS($C$5:C5)*2)

ROWS($C$5:C5)→ Here the ROWS function extracts the row number, which provides an output -”1”
=INDEX($B$5:$B$14,ROWS($C$5:C5)*2)→ In this part, ($B$5:$B$14) it’s the cell reference for the index function where the “ROWS($C$5:C5)*2” is the row_num extracting the data from the cell (B6) which is- “4500”.

Merge INDEX and ROWS Functions to Skip Cells When Dragging

We extracted only the sales volume while skipping the cells with names when dragging.

Merge INDEX and ROWS Functions to Skip Cells When Dragging

Steps:

Choose cells (B5:B6). After choosing two cells with one blank and one with the number “1” Excel automatically understands how you want to fill your cells by dragging the fill handle.
Selecting cells pull the “fill handle” down.

Get the output in your hands just the way you wanted, skipping cells when dragging the fill handle.

Use Fill Handle to Skip Cells When Dragging in Excel

Steps:

Choose a cell to write the formula as we are extracting only the numbers from the column thus we choose cell (C6).
Put the formula down-

=IF(MOD(ROW()-2,2)=0, INDEX($B:$B, INT((ROW()-2)/1)+2), "")

Formula Breakdown:

(ROW()-2)→ Here the ROW function extracts the row number which is “6” as the chosen cell is cell (C6). after that subtracting with “-2”.
Output is “4”
INT((ROW()-2)/1)→ The INT function returns the integer number which is “4”.
MOD(ROW()-2,2)→ The MOD function extracts the remaining from the arguments. As there are no remainings thus the output stands to “0”.
=IF(MOD(ROW()-2,2)=0, INDEX($B:$B, INT((ROW()-2)/1)+2), “”)→ in this final part the IF function runs inside the string where after completing logical_test which is “(MOD(ROW()-2,2)=0” it goes for the argument where “INDEX($B:$B, INT((ROW()-2)/1)+2)” returns an output “4500” and if the argument is not met –” “ it will return a blank cell.