Wasim Akram
While working in Microsoft Excel sometimes you might feel the need to skip cells when dragging the fill handle to fill cells. Pulling the fill handle we fill cells rows or column-wise to get the output. The fill handle automatically fills all the cells in the range. But you can also skip cells while dragging. Today, I am going to share with you how to skip cells when dragging in excel. Stay tuned!
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Easy Methods to Skip Cells When Dragging in Excel
In the following article, I have explained 4 simple and easy methods to skip cells when dragging in excel.
Suppose we have a dataset of some Particulars containing some Employee Name and their Total Sales in the same column. Now we are going to rearrange them by dragging the fill handle while skipping cells.
1. Combine OFFSET and ROW Functions to Skip Cells When Dragging
With the combination of the OFFSET and ROW functions, you can easily skip cells when dragging in a new column. Follow the steps below-
Steps:
- Starting with, choose a cell to apply the formula. Here I have selected cell (C5).
- Write the formula down-
=OFFSET($B$5,(ROW(B4)-4)*2,0)Where,
- (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)→ In this part the OFFSET function returns output on the basis of a cell reference ($B$5), and then (2,0) commanding 2 rows down and 0 stands for staying in the same column.
- Hence click the Enter button and drag down the “fill handle” to fill all the cells.
- Thus, we will get the names in a new column by dragging and skipping their sales volume from the previous column.
Read More: How to Skip Columns in Excel Formula (3 Easy Ways)
2. Merge INDEX and ROWS Functions to Skip Cells When Dragging
If you want you can also utilize the INDEX and ROWS function together to skip cells when dragging. Merging both functions we will extract only the sales volume from the column.
Steps:
- First, select a cell (C5) to apply the formula-
=INDEX($B$5:$B$14,ROWS($C$5:C5)*2)Where,
- 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”.
- In the same fashion, press Enter and pull the “fill handle”
- Here we have extracted only the sales volume while skipping the cells with names when dragging.
Read More: How to Skip Hidden Cells When Pasting in Excel (2 Methods)
3. Use Fill Handle to Skip Cells When Dragging in Excel
The fill handle is a tremendous feature of excel. Using this amazing tool you don’t have to copy or fill cells with series manually. Just drag and fill according to your choice. Using the fill handle feature you can skip cells too when dragging.
Suppose you want to fill some cells with serial numbers like 1, 2, 3, 4, and 5. But you want to skip with a blank cell between every serial number. To do so just follow the steps below-
Steps:
- Above all, 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.
- Now, selecting cells pull the “fill handle” down.
- Therefore, you will get the output in your hands just the way you wanted, skipping cells when dragging the fill handle.
Read More: Skip to Next Result with VLOOKUP If Blank Cell Is Present
4. Combine IF, MOD, ROW, INDEX, and INT Functions to Skip Cells When Dragging
Well, In this part I have also attached another method which is combining the IF, MOD, ROW, INDEX, and INT functions to skip cells when dragging.
Steps:
- Similarly, 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), "")- (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)→ In this part, 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 next argument where “INDEX($B:$B, INT((ROW()-2)/1)+2)” returns an output “4500” and if the argument is not met –” “ then it will return a blank cell.
- Hit the Enter button and drag down the “fill handle”.
- In conclusion, we have the final output in our hands extracting only the sales volume. In this, we skipped cells (B5:B7:B9:B11:B13) while dragging in excel.
Read More: Skip to Next Cell If a Cell Is Blank in Excel (5 Easy Ways)
Things to Remember
- While applying formulas don’t forget to complete formulas with closing brackets. Otherwise, the formulas won’t work.
Conclusion
In this article, I have tried to cover all the methods to skip cells when dragging in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- How to Skip Every Other Column Using Excel Formula (3 Methods)
- Excel Formula to Skip Rows Based on Value (7 Examples)
- How to Skip a Column When Selecting in Excel (4 Easy Methods)
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
- How to Skip Lines in Excel (4 Suitable Ways)
