## How to skip cells when dragging formula in Excel?

When working on an Excel worksheet, sometimes, you may want to skip every other or nth cells when dragging and applying a formula to other cells as below screenshot shown. How could you complete this task in Excel?

#### Skip cells when dragging formula with OFFSET and ROW functions

In Excel, with the combination of the OFFSET and ROW functions, you can create a formula to easily skip cells when dragging it in a column.

Enter or copy the following formula into a blank cell, and then drag the fill handle down to extract every other cell from the column A starting at the first cell until zeros are appeared as below screenshot shown:

=OFFSET(\$A\$2,(ROW(C1)-1)*2,0)
Note: In this formula, A2 is the first cell of the data list from where to extract values.. The number 2 means to skip every second cells when dragging the formula. If you need to skip every third cells, please change the number to 3.

Tips: To get every other cell that starts from the second cell within the specific data range, please use this formula: =OFFSET(\$A\$2,ROW(D1)*2-1,0), and then drag the fill handle down to get the results until zeros are appeared, see screenshot:

#### Skip cells when dragging formula with INDEX and ROWS functions

There is another formula which is created with the INDEX and ROWS functions may do you a favor, please do as this:

Copy or enter the below formula into a blank cell, and then drag the fill handle down to get every other cell which starts at the first cell until errors are appeared as below screenshot shown:

=INDEX(\$A\$2:\$A\$13,ROWS(\$C\$1:C1)*2-1)

Tips: To extract the values that starts from the second cell within the specific data range, please use the below formula: =INDEX(\$A\$2:\$A\$13,ROWS(\$D\$1:D1)*2)
Note: In the above formulas, the ROWS function returns the row number of every 2 cells, and the INDEX function locates the corresponding rows in the range \$A\$2:\$A\$13 and returns the values in the rows of the range.

## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

### Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!