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

Method 1 – Combine OFFSET and ROW Functions to Skip Cells When Dragging

Steps:

  • Choose a cell to apply the formula. We selected cell (C5).
  • Write the formula down-
=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


Method 2 – Merge INDEX and ROWS Functions to Skip Cells When Dragging

Steps:

  • Select a cell (C5) to apply the formula-
=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

  • Press Enter and pull the “fill handle” down.

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

Merge INDEX and ROWS Functions to Skip Cells When Dragging


Method 3 – Use Fill Handle to Skip Cells When Dragging in Excel

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


Method 4 – Combine IF, MOD, ROW, INDEX, and INT Functions to Skip Cells When Dragging

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.

Combine IF, MOD, ROW, INDEX, and INT Functions to Skip Cells When Dragging

  • Hit the Enter button and drag down the “fill handle”.

Combine IF, MOD, ROW, INDEX, and INT Functions to Skip Cells When Dragging

  • The final output in our hands extracts only the sales volume. In this, we skipped cells (B5:B7:B9:B11:B13) while dragging in excel.

Combine IF, MOD, ROW, INDEX, and INT Functions to Skip Cells When Dragging


Things to Remember

  • While applying formulas don’t forget to complete formulas with closing brackets. Otherwise, the formulas won’t work.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Related Articles


<< Go Back to Skip Cells  | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo