Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Either simple or impossible...

Either simple or impossible...

Either simple or impossible...

Hey, guys..

I have several rows in my spreadsheet that use the same formula, although the cells referenced by the formulas increment.  Normally, I could just copy the cells down and let the cell #s increment.  No problem.

In this case, however, I have borders around each of the cells in the rows in question.  I want a gap between the borders.  Is there any way to insert a gap between two rows, in lieu of inserting another row?  If I insert another row, my formulas do not increment correctly, and I have to correct them manually.

If that made absolutely no sense, I apologize.  I'm finding it hard to describe what I'm trying to do.

RE: Either simple or impossible...

You could use a smaller font and/or increase the row height to get space between the rows.  Does that help?

RE: Either simple or impossible...

No, my problem is the border surrounding the cells.  If I decrease the font size, the borders still butt against one another, with no space between.  If I increase the row height, the borders follow the height of the row, and I'm left with the same problem.

Thanks, though!!

RE: Either simple or impossible...

Maybe split the cells surrounding the cell you work with  into two colums would make it.

Just my two cents

Cyril Guichard
Mechanical Engineer

RE: Either simple or impossible...

You can perhaps:
1. use double borders, or

2. setup the formula's like this, for example,
A1: 1
A3: =A1+1
then select rows 3 and 4, copy (Ctrl-C), select down as many rows as you like (should be an even number), and paste. This will fill A5: =A3+1, A7: =A5+1, etc.

3. download ASAP utilities (www.asap-utilities.com) which contains a tool to add rows between each row in your selection.


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Either simple or impossible...


I'm assuming you want a row that has borders followed by a row without borders etc...

Assuming this is the case, you need to border the rows you want bordered then select both the row you want and the row above it (or below it).  If you then click on the black square in the lower right corner and drag downward, you should have a row that is not bordered followed by a row that is bordered.

If I'm off track let me know and I'll try again.


RE: Either simple or impossible...

No, I want the rows sequential so I can copy my formulas down and have the references increment properly (by +1).  If I add a blank row in between, for my unbordered space, my references increment by two when I copy.

RE: Either simple or impossible...

Is this what you want (in general):

     A     B
1    1    =A1   (With border)
2    2    =A2   (Without Border)
3    3    =A3   (With Border)
4    4    =A4   (Without border)

If it is, you need to follow what I said before and select one row with a border and one without a border in order to retain both formula and border increment.


PS:  If not, please post something visual so we can better understand your problem.

RE: Either simple or impossible...

while i am short on time and do not have my pc with me, i recently (i.e. w/in past 2 months) came across a good technique for using the conditional formatting tool, that is special formatting based on alternating or every x rows.  it may be of interest to you.  i cannot remember the website (perhaps, this forum referred to it).  check previous posts in this forum.  when conditions allow, i will post the technique (might b a day or 2 - traveling from ak to contential us).

RE: Either simple or impossible...

how about if you drag out the cells (no spaces, +1 increments), then insert the rows? You could also fill in formulas in the cells, then drag them to their desired locations.  

RE: Either simple or impossible...

There is no way to get a separate border around to consecutive rows, so that they show as 2 separate borders.

You can acheive a similar look to what you want by using the double line format under the: format (menu) > cell (menu item) > border (tab).

This still has the problems of the ends of the cells still being connected together. Try it and see.


RE: Either simple or impossible...

It is a bit of work but here is one way:
On a column that you can hide, say Column A, enter 1 say at row 5 then 2 at next cell below (row 6). At the 3rd cell down (A7) write formula = A5. Then copy this cell all the way down as long as you want your spreadsheet to be. You should get a colum of nos alternating between 1 & 2.

Next on you spreadsheet, say you want the blank space to occur at every other row with the no. 2 in column A.

Prefix each of your formula by a condition statement to display blank whenever the no. 2 is at the spreadsheer row in column A as follows, for row 1:

If($A1=2,"",write your formula for the particular cell)
for row 2:
If($A2=2,"",copy your formula for the cell above)

RE: Either simple or impossible...

As menioned above conditional formating will do it. the condition test looks to see if the row number is odd or even and formats the cell accordinatley.

This allowa you, for example, shade even second line to make rading across rows easlier. The great thing about this method is when you insert a row you stil get every second line shaded.

to test to see if the row is odd or even use 'mod(row(a1),2)'. 0=even 1 = odd

Even rows format a boarder or shading etc, odd rows not format.

RE: Either simple or impossible...

I didn't see these options offered, so I'll offer them.  

>  You could simply insert gaps in the referenced data.

>  You could replace the relative references with fixed references prior to inserting the extra cells.  For example, if the referenced cells are A1,A2,A3, replace A with A$, resulting in references to A$1, A$2, A$3, which you can now insert any number of cells in between without affecting the references.


RE: Either simple or impossible...

I could do that, but I don't want to have to type in each reference individually.  I have quite a few formulas, and I was hoping to just drag them down, having them increment.. If I have to insert the $, it's going to be quite a bit of clicking around.

Thanks, though!

RE: Either simple or impossible...

You don't need to type them individually; you can simply do a global find and replace on the selection.  Select the range of cells, hit [ctrl]-h, enter the column designator in the find, enter the column designator followed by the "$" and replace all.


RE: Either simple or impossible...

You can do this by breaking it into two "parts".  The first part is a condition to see if you are displaying or skipping the row.  The second part would use the OFFSET function in conjunction with COUNT.  This would be in place of the actual reference in your formula, so that the location of the reference is computed, then "looked up".  It is hard to provide the exact syntax you would need without an example, but in general what you could do is embed the COUNT function into the OFFSET function.

Here is an example of what I'm talking about:
Source data:

      Col A
R1     heading
R2     value1
R3     value2
R4     value3

Then your formatted, skip-a-row, formula in column B would be:

      Col B
R1     heading
R2     =IF(INT(COUNTA($B$2:B2)/2)<>COUNTA($B$2:B2)/2,"",OFFSET($A$2,COUNT($B$2:B2),0,1,1))
R3     =IF(INT(COUNTA($B$2:B3)/2)<>COUNTA($B$2:B3)/2,"",OFFSET($A$2,COUNT($B$2:B3),0,1,1))
R4     =if(row(a3)<>2*counta($a$2:a3),offset(sheet1!$a$2, count(sheet2!$a$2:a3),0,1,1),"")   

Here's the breakdown on the steps:
IF(INT(COUNTA($B$2:B2)/2)<>COUNTA($B$2:B2)/2,"", will skip every other row, change the <> to a = if needed for your app.
OFFSET($A$2,COUNT($B$2:B3),0,1,1)) will lookup the reference value by counting "how far down the list you are" then offsetting accordingly.

I've tested this, and it seems to work.  The OFFSET function lends itself to long formulas but elegant solutions.  You could use conditional formatting to throw on a border if the cell has a value (half the cells will contain "").  Because of the length of this expression, if you are using multiple references in the column B formula you might want to split operations into several columns for ease of understanding the formula....

Hope this makes sense, & good luck!

RE: Either simple or impossible...

Okay, I missed a couple things on my last post.  Ignore the R4 Column B formula, this is supposed to be a fill down of the one above.

Second, this will actually put in blank rows so that the borders look like you wanted.  **You can fill the column B formula down without skipping rows**.  If it is critical that you do not skip any rows, but have a space between the cell borders, this is not the solution.

RE: Either simple or impossible...

If your problem is to get little gaps between consecutive row borders. Double borders should be helpful. Or otherwise using conditional formating you can have borders in alternative rows.


Vineet Maloo

RE: Either simple or impossible...


Consider Column B

YOu put the formula in first row cell

COpy the formula in next row cell i.e. row cell number 2

Border first row cell i.e. row cell number 1

Leave row number 2 borderless

Then Select both the row cells (row cell number 1
and 2)

click on the black square in the lower right corner and drag downward.

you have a row that is not bordered followed by a row that is bordered sequentially.

This is similar to jproj(chemical)

Please check & let me know if it is working.

RE: Either simple or impossible...

KevinNZ is right, it works with conditional formatting. Highlight the column you want formatted. Choose “Formula Is” and use =(MOD(ROW($A1),2)=0)  as the formula.

RE: Either simple or impossible...

Here is an alternate solution that may work for you.
The workaround is in the method of copying not by reformating the rows around the cells.

1. Insert one or more rows between each formula and
   format cells as required.
2. Enable Datafilter and select "Non Blanks" cells
   (This filters out all blank rows)
3. Copy 1st cell and paste into other cells
   Use Paste Special if you only want to copy formula's

4. If this trick did not work for you then add a custom
   command "Select Visible Cells" to your one of your
   toolbars. To add this command. Right-click toolbar,
   select Customize-Commands then Category: Edit and find
   command "Select Visible Cells" at the bottom.
5. Now when you copy the 1st cell, highlight the cells you
   want to paste to and click on "Select Visible Cells"
   toolbar first and then Paste info
6. Now turn-off the Data Filter or select "All" from the
   drop down box and you are all done.

The last feature is very handy and usefull when you are working on 3-d spreadsheets and want to paste information down to other grouped worksheets. It's a powerfull feature, which can save you quite a bit of time. Also tricky as you can mess up a workbook in a hurry if multiple worksheets are group together for editing.

Play with Excel's Copy & Paste as it has some need features which can really speed up your work. Like copying non-continuous cells and pasting into continuous block. Copying one cell and paste it to non-continuous cells. Note this works only for one row or one column.

Hope this works


RE: Either simple or impossible...

Maybe I'm oversimplifying: can't you just add the rows *after* you copy all the cells?  Or, copy the cells once, add the rows, and then take advantage of the Excel-imposed fix? (something tells me that last suggestion fails to understand the problem you're having)


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close