I have a question about Excel and I am not able to find a solution. It seems a basic issue but somehow it looks more complicated than I thought.

I have a column where I want the value to increase by +1 every 7 rows. I thought I could simply type the first 7 rows with 1, the next 7 rows in the same column with 2, and then select them all and double-click to extend.

But instead this is what happens:Sample Column

Do I need a function to do this? thanks! M

5 Answers

Let assume you are incrementing the number of column A. On A1, enter 1. On cell A2, enter the formula:

=IF(MOD(ROW(A1),7)=0,A1+1,A1)

Drag the cell down to fill the other cells.

2

What you are doing won't won't like you expected. You need to use some formula for that.

There are different ways to do the same. I have used this one:

=ROUNDDOWN((1+ROW()/7);0)

It will increase the number by 1 at every 7th row based on the row number. The result is rounded down to the whole number.

Just put this formula to the first column.

Input this formula and drag down as far as you need. Once you have what you need, replace formulas with special values (paste as text), then sort.

 =MOD(ROWS($A$2:A2)-1,7)+1

Here's the unsorted version before replacing formulas with text...

With Office 365 we can fill the column dynamically with:

=INT(SEQUENCE(49,,1,1/7))

Put that in A1 and it will spill down 49 rows with your pattern.

I see lots of answers using formulas, and copy & paste values.  Would you like a solution that doesn't use formula(s)?

Let's say you want the 1 1 1 1 1 1 1 2 2 2 2 2 2 2 3 3 3 3 3 3 3 … sequence in Column A, cells A1:100.

• Enter 1 in B1 and 2 in B8.  (Leave B2:B7 and B9:B14 blank.)
• Select B1:B14 and drag/fill down to B100.  This will give you 3 in B15, 4 in B22, etc.
• Select B1:B100 and "copy" it (e.g., by pressing Ctrl+C).
• Click in A1 and "paste" (e.g., by pressing Ctrl+V).
• Select A2 (by clicking in it, or by pressing cursor down) and do "Paste" → "Paste Special" → "Add".
• Select A3 (by clicking in it, or by pressing cursor down) and do "Paste" → "Paste Special" → "Add".
• Repeat the above for A4, A5, A6 and A7.
• If necessary (because 100 isn't a multiple of 7) clear A101:A105.
• Clear Column B.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy