*In this tutorial, you will learn how to create a number sequence in Excel with formulas. Additionally, we'll show you how to auto generate a series of Roman numbers and random integers - all by using a new dynamic array SEQUENCE function.*

The times when you had to put numbers in sequence in Excel manually are long gone. In modern Excel, you can make a simple number series in a flash with the Auto Fill feature. If you have a more specific task in mind, then use the SEQUENCE function, which is specially designed for this purpose.

## Excel SEQUENCE function

The SEQUENCE function in Excel is used to generate an array of sequential numbers such as 1, 2, 3, etc.

It is a new dynamic array function introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.

The function has the following syntax:

Where:

**Rows** (optional) - the number of rows to fill.

**Columns** (optional) - the number of columns to fill. If omitted, defaults to 1 column.

**Start** (optional) - the starting number in the sequence. If omitted, defaults to 1.

**Step** (optional) - the increment for each subsequent value in the sequence. It can be positive or negative.

- If positive, subsequent values increase, creating an ascending sequence.
- If negative, subsequent values decrease, producing a descending sequence.
- If omitted, the step defaults to 1.

The SEQUENCE function is only supported in Excel for Microsoft 365, Excel 2021, and Excel for the web.

## Basic formula to create a number sequence in Excel

If you are looking to populate a column of rows with sequential numbers starting at 1, you can use the Excel SEQUENCE function in its simplest form:

To put numbers in a **column**:

*n*)

To place numbers in a **row**:

*n*)

Where *n* is the number of elements in the sequence.

For example, to populate a column with 10 incremental numbers, type the below formula in the first cell (A2 in our case) and press the Enter key:

`=SEQUENCE(10)`

The results will spill in the other rows automatically.

To make a horizontal sequence, set the *rows* argument to 1 (or omit it) and define the number of *columns*, 8 in our case:

`=SEQUENCE(1,8)`

If you'd like to fill a **range of cells** with sequential numbers, then define both the *rows* and *columns* arguments. For instance, to populate 5 rows and 3 columns, you'd use this formula:

`=SEQUENCE(5,3)`

To **start** **with a specific number**, say 100, supply that number in the 3^{rd} argument:

`=SEQUENCE(5,3,100)`

To generate a list of numbers with a **specific increment step**, define the step in the 4^{th} argument, 10 in our case:

`=SEQUENCE(5,3,100,10)`

Translated into plain English, our complete formula reads as follows:

### SEQUENCE function - things to remember

To efficiently do a sequence of numbers in Excel, please remember these 4 simple facts:

- The SEQUENCE function is only available with Microsoft 365 subscriptions and Excel 2021. In Excel 2019, Excel 2016 and earlier versions, it does not work since those versions do not support dynamic arrays.
- If the array of sequential numbers is the final result, Excel outputs all the numbers automatically in a so-called spill range. So, be sure you have enough empty cells down and to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
- The resulting array can be one-dimensional or two-dimensional, depending on how you configure the
*rows*and*columns*arguments. - Any optional argument that is not set defaults to 1.

## How to create a number sequence in Excel - formula examples

Though the basic SEQUENCE formula does not look very exciting, when combined with other functions, it takes on a whole new level of usefulness.

### Make a decreasing (descending) sequence in Excel

To generate a descending sequential series, such that each subsequent value is less than the preceding one, supply a **negative** number for the *step* argument.

For example, to create a list of numbers starting at 10 and decreasing by 1, use this formula:

`=SEQUENCE(10, 1, 10, -1)`

### Force a two-dimensional sequence to move vertically top to bottom

When populating a range of cells with sequential numbers, by default, the series always goes horizontally across the first row and then down to the next row, just like reading a book from left to right. To get it to propagate vertically, i.e. top to bottom across the first column and then right to the next column, nest SEQUENCE in the TRANSPOSE function. Please note that TRANSPOSE swaps rows and columns, so you should specify them in the reverse order:

*columns*,

*rows*, start, step))

For example, to fill 5 rows and 3 columns with sequential numbers starting at 100 and incremented by 10, the formula takes this form:

`=TRANSPOSE(SEQUENCE(3, 5, 100, 10))`

To better understand the approach, please have a look at the screenshot below. Here, we input all the parameters in separate cells (E1:E4) and create 2 sequences with the below formulas. Please pay attention *rows* and *columns* are supplied in different order!

Sequence that moves vertically top to bottom (row-wise):

`=TRANSPOSE(SEQUENCE(E2, E1, E3, E4))`

Regular sequence that moves horizontally left to right (column-wise):

`=SEQUENCE(E1, E2, E3, E4)`

### Create a sequence of Roman numbers

Need a Roman number sequence for some task, or just for fun? That's easy! Build a regular SEQUENCE formula and warp it in the ROMAN function. For example:

`=ROMAN(SEQUENCE(B1, B2, B3, B4))`

Where B1 is the number of rows, B2 is the number of columns, B3 is the start number and B4 is the step.

### Generate an increasing or decreasing sequence of random numbers

As you probably know, in new Excel there is a special function for generating random numbers, RANDARRAY, which we discussed a few articles ago. This function can do a lot of useful things, but in our case it cannot help. To generate either an ascending or descending series of random whole numbers, we'll be needing the good old RANDBETWEEN function for the *step* argument of SEQUENCE.

For example, to create a series of **increasing random numbers** that spills in as many rows and columns as specified in B1 and B2, respectively, and start at the integer in B3, the formula goes as follows:

`=SEQUENCE(B1, B2, B3, RANDBETWEEN(1, 10))`

Depending on whether you want a smaller or bigger step, supply a lower or higher number for the second argument of RANDBETWEEN.

To make a sequence of **decreasing random numbers**, the *step* should be negative, so you put the minus sign before the RANDBETWEEN function:

`=SEQUENCE(B1, B2, B3, -RANDBETWEEN(1, 10))`

Note. Because the Excel RANDBETWEEN function is **volatile**, it will generate new random values with every change in your worksheet. As the result, your sequence of random numbers will be continuously changing. To prevent this from happening, you can use Excel's *Paste Special* > *Values* feature to replace formulas with values.

## Excel SEQUENCE function missing

Like any other dynamic array function, SEQUENCE is only available in Excel for Microsoft 365 and Excel 2021 that support dynamic arrays. You won't find it in pre-dynamic Excel 2019, Excel 2016, and lower.

That's how to create sequence in Excel with formulas. I hope the examples were both useful and fun. Anyway, thank you for reading and hope to see you on our blog next week!

## Practice workbook for download

Excel SEQUENCE formula examples (.xlsx file)

## 410 comments

Hello,

I need advice on arranging around 100,000 phone numbers in sequences. For example, i have below list of numbers:

1001023

1001024

1001093

1001094

1001095

4677662

4677663

4677664

4677665

4677666

4677667

4677668

4677669

In ascending order, first 2 numbers are in a sequence of 2, next 3 numbers are in a sequence of 3, remaining numbers are in a sequence of 8. So i want to get a value of 2 against first 2 numbers, value of 3 against next 3 numbers and value of 8 against all the remaining numbers. Kindly advise.

I have a situation in Excel where there is one workbook and it has 6 worksheets in all.First one is a MasterSheet and other

5 sheets are named as S1,S2,S3,S4 and S5.Now I want to link these 5 five sheets with the Master at various cells in a column

sequentially.

Like in S1 E1 shud link with F2 cell in Master,in S2 E1 with F3,in S3 E1 with F4,in S4 E1 with F5 and in S5 E1 in F6 cell in Master.

How can it be done ?

Looking forward to hearing from you.

Hello!

If I understand your task correctly, the following tutorial should help: How to create external reference in Excel to refer to another sheet or workbook.

I would to have my out would like this. Kindly help us

Product Brand Sequence

A1 Apple 01

A1 Apple 01

A1 Mango 02

A1 Mango 02

A2 Mango 01

A2 Mango 01

A2 Mango 01

A2 Mango 01

A3 Banana 01

A4 Orange 01

A1 Mango 02

A2 Mango 01

A3 Banana 01

A4 Orange 01

Hi!

I am not sure I fully understand what you mean.

Hi,

I need to get the following formula to work and I can get it to come up except it's removing the 00 and treating the second last number as a negative and then minus the last number as well I realise this is due to the hyphen/subtraction symbol just wondering if there is a way to bypass this?

ABC-001-10412-7

ABC-002-10412-7

ABC-003-10412-7

ABC-004-10412-7

The formula I have used is - ="ABC-"&(SEQUENCE(004,,001)&-10412-7) - excel is removing the 00 after I enter of course and the result is

ABC-1-10419

ABC-2-10419

ABC-3-10419

ABC-4-10419

Assistance would be greatly appreciated.

Thanks

Hello!

When concatenating text values, use quotes. To display leading zeros in a number, apply these guidelines: Leading zeros in Excel: how to add, remove and hide.

If I got you right, the formula below will help you with your task:

="ABC-"&TEXT(SEQUENCE(4,,1),"000")&"-10412-7"

I hope my advice will help you solve your task.

Thank you so much yes it did!!

Hello, I need help with a formula for this... I'm out of ideas..

A B

2 = -2000

(2,5= -1750)

3 = -1500

4 = -1000

5 = -500

6 = 0

7 = 500

8 = 1000

9 = 1500

10 = 2000

Thank you very much for any help.

Hello!

If I understand your task correctly, try the following formula:

=-2000+(A1-2)*500

HI I need to have the data like the following -

Part1

Part1

Part1

Part1

Part2

Part2

Part2

Part2

and so on till 2010 rows and "Part1" should apply to 40 rows and it should increase the counts like Part2 part3 .... till it reaches "Part50" to fill up 2010 rows .

I believe my query is somewhat similar to your latest reply to a user's query -

=CEILING(SEQUENCE(300,1,1,1)/3,1)+44858 only it had dates repeated like -

Oct 25

Oct 25

Oct 25

Oct 26

Oct 26

Oct 26

Please let me know the quickest and most effective approach to accomplish this. Your assistance would be highly appreciated.

Hi!

Combine string and formula to get a sequence of text.

="Part"&CEILING(SEQUENCE(2010,1,1,1)/40,1)

Hi

I would like to create a series for dates from say Oct 25 - Dec 10 with 3 repetitions of each day

So I would like my series to look like this:

Oct 25

Oct 25

Oct 25

Oct 26

Oct 26

Oct 26

Oct 27

Oct 27

Oct 27

And so on till December

Could you please tell me a quick and efficient way to do this? I would greatly appreciate your help.

Hello!

Since dates in Excel are numbers, create a sequence of numbers as described in the article above.

=CEILING(SEQUENCE(300,1,1,1)/3,1)+44858

Set the date format in the cell.

Hi,

I have a data which I need to sequence/group. I actually don't know if its possible (still crossing fingers that it is possible).

For example, document number 1, 2, 3, 9, 10, 25, 26, 28, 30, 31.

I need to summarize it to 1-3, 9-10, 25-26, 28, 30-31.

Is there a way to do it?

Hi!

There is no logical pattern in your grouping.

Hello,

I need to create the following sequence

149597883002568 (360360)

149597882642208 (360360)

149597882281848 (360360)

149597881921488 (360360)

149597881561128 (360360)

149597881200768 (360360)

149597880840408 (360360)

149597880480048 (360360)

149597880122691 (357357)

149597879762331 (360360)

and so on

1 to 8 is 360360, 8 to 9 is 357357 and 9 to 10 back again to 360360

Any advice?

Hello!

To determine every eighth value, use the MOD function with a divisor of 8.

=IF(MOD(SEQUENCE(100,1,1,1),8)=0,357357,360360)

This should solve your task.

First thanks for answering but I'm sorry, I think I'm not really detail when I explaining

I need to create sequence that the result is looks like this

1. 149597883002568

2. 149597882642208

3. 149597882281848

4. 149597881921488

5. 149597881561128

6. 149597881200768

7. 149597880840408

8. 149597880480048

9. 149597880122691

10. 149597879762331

The first to eight values always increases by 360360, but from eight to nine value, it increases by 357357, nine to ten value is back again to 360360

And I need to create that sequence more than 100

Hi!

Sequentially add these numbers to the original number. If the sequence is written in B1, then the formula in C1 can be:

=$A$1+SUM($B$1:B1)

Copy the formula down the column.

How to make a sequence more than 100 - read the instructions for the SEQUENCE function.

Also note that in your example you're subtracting numbers. In addition, Excel displays such large numbers (more than 15 digits) in exponential format. To see it in full, convert the number to text using the TEXT function.

Hello,

I'm trying to figure out how to sequence

a3i5a1p

a3i5a2p...

a3i5a9p

a3i5aap

a3i5abp...

then group them in a range of 25. a3i5 and the p never change, and the other two switch between a-z and 0-9. I am working with excel 2013.

Hello!

Place 42 characters 0-9 a-z in a separate column. For example, in Z1:Z42.

To dynamically refer to these symbols, use the INDIRECT function.

="a3i5"&INDIRECT("Z"&(CEILING(ROW()/42,1)+10)) & INDIRECT("Z"&(TRUNC(MOD((ROW(A1)-1)/1,42)+1)))&"p"

For more information, please visit: Creating indirect references from cell values and text.

I hope it’ll be helpful. If something is still unclear, please feel free to ask.

If i have raws as following

1

2

3

5

6

7

10

12

I need to write " 1To 3,5 To7, 10,12"

Hello!

To combine numbers and text, use this guide: CONCATENATE in Excel: combine text strings, cells and columns.

Thanks this is exactly what I was looking for!

1

Package ID : 15543

Full Time Senior (5+) Java Web

Client: Rui Serra (Tendaji)

Nrupeshkumar Modi [Java Web]

8-Jun-21

2

Package ID : 15570

Full Time Senior (5+) Java Web

Client: Mui Mui Seng (Pan United)

Harshil Modi [Java Web]

14-Jun-21

3

Package ID : 13459

Full Time Senior (5+) Java Web

Client: Rui Serra (Tendaji)

Mayuresh Ratnaparkhi [Java Web]

Hi , I need to get it into tabular format in excel. In which i need separate column for each filed and subsequent data need to be filled automatically. I tried to put series formula or sequence but its not coming. I am using excel 2016 .

Hi!

Unfortunately, it is impossible to understand what sequence you are talking about. If you need to split values by columns, use this guide: How to split cells in Excel.

Hi, Great tips!

Trying to get his working in a table but cannot. I also cannot insert a row in the middle of the sequence.

Do you have a work around for either of these issues, please?

Thank you,

Richard.

Hi!

The information you provided is not enough to understand your case and give you any advice, sorry.

Thank you for answering my question

If we want the sequence of numbers to depend on other data, for example, in a table, every time the name of a person who works in a department of the organization is added, one unit should be added to the number.and if In the financial section I add a new person the number should start from one

Hello Genius

I want to create a decreasing series whereby the first and the last letters remain constant while the middle number is decreasing. For example

MH 42 - A1

MH 41 - A1

MH 40 - A1

MH 39- A1

This should decrease to zero in that sequence.

Thanks in advance

Hi!

To create a descending sequence, use the step=-1 argument in the SEQUENCE function.

To concatenate formula with text, use the & operator as described in this guide: CONCATENATE in Excel: combine text strings, cells and columns.

You can use this formula:

="MH "&SEQUENCE(42,,42,-1)&" - A1"

Thank you Sir.

But it's displaying a "#Name Error"

Hi!

If you have an older version of Excel, try this array formula -

="MH "&SORT(CEILING(ROW(A1:A42),1),,-1)&" - A1"

The SORT function will provide descending order of values.

Press Ctrl + Shift + Enter so that array function works.

I am using 2021 version.

Both formulas work for me.

Hello,

this is what I need help with please.

For example, If there is 4 in column X then the subsequent columns need to be filled in with 0,0,0,1,0,0. , and similarly for numbers 1 to 6. Is there a way I can fill in all columns in front of column X that have the 4's in the data set in the same way at once rather than one at a time?

Hello!

You can use the CHOOSE function to select one of the 6 options.

=IFERROR(CHOOSE(A1,{1,0,0,0,0,0}, {0,1,0,0,0,0},{0,0,1,0,0,0}, {0,0,0,1,0,0},{0,0,0,0,1,0}, {0,0,0,0,0,1}),"")

I hope my advice will help you solve your task.

Please tell me how will I get number periodicity for example I have 1 to 90 numbers in column "A" I need output in "B" as (0to10), (11 to 20), (21 to 30) so on...

Hi!

I’m sorry but your description doesn’t give me a complete understanding of your task. "(0to10)" - this is text. Please describe your problem in more detail.

Hi,

I am looking to insert validation in Excel to enter number in the format that should range from 0000-000-00 to 9999-999-99, and no other format is acceptable.

Hello!

We have a special tutorial on this. Please see: Excel Data Validation using regular expressions.

I hope my advice will help you solve your task.

Hello,

I want to fill in an amount in a cell, i.e: "5" in cell A3

With following command: ="Level " & sequence(A3) i want the following displayed

Level 1

Level 2

Level 3

Level 4

Level 5

But I only get displayed: Level 1

What am I doing wrong ?

Hi!

Carefully read the recommendations and examples in the article above.

Please use the formula below:

="Level "&SEQUENCE(A3,1,1,1)

Hello Alexander,

I copied your formula, but still not working.

TOm

Hi!

I'm sorry, but the formula works. Cell A3 should contain the number 5

Alexander,

Even when I change A3 direct to a number, i.e 10 , I get the message 'parseer fout ( =parse error)

I don't get it.

Tom

Hi!

If you wrote that you use Google Sheets, you would save a lot of your time and mine.

=ARRAYFORMULA("Level "&SEQUENCE(A3,1,1,1))

Unfortunatly not, even when I change A3 to a number, i.e 10 it I get the message '"parseer fout" (= parse error)

I don't get it,

Tom

Hello team!

I need a solution to increment alphanumeric numbers by 1 that have a string as the prefix, and the suffix as a sequential number. I need the increments to restart from 0 if the suffix string is different.

For example, in column A, I have sets of different strings, e.g. “ATD” in A2:A7, “FMR” in A8:A11, “OWG” in A12:A15, “ATD” again from A16:A19, etc.

In column B, I would like a sequence of numbers which includes the string from column A as the prefix and the suffix “-R00”, incremented by one for each instance of that particular string.

The caveat is that if the string already exists, e.g. “ATD”, I would need the numbering to continue from the last previous instance in column B.

So in column B, from cell B2, I would have the following:

2 ATD-R00

3 ATD-R01

4 ATD-R02

5 ATD-R03

6 ATD-R04

7 ATD-R05

8 FMR-R00

9 FMR-R01

10 FMR-R02

11 FMR-R03

12 OWG-R00

13 OWG-R01

14 OWG-R02

15 OWG-R03

16 ATD-R06

17 ATD-R07

18 ATD-R08

19 ATD-R09

I have written the current formula =IF($A2=$A1,CONCATENATE($A2,"-R"&TEXT(ROWS($1:1)-1,"00")),CONCATENATE(A2,"-R00")) in column B, and although this restarts the numbering to R00 for a different string, the next number suffix is not 001 as I require. Could you advise what the best formula is for this?

Thank you!

Hello!

Use COUNTIF function to count the number of prefixes up to the current row.

To display a number with two digits, use the TEXT function.

=A1&"-R"&TEXT(COUNTIF($A$1:A1,A1)-1,"00")

You can copy this formula down along the column.

Absolutely perfect, thank you for your speedy response, Alexander!

Genius, thank you!! I wanted to number through starting at 1, for each category in a different column and this works a treat with amendment to my purpose :)

=TEXT(COUNTIF($A$1:A1,A1),"00")

Hi,

Thanks a lot for this.

I would like to go in the sequence 001,002,003 etc down a column

using the sequence function I am getting 001,011,021,031 etc.

Would appreciate it if you could let me know how to correct this.

Thanks.

Hello!

To create a sequence, use the formulas recommended above.

=TEXT(SEQUENCE(100,,1,1),"000")

To show leading zeros, use these guidelines: How to add leading zeros in Excel.

Hi!

I would like to make a pattern going down a column:

1

4

2

4

2

4

2

4

1

The first and last value must always be 1, and in-between needs to be a pattern of 4,2,4,2,4... The pattern needs to be autofilled in when I add a value in the column next to it on the same row (it needs to be applied to blank cells so if I add or delete values the pattern remains with 1 at the start and end). I can't figure out what to use so that the first and value stay at 1. Much appreciated if possible to help!

Hi!

I do not think that the sequence of your numbers can be described mathematically.

Hello,

I have a very large table consisting of 5-digit ID codes (approximately 8,000 rows). For each ID code, I need to concatenate or append a separate table consisting of 4-digit product codes (3,000 rows). The final list would be in the neighbourhood of 24m rows (8k x 3k). Is this something I can do in Excel or perhaps Power Query and how?

tbl_IDCode tbl_ProdCode

20456 1111

20457 2333

20468 2654

20471

End Result

204561111

204562333

204562654

204571111

204572333

204572654

204681111

204682333

204682654

204711111

204712333

204712654

Thank you,

Dan

Hello!

With this formula, you can form an array of values.

=TRANSPOSE(INDEX(A1:A4;SEQUENCE(1;4;1;1))&B1:B3)

Then replace formulas with values using Paste Special.

Then use the Create Cards tool from the Ultimate Suite for Excel.

You can install Ultimate Suite in a trial mode and check how it works for free.

I hope it’ll be helpful. If something is still unclear, please feel free to ask.

Hi I have a question where I need to create a column of index numbers

beginning at 20 and incrementing by 1 so that the last number in the column is 44 how do I cap it at 44.

Hi!

Just specify in the formula that you need 25 numbers:

=SEQUENCE(25,1,20,1)

hi

is it possible to sequence

2222/22

2223/22

2224/22 etc

thanks

Hi!

Use the & operator to concatenate a number sequence with text. There are a lot of similar questions in the comments below.

=SEQUENCE(100,,2222,1)&"/22"

001/22-23

002/22-23

HOW TO CONTINUE THIS SEQUENCE ?

Hi!

Please read the comments below your question. There is a solution to your problem.

Hi Alexander, I am hoping you can help with a formula I have been struggling to find. I need to list all values between two original values. For example, I have the following 2 values: ABC2100701 & ABC2101800. I need a formula to list the entire range between these 2. Is that possible at all?

Hello!

I believe the following formula will help you solve your task:

="ABC210"&TEXT(SEQUENCE(1100,1,701,1),"0000")

You can learn more about TEXT function in Excel in this article on our blog.

Hello,

I want to type something in sequence, like below.

A0101

A0104

A0201

A0204

A0301

A0304

etc.

Up to

A7001

A7004

Is there a formula doing that typing quickly?

Hello!

The formula below will do the trick for you:

="A"&TEXT(CEILING(ROW(A1)/2,1),"00") &"0"& (TRUNC(MOD((ROW(L1)-1)/1,2)+1))^2

Copy this formula down along the column.

Hi - I have a slightly random one. For livestock animals such as cattle, they have passport numbers that go up in a specific sequence, for example:

UK121629 101653

UK121629 201654

UK121629 301655

UK121629 401656

UK121629 501657

UK121629 601658

UK121629 701659

UK121629 101660

UK121629 201661

UK121629 301662

UK121629 401663

UK121629 501664

UK121629 601665

UK121629 701666

There's UK for the country, then the herd number of the farmer (which usually stays the same) and then the last 6 digits are the individual animal's tag number. This goes up by one each time for the last 4 digits, but the first two numbers go up by 10 each time, until they reach 70, then it goes back to 10,20,30 and so on.

Is there any way I can make this into a sequence?

Thank you!

Hello!

If I got you right, the formula below will help you with your task:

="UK121629 "&TRUNC(MOD((ROW(A1)-1)/1,7)+1)&"0"&ROW(A1653)

Copy this formula down along the column.

That's great thank you - So next question... If I have a list of unordered passport numbers, is there a way I can order them according to this sequence?

Hi!

Sorry, I do not fully understand the task. Perhaps this article will be useful: Excel SORT function - auto sort data using formula.

Hi, what is the formula for this kind of sequence?

20220327.01.01

20220327.02.01

20220327.03.01

20220327.04.01

20220327.05.01

I already tried the formula you shared in this article, but I couldn't figure it out. Any help would be appreciated. Thank you!

Hello!

Please read the two comments just below your question. There is a solution to your problem.

Hello,

I need to create the following sequence

5-00001-01

5-00002-01

5-00003-01

5-00004-01

etc

Any advice?

Hello!

Use the TEXT function to apply a custom number format. All the information you need about the SEQUENCE function is in the article above.

="5-"&TEXT(SEQUENCE(100,1,1,1),"00000")&"-01"

There are many examples in the comments.

I am looking to create the ongoing sequence below and could use help with the formula. As I have to go very high with the numbers, I really don't want to be entering them all manually. Thanks!

2022.1.1

2022.2.1

2022.3.1

2022.4.1

2022.5.1

2022.6.1

2022.7.1

Hello!

To concatenate text and a number, use the & operator.

="2022."&SEQUENCE(100,1,1,1)&".1"

Hi, i want a column A to generate a sequential number of p/o's to an unlimited numer of rows. can you post an expample?

Hello

I hope you can help me out.

I am new to excel and am probably over my head.

I have a sheet with 8 columns in this order (last name, first name, hire date, shift, position, hours, switch and rank) I have it set up so that the data auto sorts by position first, then shift, hours, hire date and last name and when a new person is added to a master sheet they are automatically added to this sheet and placed according to the sort listed above. What i am desperately needing help on is how to auto number my rank column so that they are ranked dynamically as per multiple criteria of (shift, position, hours, hire date and alphabetically by last name. There are 4 different positions, 4 different shifts. I would like the numbering to be between shift and position and then hours but If people are tied in hours then number by hire date but if tied with that as well then number by first letter of last name alphabetically. If this is too much to ask I understand and we can get by with numbering by hand after printing. here is a mock up of what I am trying to accomplish. Ps. switch column is nothing important

last first hire shift position hours switch rank

smith bill 2-13-21 A hauler 19 1

davis fred 2-22-21 A hauler 22 2

hill mike 2-22-21 A hauler 22 3

kreg mary 2-12-19 A lifter 23 1

hall kate 5-30-18 A lifter 25 2

gore ned 3-09-19 A sorter 14 1

toews greg 3-12-17 A sorter 28 2

lao ning 4-12-20 A tech 22 1

hicks june 5-23-20 A tech 22 2

hash brian 7-04-19 B hauler 17 1

bush kim 4-09-22 B hauler 22 2

and continued on with other classes in shift b, c and d.

As you can see by the chart when fred davis and mike hill tie for hours and hire date they are then ranked by first initial of last name and when ning lao and june hicks tie for hours the ranking is next based on hire date and doesnt have to go to last name. everyone else is ranked by low hours first in their own shift and position. Hopefully I have made this readable. I did discover this formula but it only works with ranking shift, hours and position. =COUNTIFS($G$13:$G$150,"<"&G13,$F$13:$F$150,F13)+1

(g is my hours column, f is my position column and e is my shift column)

Thanks again for any help you have on this.

Sincerely, Gordon Stuart

Hello!

In Excel, you can determine the rank using the RANK function. However, it only works with numbers.

So could I somehow join the rank function with the formula I have above?

Hi!

If your table uses automatic sorting by columns D, E, F, G, then the rank will be equal to the serial number in the table. H13+1 and so on.

Hello,

I want to to do grouping of numbers based on sequence, e.g,

567, 568, 569, 570, 571 - sequence 5

230,231,232,233,234,235 - sequence 6

723, 724, 725, 726 - sequence 4

123,124,125,126 - sequence 4

Sequence should be calculated on the basis of count

Hello!

I recommend reading this guide: How to count cells with text in Excel.

If this is not what you wanted, please describe the problem in more detail.

1 9 17 25

2 10 18 26

3 11 19 27

4 12 20 28

5 13 21 29

6 14 22 30

7 15 23 31

8 16 24 32

33 41 49 57

34 42 50 58

35 43 51 59

36 44 52 60

37 45 53 61

38 46 54 62

39 47 55 63

40 48 56 64

-- how to create this sequence.. please help me ?

Hello!

Write in the first row 4 numbers - 1 9 17 25.

In cell A2 write the formula =A1+1

Copy this formula 3 columns to the right and 6 rows down.

In cell A9, write the formula =A1+32

Copy this formula 3 columns to the right and down as needed.

Here is the article that may be helpful to you: How to use AutoFill in Excel - all fill handle options.

I am trying to create a spreadsheet to help me keep track of our pay periods and check date.

Imagine this is a spread sheet..lol:

Header: Week Period Begins Period Ends Check Date

1 12/19/21 01/01/21 01/08/21

2 01/02/21 01/15/21 01/22/21

3 01/16/21 01/29/21 02/05/21

4 01/30/21 02/12/21 02/19/21

I made this with my phone side ways.

I am a nube when it comes to formulas, but I try.

Can you help me? Ty!!

Todd Hurley

Hello!

What result do you want to get exactly?

A simple spreadsheet to keep track of bi-weekly pay periods every fiscal year. So at the beginning of every new year I enter the starting dates for period begins, period ends, and Check Date. The cells below each start date automatically configured with a formula for the remaining pay periods for the remaining year. If that makes sense.

Also each pay period header increase by 2 weeks/14 days.

Hi!

If I understand you correctly, write down the date 19.12.21 in cell B1. Try to enter the following formula in cell B2 and then copy it down along the column:

=B1+14

Do the same with columns C and D.

It worked!

Thank you very much for your help!! Much appreciated.

Hi!?

I created a Payroll Spread calculation hours per day x pay per hour to get total gross weekly pay.

I did:

Row M is the daily total gross pay:

M3 = $139.88 + M4= $133.13

=SUM(M3:M4) which should equal

$273.01

How ever the cell shows: $273.00

I made certain to format the cell to currency with 2 decimal places but the .01 is missing. I am a newbie to excel or OpenOffice.

What am I missing? Thank you for your time.?

Todd

Hello!

If your numbers are derived from calculations, then in cells M3 and M4 they are displayed with rounding to two decimal places. However, the number is not rounded. For example, 139.884356 is displayed as 139.88

When adding such numbers, the result is different from what you see in the cells.

To avoid this, use round numbers to 2 decimal places in your formulas.

Hi there

Is there a way to combine SEQUENCE and MAX functions? I would like to use SEQUENCE to have a series of descending numbers but don't want the negative numbers in the sequence (i.e. once the descending SEQUENCE goes below zero) as it affects my other SUM functions. I tried combining SEQUENCE and MAX functions in an attempt to remedy this but wasn't having much luck combining the two!

I am trying to examine the effect of different reduction rates vs time, attempt was:

=MAX(0,(SEQUENCE(24,1,B1-C1))

Where, B1 = Starting Number C1= Reduction Rate

Thanks in advance

Hello!

Write an example of a sequence of values that you want to get.

e.g.

Starting Point = x = 20

Time = 24 months

Reduction Rate = (x-y), where y ranges between y = 2.8 per month

Would like SEQUENCE to decrease from 20 until it reaches zero and then stop, not continue until the sequence covers the full 24 months.

Hello!

If I understand your task correctly, you can create a sequence using the IF function.

A1 - 20

A2 - =IF(A1-2.8>0,A1-2.8,0)

After that you can copy this formula down along the column.

Hi there,

I am wondering if there is a way to create a code based on an order number and quantity

Order # Quantity

324954 1

434928 3

224860 4

724782 2

Would like the codes to look the same as below:

324954 - 01

434928 - 01 | 434928 - 02 | 434928 - 03 |

224860 - 01 | 224860 - 02 | 224860 - 03 | 224860 - 04 |

724782 - 01 | 724782 - 02 |

Not sure if this is possible.

A pot can hold 1000 coins. I would like to display this as a primary key. For example,

P1C1

P1C2

P1C3

...

P1C999

P1C1000

P2C1

P2C2

...

P2C998

P2C999

P2C1000

P3C1

P3C2

P3C4

The list should have no bottom limit, allowing as many entries as possible.

What is the best way to do this?

Any help would be greatly appreciated!

Hi!

If I got you right, the formula below will help you with your task:

="P"&CEILING(SEQUENCE(4500,1,1,1)/1000,1)& "C" &TRUNC(MOD((SEQUENCE(4500,1,1,1)-1)/1,1000)+1)

what would be the best formula to get gaps on this sequence

220-0007

220-0009

221-0001

223-0001

223-0002

223-0004

to return the exact missing numbers

i.e 220-0008 & 223-0003 are missing.

what would be the best formula to get gaps on this sequence

220-0007

220-0009

221-0001

223-0001

223-0002

223-0004

to return the exact missing numbers

i.e 220-0008 & 223-0003 are missing.

Hello!

Write in another table the sequence that you think is correct. Then compare these columns using the Compare Tables tool.

There is no pattern in your data, so I cannot suggest a formula.

Hello Sir,

Thanks for your help in advance. Every time a record is added to excel I have to generate a sequence number automatically. Sequence=Stage.Phase.Next Number - Can you please help how to do this in excel.

Stage Phase Sequence

1 1 1.1.1

1 1 1.1.2

1 2 1.2.1

2 2 2.2.1

2 3 2.3.1

2 3 2.3.2

Hi!

I don't see any pattern in your data. If it exists, please describe it.

Pattern is Stage.Phase.Next Incremental number in that Stage.Phase

Stage.......Phase.......Sequence

1000...........1..............1000.1.1

1000...........1..............1000.1.2

1000...........1..............1000.1.3

2000...........2..............2000.2.1

2000...........2..............2000.2.2

1000...........2..............1000.2.1

1000...........2..............1000.2.2

Hope it makes sense...thank you!

I did it successfully using COUNTIF. Thank you for your time!

Correction to previous post -

The format for both methods in my first post got off-alignment when I submitted.

Here is my attempt to correct the formatting of the 2 methods so that it's readable (I hope it doesn't get messed up again).

Method 1

========

Tab Cell E1

==== ========

01 01-Jan-2022

02 =('01'!E1+1)

03 =('02'!E1+1)

04 =('03'!E1+1)

05 =('04'!E1+1)

Method 2

========

Tab Cell E1

==== ========

01 01-Jan-2022

02 =('01'!E1+1)

03 =('01'!E1+2)

04 =('01'!E1+3)

05 =('01'!E1+4)

I was searching Google for a better way to do the following and your website popped up. I hope you can help and that it’s not something that can’t be done.

I have a workbook for each month with either 28,30 or 31 tabs/sheets – one for each day depending on the month.

I have a date field in the same cell (E1) for each tab - date format is dd-mmm-yyyy.

I would like the same formula for cell E1 in each tab that increments the date without me having to edit the formula in cell E1 in each tab to change the tab name (Method 1) or increment number (Method 2) to point to the previous tab and add the increment number.

I have the following 2 formulae that works but I have to change each tab (from tab ‘02’ onwards) to either point to the previous tab name and add 1 or add an increasing increment number to tab name ‘01’. Is there a way to specify the previous tab without having to actually name it – ie. is there a variable name that represents the previous tab ? Here is what currently works, but to me is a bit clumsy.

Thanks, in advance, for any suggestions.

Method 1

Tab Name Cell E1

======== ==========

01 01-Jan-2022

02 =('01'!E1+1)

03 =('02'!E1+1)

04 =('03'!E1+1)

05 =('04'!E1+1)

etc etc

Method 2 - it also works if I do this :

Tab Name Cell E1

======== ===========

01 01-Jan-2022

02 =('01'!E1+1)

03 =('01'!E1+2)

04 =('01'!E1+3)

05 =('01'!E1+4)

etc etc

Hello!

You can only get the name of the previous worksheet using a VBA macro. Regular Excel formulas can only get the name of the sheet where the cell is located:

=MID(CELL("filename",Sheet1!A2),SEARCH("]",CELL("filename",Sheet1!A2))+1,31)

Method 1

Tab Name . . . . Cell E1

======== . . . . ===========

01 . . . . . . . . 01-Jan-2022

02 . . . . . . . . =('01'!E1+1)

03 . . . . . . . . =('02'!E1+1)

04 . . . . . . . . =('03'!E1+1)

05 . . . . . . . . =('04'!E1+1)

etc . . . . . . . . etc

Method 2 - it also works if I do this :

Tab Name . . . . Cell E1

======== . . . . ===========

01 . . . . . . . . 01-Jan-2022

02 . . . . . . . . =('01'!E1+1)

03 . . . . . . . . =('01'!E1+2)

04 . . . . . . . . =('01'!E1+3)

05 . . . . . . . . =('01'!E1+4)

etc . . . . . . . . etc

Hi, is it possible to arrange the sequence formula to add number until the end of the table and automatically add a number if the table row range is extended?

Hello!

In the first argument of the SEQUENCE function, you can use the COUNTA function, which will count the number of rows with data in the table. When adding. data in column A, the SEQUENCE function will add one more value.

=SEQUENCE(COUNTA(A:A),1,1,1)

I hope my advice will help you solve your task.

I am attempting to create a series of mobile numbers. The first four digits and final two digits should not change.

Example:

567-000-042

567-000-142

567-000-242

567-000-343

The series should create a list of all possible combinations from 567-000-042 to 567-999-942 (only the 4th-7th digits will change. The first 3 digits and final two digits should not change.) Thank you.

Hello!

Use the TEXT function to write the number in the desired format.

="567-"&TEXT(CEILING(SEQUENCE(1000,1,0,1),1),"000-0")&"42"

I hope my advice will help you solve your task.

Hi there,

I need to create a sequential list of barcode numbes - 20 digits, the first two digits being "00"

What do i need to do to the formula to make that come out, without getting rid of the two "00" at the front of the number

Hello!

To have a number start with "00", save number as text. You can use the & operator to do this:

"00" & 123456

I hope it’ll be helpful.

HELLO SIR

EMP ID Generate :- NAME WITH NUMBER

PRAKASH P0001

PRATIK P0002

POOJA P0003

I don't know how I can.

Hi!

Pay attention to this comment.

I hope it’ll be helpful.

How do I write a formula to have 0 to 9 followed by A to Z in a sequence and then to be copied to let's say copied to 180 rows?

0

1

2

3

4

5

6

7

8

9

0

A

B

C

etc..

I want to create following sequence

01-01-21

01-01-21

01-01-21

02-01-21

02-01-21

02-01-21

03-01-21

03-01-21

01-01-21

and so on